Medallion architecture (bronze, silver, gold)

The medallion architecture organizes the lakehouse into layers, and each layer has a contract.

• Bronze: the data as it arrived from the source (JSON, CSV, CDC), with ingestion metadata (when, from where). Nothing is discarded: if the silver rule is wrong, you can reprocess from here.
• Silver: clean and conformed. Correct types, duplicates removed, valid keys, standardized names. This is where quality rules block or set aside what is not fit for use.
• Gold: aggregated and modeled for whoever consumes it: business metrics, dimension and fact tables, what feeds Power BI and reports.

The three layers, in SQL that runs here

The events table plays the role of bronze (it arrived with one repeated event). Silver deduplicates and only accepts events for orders that exist. Gold answers "how many orders were paid per day".

CREATE VIEW silver_events AS
SELECT DISTINCT e.event_id, e.order_id, e.type, e.ts
FROM events e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.id = e.order_id);

CREATE VIEW gold_payments_per_day AS
SELECT date(ts) AS day, COUNT(*) AS payments
FROM silver_events
WHERE type = 'paid'
GROUP BY date(ts);

SELECT (SELECT COUNT(*) FROM events) AS bronze,
       (SELECT COUNT(*) FROM silver_events) AS silver,
       (SELECT SUM(payments) FROM gold_payments_per_day) AS gold_paid;

bronze | silver | gold_paid
------ | ------ | ---------
7      | 6      | 2

What not to do

• Reading from bronze in the report "because it is more up to date": the report inherits all the junk.
• A silver that already aggregates: then there is nowhere to reuse the clean, detailed data for another question.
• Fixing data in gold by hand: the fix disappears on the next load. The rule lives in silver, in code.

On Databricks

Each layer is usually a schema (or catalog) in Unity Catalog, with different permissions: engineering writes to all of them, analysts read only gold. The load between layers is a job (or a declarative pipeline) with one task per step.

bronze = spark.readStream.table("bronze.events")
silver = (bronze
    .dropDuplicates(["event_id"])
    .where("order_id IS NOT NULL"))
(silver.writeStream
    .option("checkpointLocation", "/Volumes/lakehouse/silver/_checkpoints/events")
    .toTable("silver.events"))