Medallion architecture: bronze, silver and gold layers, and when to skip them

Where does the medallion pattern come from?

A Databricks blog post dated August 14, 2019 describes tables for data ingestion ("Bronze"), transformation and feature engineering ("Silver"), and machine learning training or prediction ("Gold"), and calls the set a multi-hop architecture; the post does not use the word medallion. The current Databricks documentation treats the names as synonyms: a way to organize data logically so that structure and quality improve step by step from bronze to silver to gold.

Databricks calls following the pattern "a recommended best practice but not a requirement", and the Delta Lake project's medallion explainer describes it as a name for a tier-based architecture to follow in a flexible manner: a convention, not a mandate. Other platforms have adopted it; Microsoft calls medallion architecture the recommended design approach for Fabric.

The pattern assumes a lakehouse underneath, which the CIDR 2021 paper characterizes by open direct-access data formats such as Apache Parquet and ORC, first-class support for machine learning and data science workloads, and state-of-the-art performance.

What does each layer hold?

Databricks and Microsoft describe the same three-way split with different adjectives: bronze (raw), silver (validated or enriched) and gold (enriched or curated). The words differ and the roles do not. The table pairs the roles they document with the load method and checks this guide recommends.

Bronze: keep what arrived

Bronze holds raw, unvalidated data. Databricks lists its traits: it keeps the raw state of the source in its original format, is appended incrementally and grows over time, is the single source of truth, and enables reprocessing and auditing by retaining all historical data. Microsoft's Fabric guidance is blunter: store everything exactly as it arrives, with no changes allowed.

Validation in bronze is minimal on purpose: Databricks recommends storing most fields as strings, VARIANT or binary to protect against unexpected schema changes, plus metadata columns for provenance such as the source file name. It also says bronze is meant for the workloads that build silver, not for analysts and data scientists.

Silver: one validated row per record

Silver is where cleanup and validation happen. Databricks lists the operations: schema enforcement, handling nulls and missing values, deduplication, resolving out-of-order and late-arriving data, data quality checks, schema evolution, type casting and joins. It advises against writing to silver directly from ingestion: schema changes and corrupt records in the sources would turn into load failures.

Databricks's bar for silver: it "should always include at least one validated, non-aggregated representation of each record", so keep the grain of the source: one row per order, per payment, per customer version. Conform keys here, so a customer has one id from the shop, the payments provider and the CRM alike. Where attribute history matters, such as the address a customer had when an order shipped, keep versions in a slowly changing dimension of Type 2; the Delta Lake merge documentation shows the pattern.

Gold: shaped for a question

Gold holds what people and applications query: dimensional models, aggregates and feature tables. Databricks describes gold data as aggregated and filtered for specific needs, mapped to business functions, with some customers keeping several gold layers for domains such as HR, finance and IT. Gold reads from silver, not bronze: the 2019 post warns that connecting every gold table straight to raw data makes each business unit repeat the same ETL and invites diverging metric definitions. The Delta Lake project notes exceptions, such as a gold table that joins a bronze table and a silver table.

How do orders and payments move through the layers?

The example is an online shop. Orders arrive as change events from the shop's database, and payments arrive from a payments API. Every value is illustrative. The SQL and Python below were run on Apache Spark 4.2.0 with Delta Lake 4.4.1 in local mode.

Bronze: land the events

Bronze keeps the business columns as strings and adds three metadata columns. _event_id is whatever uniquely identifies a received event, such as a change log position or a file name plus a row number.

CREATE TABLE IF NOT EXISTS bronze.orders_cdc (
  order_id     STRING,
  customer_id  STRING,
  status       STRING,
  total        STRING,
  currency     STRING,
  created_at   STRING,
  updated_at   STRING,
  _event_id    STRING,
  _ingested_at TIMESTAMP,
  _source      STRING
) USING DELTA;

A first delivery puts six rows in bronze for four orders, all dated 2026-10-07. Order 1001 appears three times: pending, then paid, then a duplicate of the pending event that arrives late. Order 1002 carries a decimal comma in its total.

Silver: type, deduplicate, quarantine

The silver load makes four decisions, and each one shows in the code.

1. Type without failing. try_cast returns NULL for a value it cannot convert. In Spark 4.2, ANSI mode is on by default, so a plain CAST('12,50' AS DECIMAL(12,2)) raises an error instead; in the local run, swapping try_cast for CAST failed the whole streaming query on that one value.
2. Divert, do not drop. Rows whose key, timestamp or amount cannot be typed go to a quarantine table with a reason and a timestamp; the insert is a merge keyed on _event_id, so a restart cannot insert the same reject twice.
3. One row per key. ROW_NUMBER() keeps the latest valid event per order; a merge needs at most one source row per target row.
4. Only newer wins. WHEN MATCHED updates a row only when the incoming updated_at is later, so a late duplicate cannot undo a newer state.

MERGE INTO quarantine.orders_rejected AS q
USING (
  SELECT *,
         'order_id, updated_at or total is not valid' AS reason,
         current_timestamp() AS rejected_at
  FROM orders_batch
  WHERE try_cast(order_id AS BIGINT) IS NULL
     OR try_cast(updated_at AS TIMESTAMP) IS NULL
     OR try_cast(total AS DECIMAL(12,2)) IS NULL
) AS r
ON q._event_id = r._event_id
WHEN NOT MATCHED THEN INSERT *;

CREATE OR REPLACE TEMP VIEW orders_latest AS
WITH typed AS (
  SELECT try_cast(order_id AS BIGINT)      AS order_id,
         try_cast(customer_id AS BIGINT)   AS customer_id,
         status,
         try_cast(total AS DECIMAL(12,2))  AS total_amount,
         currency,
         try_cast(created_at AS TIMESTAMP) AS created_at,
         try_cast(updated_at AS TIMESTAMP) AS updated_at,
         _ingested_at
  FROM orders_batch
),
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY updated_at DESC, _ingested_at DESC) AS rn
  FROM typed
  WHERE order_id IS NOT NULL AND updated_at IS NOT NULL AND total_amount IS NOT NULL
)
SELECT order_id, customer_id, status, total_amount, currency, created_at, updated_at
FROM ranked
WHERE rn = 1;

MERGE INTO silver.orders AS t
USING orders_latest AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

The job reads bronze as a stream, which Databricks recommends for append-only sources, and runs the three statements per micro-batch, held in QUARANTINE_SQL, LATEST_SQL and MERGE_SQL.

def load_silver(batch_df, batch_id):
    batch_df.createOrReplaceTempView("orders_batch")
    for statement in (QUARANTINE_SQL, LATEST_SQL, MERGE_SQL):
        batch_df.sparkSession.sql(statement)


(
    spark.readStream.table("bronze.orders_cdc")
    .writeStream.foreachBatch(load_silver)
    .option("checkpointLocation", "/checkpoints/silver_orders")
    .trigger(availableNow=True)
    .start()
    .awaitTermination()
)

After the first run, silver holds order 1001 (paid, 129.90), order 1003 (paid, 49.00) and order 1004 (cancelled, 80.00), and quarantine holds order 1002 with its 12,50 total. A later delivery brings a stale copy of the pending event for order 1001 and a corrected event for order 1002 with a total of 12.50. On the next run order 1002 enters silver and order 1001 stays paid. Applying the first micro-batch twice, or restarting after it with no new data, left silver at three rows and quarantine at one.

Gold: model for the question

Gold needs payments as well as orders. Silver also holds a payments table, loaded the same way: a 129.90 capture and a 20.00 refund for order 1001, and a 49.00 capture for order 1003.

CREATE OR REPLACE TABLE gold.fct_orders USING DELTA AS
WITH paid AS (
  SELECT order_id,
         SUM(CASE WHEN kind = 'capture' THEN amount ELSE 0 END) AS amount_captured,
         SUM(CASE WHEN kind = 'refund'  THEN amount ELSE 0 END) AS amount_refunded
  FROM silver.payments
  GROUP BY order_id
)
SELECT o.order_id,
       o.customer_id,
       CAST(o.created_at AS DATE) AS order_date,
       o.status AS order_status,
       o.currency,
       o.total_amount,
       COALESCE(p.amount_captured, 0) AS amount_captured,
       COALESCE(p.amount_refunded, 0) AS amount_refunded
FROM silver.orders AS o
LEFT JOIN paid AS p ON p.order_id = o.order_id;

CREATE OR REPLACE TABLE gold.daily_revenue USING DELTA AS
SELECT order_date,
       currency,
       COUNT_IF(amount_captured > 0) AS paid_orders,
       SUM(amount_captured - amount_refunded) AS net_revenue
FROM gold.fct_orders
WHERE order_status <> 'cancelled'
GROUP BY order_date, currency;

fct_orders has one row per order. daily_revenue has one row for 2026-10-07 in CAD, with 2 paid orders and net revenue of 158.90, which is 129.90 - 20.00 + 49.00. The cancelled order is excluded, and order 1002 is marked paid but has no captured payment yet, so it adds nothing until the payment arrives. A reconciliation test would flag that gap.

Rebuilding gold in full with CREATE OR REPLACE TABLE is fine while silver is small. When the rebuild gets slow, refresh from silver's change feed (next section) or use materialized views: Databricks shows a gold aggregate as one, and Fabric's materialized lake views pick incremental, full or no refresh per view.

How do you make a rerun safe?

Every pipeline gets rerun: a job restarts, a bug is fixed, a source resends a file. A safe rerun changes nothing that should not change. The example guards against four specific failures.

A bank's regulatory data proof of concept met the same problem from the other side: its raw layer was append-only, and without de-duplication to the latest version above it, the first correction would have been counted twice (see the case study).

The availableNow trigger suits scheduled runs: Spark's documentation says it processes all available data at the time of the run, in possibly several micro-batches, then stops on its own, so the compute can shut down between runs. Databricks lists triggered incremental ingestion as lower cost and higher latency than a continuous stream, so pick the slowest schedule the business accepts. foreachBatch hands your function each micro-batch and its id, and Delta Lake warns that restarts can reapply a batch, so the merge inside it must be idempotent.

Spark can also drop duplicates inside the stream. A watermark sets how late a duplicate may arrive and lets Spark discard old state; without one, the query stores state for all past records. A merge on the business key has no such time window, because it compares against the table itself, so treat stream-level deduplication as an optimization and the merge as the safeguard.

To refresh gold from only what changed, turn on the change data feed. It is off by default and records only changes made after it is enabled, and it suits silver and gold tables because ETL can process just the row-level changes.

ALTER TABLE silver.orders SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

-- the second argument is the first table version to read
SELECT order_id, status, _change_type, _commit_version
FROM table_changes('silver.orders', 1);

The strict > in the merge matters here. In the local run, the same merge written with >= added an update_preimage and an update_postimage row to the feed for every identical replay, although nothing had changed. With >, replays added nothing.

Reprocessing is the payoff for keeping bronze: when a silver rule turns out to be wrong, fix the SQL and replay bronze through it. In the example run, deleting silver and merging all of bronze in one pass rebuilt the same four orders. On a real table, rebuild into a new table, compare counts and totals, then swap. How far back a replay reaches depends on bronze retention, so set it on purpose.

Which checks stop bad data between layers?

A layer boundary is a good place for a check, because the next layer should only see data that passed it. Four mechanisms cover most cases, and they differ in what happens to a failing row.

Delta Lake constraints are the built-in gate. Adding one with ALTER TABLE ... ADD CONSTRAINT first verifies that all existing rows satisfy it, and it upgrades the table's writer protocol version, so confirm every engine writing to the table supports the new protocol first.

ALTER TABLE silver.orders
  ADD CONSTRAINT total_not_negative CHECK (total_amount >= 0);

In the local run, an insert of a negative total was rejected with a constraint violation error.

Lakeflow pipelines is the current name of the product formerly known as Delta Live Tables; Databricks's rename notice says no migration is needed, and the new dp Python API adds compatibility with Apache Spark Declarative Pipelines from Spark 4.1. Quality rules there are expectations, with three actions: warn, the default, writes invalid records to the target; drop removes them before the write and logs the count; fail stops the update until someone intervenes. The same page describes a quarantine pattern for cases where neither dropping nor failing is acceptable.

CREATE OR REFRESH STREAMING TABLE silver_orders (
  CONSTRAINT valid_total EXPECT (total_amount >= 0) ON VIOLATION DROP ROW
) AS SELECT * FROM STREAM(bronze_orders_typed);

An expectation cannot call a custom Python function, call an external service or use a subquery on another table, so a rule such as "the customer exists" belongs in a join in silver or in a dbt test.

dbt data tests sit in YAML next to the model and run after the build. dbt ships four generic tests, unique, not_null, accepted_values and relationships, and a test passes when its query returns zero failing rows.

models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: order_status
        data_tests:
          - accepted_values:
              arguments:
                values: [pending, paid, cancelled]

The arguments: key needs dbt 1.10.5 or later; older versions set the values as a top-level property of the test. A singular test (a SQL file returning failing rows) can reconcile gold totals with silver. For the wider practice of tests, contracts and freshness checks, see our guide to building data analytics software.

The quarantine table is a plain Delta table holding the rejected row, a reason and a timestamp. Two habits make it useful: alert when the reject count rises, and give every reject a path back. When the source corrects a record, the corrected event arrives as a new bronze row and flows through, as order 1002 did.

Which table format and catalog should the layers use?

The table format decides which engines can read the layers and what upkeep they need. All three open formats support row-level upserts, so the pattern works on each.

You do not have to pick one format for every consumer. Delta Universal Format (UniForm) lets Iceberg and Hudi clients read a Delta table: it generates the metadata asynchronously, and a single copy of the data files serves clients of all formats. It works because all three formats are Parquet data files plus a metadata layer, and it has requirements such as column mapping, so test it on one table first. Our piece on data compression covers how Parquet shrinks data.

The catalog is the second decision. Status of the main options as of October 2026:

Decide from the engines that must read and write the tables, then choose the catalog those engines support. Two of our cases show the range: a proof of concept on a bank's Cloudera platform keeps its raw tables in Apache Iceberg, and a healthcare data lake on AWS catalogs datasets in Amazon S3 with AWS Glue, queries them with Amazon Athena and runs serverless.

How does the pattern look on Databricks, Fabric and dbt?

Fabric offers two deployment patterns: each layer as a lakehouse, with business users reading through the SQL analytics endpoint, or bronze and silver as lakehouses and gold as a data warehouse. Microsoft recommends one workspace per lakehouse, for more control and better governance at the layer level, and shortcuts in bronze instead of copies when the source is in OneLake, ADLS Gen2, Amazon S3 or Google storage. Materialized lake views declare silver and gold in SQL while Fabric handles dependencies and picks the refresh. File size guidance varies by layer: 128 MB to 1 GB, smaller in bronze, larger in gold.

dbt's structure guide describes a path from source-conformed to business-conformed data through staging, intermediate and marts models. Its staging page asks for a one-to-one relationship between staging models and source tables. A common mapping, which is a convention and not dbt's wording, is that bronze is what dbt reads as sources, staging and intermediate models play the silver role, and marts play the gold role.

Who may read each layer, and how do you trace a number?

Access should follow the layers; the table lists Databricks's intended readers. Whatever personal data a source holds lands in bronze untouched, so bronze is the layer to lock down. Give analysts silver or gold, and decide at the silver step which identifiers are dropped, masked or moved to a restricted table.

Make the layer boundary a permission boundary. In Databricks's example each layer is a separate schema in one catalog, and the Delta Lake project shows the same namespacing, so a name such as something_cool.bronze.raw_transactions states its layer. If models or assistants query your data, treat them like any other reader and point them at gold: our guide to generative AI for databases covers safe, read-only access.

Plan deletion for each layer. Removing a person's rows from silver does not remove them from bronze or from earlier table versions. In Delta Lake, VACUUM removes files that are no longer referenced and are older than the retention threshold, 7 days by default; it is not triggered automatically, and time travel to versions older than the retention period is lost after it runs. The change data feed follows the table's retention, so VACUUM deletes that history too.

Lineage answers where a figure came from. OpenLineage is an open framework for collecting and analyzing lineage; its model records datasets, jobs and runs, with integrations for Apache Spark, dbt and Apache Airflow.

When is the medallion pattern the wrong choice?

The pattern is a default, and it has costs: each materialized layer stores a copy of the data and runs a job to build it. These are the failures to look for, with the fix for each.

Three copies with no purpose. Name the job of each layer: bronze preserves and replays, silver validates and conforms, gold models for consumption. A layer with no job should be merged into its neighbour or turned into a view. dbt's staging guide takes that line: staging models should typically be materialized as views, because consumers do not query them and a copy would waste warehouse space.

Layers as physical mandates. Databricks defines the pattern as a logical organization of data, and the Delta Lake project says to follow it flexibly. Three layers can be three schemas in one catalog, or three workspaces on Fabric. Splitting data across more accounts and pipelines than your team can run is a cost, not part of the pattern.

Gold sprawl. Several gold layers are fine when each serves a distinct domain, as in Databricks's HR, finance and IT example. The failure is several gold tables for the same entity, each with its own revenue formula, the diverging-metrics problem the 2019 post warned about. Keep gold at the grain of a business process, model it as a star schema, and define each metric once in a semantic layer.

Small data. If the data fits comfortably in a warehouse and one team owns the pipeline, raw tables plus dbt staging and marts models give the same discipline without a lakehouse. As our data platform guidance puts it, a warehouse suits structured reporting at modest volume and a lakehouse suits mixed data, larger volumes and machine learning on the same storage; a sound warehouse earns tests, definitions and monitoring, not a migration. Our overview of big data analytics explains when big data tools are not needed.

Latency and cost per hop. Databricks lists continuous incremental ingestion as higher cost and lower latency than triggered ingestion. Every hop adds one or the other. If one report needs data within seconds, serve it from a purpose-built path instead of pushing every table through three continuous hops.

Year two. Upkeep arrives after launch. VACUUM does not run on its own, and a Delta table's history grows until retention removes it. One lesson from the bank proof of concept: dropping and recreating an Iceberg table erases its snapshots, so write with insert-overwrite instead. Schedule maintenance when you build the pipeline, not when the storage bill arrives.

How do you roll it out?

Start narrow and add layers as each earns its place.

1. Pick one question and trace it back. Choose one report or model that people rely on or dispute, and list the sources behind each number. That is your first gold table and its inputs.
2. Land the sources in bronze. Append-only, original values as strings, metadata columns, and a retention period that covers your replay window. No cleanup.
3. Build silver for those sources. Types, conformed keys, one row per record, a guarded merge, a quarantine table. Add CHECK constraints for rules that must never break.
4. Build the one gold table and test it. Fix the grain, add uniqueness, accepted-value and relationship tests, and reconcile it to a number the business already trusts.
5. Rehearse a replay. Rebuild silver from bronze into a scratch schema, compare counts and totals with production, and time the run.
6. Schedule the upkeep. Choose the slowest trigger the use case accepts, schedule VACUUM or snapshot expiry, set file sizes by layer, and alert on quarantine counts and freshness.
7. Lock down and trace. Give each layer its own access rules, keep raw personal data in bronze only, and capture lineage.
8. Add the next source. Repeat steps 2 to 4, and add a layer only when you can name its job.

If you want a second pair of eyes on the design, our data platform service starts with a data platform assessment: a review of your sources, reports and current pipelines, ending in a target architecture and a first use case worth building. The bank proof of concept lists medallion layers and SCD Type 2 among its data modelling techniques and is the closest example of this guide's design on real regulatory data.