Lakehouse Data Modeling

Medallion architecture with real semantics

Everyone can recite Bronze/Silver/Gold. What separates a strong answer is precise semantics per layer — what guarantees each layer makes and to whom. Treat each layer as a contract:

The point of Bronze being append-only and replayable is operational: when a Silver transform has a bug, you fix the code and rebuild Silver from Bronze. You never ask SAP to resend three years of journal entries. That replay argument is the one to lead with in interviews.

Dimensional modeling still matters on the lakehouse

Delta did not repeal Kimball. Star schemas survive because BI tools, the columnar engines underneath them, and humans all reason best over facts joined to conformed dimensions. What changes on Databricks:

• Facts and dimensions are Delta tables in Unity Catalog — usually managed tables so predictive optimization handles OPTIMIZE/VACUUM, with liquid clustering (CLUSTER BY AUTO for new tables) on the common join and filter keys instead of legacy partitioning + Z-ORDER.
• Surrogate keys via identity columns. GENERATED ALWAYS AS IDENTITY gives you monotonically increasing surrogate keys without a sequence service. Caveat worth volunteering: identity columns serialize concurrent writes to that table, so design dimension loads as a single writer.
• Conformed dimensions are the cure for gold sprawl. One dim_company, one dim_account, one dim_date, shared across marts so "Budget vs Actual by legal entity" means the same thing in every dashboard.
• Wide denormalized tables are fine for Gold serving, but keep the dimensional model in Silver/Gold as the source of those flattened views — denormalize last, not first.

SCD Type 1 and Type 2 on Delta

Type 1 (overwrite, no history) is a plain MERGE INTO: update on match, insert on no match. Use it for correction-style attributes where history has no business meaning.

Type 2 (full history with validity windows) is the pattern interviews drill. The classic single-statement trick: union the staged changes with a NULL-keyed copy, so one source row expires the current version and its NULL-keyed twin inserts the new version.

-- Staged changes already deduplicated to one row per business key
MERGE INTO silver.dim_customer AS tgt
USING (
  -- Rows that match current records: used to close them out
  SELECT s.customer_id AS merge_key, s.*
  FROM stg_customer_changes s

  UNION ALL

  -- NULL merge_key never matches, so these fall through to INSERT
  SELECT NULL AS merge_key, s.*
  FROM stg_customer_changes s
  JOIN silver.dim_customer t
    ON  s.customer_id = t.customer_id
    AND t.is_current = true
  WHERE s.attr_hash <> t.attr_hash          -- only real changes spawn a new version
) AS src
ON  tgt.customer_id = src.merge_key
AND tgt.is_current  = true

WHEN MATCHED AND tgt.attr_hash <> src.attr_hash THEN
  UPDATE SET tgt.is_current = false,
             tgt.valid_to   = src.change_ts

WHEN NOT MATCHED THEN
  INSERT (customer_id, customer_name, segment, attr_hash,
          valid_from, valid_to, is_current)
  VALUES (src.customer_id, src.customer_name, src.segment, src.attr_hash,
          src.change_ts, TIMESTAMP'9999-12-31', true);
-- customer_sk is GENERATED ALWAYS AS IDENTITY: omit it from the INSERT list

Design notes that show seniority: compare an attr_hash (hash of tracked columns) instead of column-by-column comparison; pick a deterministic valid_to sentinel; keep is_current as a flag so BI filters are cheap; and cluster the dimension on the business key.

The declarative alternative: AUTO CDC in Lakeflow Spark Declarative Pipelines

Lakeflow Spark Declarative Pipelines (the product formerly called Delta Live Tables) gives you SCD1/SCD2 as a declaration instead of a hand-rolled MERGE. The current SQL API is AUTO CDC INTO (successor to APPLY CHANGES INTO); in Python the import moved from import dlt to from pyspark import pipelines as dp.

CREATE OR REFRESH STREAMING TABLE silver.dim_customer;

CREATE FLOW customer_cdc AS AUTO CDC INTO silver.dim_customer
FROM STREAM(bronze.customer_cdc_feed)
KEYS (customer_id)
APPLY AS DELETE WHEN op_code = 'D'
SEQUENCE BY commit_ts                  -- ordering column for out-of-order events
STORED AS SCD TYPE 2
TRACK HISTORY EXCEPT (op_code, commit_ts);

What it buys you: out-of-order event handling via SEQUENCE BY, automatic __START_AT/__END_AT columns, delete and truncate handling, and no MERGE choreography to maintain. Know both: hand-rolled MERGE proves you understand the mechanics; AUTO CDC proves you know the platform direction. Be honest about which you have run in production — for you that is MERGE-based pipelines orchestrated by ADF + Databricks Jobs; SDP/AUTO CDC is lab knowledge as of mid-2026.

CDC ingestion patterns

SCD logic is only half the story; the other half is how change data reaches Bronze:

• Log-based CDC tools — Qlik Replicate, GoldenGate, Debezium-style feeds. They emit insert/update/delete events with op codes and a sequence (LSN/SCN/commit timestamp). Land them append-only in Bronze; let Silver collapse them. Never apply CDC events directly to Bronze.
• Watermark / high-water-mark incremental loads — when the source has a reliable modified timestamp or ascending key, persist the max value loaded in a control table, extract WHERE modified_ts > :watermark, then advance the watermark only after a successful commit. Cheap and tool-free, but blind to hard deletes and to rows updated without touching the timestamp — say that limitation unprompted.
• Periodic full snapshots + diff — last resort for small reference data or sources with no CDC story; compute changes by hashing against the previous snapshot.
• Managed connectors — Lakeflow Connect now offers GA connectors (SQL Server, Salesforce, Workday and more); worth naming as the platform-native option even if your production patterns predate it.
• Late-arriving data — design for it, don't be surprised by it. For facts: a late journal line gets inserted with its true posting date, and aggregates are rebuilt for affected periods (keep Gold rebuilds partition- or cluster-aligned by period). For dimensions: late-arriving dimension members get an inferred "unknown" row inserted at fact-load time and enriched when the real attributes arrive — the classic early-arriving-fact fix.

Data quality as a design layer

Quality is a Silver-boundary concern: data should not earn the "Silver" label without passing checks.

• Delta constraints — NOT NULL and CHECK constraints enforce invariants at write time and fail the transaction. Use for must-never-break rules (no null business keys, amounts within sane bounds).
• Expectations — in Lakeflow Spark Declarative Pipelines, EXPECT ... ON VIOLATION DROP ROW / FAIL UPDATE gives you declared rules with metrics. Outside SDP, implement the same idea manually: a rules table evaluated in PySpark.
• Quarantine tables — instead of dropping bad rows, route them: split the DataFrame on the validation predicate, write failures to silver.customer_quarantine with the rule that failed and the source batch id, and alert when quarantine counts spike. Quarantine preserves auditability and lets stewards repair and replay.
• Reconciliation checks — control totals between layers: row counts and sum-of-amount comparisons Bronze→Silver→Gold per batch, persisted to an audit table, surfaced in monitoring. In finance this is non-negotiable (more below).

Metadata-driven pipeline frameworks

Enterprises with 50+ sources do not write 50 hand-crafted pipelines. They build one parameterized framework driven by configuration: a control schema holding source connection, object list, load type (full/incremental/CDC), watermark column, target layer/table, quality rules, and schedule. A generic ADF pipeline (or Lakeflow Job) iterates the config; generic PySpark handles each load pattern. Benefits: onboarding a new table is a config row not a code deploy, standards are enforced by construction, and the audit/reconciliation framework comes free. Costs: the framework is real software — it needs versioning, testing, and an owner; and truly bespoke transforms still need escape hatches to custom code. Saying both sides is what makes the answer senior.

Semantic layer strategy

Gold tables are not the end of the model — someone has to own metric definitions, hierarchies, and row-level security at the consumption edge. Two dominant enterprise patterns, plus a platform-native newcomer:

Watch the platform-native option too: Unity Catalog metric views (GA as of mid-2026) put governed metric definitions inside the catalog itself, queryable from SQL, AI/BI dashboards, and AI/BI Genie — a direct answer to "where do certified KPIs live so both dashboards and GenAI agents agree."

Finance differentiator: modeling the Universal Journal

Generic modeling answers are table stakes; finance-domain depth is your edge. What makes finance data hard:

• SAP ACDOCA (Universal Journal). One very wide line-item table unifying FI and CO: every posting carries ledger, company code, account, profit/cost center, and multiple parallel currency columns (transaction, company code, group currency). Model it as the atomic fact in Silver; do not pre-aggregate in Bronze. Key/cluster by company code and fiscal period; carry ledger and currency type explicitly so consumers cannot accidentally mix them.
• P&L hierarchies. Financial statement structures are parent-child hierarchies (account → node → statement line) that change over time. Flatten parent-child to level columns for BI, version the hierarchy (effective-dated bridge or SCD2 hierarchy dimension), and expect "restate history under the new hierarchy" as a standard ask — which means hierarchy joins at query time, not baked into facts.
• Actuals / Plan / Budget / Forecast variance models. Different sources (ERP actuals vs HFM / IBM Planning Analytics plans), different grains (daily document lines vs monthly cost-center plans), different update cadences. Conform them into one fact with a version dimension (Actual, Plan, Budget, Forecast-cycle) at a common grain, so variance is a pivot, not a join puzzle. Forecasts restate monthly — model forecast cycles as distinct version members, never overwrite.
• Restatements and reversals. Finance corrects via reversing documents and reposted periods, not UPDATEs. Your facts must be additive so a reversal nets out, and period-close means data for a "closed" month can still change until close completes — schedule rebuilds accordingly.
• Reconciliation to the penny. A CFO dashboard that differs from the GL by one cent is wrong, period. Build automated trial-balance ties: Gold P&L totals reconciled to source GL balances per entity/period each load, with failures blocking publication rather than emailing someone later.