How we use metadata and generative AI to rethink how we migrate complex SQL logic into a modern data platform.
💡 Summary
==Most large organizations run on decades of SQL logic — thousands of queries that transform source data into business models.
When these legacy platforms are replaced, the biggest challenge isn’t the data itself — it’s the embedded== ==logic====.==In this series, I share how I built an AI-assisted migration framework that parses and restructures SQL into modular, metadata-driven building blocks.
The result: a transparent, governed, and reusable architecture that turns technical SQL into explainable logic.
Press enter or click to view image in full size
The Problem Hidden in SQL
==Every enterprise data platform eventually reaches a point where the SQL becomes unmanageable.==
At first, the code looks harmless — a few joins, some filters, maybe a calculation or two. But over years, teams add hundreds of Common Table Expressions (CTEs), snapshots, DISTINCTs, and workarounds.
What started as “temporary logic” becomes business critical.
When it’s time to move this logic to a new platform — say, Databricks or Snowflake — we realize we’re not just migrating SQL.
We’re migrating the company’s entire data transformation logic, often undocumented and inconsistent.
Why Manual Migration Fails
Traditional migration projects rely on developers rewriting queries by hand. They try to reproduce the same logic in a new schema or layer, usually under pressure and without full lineage.
The result:
- Inconsistent naming and semantics
- Hidden data-quality patches mixed with integration logic
- Repeated derivations across hundreds of queries
- Fragile validation processes that rely on row counts rather than intent
The deeper issue is that SQL itself is not the problem — it’s how we use it.
SQL was never designed to capture semantics, intent, or metadata.
A Metadata-Driven Mindset
To solve this, we began by treating SQL as a source of metadata, not just code.
Using Python and SQLGlot, we parsed each query into structured artifacts:
- Column lineage, joins, and transformations
- CTE hierarchies
- Functions, keywords, and dependencies
- Input/output tables and datatypes
This metadata is stored in DuckDB and organized per layer, schema, and target table. From there, it becomes the foundation for analysis, rule-based transformation, and ultimately AI-assisted rewriting.
Why AI Belongs in the Process
Large Language Models (LLMs) like ChatGPT aren’t magic — but they’re exceptional at pattern interpretation and rule translation.
Once we had structured metadata, we could generate AI prompts that describe:
- The SQL structure and intent
- The relevant rules to apply (e.g. SCD2 filtering, deduplication, naming)
- The target schema and conventions of the new platform
AI could then assist in rewriting queries step by step, while Python validated the output against the extracted metadata.
In essence, AI became an intelligent “migration assistant” working under strict governance.
From Chaos to Structure
The key insight:
You can’t migrate SQL efficiently until you extract its logic into metadata.
Once the logic is decomposed into YAML-based building blocks, AI can:
- Apply rules deterministically
- Split large SQLs into modular, testable parts
- Suggest consistent naming and SCD2 filtering
- Generate documentation automatically
The end result is not just “migrated SQL” — it’s a logical model that’s explainable, validated, and future-proof.
Why It Matters
In financial institutions, regulatory reporting and risk analytics depend on traceable data transformations. By combining metadata extraction, rule-based AI guidance, and automated validation, we gain:
- Transparency: Each transformation is documented and linked to rules.
- Consistency: The same logic is applied across hundreds of queries.
- Agility: Business modelers can understand and adjust mappings without diving into raw SQL.
- Governance: Every change becomes explainable — a key requirement for regulated domains.
Key Takeaways
- Legacy SQL hides enormous business logic — extracting it as metadata is the first step.
- AI works best when guided by deterministic rules and structured context.
- A metadata-driven approach bridges the gap between business modeling and technical migration.
- The goal isn’t just to move SQL — it’s to rebuild understanding.
➡️ Next in the series:
“Extracting the Hidden Metadata Inside SQL” — how Python and SQLGlot can turn thousands of lines of SQL into structured metadata and reusable logic.
🧭 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.