Medallion lakehouse

The problem

The first data pipeline anyone writes reads from a source and writes the thing the dashboard needs. It works. The second one does the same, with a slightly different definition of "active customer". By the tenth, nobody can explain why finance and sales report different revenue, the only way to fix a parsing bug from three months ago is to ask the source system for the data again — and the source system no longer has it.

Three failures are hiding in that story, and they are the three the medallion pattern is designed to prevent:

• No recovery point. If the transformation was wrong, the input is gone.
• No shared definitions. Business logic is duplicated per dashboard, so it drifts per dashboard.
• No separation of concerns. Parsing, cleaning, joining and aggregating all happen in one query, so a change to any of them risks all of them.

The fix is a deliberate constraint: data moves in one direction through named layers, and each layer has a contract about what it guarantees.

How it works

The flow reads left to right, and the arrow that matters most points backwards:

1. Sources hand over data however they can — a JDBC pull, a nightly file drop, a SaaS export.
2. Ingestion lands it incrementally. On Databricks this is Auto Loader for files and a managed connector for systems that have one. It tracks what it has already seen so a rerun is not a reload.
3. Bronze stores it unchanged, append-only, with provenance columns attached.
4. Silver parses, types, validates, deduplicates and conforms — one row per real-world thing, with a key the rest of the business recognises.
5. Gold aggregates into tables shaped for a specific question, with metric definitions agreed once.
6. Consumers read Gold, and read Silver when they need to ask something Gold does not answer.
7. Rebuilds run forward from Bronze whenever logic changes — which it will.

What each layer guarantees

Write this table down before you write any pipeline code, and then hold the line on it. Most medallion implementations fail not because the layers are wrong but because nobody agreed what belongs in each.

Getting data in

Ingestion is the part that decides whether reruns are cheap. The rule is that every landing job must be able to run twice without producing twice the data, and must be able to resume without reprocessing everything it has already seen.

Auto Loader (the cloudFiles source) is the default for anything that arrives as files. It keeps its own record of which files it has processed, so new files are picked up and old ones are not:

(spark.readStream
   .format("cloudFiles")
   .option("cloudFiles.format", "json")
   .option("cloudFiles.schemaLocation", f"{chk}/schema")
   .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
   .option("rescuedDataColumn", "_rescued_data")   # nothing is silently dropped
   .load("abfss://landing@acct.dfs.core.windows.net/erp/orders/")
   .selectExpr(
       "*",
       "_metadata.file_path  as _source_file",
       "_metadata.file_modification_time as _source_modified_at",
       "current_timestamp()  as _ingested_at")
   .writeStream
   .option("checkpointLocation", f"{chk}/bronze_orders")
   .trigger(availableNow=True)          # batch semantics, streaming bookkeeping
   .toTable("bronze.erp.orders_raw"))

Two details in there carry most of the weight. trigger(availableNow=True) gives you a batch job — it starts, consumes everything outstanding, and stops — while keeping the checkpointing and exactly-once guarantees of Structured Streaming. And rescuedDataColumn catches fields that do not match the expected schema instead of discarding them, which is the file-ingestion equivalent of not filtering in Bronze.

For other sources, in rough order of preference:

• A managed connector (Lakeflow Connect) where one exists for the system. Less code to own, and CDC handled for you.
• A CDC stream from the source database, landed as files or onto a message bus. This is the only approach that captures deletes correctly.
• An incremental JDBC pull on a reliable high-water mark. Workable, but be honest that it will miss hard deletes and any update that does not move the watermark.
• A full snapshot per run. Perfectly acceptable for small, slowly changing reference data, and far simpler than the alternatives. Stop apologising for it.

The three layers, concretely

Bronze — the record of what arrived

Append-only. No deletes, no updates, no filtering, no renaming. Store the payload in whatever type keeps it faithful — a JSON string column is a perfectly good Bronze schema — and attach provenance: source system, source file or offset, ingest timestamp, and the batch or run identifier that produced the row.

Retention is a real decision, not a default. Bronze is the thing that makes reprocessing possible, so keeping ninety days of it is keeping ninety days of the ability to fix mistakes. Keeping seven years of it is usually just paying for storage you will never read. Decide per source, write it in the table comment, and enforce it.

Silver — the business's shape

This is where the work is, and it is unglamorous: parse, cast, trim, deduplicate, resolve keys, apply referential rules, and decide what to do with rows that fail. Silver is where a data engineer earns their salary and where most of the pipeline's test coverage should live.

CREATE OR REFRESH TABLE silver.sales.orders
  (CONSTRAINT valid_order_id  EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW,
   CONSTRAINT valid_amount    EXPECT (amount_eur >= 0),
   CONSTRAINT known_customer  EXPECT (customer_key IS NOT NULL))
  COMMENT 'One row per order, latest version. Grain: order_id.'
AS
WITH typed AS (
  SELECT
    CAST(payload:orderId        AS STRING)    AS order_id,
    CAST(payload:customerNumber AS STRING)    AS customer_number,
    CAST(payload:total          AS DECIMAL(18,2)) AS amount_eur,
    CAST(payload:orderedAt      AS TIMESTAMP) AS ordered_at,
    _ingested_at,
    _source_file
  FROM STREAM(bronze.erp.orders_raw)
),
deduplicated AS (
  SELECT * EXCEPT (rn) FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY order_id ORDER BY _ingested_at DESC) AS rn
    FROM typed)
  WHERE rn = 1
)
SELECT d.*, c.customer_key
FROM deduplicated d
LEFT JOIN silver.sales.customers c
       ON c.customer_number = d.customer_number;

Note what is not here: no revenue definition, no "active customer" rule, no currency conversion to a reporting currency, no fiscal calendar. Those are interpretations, and interpretations belong in Gold where they can be argued about in one place.

Slowly changing dimensions live here too. Decide per entity whether Silver holds the current state (SCD type 1) or the full version history (SCD type 2); do not let it vary by whoever wrote the pipeline. In Lakeflow Declarative Pipelines this is a declaration rather than a merge you hand-write:

CREATE FLOW customers_scd AS AUTO CDC INTO silver.sales.customers
FROM STREAM(bronze.crm.customers_raw)
KEYS (customer_number)
APPLY AS DELETE WHEN operation = 'DELETE'
SEQUENCE BY _ingested_at
STORED AS SCD TYPE 2;

Gold — one table per question

Gold tables are read by people and by tools that people point at things. They should be wide, denormalised, named in business language, and documented. A star schema is a good default if the consumers are BI tools; a single flat aggregate is a better one if the consumer is a specific dashboard with a specific refresh budget.

The discipline that matters here is that a metric has exactly one definition and it lives in exactly one place. If "net revenue" appears in four Gold tables with four slightly different filters, the layering has bought you nothing. Either compute it once and join, or express it once in a metric view and let the tooling expand it.

Incremental processing

The naive implementation recomputes every layer from scratch on every run. That is genuinely the right choice while the data is small — it is simple, it is always correct, and it costs almost nothing. Switch when the recompute no longer fits the window, and switch one table at a time.

Data quality and quarantine

Expectations belong on Silver tables, where you first claim the data is usable. Three enforcement levels, and the choice between them is a business decision rather than a technical one:

• Warn — record the violation, keep the row. The default. You get a metric you can trend without blocking anything.
• Drop — remove the row from this table, and write it to a quarantine table in the same run. A dropped row that is not written somewhere is a deleted row.
• Fail — stop the pipeline. Reserve this for violations that make everything downstream meaningless, such as a primary key that is not unique.

Quarantine tables are the part people skip, and they are what turns "the pipeline is green" into something you can believe. A nightly check on quarantine row counts per source finds broken upstream changes days before a dashboard does.

Alongside expectations, keep a short list of assertions that hold across tables — row counts reconciled against the source, no orphaned foreign keys in Silver, every Gold metric within a plausible band of yesterday's. These catch the failures expectations cannot see, because they are about the shape of the whole dataset rather than any single row.

Orchestration

Layers imply dependencies, and dependencies want a scheduler rather than a stack of cron entries. The structure that holds up:

• One job per source domain, with tasks for Bronze → Silver → Gold. A broken ERP extract should not block the CRM pipeline.
• Declare dependencies, do not sleep. If Gold starts at 06:00 because Silver "usually finishes by 05:45", it will one day read yesterday's data and nobody will notice.
• Make every task idempotent. Re-running a failed task must be safe without a manual cleanup step. This is the single highest-value property in the whole platform.
• Separate the backfill path. A parameterised job that reprocesses a date range, tested while things are calm — not written at 2am during an incident.
• Alert on freshness, not just on failure. A job that succeeds while producing zero rows is the failure mode that survives longest in production.

For pipelines you are writing from scratch, declarative pipelines (Lakeflow Declarative Pipelines, previously Delta Live Tables) are worth the constraint: you declare the tables and their inputs, and the dependency graph, incremental bookkeeping and quality metrics come with it. For pipelines that need imperative control, plain jobs with notebook or Python tasks remain the right tool. Mixing both is normal.

Governance with Unity Catalog

Layers are only a contract if the contract is enforceable, and that means the layer boundary has to be a permission boundary.

• Name the layer in the catalog, not in the table name. bronze.erp.orders_raw, not main.default.bronze_erp_orders_raw. Catalogs are where grants and storage locations attach.
• Grant accordingly. Most analysts should have no access to Bronze at all. If they do, they will build on it, and the layer below becomes load-bearing by accident.
• Separate environments by catalog (dev_bronze, prd_bronze) with identical schema and table names, so the same code promotes without edits.
• Use the lineage you get for free. Column-level lineage answers "what breaks if I change this" in seconds, which is the question that otherwise stops refactors from happening.
• Tag sensitive columns and apply row filters and column masks at the Silver and Gold layer. Masking data on the way into Bronze destroys your ability to reprocess it.

Performance and cost

Most lakehouse cost problems come from a small number of causes, and they are all avoidable:

• Small files. Streaming ingestion with a short trigger produces thousands of tiny files, and every downstream read pays for it. Let predictive optimization handle compaction where it is available, and otherwise schedule OPTIMIZE.
• Over-partitioning. Partitioning by date and country and product on a table of fifty million rows creates a directory structure that costs more to traverse than to scan. Prefer liquid clustering on the columns you filter by, and do not partition tables under a terabyte at all.
• Gold tables nobody opens. Audit refresh cost against query counts once a quarter. There is always a dashboard that was built for a project that ended.
• Clusters sized for the worst table. Split the job so the one heavy transform gets the big compute and the forty light ones do not.
• Jobs that start a cluster and then wait. Serverless or a shared job cluster across tasks; cold-start time charged per task adds up fast across a hundred nightly tasks.
• Unbounded VACUUM retention. Time travel is useful; thirty days of it on every Bronze table is a storage bill. Set it deliberately per layer.

Where it goes wrong

When to use it — and when not to

The honest middle case: a two-layer version (Bronze and Gold, skipping Silver) is the right answer more often than the literature admits — specifically when there is one source domain and no cross-system conformance to do. Add Silver when you have a second source that needs to join to the first.

Implementation checklist

1. Write down the layer contract table above, adapted to your organisation, and get the data team to agree to it.
2. Pick the catalog and schema naming convention before the first table, including the environment prefix. Renaming catalogs later is painful.
3. Land Bronze with Auto Loader or a managed connector, with provenance columns and a rescued-data column on every table.
4. Decide Bronze retention per source and record it in the table comment.
5. Build Silver with expectations and a quarantine table from the first table onwards, not the tenth.
6. Decide SCD type per entity, centrally.
7. Start with full refreshes. Move individual tables to incremental only when the runtime justifies it.
8. Define each Gold metric once, and keep the definition next to the table.
9. Make every task idempotent and ship a parameterised backfill job alongside the first pipeline.
10. Alert on freshness and on quarantine volume, not only on task failure.
11. Revoke Bronze access from everyone who does not maintain pipelines.
12. Schedule a rebuild drill. An untested reprocessing path is not a reprocessing path.