How rules and metadata turned SQL migration into a governed, explainable process.
💡 Summary
As our AI-assisted SQL migration matured, something unexpected happened: it started to behave like a governance system.
By combining metadata, YAML rules, and validation feedback, we didn’t just automate SQL rewriting — we made every decision explainable, every transformation traceable, and every piece of logic reusable.
This is the moment where technical automation became data governance in action.
Press enter or click to view image in full size
From Code to Governed Logic
When we began, our goal was to migrate hundreds of legacy SQL queries from an old data platform into a new, standardized environment.
At first, this sounded like a technical problem — converting dialects, aligning schemas, fixing syntax.
But as soon as we decomposed SQL into metadata and applied rules, we realized we were doing something much more powerful.
We weren’t just rewriting queries —
we were codifying principles.
Rules like:
- “Never use DISTINCT for deduplication.”
- “Apply SCD2 filtering in the first CTE.”
- “Never mix DQ fixes in integration queries.”
- “Use LEFT JOIN with WHERE NULL for exclusion.”
These weren’t just code conventions. They were governance policies, implemented directly in the transformation logic.
Governance as Code
We started calling this approach Governance as Code — a concept that extends Infrastructure as Code into the semantic layer.
Each YAML rule defines:
- What the logic should do (and not do)
- Why it exists (rationale)
- When it applies (conditional keywords)
- How to validate it (checklist and invariant tests)
Once these rules were applied by AI and validated in Python, we had a full audit trail:
- Which rules were triggered for each SQL query
- What transformations were made
- Whether validation passed for each rule
- How the output differed from the source
That means governance wasn’t a separate layer anymore — it was baked into the process.
Data Quality as a First-Class Citizen
One of the biggest advantages of this method was how naturally Data Quality (DQ) became part of the flow.
For example:
- If a query relied on DISTINCT to hide duplicates, we flagged it.
- If a join condition used literals or untyped casts, we logged a warning.
- If a DQ fix was applied in a business integration step, we separated it into its own _dq_fix__ CTE and added metadata like the DQ incident ID and owner.
Each correction was handled with the same rigor as functional transformations. Even temporary fixes carried traceability: why it exists, who owns it, when it should be reviewed.
This approach closed the gap between data engineering and data stewardship.
Transparency in Every Step
Transparency wasn’t just a byproduct — we designed for it.
Every SQL file generated by the pipeline included a comment header like this:
/*
SQL Migration Comment Header
Purpose : Migrate and optimize legacy query for new data platform
Input : curation.customer_tx
Output : business.customer_summary
Functional PIT : {functional_pit}
Rules Applied : [FLT_SCD2_FILTERING, DEDUP_LEFT_JOIN_NULL, JN_PREPROCESSED_JOINS]
Validation : Passed (all invariants satisfied)
*/
Anyone reviewing the SQL could instantly see:
- What logic was applied
- Why certain patterns were chosen
- Whether validation succeeded
This made code reviews faster and governance reviews unnecessary — because governance was already in the code.
How It Scales
As we migrated more queries, we realized the framework could scale in two directions:
1️⃣ Horizontally — across more SQL logic
New rule sets can be added easily (e.g., for PIVOTs, case handling, or time-window joins). AI automatically incorporates them based on keywords.
2️⃣ Vertically — across the data platform lifecycle
The same rules can apply not only during migration but also during new development:
- Model validation in dbt or Databricks
- Continuous linting in CI/CD
- Automated documentation in Confluence
Governance by design isn’t just a migration approach — it’s a development culture.
A Note on Explainability
For regulated domains like Finance and Risk, explainability is more than a best practice — it’s a compliance requirement. Every number in a report must be traceable to its source and transformation logic.
By generating both:
- Structured metadata, and
- Human-readable documentation (in SQL comment headers and markdown),
we ensured that every attribute in the business model could be explained — functionally and technically — without opening a single ETL job or BI report.
That’s true data lineage — not inferred, but engineered.
The Cultural Shift
At some point, governance stopped feeling like a control mechanism.
It became a shared language between developers, modelers, and AI.
The engineers focus on performance and technical optimization.
The modelers focus on clarity and business mapping.
And the AI enforces consistency and explains every decision.
When those three roles align, you don’t just modernize SQL —
you modernize the way people think about it.
Key Takeaways
- Governance works best when it’s designed into the process, not added after.
- YAML rules act as executable policies — governance as code.
- Data Quality logic belongs in its own layer, fully documented and owned.
- Automatic headers and validation reports make every query auditable.
- Governance by design bridges AI automation, data modeling, and compliance.
➡️ Next in the series:
“The Power of YAML Rules: Deduplication, Unions, and Optimization Patterns” — a deep dive into how specific rule categories turned SQL chaos into clean, 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.