How to keep conceptual, logical, and physical data models synchronized and collaborative in modern metadata-as-code environments
🧩 Metadata-Driven Modeling Series
A continuing series exploring how modern data platforms connect business meaning and technical implementation using metadata-as-code.
1️⃣ From Business Concepts to Database Blueprint — bridging conceptual and logical models inspired by Alec Sharp.
2️⃣ Linking Conceptual and Logical Data Models in Metadata — maintaining traceability and automation between business concepts and implementation models using YAML.
3️⃣ Scaling Metadata-Driven Data Modeling — this article.
4️⃣ Generating Logical Models Automatically from Conceptual Metadata — coming soon.
1. The Question That Sparked This Post
In a recent LinkedIn discussion, Simon Bloomer asked:
“How do you deal with conceptual and logical models and sync with physical implementation?
And is this scalable to have a team of 10+ modellers working on the same models — maybe even on the same entities?”
It’s a question many teams face today. Traditional modeling tools like Erwin, PowerDesigner, and ER/Studio still excel at visual clarity, but they struggle to live alongside dbt-centric, Git-based data platforms where everything — code, tests, pipelines — is version-controlled.
So, how do we bring conceptual discipline, logical structure, and physical automation together in one sustainable workflow?
2. Models as Code — The Foundation
The answer starts with treating models as first-class code assets.
Each modeling layer lives as a set of YAML files under Git, with explicit links between layers:
models/
├── conceptual/
│ ├── customer.yaml
│ └── order.yaml
├── logical/
│ ├── customer.yaml
│ ├── contact_point.yaml
│ └── order.yaml
└── physical/
├── customer.sql
├── contact_point.sql
└── order.sql
Every logical element references its conceptual origin through a derived_from or conceptual_link property — preserving semantic lineage from business definition to implementation.
Validation schemas (JSON Schema or Pydantic) act as contracts: every YAML file must comply before it can be merged.
3. Traceability and Automation
This structure allows for traceable automation:
- Conceptual layer defines business meaning and relationships.
- Logical layer adds structure, normalization, and keys while retaining conceptual links.
- Physical layer (dbt or SQL) references its logical sources, ensuring end-to-end lineage.
From the same metadata we can:
- Generate ERDs and mapping diagrams automatically in draw.io or Mermaid.
- Create dbt models or SQL templates directly from YAML.
- Publish documentation and lineage dashboards in Confluence or Notion.
Every table and column remains semantically grounded in its original business concept.
4. Collaboration at Scale
Simon’s second question — can this scale for 10+ modelers? — is key.
The short answer: yes, because the workflow is Git-native.
We apply the same principles software engineers use for code collaboration to metadata:
- Branch-per-change. Each modeler works on a separate branch.
- Pull requests. Every change is reviewed and validated before merge.
- Automated checks. Pre-commit hooks validate structure, lineage, and normalization.
- Continuous Metadata Integration (CMI). CI pipelines test, diagram, and publish models automatically — the metadata equivalent of Continuous Integration.
- Fine-grained files. One entity per YAML (or smaller) minimizes merge conflicts and supports parallel development.
This turns classical Work-in-Progress modeling into a modern, auditable process that scales effortlessly across teams and repositories.
5. The Human Workflow
In this setup, collaboration is layered but continuous:
Press enter or click to view image in full size
Because everything is stored as text, the models integrate seamlessly with modern DevOps: automated testing, semantic diffing, and change traceability become trivial.
6. A Living System, Not Static Documentation
Once models are stored as executable metadata:
- Any YAML edit can regenerate documentation, lineage, or SQL automatically.
- Validation ensures that conceptual and logical layers remain synchronized.
- The system becomes self-documenting — each change is versioned, reviewable, and reversible.
This isn’t replacing traditional modeling discipline — it’s re-engineering it for the metadata age.
7. From Discussion to Implementation
I’m currently working on the practical implementation of this concept: automatically generating logical models from conceptual metadata in Python, implementing Alec Sharp’s normalization patterns (1NF → 2NF → 3NF).
That next article will show how this linkage works in code — turning business definitions directly into normalized logical models ready for dbt or other downstream generation.
Stay tuned for: “Generating Logical Models Automatically from Conceptual Metadata” — coming soon.
8. Related Reading
If you’d like to explore the foundations of this approach:
🧠 Linking Conceptual and Logical Data Models in Metadata
🤝 Collaborative Data Modeling in VS Code — Bridging Modelers and Engineers through Metadata
🔄 Continuous Metadata Integration — From Work-in-Progress Modeling to Git-Driven Metadata Management
9. In Summary
Classic data modeling tools aren’t dead — but the way we use them is evolving. By managing conceptual, logical, and physical models as metadata under version control, we preserve business meaning, automate consistency, and enable true collaboration at scale.
This is where data modeling meets DevOps — a living, versioned, executable architecture where models are both human-readable and machine-actionable.