Skip to main content

Medallion Architecture (Bronze → Silver → Gold)

As data pipelines evolved in the cloud era, organizations needed a standardized way to describe the "state" or "quality" of data as it moved through the system. Databricks popularized the Medallion Architecture, a multi-hop pattern that has now become the industry standard for building pipelines in Snowflake.

The architecture organizes data into three distinct layers: Bronze, Silver, and Gold.

Topics Covered​

  • The Bronze Layer (Raw)
  • The Silver Layer (Cleansed)
  • The Gold Layer (Curated)
  • Implementation via Dynamic Tables

1. The Bronze Layer (Raw)​

The Bronze layer is the landing zone. Its sole purpose is to capture data from upstream source systems as quickly and losslessly as possible.

Characteristics:

  • Data is stored in its raw, original format (often VARIANT for JSON or Parquet).
  • No data is filtered or deleted. If the source system sends garbage, you store the garbage.
  • Why? If a pipeline breaks in the Silver layer, you need the raw Bronze data to replay the pipeline. If you drop data during ingestion, it is lost forever.

2. The Silver Layer (Cleansed & Conformed)​

The Silver layer acts as the enterprise "Single Source of Truth". This is where data engineers spend 80% of their time.

Characteristics:

  • Data is extracted from raw JSON/Variants and strictly typed (e.g., VARCHAR, TIMESTAMP).
  • Strings are standardized (e.g., converting "mumbai", "MUMBAI", and "Mumbai " to "Mumbai").
  • Invalid rows are filtered out or flagged.
  • Complex entities are resolved (e.g., merging Salesforce Customer data with ERP Customer data to create a single DIM_CUSTOMER table).

3. The Gold Layer (Curated & Business-Ready)​

The Gold layer is built specifically for consumption by business users, BI dashboards (Tableau/PowerBI), and Data Scientists.

Characteristics:

  • Highly refined, aggregated data.
  • Denormalized structures (One-Big-Table or Star Schemas) optimized for fast read performance.
  • Complex business logic is applied here (e.g., defining how "Net Margin" is calculated so every analyst uses the exact same formula).
  • Security: The Gold layer is typically the only layer exposed to end-users via Secure Views.

Hands-On: Building a Medallion Pipeline​

Let's build a mini-pipeline for a food delivery app in Delhi. We will use Dynamic Tables to seamlessly move data from Bronze to Gold.

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_DELIVERY_DB;
USE DATABASE BEEMS_DELIVERY_DB;

-- Create the Layers as Schemas
CREATE SCHEMA IF NOT EXISTS BRONZE;
CREATE SCHEMA IF NOT EXISTS SILVER;
CREATE SCHEMA IF NOT EXISTS GOLD;

2. The Bronze Layer​

We simulate Snowpipe ingesting raw JSON logs into the Bronze table.

CREATE OR REPLACE TABLE BRONZE.RAW_ORDERS (
INGESTED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
RAW_JSON VARIANT
);

INSERT INTO BRONZE.RAW_ORDERS (RAW_JSON)
SELECT PARSE_JSON(column1) FROM VALUES
('{"order_id": "ORD-1", "city": "delhi", "amount": 550.50, "status": "DELIVERED"}'),
('{"order_id": "ORD-2", "city": "DELHI", "amount": 200.00, "status": "CANCELLED"}'),
('{"order_id": "ORD-3", "city": "gurugram", "amount": "null", "status": "FAILED"}');

3. The Silver Layer​

We use a Dynamic Table to pull data from Bronze, clean it, and standardize it. Notice we use TARGET_LAG = 'DOWNSTREAM'. This tells Snowflake: "Don't refresh the Silver table on a fixed schedule. Only refresh it when the Gold table asks for data."

(To verify your role can create Dynamic Tables, run SELECT CURRENT_ROLE();)

CREATE OR REPLACE DYNAMIC TABLE SILVER.CLEAN_ORDERS
TARGET_LAG = 'DOWNSTREAM'
WAREHOUSE = COMPUTE_WH
AS
SELECT
RAW_JSON:order_id::STRING AS ORDER_ID,
INITCAP(RAW_JSON:city::STRING) AS CITY,
-- Handle missing amounts gracefully
IFF(RAW_JSON:amount::STRING = 'null', 0.0, RAW_JSON:amount::NUMBER(10,2)) AS ORDER_AMOUNT,
RAW_JSON:status::STRING AS ORDER_STATUS
FROM BRONZE.RAW_ORDERS;

4. The Gold Layer​

The Operations Manager only cares about one thing: How much revenue was generated from delivered orders in each city? We build an aggregated Gold table that automatically refreshes every 15 minutes.

CREATE OR REPLACE DYNAMIC TABLE GOLD.CITY_REVENUE_SUMMARY
TARGET_LAG = '15 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
CITY,
COUNT(ORDER_ID) AS TOTAL_ORDERS,
SUM(ORDER_AMOUNT) AS TOTAL_REVENUE
FROM SILVER.CLEAN_ORDERS
WHERE ORDER_STATUS = 'DELIVERED'
GROUP BY CITY;

-- Force a refresh for testing
ALTER DYNAMIC TABLE GOLD.CITY_REVENUE_SUMMARY REFRESH;

-- The BI Analyst queries this final output
SELECT * FROM GOLD.CITY_REVENUE_SUMMARY;

Naming Conventions and Best Practices​

To keep a Snowflake account organized, you should strictly enforce naming conventions:

  • Schemas: Group tables logically (BRONZE, SILVER, GOLD).
  • Table Prefixes: RAW_ for Bronze, STG_ or CLEAN_ for Silver, DIM_/FACT_/AGG_ for Gold.
  • RBAC (Role-Based Access Control):
    • DATA_ENGINEER_ROLE: Has read/write access to all layers.
    • DATA_ANALYST_ROLE: Has read-only access to the GOLD schema only. They should never see the messy JSON in the BRONZE schema.

Summary​

  • Bronze (Raw): Lossless storage of incoming data exactly as it arrives.
  • Silver (Cleansed): Structured, clean, validated single source of truth.
  • Gold (Curated): Aggregated, business-ready views optimized for reporting.
  • Dynamic Tables allow you to chain these layers together declaratively without manually writing complex orchestration Tasks.