The MDDE Loop — Parse, Observe, Optimize, Generate

A continuous feedback cycle

The MDDE Loop — Parse, Observe, Optimize, Generate

A Continuous Feedback Cycle

In the previous article, we looked at the architecture absorption problem:

As systems grow, structure disappears into SQL.
MDDE does not solve this by replacing SQL.
It solves it by creating a loop.

Not a one-time migration step.
Not a modeling exercise.
A continuous feedback cycle.

This is the MDDE Loop:

Parse → Observe → Optimize → Generate

And then back again.

1. Parse — Extract Logical Structure From SQL

The loop always begins with existing SQL.

Not conceptual models.
Not hand-written YAML.
Not theoretical abstractions.

Real SQL.

SELECT  
    o.order_id,  
    c.customer_name,  
    SUM(oi.quantity * oi.unit_price) AS total_amount,  
    MAX(oi.line_date) AS last_line_date  
FROM orders o  
JOIN customers c ON o.customer_id = c.customer_id  
JOIN order_items oi ON o.order_id = oi.order_id  
GROUP BY o.order_id, c.customer_name;

MDDE parses this into a structured metadata model.

Under the hood, the parser extracts:

Logical entities (tables, views, CTEs, derived entities)
Attributes (columns with lineage)
Relationships (joins, inferred foreign keys)
Functions (reusable expressions)
Filters (where conditions)
Mappings (source-to-target attribute lineage)

In practice:

python -m mdde.importer.sql_importer path/to/sql --name sales_model

This does not rewrite your SQL.
It makes its structure explicit.

What Gets Extracted?

Logical Entity

entity:  
  name: order_summary  
  entity_type: query  
  source_entities:  
    - orders  
    - customers  
    - order_items

Attributes

attributes:  
  - name: order_id  
    origin: orders.order_id  
- name: customer_name  
    origin: customers.customer_name  
  - name: total_amount  
    expression: SUM(quantity * unit_price)  
  - name: last_line_date  
    expression: MAX(line_date)

Relationships

relationships:  
  - from: orders.customer_id  
    to: customers.customer_id  
    type: inner_join  
- from: orders.order_id  
    to: order_items.order_id  
    type: inner_join

Mapping (Lineage)

mappings:  
  - target: total_amount  
    sources:  
      - order_items.quantity  
      - order_items.unit_price  
- target: last_line_date  
    sources:  
      - order_items.line_date

Classification Is a Secondary Step

At this stage, MDDE has not assumed:

Fact table
Dimension table
Mart layer
Integration layer

However, once logical structure is explicit, MDDE can classify entities using stereotypes.

For example:

entity:  
  name: order_summary  
  stereotype: fact

Or:

entity:  
  name: customer  
  stereotype: dimension

But classification is derived — not assumed.
The foundation is logical structure.

Why This Matters

By extracting logical entities and mappings first, MDDE avoids:

Hardcoding dimensional assumptions
Forcing star-schema thinking
Overfitting to one modeling paradigm

Instead, it builds a neutral structural representation.

From there, you can:

Apply dimensional modeling
Apply Data Vault classification
Apply SCD detection
Apply compliance rules
Apply optimizer passes

Structure first.
Semantics second.

2. Observe — Detect Patterns Without Forcing Change

Once structure is explicit, MDDE begins observing.
Observation is separate from action.
This is one of the most important design principles.

MDDE generates:

Pattern detections
Quality findings
Optimization suggestions
Compliance signals

Example observations:

- observation_id: obs_001  
  category: pattern  
  title: "Aggregation entity detected"  
  message: "2 dimensions, 2 measures"  
- observation_id: obs_002  
  category: refactoring  
  title: "Reusable expression detected"  
  message: "quantity * unit_price appears multiple times"  
- observation_id: obs_003  
  category: compliance  
  severity: warning  
  title: "Naming convention mismatch"

Nothing is changed automatically.
Understanding comes first.
You cannot optimize what you cannot see.

3. Optimize — Structured, Reversible Improvements

Optimization in MDDE is not rewriting SQL blindly.
It is structured transformation passes applied to metadata.

Examples of optimizer passes:

Extract reusable functions
Normalize filters
Detect deduplication patterns
Classify entity stereotypes (fact, dimension, kernel, bridge)
Identify SCD patterns
Detect join reordering opportunities

Run optimizer:

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

Each optimizer pass:

Emits diagnostics
Produces proposals
Is reversible
Leaves an audit trail

Optimization becomes explainable engineering — not manual rewriting.

4. Generate — Produce Canonical SQL (If You Want)

Generation is optional.
But powerful.
Once metadata is structured and optimized, MDDE can regenerate SQL:

python -m mdde.scripts.export_model mdl_sales \  
    --output workspace/applications/sql/sales_model \  
    --ddl --html-documentation

Generated SQL is:

Deterministic
Consistent
Structured
Documented

You can:

Compare original vs canonical
Adopt changes incrementally
Keep original SQL if preferred

MDDE does not force replacement.

It enables standardization.

The Loop Is the Point

The real power is not in any single step.
It’s in the cycle:

SQL  
  ↓  
PARSE  
  ↓  
METADATA  
  ↓  
OBSERVE  
  ↓  
OPTIMIZE  
  ↓  
GENERATE  
  ↓  
SQL (again)

Each iteration:

Reduces ambiguity
Surfaces hidden structure
Improves consistency
Makes lineage clearer
Lowers change risk

And then the loop can run again.

Human Control Remains Central

Important clarification:
MDDE does not automate architecture decisions.
It automates visibility.

Engineers decide:

Which proposals to accept
Which optimizations to apply
Which rules to enforce
When to regenerate SQL

The system reduces uncertainty.
It does not remove judgment.

Why This Loop Matters in Practice

In migration programs, this loop enables:

Safe regression comparison
Detection of semantic drift
Refactoring with confidence
Platform portability

In enterprise environments, it enables:

Governance overlays
Data quality enforcement
Compliance scoring
Multi-dialect deployment

In everyday engineering, it enables:

Clearer reviews
Faster onboarding
Reduced duplication
Observable change impact

Separation of Observation and Action

This deserves emphasis.
Many frameworks conflate detection and enforcement.
MDDE does not.

You can:

Parse without optimizing
Observe without generating
Generate without enforcing
Enforce selectively

This makes the loop safe in production environments.

Where VSCode and AI Fit

MDDE is being built in VSCode.

Claude Code is used to:

Accelerate parser refinement
Generate optimizer patterns
Validate metadata structure
Assist with deterministic generation

But the loop itself is deterministic.
AI assists development.
It does not replace the metadata engine.

Closing

MDDE is not a modeling tool.
It is not a SQL generator.
It is not a governance layer.

It is a loop that keeps architecture visible.
SQL remains execution.
Metadata becomes structure.
The loop keeps them aligned.

Next in the Series

In the next article: Workspace Anatomy — How MDDE Organizes Models, Entities, and Artifacts

We’ll go deep into:

The folder structure
Three-layer YAML separation
Co-located artifacts
Automation scripts
Why “one entity, one folder” reduces chaos

The loop gives you structure.
The workspace makes it usable.

🔧 Technical Deep Dive — Optimizer Diagnostics in Practice

Each optimizer pass emits structured diagnostics.

Inspect Optimizer Results

SELECT  
    pass_name,  
    scanned_count,  
    extracted_count,  
    skipped_count,  
    severity,  
    message  
FROM metadata.optimizer_diagnostics  
WHERE model_id = 'mdl_sales'  
ORDER BY created_at DESC;

Example result:

Optimization becomes measurable.

Example: Function Extraction Pass

Before:

SUM(order_items.quantity * order_items.unit_price)

After metadata extraction:

function_def:  
  name: line_amount  
  expression: quantity * unit_price

The aggregate now references:

SUM(line_amount)

Reusable.
Explicit.
Traceable.