Overview
The Medallion Architecture (also called multi-hop architecture) organises data into three progressive layers: Bronze (raw), Silver (cleaned and conformed), and Gold (aggregated and analytics-ready). Each layer adds more value and structure to the data as it flows through.
This project implements a full data warehouse on SQL Server, ingesting raw source data, applying transformations, and producing Gold-layer views and fact/dimension tables that power business intelligence reports. The same principles apply to cloud warehouses like Snowflake, BigQuery, and Azure Synapse.
Prerequisites
Architecture
Each layer is a separate schema in SQL Server: bronze, silver, gold. Transformations are stored procedures or views that reference the layer below.
Tutorial steps
Create the database and schemas
Set up the warehouse database with three schemas: bronze, silver, and gold. This logical separation enforces the layer boundaries and makes access control easier.
CREATE DATABASE DataWarehouse;
GO
USE DataWarehouse;
CREATE SCHEMA bronze;
CREATE SCHEMA silver;
CREATE SCHEMA gold;
Design and load the Bronze layer
Bronze tables mirror your source systems exactly — no transformations. Load raw data using BULK INSERT or SSIS. Preserve all columns including nulls, duplicates, and inconsistent formatting. Timestamps should record exactly when the record was ingested.
CREATE TABLE bronze.crm_customers (
customer_id NVARCHAR(50),
first_name NVARCHAR(100),
last_name NVARCHAR(100),
email NVARCHAR(200),
create_date NVARCHAR(50), -- kept as string, cleaned in Silver
dwh_load_date DATETIME DEFAULT GETDATE()
);
BULK INSERT bronze.crm_customers
FROM 'C:\data\crm_customers.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2);
Build the Silver layer — clean and standardise
Silver tables contain cleaned, typed, and deduplicated data. Write stored procedures that read from Bronze and produce Silver. Handle nulls, cast strings to proper types, trim whitespace, and resolve duplicates using ROW_NUMBER().
CREATE TABLE silver.customers (
customer_key INT IDENTITY(1,1) PRIMARY KEY,
customer_id NVARCHAR(50) NOT NULL,
first_name NVARCHAR(100),
last_name NVARCHAR(100),
email NVARCHAR(200),
create_date DATE,
dwh_load_date DATETIME DEFAULT GETDATE()
);
-- Stored proc to load Silver
CREATE PROCEDURE silver.load_customers AS
BEGIN
TRUNCATE TABLE silver.customers;
INSERT INTO silver.customers (customer_id, first_name, last_name, email, create_date)
SELECT customer_id,
TRIM(first_name),
TRIM(last_name),
LOWER(TRIM(email)),
TRY_CAST(create_date AS DATE)
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY dwh_load_date DESC) AS rn
FROM bronze.crm_customers
) t WHERE rn = 1;
END;
Build the Gold layer — model for analytics
The Gold layer uses a Star Schema: a central fact table surrounded by dimension tables. Create views (or materialised tables) that join Silver tables into business-meaningful structures. Dimensions hold descriptive attributes; facts hold measurable events.
-- Dimension: customers
CREATE VIEW gold.dim_customers AS
SELECT customer_key,
customer_id,
first_name + ' ' + last_name AS full_name,
email,
create_date
FROM silver.customers;
-- Fact: sales orders
CREATE VIEW gold.fact_sales AS
SELECT o.order_key,
o.order_date,
c.customer_key,
p.product_key,
o.quantity,
o.unit_price,
o.quantity * o.unit_price AS revenue
FROM silver.orders o
JOIN gold.dim_customers c ON o.customer_id = c.customer_id
JOIN gold.dim_products p ON o.product_id = p.product_id;
Run analytical queries against Gold
With the Star Schema in place, business questions become simple queries. Analysts can join fact and dimension tables without knowing the underlying cleaning logic.
-- Monthly revenue by customer segment
SELECT FORMAT(f.order_date, 'yyyy-MM') AS month,
COUNT(DISTINCT f.customer_key) AS unique_customers,
SUM(f.revenue) AS total_revenue
FROM gold.fact_sales f
GROUP BY FORMAT(f.order_date, 'yyyy-MM')
ORDER BY month;
Automate the pipeline
Create a master stored procedure that calls each layer's load procedure in order: Bronze → Silver → Gold. Schedule it using SQL Server Agent jobs to run on your desired cadence (hourly, daily, etc.).
CREATE PROCEDURE dbo.run_etl_pipeline AS
BEGIN
EXEC bronze.load_all;
EXEC silver.load_all;
PRINT 'ETL pipeline completed: ' + CONVERT(NVARCHAR, GETDATE(), 120);
END;