Outcome
Estimated turnaround improved from roughly 4-10 hours to under 1 hour.
Case study
Building an AI-Powered Text-to-SQL System to Scale Data Access
Structured metadata, AI SQL agents, and Athena-based validation turned repeated ad-hoc analytics into a reusable Text-to-SQL workflow.
PublishedMar 23, 2026UpdatedSep 25, 2026
Overview
Text-to-SQL only became useful after the data model was made explicit.
Context
Ad-hoc analytics requests were consuming too much analyst time. In practice, the slow part was not SQL syntax. It was finding the right tables, understanding what columns meant, mapping business logic correctly, and figuring out join patterns that could actually be trusted.
That made even routine questions expensive. From operating recollection, complex requests could take roughly 4 to 10 hours, which limited how many requests the team could support each month.
Approach
I built a structured metadata layer in YAML that captured how analysts actually reason about the data model: table definitions, column semantics, keys, partitioning, join patterns, and business terminology.
The workflow then used AI coding agents to generate SQL, execute it in Athena, and validate results before anything was handed back. The leverage came from structuring context well enough that the system could reuse it repeatedly.
Workflow
A practical validation loop around AI-generated SQL
The workflow narrowed context first, then generated, executed, and checked SQL before any result was handed back to a human.
Scoped context
Tables were grouped by domain and narrowed through index files so the agent only saw the relevant slice of metadata.
SQL generation
AI coding agents used the structured metadata to generate and revise SQL based on the actual business question.
Execution and QA
The workflow ran queries in Athena, checked execution success, and validated results before handing anything back to a human.
Reusable knowledge
The resulting structure could be updated and reused so later requests started from a better base instead of from scratch.
System flow
From question to validated answer
The system did not stop at generation. It executed, checked, and iterated until the result was reliable enough for review.
Results
Structured context improved speed and capacity.
Before
~4 to 10 hrsEstimated ad-hoc turnaround before the system.
After
Under 1 hrEstimated turnaround after the workflow changed.
Capacity
~20 to 50+Estimated monthly ad-hoc request capacity.
These are retrospective operating estimates, not a measured benchmark. The measurement period, sample, and request-level records are unavailable.
Takeaways
The real system was the knowledge architecture.
What mattered
The core problem was not generating SQL text. It was capturing how analysts interpret the data model and business logic in a form AI could actually reuse.
Why it worked
By structuring table semantics, joins, and business terms well enough, the workflow reduced repeated research work instead of only accelerating typing.
Related
Continue with connected pages
Case studies and methods that connect to the same operational questions.
Case study
Graph-Based Warehouse Optimization and Simulation
Built a warehouse optimization workflow that combined graph construction and simulation to test inventory placement and picker travel distance before a full optimizer existed.
Method
Seasonality Index for Inventory Planning
An inventory planning method that estimates SKU-level seasonality, groups products into seasonal buckets, and connects those signals back to category planning.