CTE Normalization & UNION Handling — Making Derived Queries First-Class Metadata Citizens

Why Derived Queries Must Be Modeled — Not Rendered

CTE Normalization & UNION Handling — Making Derived Queries First-Class Metadata Citizens

Why Derived Queries Must Be Modeled — Not Rendered

Most SQL complexity does not live in base tables.

It lives in:

CTEs
Subqueries
UNION chains
Nested derived queries

In many data platforms, these constructs are treated as textual blocks.

Parsed.
Rendered.
Forgotten.

MDDE treats them differently.

Derived queries are modeled as entities.
Not as strings.

The Problem With Textual CTE Handling

Consider:

WITH filtered_orders AS (  
    SELECT *  
    FROM orders  
    WHERE order_date >= '2024-01-01'  
),  
aggregated_orders AS (  
    SELECT customer_id, COUNT(*) AS order_count  
    FROM filtered_orders  
    GROUP BY customer_id  
)  
SELECT *  
FROM aggregated_orders;

Traditional tooling:

Keeps CTEs inline
Preserves order of appearance
Treats them as part of the final SQL blob

This causes problems:

Lineage between CTEs is implicit
Dependency resolution is fragile
Duplicate CTE names become hard to detect
UNION branches inside CTEs become ambiguous
SQL regeneration becomes order-sensitive

MDDE normalizes CTEs into metadata entities.

CTE as Entity

When parsed, each CTE becomes:

metadata.entity  
  entity_type = 'query'  
  inheritance_kind = 'cte'

Lineage between CTEs is captured via:

metadata.entity_mapping

This is critical.

CTEs are not connected via parent_query_id for lineage.
parent_query_id represents structural containment.

Lineage is always represented explicitly in entity_mapping.
That architectural separation prevents subtle bugs.

Structural vs Lineage Modeling

There are two relationships at play:

Structural containment
(Which query contains which subquery)
Data lineage
(Which entity reads from which entity)

Confusing these leads to generator instability.

MDDE enforces:

parent_query_id → structure only
entity_mapping.source_entity_id → lineage only

That decision prevents generator ambiguity.

Dependency Graph Construction

Once CTEs are normalized into entities, MDDE builds a dependency graph:

Nodes:

Query entities
Base tables

Edges:

entity_mapping rows

Then:

Non-root CTE entities are pruned from standalone generation
Topological sort determines correct WITH ordering
Duplicate emission is prevented

This avoids:

Re-rendering nested UNION entities
Emitting inline subqueries twice
Producing orphan CTEs

UNION Handling: A Structural Redesign

UNION queries are often mishandled in SQL generators.

Typical flawed model:

Main query is parent
UNION branches are children
Generator tries to render all entities independently

This produces:

Duplicate SELECT statements
Broken nesting
Incorrect branch ordering

MDDE redesigns UNION as a derived entity:

metadata.entity  
  entity_type = 'query'  
  combine_type = 'UNION' | 'UNION ALL'

Each branch becomes:

metadata.entity_mapping  
  source_type = 'union_branch'  
  ordinal_position = 1, 2, 3...

UNION branches are not independent queries.

They are ordered lineage components of one derived entity.

Why This Matters

Without explicit UNION modeling:

Branch projection mismatch goes undetected
Column alignment errors hide until runtime
Canonical ordering becomes unstable
Inline UNION inside CTE breaks rendering

With explicit modeling:

Branch count can be validated
Projection alignment can be enforced
Canonical regeneration becomes deterministic

Guardrails in SQL Generation

Generator rules enforce:

UNION entities must have ≥ 2 branches
Projection schemas must align
Non-root UNION entities cannot be rendered standalone
WITH clauses emit each CTE exactly once

Example structural validation query:

SELECT entity_name  
FROM metadata.entity  
WHERE combine_type IS NOT NULL  
AND entity_id NOT IN (  
    SELECT DISTINCT target_entity_id  
    FROM metadata.entity_mapping  
);

Detects orphan UNION entities.

Inline Subqueries

Subqueries inside SELECT or WHERE clauses are modeled as:

inheritance_kind = 'subquery'

These entities:

Have parent_query_id
Participate in lineage
Are never emitted as top-level SQL

This avoids accidental standalone rendering.

CTE Normalization in Migration Context

During migrations:

Developers often inline CTEs differently
UNION ordering changes
Subqueries move between WITH and inline

Textual diff explodes.

Metadata normalization eliminates cosmetic drift.
Only structural changes remain visible.

That reduces migration risk dramatically.

🔧 Technical Deep Dive

Inspect CTE Lineage

SELECT  
    parent.entity_name AS parent_cte,  
    child.entity_name  AS child_source  
FROM metadata.entity_mapping em  
JOIN metadata.entity parent  
  ON em.target_entity_id = parent.entity_id  
JOIN metadata.entity child  
  ON em.source_entity_id = child.entity_id  
WHERE parent.inheritance_kind = 'cte';

Validate UNION Branch Count

SELECT entity_name  
FROM metadata.entity e  
LEFT JOIN metadata.entity_mapping em  
  ON e.entity_id = em.target_entity_id  
WHERE e.combine_type IS NOT NULL  
GROUP BY entity_name  
HAVING COUNT(*) < 2;

Topological Order for WITH Clause

Dependency ordering is derived from:

SELECT source_entity_id, target_entity_id  
FROM metadata.entity_mapping;

Then resolved via topological sort in the generator.

The Principle

Derived queries are not formatting constructs.

They are structural entities.

Once CTEs and UNIONs are modeled explicitly:

SQL generation becomes stable
Dependency graphs become reliable
Lineage becomes precise
Migration becomes safer
CI validation becomes enforceable

This is not about clever parsing.
It is about architectural modeling.

Next in the Advanced Series

Next we move into:

Compliance Scoring & CI Gates — Making Architecture Measurable

Where we’ll cover:

Scoring models based on metadata
Hard CI gates
Structural invariants
Enforcing deterministic design
Optimizer diagnostics as measurable signals