Querying the Metadata Itself Building a Metadata Catalog with DuckDB
How to turn your YAML-based data models into a searchable, queryable, and visual metadata repository
Summary
In the previous article, we published conceptual, logical, and physical data models to Notion and Confluence — making them accessible to the wider organization.
Now we’ll take the next leap: treating metadata as data.By loading your YAML files into DuckDB, you can query, analyze, and visualize your entire modeling landscape — from conceptual entities to SQL tables — using standard SQL.
1. Why Query Metadata?
When metadata is just a collection of YAML files, it’s human-readable — but not easily searchable, analyzable, or visualizable.
By transforming it into a queryable dataset, you can answer powerful questions:
- “Which logical attributes derive from the concept Customer?”
- “Which entities have no primary key defined?”
- “Which relationships are missing lineage?”
- “How many tables are generated from conceptual entities per domain?”
And because DuckDB is embedded and lightning-fast, you can perform this analysis locally — even inside VSCode.
2. The Metadata Pipeline in Context
We now have a complete metadata flow:
YAML → Logical Model → SQL / Diagrams → Notion / Confluence → DuckDB Catalog
Each layer enriches the previous one. DuckDB acts as the analytics layer for your modeling metadata — enabling ad hoc queries, lineage exploration, and dashboards.
3. Defining the DuckDB Schema
Let’s create a lightweight schema to store our metadata.
Each YAML file becomes one or more rows in DuckDB tables.
CREATE SCHEMA IF NOT EXISTS metadata;
CREATE TABLE metadata.entities (
layer TEXT,
entity TEXT,
description TEXT,
derived_from TEXT
);
CREATE TABLE metadata.attributes (
entity TEXT,
name TEXT,
datatype TEXT,
atomic BOOLEAN,
derived_from TEXT
);
CREATE TABLE metadata.relationships (
entity TEXT,
name TEXT,
target TEXT,
type TEXT,
conceptual_link TEXT
);
This mirrors the YAML structure and keeps the model extensible for future layers (e.g. physical tables or Notion URLs).
4. Loading YAML into DuckDB
Here’s a compact Python script to load all YAMLs into the DuckDB catalog:
import yaml, duckdb, pathlib
db = duckdb.connect(“data/metadata_catalog.duckdb”)
def insert_entities(conn, folder):
for f in pathlib.Path(folder).glob(“*.yaml”):
data = yaml.safe_load(open(f))
conn.execute(“INSERT INTO metadata.entities VALUES (?, ?, ?, ?)”,
[data[“layer”], data[“entity”], data.get(“description”,”“),
data.get(“derived_from”,”“)])
for a in data.get(“attributes”, []):
conn.execute(“INSERT INTO metadata.attributes VALUES (?, ?, ?, ?, ?)”,
[data[“entity”], a[“name”], a.get(“datatype”,”string”),
a.get(“atomic”, False), a.get(“derived_from”,”“)])
for r in data.get(“relationships”, []):
conn.execute(“INSERT INTO metadata.relationships VALUES (?, ?, ?, ?, ?)”,
[data[“entity”], r[“name”], r[“target”],
r.get(“type”,”“), r.get(“conceptual_link”,”“)])
insert_entities(db, “models/conceptual”)
insert_entities(db, “models/logical”)
insert_entities(db, “models/physical”)
print(“✅ Metadata loaded into DuckDB”)
This script automatically parses every YAML file in your model directories and populates the DuckDB schema.
5. Example Queries
Once the data is in DuckDB, the fun begins.
1️⃣ Trace lineage from logical to conceptual:
SELECT a.entity, a.name, a.derived_from
FROM metadata.attributes a
WHERE a.derived_from LIKE ‘conceptual.Customer%’;
2️⃣ Check entities missing primary keys:
SELECT entity
FROM metadata.entities e
WHERE e.entity NOT IN (
SELECT DISTINCT entity FROM metadata.attributes WHERE name ILIKE ‘%id%’
);
3️⃣ Find conceptual entities with no logical descendants:
SELECT e.entity
FROM metadata.entities e
WHERE e.layer = ‘conceptual’
AND e.entity NOT IN (
SELECT DISTINCT derived_from
FROM metadata.entities WHERE layer = ‘logical’
);
4️⃣ Count entities by layer:
SELECT layer, COUNT(*) AS entities
FROM metadata.entities
GROUP BY layer;
You can even export results as Parquet or visualize them with tools like DuckDB’s built-in Plot, Observable, or Streamlit.
6. Integrating the Metadata Catalog in VSCode
Because DuckDB runs locally, you can integrate it directly into your VSCode workspace:
.vscode/
├── settings.json
├── metadata/
│ ├── catalog.duckdb
│ └── queries/
│ ├── lineage.sql
│ ├── completeness.sql
│ └── entities_per_layer.sql
This turns your IDE into a metadata command center — where you can explore lineage, validate completeness, and even measure modeling quality.
7. Visualizing Metadata Relationships
You can also use the catalog to auto-generate relationship diagrams.
For example, using Graphviz or Mermaid:
import duckdb
conn = duckdb.connect(“data/metadata_catalog.duckdb”)
rows = conn.execute(“”“
SELECT entity, target, type
FROM metadata.relationships
WHERE type IN (‘1:M’, ‘M:1’)
“”“).fetchall()
with open(“docs/relationships.mmd”, “w”) as f:
f.write(“graph TD\n”)
for e, t, typ in rows:
arrow = “–>” if typ == “1:M” else “<–“
f.write(f” {e} {arrow} {t}\n”)
Render the result as a live ER-style diagram — all sourced from your metadata catalog.
8. Connecting to Notion or BI Tools
Because DuckDB supports SQL over Parquet and CSV, you can export the metadata for use in:
- 🧭 Notion dashboards (via CSV embed)
- 📊 Power BI or Tableau for lineage metrics
- 🧩 Grafana for modeling KPIs (e.g. completeness %)
Example export:
COPY (SELECT * FROM metadata.entities) TO ‘export/entities.parquet’ (FORMAT PARQUET);
9. Why This Matters
By turning your metadata into data, you gain:
This approach makes your modeling ecosystem auditable, queryable, and self-describing.
10. In Summary
With DuckDB, we’ve transformed our static metadata files into a living catalog:
- Queryable: You can explore models like data.
- Traceable: Every link between conceptual, logical, and physical layers is explicit.
- Actionable: Insights drive governance, documentation, and automation.
Metadata modeling doesn’t end with YAML — it’s just the beginning of a metadata intelligence system.
11. Next in the Series
“Metadata Observability: Measuring Quality, Completeness, and Change in Your Data Models.”
We’ll explore how to detect inconsistencies, missing lineage, and evolving concepts automatically using DuckDB queries and metadata rules.