The Power of YAML Rules: Deduplication, Unions, and Optimization Patterns

How rule categories helped us turn complex SQL into clean, modular, and governed transformations.

💡 Summary

After extracting metadata and defining our governance framework, we faced the next challenge: how to translate hundreds of SQL quirks into consistent transformation patterns.

The solution came from organizing our YAML rules into categories — each representing a specific type of logic.
By applying these rule sets systematically, we transformed legacy SQL into modular building blocks that were cleaner, faster, and auditable.

Press enter or click to view image in full size

The Philosophy Behind the Rules

Legacy SQL evolves like sediment — each layer built on top of the last. What starts as a clean design often turns into a mixture of quick fixes, distinct clauses, and inline subqueries.

Our goal was to separate concerns: Each query should handle one logical function — filtering, joining, deriving, deduplicating — and nothing else.

To enforce that discipline, we created YAML rule categories that encode best practices for each type of transformation. These rules aren’t arbitrary — they reflect years of accumulated experience in data engineering, applied in a machine-readable way.

Aggregation Rules: Isolate and Simplify

Aggregation is where SQL gets messy fast — especially when DISTINCT sneaks in to “fix” duplicates.

Our aggregation rules enforce three principles:

Group By in its own step — never mix aggregations with derivations.
No DISTINCT hacks — use ROW_NUMBER or LEFT JOIN to control duplicates.
Explicit naming — aggregated columns always get descriptive aliases.

Example rule:

id: AGG_ISOLATE_GROUPBY
name: Isolate GROUP BY in a dedicated CTE
rationale: Prevent mixed transformations and improve reusability.
examples:
before: |
SELECT cust_id, SUM(amount) FROM sales WHERE region=’EU’ GROUP BY cust_id;
after: |
WITH sales_eu AS (SELECT * FROM sales WHERE region=’EU’),
agg_sales AS (SELECT cust_id, SUM(amount) AS total_amount FROM sales_eu GROUP BY cust_id)
SELECT * FROM agg_sales;

This rule alone improved performance and readability across hundreds of queries.

Filter Rules: Consistency and Control

Filtering rules ensure we apply SCD2 logic consistently.

We replaced ad-hoc snapshot filters like:

WHERE snapshot_date = ‘2024-12-31’

with deterministic SCD2 filters:

WHERE ‘{functional_pit}’ >= valid_from
AND ‘{functional_pit}’ < COALESCE(valid_to, ‘9999-12-31’)

Each filter rule specifies where filtering should happen (in the first CTE per source) and how it should be expressed.

Rule philosophy: Filters must be early, isolated, and explainable.

Join Rules: Clean, Preprocessed, and Safe

We found many joins with inline transformations in the ON clause — casting, trimming, even string concatenations.

Our join rules enforce:

No functions or arithmetic in JOIN conditions
Preprocess keys in earlier CTEs
Use descriptive aliases (join_customer, not c1)
Avoid LEFT JOINs that aren’t used downstream

Example rule:

id: JN_PREPROCESSED_KEYS
name: Precompute join keys before joining
rationale: Improves readability and reduces repeated computation.

These rules made our joins cleaner — and eliminated subtle logic bugs that had existed for years.

Deduplication Rules: Clarity Over Hacks

This was one of the most impactful rule sets. We replaced dozens of DISTINCT + GROUP BY “fixes” with clear, explicit dedup logic.

Key principles:

LEFT JOIN with WHERE NULL is the default anti-join pattern
Never use DISTINCT or aggregate hacks to hide DQ issues
Separate CTEs for DQ fixes, with incident IDs and owners documented

Example:

WITH processed AS (SELECT DISTINCT order_id FROM processed_orders),
deduped AS (
SELECT o.*
FROM orders o
LEFT JOIN processed p ON o.order_id = p.order_id
WHERE p.order_id IS NULL
)

This pattern is transparent, auditable, and far easier to optimize.

Union Rules: Keep It Clean and Intentional

Union logic was another major source of confusion.
Inline unions nested in subqueries often caused parsing errors and performance issues.

Our union rules enforce:

Always use UNION ALL — never plain UNION
Never inline unions inside FROM clauses
Create a dedicated CTE for union operations
No filters or joins inside the union itself

Example:

WITH a AS (SELECT id FROM region_a),
b AS (SELECT id FROM region_b),
combined AS (
SELECT * FROM a
UNION ALL
SELECT * FROM b
)
SELECT * FROM combined;

This rule made every union operation predictable — both for humans and AI.

Optimization Rules: Performance Meets Clarity

Once logic was clean, we focused on efficiency.
Optimization rules included:

Shift-left transformations: perform derivations early
Specify columns explicitly (no SELECT *)
Reuse sub-CTEs when logic repeats
Materialize heavy transformations selectively

We also added validation invariants to detect inefficiencies automatically:

INV_NO_SELECT_STAR — no wildcards allowed
INV_SCD2_PIT_ONLY — no invalid date filters
INV_CTE_SINGLE_CONCERN — each CTE has a single purpose

With these invariants in place, our validation scripts caught issues before they reached production.

Modular Rules: One Step, One Purpose

Modular design was a cornerstone of the new SQL style.

We enforced:

One transformation type per CTE
Filters and derivations separated
Subqueries replaced with named CTEs
Clear materialization choices (CTE, view, table)

This modular structure made queries not only easier to read — but also easier to reuse, test, and document.

Why This Matters

By the time we had migrated the first few hundred queries, we noticed something remarkable:
The resulting SQL was shorter, faster, and more readable — not because of AI magic, but because every rule removed ambiguity.

AI simply followed the same principles we did — encoded in YAML.

The combination of metadata + rules + validation gave us something that traditional ETL frameworks rarely achieve: logical integrity and consistency at scale.

Key Takeaways

Each YAML rule category captures a specific type of engineering discipline.
Rules aren’t syntax preferences — they’re governance policies in code.
Deduplication, unions, and optimization rules deliver the biggest clarity gains.
Modularity and explicitness are the foundation of maintainable SQL.
The best part: these rules make AI predictable, explainable, and auditable.

➡️ Next in the series:
“The Python Rule Engine: Automating Transformation, Validation, and Documentation” — how we built the engine that applies rules, routes AI prompts, and generates explainable SQL.

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