Workspace Anatomy — How MDDE Organizes Models, Entities, and Artifacts

The Model Workspace

Workspace Anatomy — How MDDE Organizes Models, Entities, and Artifacts

The workspace as architectural surface.

When structure becomes visible, the next question is inevitable:
Where does it live?

If metadata exists only inside a database, it becomes opaque.
If it exists only in diagrams, it becomes stale.
If it exists only in SQL, it remains absorbed.

MDDE solves this by making the workspace itself an architectural surface.

This article walks through how models, entities, mappings, and artifacts are organized — and why that structure matters.

Why Folder Structure Is Architecture

Most SQL-based projects look like this:

models/  
  sales.sql  
  customers.sql  
  orders.sql

Maybe a schema.yml.
Maybe a documentation folder.
But structural intent, quality rules, lineage, and mappings are scattered.

MDDE takes a different approach:

One entity, one folder.
One model, one coherent workspace.

Structure is not hidden in files.

It is reflected in the filesystem itself.

The Model Workspace

A typical model lives here:

workspace/applications/{tool}/{project}/{model_name}/

Inside:

model.conceptual.yaml  
model.logical.yaml  
model.technical.yaml  
_model.md  
README.md

This separation is deliberate.

The Three-Layer YAML Philosophy

MDDE separates concerns explicitly.

1. Conceptual — Business Layer

Business speaks here.

entity:  
  name: Customer  
  description: A person or organization that purchases products  
  domain: Sales

No technical IDs.
No SQL.
No system concerns.

This layer is stable across platforms.

2. Logical — Structural Layer

Modelers work here.

entity:  
  name: customer  
  stereotype: dimension  
  attributes:  
    - name: customer_id  
      data_type: VARCHAR(50)  
      mandatory: true  
      is_primary_key: true  
    - name: customer_name  
      data_type: VARCHAR(255)

Structure.
Types.
Relationships.
Constraints.

This layer defines how data is shaped.

3. Technical — System Layer

The system tracks here.

entity:  
  entity_id: ent_a1b2c3d4e5f6  
  model_id: mdl_xyz123  
  origin_type: sql_import  
  origin_id: 7c045dc0-37d7-11ec-8866-8b7656a20503

Round-trip safety.
Stable identifiers.
Lineage tracking.

This layer exists so humans don’t have to think about it.

Entity-Level Organization

Each entity gets its own folder:

entities/  
  customer/  
    entity.conceptual.yaml  
    entity.logical.yaml  
    entity.technical.yaml  
    _entity.md  
    code/  
    diagrams/  
    mappings/  
    incoming_relationships/  
    outgoing_relationships/

Why this matters:

Minimizes merge conflicts
Makes diffs readable
Encourages fine-grained ownership
Co-locates everything relevant

No more scrolling through massive files.

Mappings Are First-Class

Transformations are not buried in SQL.

They live under:

entities/{entity_name}/mappings/{source_alias}/

Inside:

mapping.logical.yaml  
mapping.technical.yaml  
filters/  
functions/

This makes source-to-target logic inspectable.
Lineage is not inferred at runtime.
It is materialized in metadata.

Co-Located Artifacts

Each entity folder can contain:

code/  
  ddl/create_table.sql  
  dq/duplicates.sql  
  profiling/profile.sql  
diagrams/  
  structure.md

Artifacts stay next to their metadata definition.

No separate documentation universe.
No drift between model and execution.

Model-Level Assets

At the model root:

diagrams/  
  model_erd.md  
  model_diagram.html  
documentation/  
  model_documentation.html  
code/  
  ddl/create_all_tables.sql  
  profiling/column_statistics.sql

Everything regenerates from metadata.
Documentation is not manually maintained.

Automation Scripts

Every model workspace generates scripts:

scripts/  
  reimport.cmd  
  analyze.cmd  
  optimize.cmd  
  export.cmd  
  full_pipeline.cmd

This matters more than it sounds.
Engineers should not memorize CLI invocations.
They should run:

full_pipeline.cmd

And see:

Re-import
Analyze
Optimize
Export

Clear. Deterministic. Repeatable.

Why This Structure Scales

This layout enables:

Git-based governance
Clean pull requests
Reviewable changes
Incremental adoption
Cross-platform portability

It supports:

dbt integration
Enterprise model import
Multi-dialect generation
Data quality propagation

Without becoming tool-dependent.

Built in VSCode

This workspace is designed for:

Version control
Diffability
AI assistance
Developer ergonomics

Claude Code helps generate and validate metadata,
but the structure itself is deterministic and human-readable.

No hidden state.
No opaque binary models.

Structure Reduces Fear

When:

Every entity has a folder
Every transformation has a mapping
Every relationship is explicit
Every script is reproducible

Change becomes less intimidating.
Because structure is visible.

Next in the Series

In the next article: From dbt to MDDE — Bridging Code-First and Metadata-First Approaches

We’ll explore:

How MDDE imports dbt projects
How enriched metadata improves governance
How export keeps dbt execution intact
Why this is not an either/or decision

🔧 Technical Deep Dive — Round-Trip Safety

Each entity has stable technical identifiers.

Inspect Entity Identity

SELECT  
    entity_id,  
    entity_name,  
    origin_type,  
    origin_id  
FROM metadata.entity  
WHERE entity_name = 'customer';

origin_id ensures round-trip integrity when:

Re-importing SQL
Syncing with external tools
Regenerating YAML
Comparing versions

No accidental duplication.
No identity drift.

Example: Safe Reimport

python -m mdde.importer.sql_importer \  
    --model-id mdl_sales \  
    --db databases/meta.duckdb

Entities are matched by entity_id, not name alone.
Structural continuity is preserved.