Extracting the Hidden Metadata Inside SQL

How we turned hundreds of legacy SQL queries into structured metadata that AI — and humans — can reason about.

💡 Summary

==Before AI can help us migrate SQL, it needs structure. That structure lives inside the queries themselves — but hidden among joins, aliases, and filters.==

In this part of the series, we describe how we used Python and SQLGlot to parse hundreds of legacy SQL statements into machine-readable metadata.
This metadata became the foundation for everything that followed: rule-based transformation, validation, and eventually AI-assisted rewriting.

Press enter or click to view image in full size

Why We Started with Metadata, Not AI

==When we first looked at the SQL from the old platform, we saw what every migration team eventually faces: thousands of lines of logic, written by different people, for different purposes, over many years.==

Some queries had twenty CTEs. Some used DISTINCT as a data-quality patch. Others mixed snapshot logic, timeline reconstruction, and aggregations — all in one statement.

We realized that jumping straight into rewriting would be chaos.
Before we could migrate, we needed to understand what was actually happening inside those queries.

So instead of rewriting SQL, we built a parser to extract its DNA.

Turning SQL into a Metadata Graph

We used SQLGlot, a Python SQL parser that can handle different dialects and return a clean syntax tree.
Around it, we built a Python framework that analyzed every query and stored its structure in a DuckDB metadata database.

Each parsed query produced a folder structure like this:

/metadata/
/business/
/customer/
/cte_filter/
column_mapping.csv
join.csv
relationship.csv
table.csv
/cte_aggregate/
column_mapping.csv
…

We didn’t just save the SQL — we saved everything we could extract:

CTE hierarchy and dependency graph
Column lineage and transformation expressions
Joins and join conditions
Datatypes, casts, and default values
Subqueries, filters, and WHERE IN clauses
Keywords (UNION, DISTINCT, GROUP BY, CASE, etc.)
Literal values and potential DQ indicators
Aliases mapped back to source tables

By the time the script finished running, we had a complete metadata representation of every SQL query.

From Text to Structure

Each metadata file represented a specific perspective on the same query.

column.csv → every column, its expression, input count, datatype, default value
column_mapping.csv → lineage between source and target fields
join.csv → join type, condition columns, and null-safety logic
keyword.csv → inventory of SQL constructs used
table.csv → table names, schemas, CTE nesting depth

All of these CSVs were automatically loaded into DuckDB so we could query them like any other dataset. For the first time, we could ask questions about the SQL instead of reading it line by line.

“How many queries use DISTINCT?”
“Which CTEs are never referenced?”
“Where do we still rely on snapshot_date?”

This changed everything.
The migration stopped being a guessing game and became an engineering problem — measurable, traceable, and automatable.

Building the Foundation for AI

Once the metadata existed, AI finally had something to work with.

We could generate precise prompts such as:

“Rewrite this SQL using SCD2 filtering and standardized naming rules.
Input columns and joins are described below in metadata.”

That prompt would include only the relevant rules — no noise, no 2,000-line context dump.
Because the metadata already captured structure, the AI could focus on logic transformation instead of text parsing.

I’ve learned that AI performs best when the problem is deterministic and the context is structured. By investing in metadata extraction first, we set the stage for consistent, explainable automation later.

Why This Step Was Essential

Looking back, this step was the quiet turning point of the project.
It’s not glamorous — no fancy dashboards or machine learning — just careful parsing, logging, and structure.

But it gave us three superpowers:

Traceability — every output column can be traced back to its inputs.
Reusability — metadata can feed validation scripts, rule generators, and documentation.
Governance — once logic is explicit, it can be reviewed and standardized.

When people talk about “AI in data engineering,” they often start with the model. We started with the metadata — because without it, AI has nothing solid to stand on.

Key Takeaways

You can’t migrate what you don’t understand — and metadata is understanding made explicit.
SQL parsing with Python and SQLGlot turns unstructured logic into structured assets.
Storing metadata in DuckDB enables scalable analysis, validation, and automation.
The richer the metadata, the smarter (and safer) AI guidance becomes.

➡️ Next in the series:
“From Raw SQL to Logical Building Blocks” — how we designed YAML-based rule sets that translate raw SQL logic into reusable, modular transformation patterns.

🧭 About This Series

This article is part of the series “AI-Assisted SQL Migration” — an exploration of how we can combine metadata, Python, and Generative AI to transform the way we design, migrate, and govern data platforms.

Series Overview:
1️⃣ Why AI-Assisted SQL Migration Matters
2️⃣ Extracting the Hidden Metadata Inside SQL
3️⃣ From Raw SQL to Logical Building Blocks
4️⃣ Teaching AI to Apply Rules: Prompts, Validation, and Feedback Loops
5️⃣ Governance by Design: Making SQL Transparent, Modular, and Auditable
6️⃣ The Power of YAML Rules: Deduplication, Unions, and Optimization Patterns
7️⃣ The Python Rule Engine: Automating Transformation, Validation, and Documentation
8️⃣ Beyond Migration: Copilot-Assisted Data Modeling in VS Code
9️⃣ AI and Metadata-Driven Collaboration: The Future of Data Development

💬 Related Essay:
Do We Still Need Traditional Data Modeling Tools? — A reflection on whether AI-assisted, VS Code-based modeling makes legacy tools like PowerDesigner or ERwin obsolete.

🤝 Join the Conversation

I’m exploring how AI, metadata, and model-driven thinking can make data platforms more transparent, explainable, and collaborative.
If you’ve experimented with similar ideas — or have thoughts on applying AI to data modeling, migration, or governance — I’d love to hear from you in the comments or connect on LinkedIn.