How to maintain traceability and automation between business concepts and implementation models using YAML

🧩 Metadata-Driven Modeling Series

A 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️⃣ Generating Logical Models Automatically from Conceptual Metadata — (coming soon): implementing Alec Sharp’s normalization patterns (1NF–2NF–3NF) in Python.

Summary

In the previous article, we explored how conceptual and logical data models evolve — from business understanding to technical precision — drawing on Alec Sharp’s Business-Oriented Data Modelling principles.

In this follow-up, we’ll translate that philosophy into metadata-as-code: how to represent both conceptual and logical data models in YAML, and how to link them to preserve business meaning all the way down to the database layer.

1. Why Linking Matters

When models become code, something is often lost.
We capture entities, attributes, and relationships — but the business intent that shaped them gets buried under structure and syntax.

By explicitly linking the Conceptual Data Model (CDM) and Logical Data Model (LDM) in metadata, we can preserve that lineage.
Every table, column, and key can trace back to the business concept that defined it.

This makes metadata:

• Traceable — each logical element has a conceptual origin.
• Automatable — logical models can be generated or validated from conceptual ones.
• Governable — changes to business concepts ripple through models predictably.

2. Folder Structure for Metadata Models

We organize YAML-based models into distinct layers in VSCode:

models/
├── conceptual/
│ ├── customer.yaml
│ └── order.yaml
├── logical/
│ ├── customer.yaml
│ ├── contact_point.yaml
│ └── order.yaml
└── physical/
├── customer.sql
├── contact_point.sql
└── order.sql

Each folder represents a level of abstraction:

• Conceptual → Business vocabulary and policies
• Logical → Structured, normalized, and precise model
• Physical → Platform-specific SQL or schema code

3. The Conceptual Model in YAML

A conceptual YAML file defines the business “things” and their relationships — free of technical constraints.

models/conceptual/customer.yaml

layer: conceptual
entity: Customer
description: A person or organization with whom we maintain a business relationship.
attributes:
– name: FullName
description: The customer’s full name.
– name: ContactPoints
description: Ways the customer can be contacted (email, phone, etc.).
relationships:
– name: places_order
target: Order
type: 1:M
normalization_hints:
multi_valued: [ContactPoints]
redundant: []
constrained: []

This model describes the business view, not the data structure.
No keys, data types, or normalization — just concepts, relationships, and high-level hints.

4. The Logical Model in YAML

The logical model builds on the conceptual one, adding structure and normalization. Each logical element references its conceptual origin using derived_from or conceptual_link.

models/logical/customer.yaml

layer: logical
entity: Customer
derived_from: conceptual.Customer
keys:
– name: CustomerID
type: surrogate
attributes:
– name: FirstName
datatype: string
atomic: true
derived_from: conceptual.Customer.FullName
– name: LastName
datatype: string
atomic: true
derived_from: conceptual.Customer.FullName
relationships:
– name: has_contact
target: ContactPoint
type: 1:M
conceptual_link: conceptual.Customer.ContactPoints

This linkage provides semantic lineage:

• derived_from points to the conceptual entity or attribute.
• conceptual_link ties relationships to conceptual definitions.

You can now query metadata to ask:

“Which logical entities or columns derive from the business concept Customer_?”_

5. Validation with Schema Rules

To enforce consistency across layers, we define a JSON Schema (or Pydantic model) describing allowed properties and constraints.

Here’s a simplified example:

{
“type”: “object”,
“required”: [“layer”, “entity”],
“properties”: {
“layer”: { “enum”: [“conceptual”, “logical”, “physical”] },
“entity”: { “pattern”: “^[A-Z][A-Za-z0-9_]$” },
“derived_from”: { “pattern”: “^conceptual\.[A-Z][A-Za-z0-9_](\.[A-Za-z0-9_]+)?$” },
“attributes”: {
“type”: “array”,
“items”: {
“properties”: {
“name”: { “pattern”: “^[A-Za-z0-9_]+$” },
“derived_from”: { “pattern”: “^conceptual\.” }
}
}
}
}
}

You can run validation automatically using a pre-commit hook or CLI:

python tools/validate.py models/conceptual/.yaml models/logical/.yaml

Invalid models (missing derived_from, wrong layer metadata, or unnormalized attributes) are flagged before they ever reach version control.

6. Automating the Transformation

With linkage and normalization hints in place, scripts can generate the logical model automatically:

• Move multi-valued attributes → to dependent entities (1NF).
• Move redundant attributes → to parent entities (2NF).
• Move constrained attributes → to reference entities (3NF).

The resulting logical YAMLs are enriched with keys, atomic attributes, and referential relationships — all derived systematically from conceptual metadata.

7. Benefits of Metadata Linkage

Press enter or click to view image in full size

By codifying Alec Sharp’s conceptual rigor into metadata, we turn business modeling into a living, executable artifact.

8. From Modeling to Generation

Once models are validated, they can drive:

• SQL and dbt model generation
• draw.io or Mermaid diagrams
• Notion documentation
• Lineage graphs or dashboards

Each logical model, entity, and attribute carries its conceptual DNA — ready to be transformed into any downstream format.

9. In Summary

The transition from conceptual clarity to logical precision doesn’t have to be manual or fragile. By representing models as YAML and enforcing lineage with validation rules, we connect the worlds of business modeling and data engineering.

This approach keeps data architecture both transparent and executable — ensuring every physical table remains anchored in the concepts that matter most.

10. Next in the Series

Generating Logical Models Automatically from Conceptual Metadata (coming soon) — how to implement Alec Sharp’s 1NF–2NF–3NF normalization patterns in Python to turn conceptual YAMLs into logical models.