Inside the MDDE Engine — Optimizer Passes, Diagnostics, and Deterministic Generation

MDDE is not just a metadata repository

---

Inside the MDDE Engine — Optimizer Passes, Diagnostics, and Deterministic Generation

MDDE is not just a metadata repository

Most articles talk about modeling.
Few talk about engines.

MDDE is not just a metadata repository.
It is an engine composed of:

• Parser layer
• Metadata writer
• Optimizer pipeline
• Proposal system
• SQL generator
• Diagnostics framework

Let’s look inside.

---

The Optimizer Architecture

Each optimizer pass follows the same contract:

def run(model_id: str, db: Connection) -> OptimizationReport:  
    ...

Each pass:

• Scans metadata
• Detects patterns
• Emits diagnostics
• Optionally produces proposals
• Never mutates blindly

Diagnostics are written to:

metadata.optimizer_diagnostics

---

Example: Filter Extraction Pass

Goal:

Detect reusable filter conditions.

Before:

WHERE order_date >= '2023-01-01'

Detected as:

filter_def:  
  name: current_year_orders  
  expression: order_date >= '2023-01-01'

Diagnostic record:

SELECT *  
FROM metadata.optimizer_diagnostics  
WHERE pass_name = 'filter_extraction';

Function Extraction Engine

Repeated expressions:

quantity * unit_price

Are normalized into:

function_def:  
  name: line_amount  
  deterministic: true

Generator substitutes:

SUM(line_amount)

Deterministic substitution ensures canonical output.

---

SQL Generation Strategy

The generator:

1. Resolves entity dependencies
2. Topologically sorts entities
3. Handles CTE normalization
4. Applies alias qualification
5. Applies dialect rendering

It never relies on string concatenation alone.
Everything is metadata-driven.

---

Deterministic Output Guarantee

For the same metadata input:

• SQL generation is identical
• Formatting is stable
• Column order is predictable
• Alias qualification is explicit

This enables:

• Clean diffs
• CI gating
• Structural regression comparison

---

Why Engine Internals Matter

Frameworks are judged by:

• Determinism
• Explainability
• Diagnostics
• Safety

MDDE was built under migration pressure.
Which means:

Failure modes were discovered early.
And engineered out.

---

🔧 Technical Deep Dive

Inspect Optimizer Diagnostics

SELECT pass_name, scanned_count, extracted_count  
FROM metadata.optimizer_diagnostics  
ORDER BY created_at DESC;

Inspect Function Definitions

SELECT name, expression, deterministic  
FROM metadata.function_def;

Run Full Optimization Pipeline

python -m mdde.scripts.optimize_model mdl_sales --verbose