design-medallion-lakehouse

name

design-medallion-lakehouse

description

Design a Databricks lakehouse from the query and freshness SLO backward: the medallion layering (what earns bronze/silver/gold), the Delta table & partitioning strategy per layer (low-cardinality partition vs liquid clustering, OPTIMIZE/Z-ORDER/VACUUM cadence, MERGE/CDC vs append/overwrite write pattern), the batch-vs-streaming call (scheduled batch is the default; Auto Loader / Structured Streaming / DLT only for a real sub-hour SLO), the Unity Catalog governance layout (catalog/schema, managed vs external, grants to groups, PII tagging), and the compute + DBU cost envelope. Reach for this when the ask is 'how should we structure this on Databricks?', 'bronze/silver/gold + table layout', 'batch or streaming?', or 'Unity Catalog setup'. Used by `lakehouse-architect` (primary).

Skill: design-medallion-lakehouse

Invoked by: lakehouse-architect (primary). Consulted by databricks-platform-engineer for the layer/table contract the code must honor.

When to invoke: "how should we structure this on Databricks?"; "bronze/silver/gold + how to lay out the Delta tables"; "batch, streaming, Auto Loader, or DLT?"; "how do we set up Unity Catalog?"; "what compute and what will it cost?"

Output: a medallion design + per-layer Delta table strategy + batch-vs-streaming call + Unity Catalog governance layout + compute/DBU cost envelope, captured in the lakehouse design template.

Procedure

1. Start from the reader and the SLO, not the tool. Who queries this, at what shape, and how fresh must it be? Write those down first — the whole design flows from them. Traverse ../../knowledge/databricks-decision-tree.md.
2. Run the batch-vs-streaming gate (Tree 1). Default to a scheduled batch job. Only choose Auto Loader (files landing), Structured Streaming (continuous events), or DLT (declarative managed) when a real sub-hour SLO forces it — and name the checkpoint/trigger/exactly-once implication when you do.
3. Layer with the medallion pattern (Tree 2). Bronze = raw, append-only, replayable. Silver = conformed, deduped, validated, typed. Gold = serving tables shaped to the reader. Justify each layer by what it earns; don't collapse a replay-valuable hop, don't add a gold table no one reads.
4. Design each Delta table for its queries (Tree 3). Partition only on a low-cardinality column that filters real queries — or use liquid clustering (verify GA/behavior at use). Never partition on a high-cardinality column (small-file explosion). Name file-size target, OPTIMIZE/Z-ORDER/VACUUM cadence, and the write pattern (MERGE/upsert/CDC vs append vs overwrite).
5. Lay out Unity Catalog governance from the start. Catalog-per-env or per-domain, schema boundaries, managed vs external tables/volumes, grants to groups not users, lineage/audit, and PII tagging. Retrofitting governance is expensive.
6. Set the compute & DBU envelope (Tree 5). Jobs compute for jobs, serverless/right-sized SQL warehouse for BI, Photon where it pays. Autoscaling bounds + spot + auto-termination. Give an order-of-magnitude DBU estimate and name the top cost drivers. Mark all pricing verify-at-use + dated.
7. State the seams and flip conditions. dbt gold modeling → analytics-engineering; external orchestration → data-orchestration; org privacy policy → data-governance-privacy; ML lifecycle → ml-engineering; Fabric/OneLake → microsoft-fabric. Name the 1-2 facts that would flip each call.

Worked example

User: "We get order events from Kafka and want a dashboard of daily revenue by region, plus the raw data kept for audits. Build it on Databricks."

• Reader & SLO: BI dashboard, refreshed daily is fine; audit needs raw retained. No sub-hour requirement.
• Batch-vs-streaming (Tree 1): despite the Kafka source, the SLO is daily → a scheduled batch read (or Trigger.AvailableNow stream-once) is cheaper than an always-on stream. Don't reflex "streaming" from "Kafka."
• Layering (Tree 2): Bronze = raw Kafka payloads appended (the audit anchor). Silver = parsed, deduped, typed orders (MERGE on order_id). Gold = daily_revenue_by_region aggregate shaped for the dashboard.
• Table design (Tree 3): silver partitioned by order_date (low-cardinality, filters queries); not by order_id (high-cardinality → tiny files). OPTIMIZE/Z-ORDER (region) nightly; VACUUM at safe retention. Gold overwritten daily.
• Unity Catalog: prod.orders catalog/schema; grants to the analytics group; PII (customer id) tagged and masked in gold.
• Compute/DBU: a small jobs-compute cluster on a nightly schedule with auto-termination; a right-sized SQL warehouse (auto-stop) for the dashboard; Photon on for the SQL aggregate. Top cost risk: leaving the warehouse always-on. (DBU figures verify-at-use.)
• Seams: if analysts want to model the gold layer in dbt → analytics-engineering.

Guardrails

• Batch is the default — streaming is earned by a real sub-hour SLO, not the word "real-time."
• Never over-partition — a high-cardinality partition column is the most common self-inflicted small-file wound.
• Governance is designed in, not bolted on — Unity Catalog layout + grants-to-groups + PII tagging from the start.
• Auto-terminate all compute — idle clusters/warehouses are the top DBU leak.
• Every runtime/feature/pricing fact carries a retrieval date + verify-at-use — DBR versions, liquid clustering/predictive-optimization GA, and DBU pricing move. See ../../knowledge/databricks-patterns-2026.md.
• The design is the architect's; the code, dbt modeling, org policy, and ML lifecycle leave this skill — route to databricks-platform-engineer / analytics-engineering / data-governance-privacy / ml-engineering.