How to connect data validation rules directly to conceptual, logical, and physical models
Summary
In the previous article, we visualized metadata lineage across conceptual, logical, and physical models — revealing how data flows through the system.
Now, we’ll take that lineage one step further and make it actionable: using it to drive data quality checks and monitoring logic.
When your quality rules are connected to metadata lineage, you can test data integrity automatically — in the right place, at the right layer, with full traceability back to the business meaning.
1. From Modeling to Monitoring
Traditional data quality checks often live in isolation — in SQL scripts, ETL jobs, or dashboards. In a metadata-driven architecture, those rules become metadata themselves: documented, traceable, and linked to the entities and attributes they protect.
With lineage in place, you can answer:
- “Which data quality checks cover this business concept?”
- “What percentage of logical attributes are validated?”
- “Which data issues trace back to a conceptual gap?”
Data quality becomes a metadata-linked contract between business and engineering.
2. Extending the Metadata Model for Quality
Let’s extend the YAML structure to include a rules section that describes validation logic and monitoring expectations.
Example: logical model with quality rules
layer: logical
entity: Customer
derived_from: conceptual.Customer
keys:
– name: CustomerID
type: surrogate
attributes:
– name: Email
datatype: string
atomic: true
derived_from: conceptual.Customer.ContactPoints
rules:
– name: valid_email_format
type: pattern
expression: “^[^@\s]+@[^@\s]+\.[^@\s]+$”
severity: warning
– name: not_null
type: completeness
expression: “Email IS NOT NULL”
severity: error
– name: DateOfBirth
datatype: date
atomic: true
rules:
– name: valid_birth_date
type: range
expression: “DateOfBirth BETWEEN ‘1900-01-01’ AND CURRENT_DATE”
This makes quality rules first-class citizens in your metadata layer — version-controlled, documented, and traceable.
3. Loading Quality Rules into DuckDB
We can now extract these rules into a metadata.quality_rules table:
CREATE TABLE metadata.quality_rules (
entity TEXT,
attribute TEXT,
name TEXT,
type TEXT,
expression TEXT,
severity TEXT,
derived_from TEXT
);
Python loader snippet:
import yaml, duckdb, pathlib
conn = duckdb.connect(“data/metadata_catalog.duckdb”)
def insert_quality_rules(folder):
for f in pathlib.Path(folder).glob(“*.yaml”):
data = yaml.safe_load(open(f))
for attr in data.get(“attributes”, []):
for rule in attr.get(“rules”, []):
conn.execute(“”“
INSERT INTO metadata.quality_rules
VALUES (?, ?, ?, ?, ?, ?, ?)
“”“, [
data[“entity”],
attr[“name”],
rule[“name”],
rule[“type”],
rule[“expression”],
rule.get(“severity”, “info”),
attr.get(“derived_from”, “”)
])
insert_quality_rules(“models/logical”)
print(“✅ Quality rules loaded into DuckDB”)
4. Evaluating Rules Automatically
You can then apply these rules directly to data in your data lake, warehouse, or test datasets.
Example using DuckDB to run quality checks dynamically:
def run_quality_checks(conn, table_name):
rules = conn.execute(“SELECT * FROM metadata.quality_rules WHERE entity = ?”, [table_name]).fetchall()
for entity, attr, name, typ, expr, sev, _ in rules:
query = f”SELECT COUNT(*) FROM {entity} WHERE NOT ({expr})”
result = conn.execute(query).fetchone()[0]
if result > 0:
print(f”❌ {entity}.{attr} failed {name} ({result} rows) [{sev}]”)
else:
print(f”✅ {entity}.{attr} passed {name}”)
This turns your metadata catalog into a data quality engine — automatically interpreting and running validation logic.
5. Linking Quality Rules to Lineage
Because each rule is tied to a derived_from field, you can trace where it originated:
SELECT q.entity, q.attribute, q.name, e.derived_from
FROM metadata.quality_rules q
JOIN metadata.entities e ON q.entity = e.entity;
This shows whether a rule is defined at:
- the conceptual layer (business policy),
- the logical layer (data structure constraint),
- or the physical layer (SQL implementation).
You can visualize this linkage in your lineage graph — with rule nodes attached to their corresponding attributes or entities.
6. Monitoring Metadata Quality Itself
Once rules are in DuckDB, you can measure rule coverage and compliance trends over time.
Coverage metric:
SELECT layer, COUNT(DISTINCT entity) AS entities, COUNT(r.name) AS rules
FROM metadata.entities e
LEFT JOIN metadata.quality_rules r ON e.entity = r.entity
GROUP BY layer;
Failure summary:
SELECT severity, COUNT(*) AS failed_checks
FROM quality_results
GROUP BY severity;
This produces dashboards showing which entities are best covered, which rules fail most often, and where business policies need reinforcement.
7. Publishing Quality Results to Notion or Confluence
Just like before, you can publish these results automatically:
- Each entity page in Notion includes:
- Rule definitions
- Last validation timestamp
- Failure counts
- Links to upstream and downstream entities
- Each Confluence page includes a “Data Quality” section embedded from your DuckDB exports.
Example Markdown block added during publishing:
Data Quality Summary
| Rule | Type | Severity | Status |
|---|---|---|---|
| valid_email_format | pattern | warning | ✅ |
| not_null | completeness | error | ✅ |
| valid_birth_date | range | warning | ⚠️ |
8. Building a Unified Monitoring Dashboard
With lineage, metadata, and quality in one database, you can visualize everything in one dashboard:
This connects business rules and technical validation in one ecosystem — the same YAMLs that describe your models also describe how to keep them healthy.
9. Why This Approach Works
By integrating lineage with data quality:
✅ Rules become self-documenting metadata
🧭 Every check has business lineage
🧩 Validation logic is consistent across layers
⚙️ Quality checks evolve automatically with models
In other words, your metadata no longer just describes data — it protects it.
10. In Summary
We’ve now added the final operational layer to the metadata-driven framework:
- Conceptual clarity
- Logical automation
- Physical generation
- Observability and lineage
- Data quality and monitoring
Together, these form a closed feedback loop between modeling, implementation, and validation — a fully traceable, self-monitoring metadata ecosystem.
11. Next in the Series
“From Metadata to Metrics: Building KPIs for Model Health, Reuse, and Evolution.”
We’ll show how to quantify model maturity and improvement over time — turning metadata itself into an analytical subject.