From Raw SQL to Logical Building Blocks

How we turned parsed SQL metadata into reusable YAML rules that define how transformations should look — not just what they do.

💡 Summary

==Once we had extracted metadata from hundreds of SQL queries, a new question emerged:== ==How do we describe the logic itself in a way that both AI and humans can understand?==

The answer came from defining logical building blocks — reusable YAML-based rules that represent the intent of SQL transformations.
Instead of copying old SQL patterns, we formalized what good transformations should look like: modular, consistent, and explainable.

Press enter or click to view image in full size

Why Metadata Alone Isn’t Enough

Parsing SQL gave us structure, but not meaning. We could see every column, join, and CTE — but we still didn’t know what made a query good, inefficient, or outdated.

For example:

==Some queries filtered snapshot data with a date column instead of proper SCD2 logic.==
Some used DISTINCT to remove duplicates caused by bad joins.
Others mixed derivations, aggregations, and filters in the same CTE.

Metadata showed us where these issues occurred — but we needed a systematic way to correct them.

So we began defining rules.

Defining the Building Blocks

We decided to represent every transformation concept — aggregation, join, filter, rename, deduplication, etc. — as a YAML rule.

Each rule has a consistent structure:

id: AGG_SEPARATE_STEP
name: Aggregation in a separate step
description: >
Ensure that any GROUP BY operation is isolated in its own CTE.
rationale: >
Simplifies query design and improves maintainability.
tags: [migration, optimization]
examples:
before: |
SELECT cust_id, SUM(amount) FROM tx GROUP BY cust_id;
after: |
WITH agg_tx AS (SELECT cust_id, SUM(amount) AS total_amount FROM tx GROUP BY cust_id)
SELECT * FROM agg_tx;
validation: >
GROUP BY should appear only in CTEs tagged as aggregation steps.

This format gave us a universal language for describing how SQL should behave —
and for letting AI reason about it without ambiguity.

Turning Rules into Categories

We grouped the rules into thematic YAML files:

Aggregation Rules — how to handle GROUP BY, DISTINCT, and deduplication.
Filter Rules — how and where to apply SCD2 filtering, point-in-time logic, and source-level filters.
Join Rules — preprocessed joins, clean conditions, and no functions in ON.
Modular Rules — how to structure CTEs, push filters early, and avoid nesting.
Naming Rules — consistent, descriptive CTE and alias naming conventions.
Optimization Rules — column pruning, early derivations, reusable sub-CTEs.
Deduplication Rules — LEFT JOIN–based anti-joins, isolated DQ fixes, no DISTINCT hacks.
Union Rules — explicit UNION ALL only, clean dedicated CTEs, no inline unions.
Metadata Rules — remove obsolete timeline columns and enforce SCD2 metadata.
Documentation Rules — generate comment headers and rule justification blocks.

By the time we were done, we had hundreds of rules, each written in YAML and version-controlled.

From Rules to Governance

At this point, something powerful happened.

The project stopped being a migration exercise and became a metadata-driven governance framework. The YAML rules didn’t just tell AI how to rewrite SQL — they also told us what “good SQL” looks like.

Each rule described:

Why it exists (rationale)
When it applies (conditional keywords)
How to validate results (checklist + validation clause)
Who owns it (tags, categories)

The same rules could drive:

AI prompt generation (which rules to include in transformation requests)
Validation scripts (using DuckDB to check for compliance)
Documentation headers (explaining what rules were applied)

In other words, the YAML became the single source of truth for SQL governance.

A Simple Example: Deduplication

Here’s one of my favorite examples — the deduplication rule.

id: DEDUP_LEFT_JOIN_NULL
name: Deduplicate using LEFT JOIN with WHERE NULL
description: >
When excluding records between two datasets, use a LEFT JOIN
combined with WHERE NULL instead of subqueries.
rationale: >
LEFT JOIN with WHERE NULL is more efficient and easier to read.
examples:
before: |
SELECT * FROM orders WHERE order_id NOT IN (SELECT order_id FROM processed_orders);
after: |
SELECT o.*
FROM orders o
LEFT JOIN processed_orders p ON o.order_id = p.order_id
WHERE p.order_id IS NULL;

This rule may look simple, but once it’s stored in YAML, it can:

Guide AI in rewriting logic correctly
Help validate that deduplication was done deterministically
Feed a documentation header explaining what happened and why

We went from hard-coded SQL patches to rule-governed logic.

Making It Actionable

Once we defined all rule categories, we wrote a Python driver that:

Reads the YAML rules
Routes them based on detected SQL keywords (e.g., UNION, DISTINCT, GROUP BY)
Applies deterministic transformations (or generates AI prompts)
Validates results against invariants (no SELECT *, proper SCD2 filters, etc.)
Inserts a documentation header listing applied rules

This turned our YAML into a living engine — one that could lint, transform, and explain.

Why This Matters

In large enterprises, SQL is both code and policy. By representing transformation logic as structured metadata and rules, we created a way to:

Automate consistency — every query follows the same design principles
Explain transformations — every change is traceable to a rule
Empower collaboration — AI, data modelers, and engineers now speak the same language

When AI rewrites SQL under these conditions, it’s no longer “guessing.”
It’s applying rules — and documenting them along the way.

Key Takeaways

Metadata tells you what a query does; rules define how it should do it.
YAML rules form the bridge between human governance and AI execution.
The same framework can drive transformation, validation, and documentation.
By defining rules first, you make AI predictable and explainable.

➡️ Next in the series:
“Teaching AI to Apply Rules: Prompts, Validation, and Feedback Loops” — how we guided AI step by step to transform SQL safely and consistently using metadata and YAML rules.

🧭 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.