Jaco van der Laan

The Python Rule Engine: Automating Transformation, Validation, and Documentation

11/10/2025  ·  Automation  ·  Data Engineering

‹ ›

How we built a rule engine that reads YAML, routes AI prompts, validates transformations, and writes explainable SQL.

💡 Summary

At this point in the series, we had defined:
– Structured metadata for every SQL query
– A library of YAML rules representing transformation logic
– A framework of AI prompts to apply those rules

The missing piece was orchestration — a way to connect all of it.
So we built a Python rule engine that loads the YAML rules, detects which apply, and drives the full lifecycle: Lint → Transform → Validate → Document.

Press enter or click to view image in full size

Why We Needed an Engine

Once we had hundreds of rules across categories — joins, filters, unions, optimization, deduplication — it became clear that we needed a consistent way to:

  1. Select relevant rules for each SQL query
  2. Apply deterministic rewrites before invoking AI
  3. Validate the result against our invariants
  4. Inject documentation and metadata

Manually managing all this logic would have been fragile. Instead, we treated the YAML library like a governance API — rules as data, executed dynamically.

The Three Phases of the Engine

Our engine follows a simple but powerful flow:

┌─────────────┐
│ LINT │ detect violations, keyword routing
└────┬────────┘
│
┌────▼────────┐
│ TRANSFORM │ deterministic rewrites or AI-assisted steps
└────┬────────┘
│
┌────▼────────┐
│ VALIDATE │ invariants, column parity, rule compliance
└─────────────┘

🧩 Phase 1 — LINT

The LINT phase runs all structural checks:

  • Naming conventions (CTEs, aliases)
  • No SELECT *
  • No inline UNION or BETWEEN filters
  • Legacy schema placeholders replaced

It uses regex or sqlglot AST patterns to detect violations early.

🧠 Phase 2 — TRANSFORM

The transformation phase is where the engine either:

  • Applies deterministic rewrites (like schema replacements or formatting), or
  • Calls AI to apply complex rules (e.g., modularizing CTEs, enforcing SCD2 filtering)

It uses the keyword routing map defined in rules_index.yaml to select relevant rule categories dynamically.

Each transformation produces:

  • The rewritten SQL
  • A list of rule IDs applied
  • A structured log of changes

✅ Phase 3 — VALIDATE

Finally, the engine checks invariants and rule compliance:

  • All expected columns exist
  • Joins match metadata
  • Dedup rules applied when DISTINCT found
  • No obsolete metadata columns
  • Validation rules from YAML executed in Python

Any warnings or violations are logged for later reporting.

How Rules Are Executed

Each rule is stored as a dictionary-like object with its own logic function:

@dataclass
class Rule:
id: str
name: str
priority: int
phase: str
conditional_keywords: List[str]
apply: Callable[[str, Dict[str, Any]], Tuple[str, List[str]]]

Each rule can modify SQL text, append findings, or generate AI prompts.
For example, a simple rule might flag a SELECT *, while another replaces old schema names with new ones based on a YAML mapping.

The engine loads rules from YAML files, then routes them based on phase and keyword matches:

if set(rule.conditional_keywords) & detected_keywords:
sql, findings = rule.apply(sql, context)

The result is a flexible pipeline — each query follows its own relevant subset of the global rule base.

Integrating AI into the Flow

When the engine encounters a rule that requires interpretation — such as modularization, deduplication, or validation — it automatically:

  1. Generates a prompt combining the relevant YAML rules and metadata
  2. Sends it to the AI model
  3. Receives the rewritten SQL
  4. Immediately validates it against metadata and invariants

We call this hybrid pattern rule-driven prompting — the AI is not deciding what to do; it’s executing what the rules prescribe.

Automatic Documentation

One of my favorite features of the engine is how it auto-generates a comment header at the top of each SQL file:

/*

SQL Migration Comment Header
Purpose : Legacy migration to common platform
Rules : [AGG_ISOLATE_GROUPBY, FLT_SCD2_FILTER, DEDUP_LEFT_JOIN_NULL]
Validation : PASSED

*/

This header is created by the same engine that runs transformations.
It pulls the rule IDs, validation outcomes, and metadata properties directly from the context.

Documentation is no longer an afterthought — it’s an output of the process.

The Benefits of a Python-Based Framework

Using Python gave us tremendous flexibility:

  • Metadata Integration: We could query DuckDB to validate columns, datatypes, and lineage.
  • Dynamic Prompting: We could compose prompts automatically from context.
  • AI Orchestration: We could sequence model calls with deterministic logic.
  • Automation: The same engine can lint, refactor, document, and validate in one run.

It also means we can extend this approach easily — for example, integrating it into Databricks notebooks or CI/CD pipelines for continuous compliance checks.

What Makes This Different

This engine isn’t another SQL formatter or code linter.
It’s a metadata-aware, rule-governed transformation system.

Where typical migration tools focus on syntax conversion, ours focuses on semantic correctness:
Does the logic still mean the same thing?
Are the filters consistent?
Are the rules traceable?

By encoding those answers as YAML and enforcing them with Python, we created a system that scales quality — not just code.

Key Takeaways

  • A rule engine operationalizes your YAML standards — governance becomes executable.
  • Splitting the process into LINT → TRANSFORM → VALIDATE keeps transformations safe and explainable.
  • Rule-driven prompting lets AI act predictably within structured boundaries.
  • Automatic documentation ensures every output is transparent and auditable.
  • The combination of Python, SQLGlot, DuckDB, and AI makes this framework reproducible, extensible, and enterprise-ready.

➡️ Next in the series:
“Beyond Migration: Copilot-Assisted Data Modeling in VS Code” — how we’re extending the same methodology to empower business developers and modelers to build mappings and documentation directly in VS Code with AI support.

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