How to translate business concepts into normalized data structures using Python and YAML
In the first two parts of this series, we explored how to:
1️⃣ Capture business concepts in a Conceptual Data Model (CDM)
2️⃣ Maintain lineage between conceptual and logical models in YAML metadataNow, we’ll take the next step: automating the transformation from conceptual to logical.
This article shows how to implement Alec Sharp’s normalization process — the same reasoning a skilled data modeler applies manually — using a Python-based generator that converts conceptual YAMLs into fully normalized logical models.
1. The Goal: From One Model to Many
A Conceptual Model is compact — often a one-pager with 10–15 entities.
A Logical Model, by contrast, might expand that to 50 or more entities after applying normalization and relationship resolution.
Instead of building those logical entities by hand, we can generate them systematically using metadata patterns.
This is the essence of metadata-driven modeling:
“If you can describe it, you can generate it.”
2. Revisiting Alec Sharp’s Normalization Steps
Alec Sharp teaches that the transition from conceptual to logical modeling is largely about resolving three attribute types:
Press enter or click to view image in full size
Each step can be captured as a rule that transforms metadata.
3. Annotating Conceptual Models with Normalization Hints
In our framework, conceptual YAMLs can include normalization_hints to guide these transformations:
layer: conceptual
entity: Customer
attributes:
– name: FullName
description: Business label for the person or company
– name: ContactPoints
description: Customer may have multiple contact methods
– name: Type
description: Full-time or part-time customer
relationships:
– name: places_order
target: Order
type: 1:M
normalization_hints:
multi_valued: [ContactPoints]
constrained: [Type]
redundant: []
This gives your automation script explicit instructions about which attributes require normalization.
4. Python Transformation Logic
Here’s a simplified version of the generator that reads conceptual YAMLs and produces logical YAMLs.
import yaml, pathlib
def generate_logical(conceptual):
entity = conceptual[“entity”]
logical = {
“layer”: “logical”,
“entity”: entity,
“derived_from”: f”conceptual.{entity}”,
“keys”: [{“name”: f”{entity}ID”, “type”: “surrogate”}],
“attributes”: [],
“relationships”: []
}
# Step 1: Resolve multi-valued attributes → characteristic entities
for attr in conceptual.get(“normalization_hints”, {}).get(“multi_valued”, []):
sub_entity = f”{entity}{attr}”
logical[“relationships”].append({
“name”: f”has_{attr.lower()}”,
“target”: sub_entity,
“type”: “1:M”,
“conceptual_link”: f”conceptual.{entity}.{attr}”
})
# Step 2: Copy atomic attributes
for attr in conceptual[“attributes”]:
if attr[“name”] not in conceptual.get(“normalization_hints”, {}).get(“multi_valued”, []):
logical[“attributes”].append({
“name”: attr[“name”],
“atomic”: True,
“derived_from”: f”conceptual.{entity}.{attr[‘name’]}”
})
return logical
def main():
conceptual_dir = pathlib.Path(“models/conceptual”)
logical_dir = pathlib.Path(“models/logical”)
logical_dir.mkdir(exist_ok=True, parents=True)
for file in conceptual_dir.glob(“*.yaml”):
with open(file) as f:
conceptual = yaml.safe_load(f)
logical = generate_logical(conceptual)
outfile = logical_dir / file.name
with open(outfile, “w”) as f:
yaml.safe_dump(logical, f)
print(f”Generated: {outfile}”)
if name == “main”:
main()
This script:
- Reads all conceptual YAMLs
- Applies the normalization rules
- Writes corresponding logical YAMLs, adding
derived_fromlinks automatically
5. Example Output
From the conceptual model above, the script would generate:
layer: logical
entity: Customer
derived_from: conceptual.Customer
keys:
– name: CustomerID
type: surrogate
attributes:
– name: FullName
atomic: true
derived_from: conceptual.Customer.FullName
– name: Type
atomic: true
derived_from: conceptual.Customer.Type
relationships:
– name: has_contactpoints
target: CustomerContactPoints
type: 1:M
conceptual_link: conceptual.Customer.ContactPoints
And create a new entity for the multi-valued attribute:
layer: logical
entity: CustomerContactPoints
derived_from: conceptual.Customer.ContactPoints
keys:
– name: ContactPointID
type: surrogate
attributes:
– name: ContactValue
atomic: true
derived_from: conceptual.Customer.ContactPoints
relationships:
– name: belongs_to_customer
target: Customer
type: M:1
6. Optional Enhancements
You can extend this approach easily:
- Add 2NF logic: move redundant attributes “up” to parent entities.
- Add 3NF logic: create reference entities for constrained attributes.
- Visualize transformations: generate draw.io or Mermaid diagrams automatically.
- Integrate into CI/CD: run normalization and validation on every commit.
This turns your modeling framework into a living metadata compiler.
7. Why This Approach Works
The beauty of this method is that it reflects the mental process of a data modeler — but codified as repeatable logic.
The conceptual model remains the single source of truth, while logical models are generated, validated, and reproducible.
It enforces three powerful principles:
- Consistency — every model follows the same structural rules
- Transparency — each attribute is traceable to its conceptual origin
- Adaptability — business changes can be regenerated instantly
8. Example Workflow in VSCode
models/
├── conceptual/
│ ├── customer.yaml
│ └── order.yaml
├── logical/
│ ├── (auto-generated)
└── tools/
├── generate_logical.py
└── validate.py
Developers or data modelers edit the conceptual models. A CI pipeline runs generate_logical.py to update the logical layer and validate.py to enforce schema rules before commit.
9. In Summary
We’ve now closed the loop:
- Part 1: From business concepts to logical structure (Alec Sharp’s philosophy)
- Part 2: Linking conceptual and logical models with YAML and schema validation
- Part 3: Automating the transformation with normalization logic
By representing and generating models as metadata, we make data architecture living, executable, and auditable — bridging the whiteboard and the data warehouse.
10. Coming Next
From YAML to SQL: Generating Physical Models and Diagrams from Metadata (coming soon) — We’ll show how to take these logical YAMLs and generate platform-specific DDL (Databricks, Snowflake, Postgres) — and even visualize the results automatically.