The MDDE Metadata Schema — 60+ Tables That Make Architecture Executable

The Architecture Itself Becomes Queryable

The MDDE Metadata Schema — 60+ Tables That Make Architecture Executable

The Architecture Itself Becomes Queryable

Most data platforms are built on SQL.

MDDE is built on metadata.
Not documentation metadata.
Not optional annotations.

Structured, relational metadata that describes:

Entities
Attributes
Relationships
Transformations
Lineage
Quality rules
Optimizer findings
Deployment behavior

In other words:

The architecture itself becomes queryable.
This article explains the schema that makes that possible.

Why a Metadata Schema at All?

When SQL is parsed into MDDE, it is not stored as text blobs.

It is decomposed into:

Entities
Attributes
Relationships
Expression trees
Mappings
Dependencies
Window definitions
Filters
Functions

Each of these becomes a first-class object.

That requires structure.

The MDDE metadata schema currently consists of 60+ normalized tables, organized into four conceptual layers:

Structural Core
Transformation Layer
Governance & Quality
Deployment & Dialect

Let’s walk through them.

1️⃣ Structural Core

At the heart of the schema:

metadata.entity  
metadata.attribute  
metadata.relationship  
metadata.attribute_mapping

These tables describe structure and lineage.

metadata.entity

Represents:

Tables
Views
CTEs
Subqueries
Derived entities

Key columns (simplified):

entity_id  
model_id  
entity_name  
entity_type  
inheritance_kind  
parent_query_id

This allows MDDE to distinguish:

Physical tables
Logical entities
Derived CTE entities
Inline UNION entities

Structure is no longer inferred from SQL text.
It is represented explicitly.

metadata.attribute

Represents columns.

attribute_id  
entity_id  
attribute_name  
data_type  
is_nullable  
ordinal_position

Attributes are attached to entities.
They carry structural and semantic metadata.

metadata.relationship

Represents joins and foreign key relationships.

relationship_id  
parent_entity_id  
child_entity_id  
cardinality  
join_condition

Relationships can be:

Explicit foreign keys
Detected joins
Logical relationships
Imported model relationships

metadata.attribute_mapping

This is where lineage lives.

mapping_id  
source_attribute_id  
target_attribute_id  
transformation_expression

Every transformation becomes a row.
This enables column-level lineage queries.

Architecture becomes relational data.

2️⃣ Transformation Layer

Where structural representation meets logic.

metadata.function_def  
metadata.filter_def  
metadata.mapping  
metadata.entity_mapping

metadata.function_def

Reusable logic extracted from SQL.

function_id  
name  
expression  
deterministic

Repeated expressions such as:

quantity * unit_price

Become reusable metadata objects.

This enables:

Canonical SQL generation
Deterministic substitution
Pattern detection
Optimization proposals

metadata.filter_def

Reusable conditions.

filter_id  
name  
expression

Filters can be normalized and reused across mappings.

metadata.mapping

Represents source-to-target transformation definitions.

mapping_id  
target_entity_id  
source_entity_id  
mapping_type

This abstracts the idea of:
“Data flows from A to B.”

metadata.entity_mapping

Represents entity-level lineage.

Critical for:

CTE resolution
UNION handling
Subquery modeling
Dependency graph construction

This table allows the SQL generator to:

Build correct WITH clauses
Avoid duplicate CTE emission
Construct dependency graphs safely

3️⃣ Governance & Quality

Where MDDE becomes operational.

metadata.data_quality_check  
metadata.optimizer_diagnostics  
metadata.proposal  
metadata.compliance_rule

metadata.data_quality_check

Generated quality rules.

check_id  
entity_id  
check_type  
generated_sql  
severity

Examples:

Null checks
Duplicate detection
Range validation
Referential integrity

Quality is derived from structure.
Not manually scripted.

metadata.optimizer_diagnostics

Every optimizer pass emits structured output.

diagnostic_id  
pass_name  
scanned_count  
extracted_count  
severity  
message

This creates:

Observability
CI integration points
Measurable architecture health

metadata.proposal

Improvement lifecycle tracking.

proposal_id  
observation_id  
status  
created_at

Proposals move through:
Draft → Proposed → Accepted → Applied → Rejected

Architecture evolves intentionally.

metadata.compliance_rule

Represents enforceable policies.

Examples:

No SELECT *
Mandatory primary keys
Naming conventions
Type consistency

Compliance can be scored.
And gated.

4️⃣ Deployment & Dialect

Where metadata becomes executable.

metadata.type_mapping  
metadata.dialect_config  
metadata.entity_window

metadata.type_mapping

Logical → dialect translation.

logical_type  
snowflake_type  
databricks_type  
duckdb_type  
bigquery_type

DDL generation becomes deterministic.

metadata.dialect_config

Dialect-specific rules:

Quoting strategy
Identifier casing
Comment syntax
Constraint handling

metadata.entity_window

Metadata for window functions.

window_id  
entity_id  
partition_by  
order_by  
frame_clause

Window logic is structured.
Not embedded in opaque SQL strings.

Architecture Is Queryable

Because everything is relational metadata, you can ask questions about your architecture directly.

Example:

Inspect Full Column-Level Lineage

SELECT  
    tgt.entity_name,  
    tgt_attr.attribute_name,  
    src.entity_name,  
    src_attr.attribute_name  
FROM metadata.attribute_mapping m  
JOIN metadata.attribute tgt_attr  
  ON m.target_attribute_id = tgt_attr.attribute_id  
JOIN metadata.entity tgt  
  ON tgt_attr.entity_id = tgt.entity_id  
JOIN metadata.attribute src_attr  
  ON m.source_attribute_id = src_attr.attribute_id  
JOIN metadata.entity src  
  ON src_attr.entity_id = src.entity_id;

This is not a diagram.
It is a query.

🔧 Technical Deep Dive

Count Metadata Objects

SELECT  
    (SELECT COUNT(*) FROM metadata.entity)        AS entities,  
    (SELECT COUNT(*) FROM metadata.attribute)     AS attributes,  
    (SELECT COUNT(*) FROM metadata.relationship)  AS relationships;

This is the real model footprint.
Not number of SQL files.
Not lines of code.

Structured architectural objects.

Inspect Model Complexity

SELECT  
    e.entity_name,  
    COUNT(a.attribute_id) AS attribute_count  
FROM metadata.entity e  
LEFT JOIN metadata.attribute a  
  ON e.entity_id = a.entity_id  
GROUP BY e.entity_name  
ORDER BY attribute_count DESC;

Architecture becomes measurable.

Why This Matters

Without a metadata schema:

SQL is opaque
Lineage is inferred
Governance is bolted on
Optimization is heuristic
CI enforcement is fragile

With a structured schema:

Architecture is explicit
Lineage is relational
Optimization is measurable
Governance is enforceable
Deployment is deterministic

MDDE does not replace SQL.
It makes the architecture behind SQL visible.

And executable.