In this post we are going to cover the staging area and the medallion architecture, and answer an interview question that comes up a lot on modern data platforms: what is a staging area, and what are the bronze, silver and gold layers?
A staging area is the place where source data lands as-is, before any transformation. The transformations then read from staging instead of from the source system. The medallion architecture takes the same idea and splits it into three named layers: bronze for raw data, silver for cleaned data, and gold for business-ready tables.
We will load a small set of raw orders into bronze, clean them into silver, and then build a gold table that a report can use directly.
Question 38 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A staging area is where source data lands as-is before it is transformed, so loads are decoupled from the source and can be re-run. The medallion architecture formalizes this into layers: bronze = raw data as it landed, silver = cleaned, typed and deduplicated, gold = business-ready tables, like star schemas and aggregates.
The classic staging area
In a classic warehouse, the extract copies source data into staging tables with as little change as possible. Every transformation after that reads from staging, not from the source.
- The source is touched once - the extract runs for a short time and then the source system is left alone.
- Loads can be re-run - if a transformation fails, we fix it and run it again from staging, without going back to the source.
- We can see what arrived - staging shows exactly what the source sent us.
Classic warehouses often empty the staging tables before each load. Modern platforms usually keep that data, with history, and that is where bronze comes in.
Medallion: bronze, silver, gold
The medallion architecture is popular on lakehouse platforms. It names three layers, and each layer only reads from the one before it.
- Bronze - raw data exactly as it landed, kept with history and load metadata such as the file name.
- Silver - cleaned and conformed: correct data types, standardized values, duplicates removed and bad rows rejected.
- Gold - business-ready: star schemas, aggregates and data marts that dashboards read.
Let's create some sample data
We need three tables, one per layer: bronze_orders holds the raw orders as text, silver_orders will hold the clean typed rows, and gold_daily_sales will hold a daily summary.
DROP TABLE IF EXISTS gold_daily_sales;
DROP TABLE IF EXISTS silver_orders;
DROP TABLE IF EXISTS bronze_orders;
-- BRONZE: raw, exactly as it landed (all text, plus load metadata)
CREATE TABLE bronze_orders (
order_id VARCHAR(20),
order_date VARCHAR(20),
customer VARCHAR(50),
amount VARCHAR(20),
_file VARCHAR(30)
);
INSERT INTO bronze_orders VALUES
('1', '2026-06-01', ' sara khan ', '25.00', 'orders_0601.csv'),
('2', '2026-06-01', 'OMAR ALI', '40.00', 'orders_0601.csv'),
('2', '2026-06-01', 'OMAR ALI', '40.00', 'orders_0601.csv'),
('3', '2026-06-02', 'Lina Park', NULL, 'orders_0602.csv'),
('4', '2026-06-02', 'Sara Khan', '15.00', 'orders_0602.csv');
-- SILVER: cleaned, typed, deduplicated
CREATE TABLE silver_orders (order_id INT PRIMARY KEY, order_date DATE, customer VARCHAR(50), amount DECIMAL(10,2));
-- GOLD: business-ready aggregate
CREATE TABLE gold_daily_sales (order_date DATE PRIMARY KEY, orders INT, revenue DECIMAL(12,2));Let's look at the bronze table.
SELECT * FROM bronze_orders ORDER BY 1;| order_id | order_date | customer | amount | _file |
|---|---|---|---|---|
| 1 | 2026-06-01 | sara khan | 25.00 | orders_0601.csv |
| 2 | 2026-06-01 | OMAR ALI | 40.00 | orders_0601.csv |
| 2 | 2026-06-01 | OMAR ALI | 40.00 | orders_0601.csv |
| 3 | 2026-06-02 | Lina Park | NULL | orders_0602.csv |
| 4 | 2026-06-02 | Sara Khan | 15.00 | orders_0602.csv |
These are five rows from two daily files, and every column is text, exactly as it arrived, with the source file in _file. Nothing is fixed here on purpose. Notice the problems: sara khan has extra spaces and lower case, order 2 arrived twice, and order 3 has no amount. Bronze keeps all of it, so if a cleaning rule turns out to be wrong, we can rebuild silver from bronze at any time.
Step 1: Bronze to silver
Now let's clean the data on its way into silver. We cast every column to its real type, trim and upper case the customer name so the same customer always looks the same, use DISTINCT to remove the exact duplicate, and reject rows that have no amount.
INSERT INTO silver_orders
SELECT DISTINCT
CAST(order_id AS INT),
CAST(order_date AS DATE),
UPPER(TRIM(customer)),
CAST(amount AS DECIMAL(10,2))
FROM bronze_orders
WHERE amount IS NOT NULL;Now let's check the result.
SELECT * FROM silver_orders ORDER BY order_id;| order_id | order_date | customer | amount |
|---|---|---|---|
| 1 | 2026-06-01 | SARA KHAN | 25.00 |
| 2 | 2026-06-01 | OMAR ALI | 40.00 |
| 4 | 2026-06-02 | SARA KHAN | 15.00 |
Five raw rows became three clean rows. The duplicate of order 2 is gone, and order 3 was rejected because its amount was missing. Orders 1 and 4 now both show SARA KHAN, even though bronze had sara khan and Sara Khan. The dates and amounts are real DATE and DECIMAL values now, not text.
Step 2: The rows we rejected
A rejected row should not just disappear. Let's find the rows that failed the rule, so they can go to a quarantine table and be reported.
SELECT order_id, customer, _file
FROM bronze_orders
WHERE amount IS NULL;| order_id | customer | _file |
|---|---|---|
| 3 | Lina Park | orders_0602.csv |
Order 3 for Lina Park, from orders_0602.csv, is the row that did not make it to silver. Because the file name was kept in bronze, someone can go back to the source and fix it.
Rows that fail validation go to a quarantine (reject) table and get reported, so the source can be fixed instead of the data being silently dropped.
Step 3: Silver to gold
Finally, let's build a gold table. Gold tables answer business questions directly, and here the question is daily sales: how many orders and how much revenue per day.
INSERT INTO gold_daily_sales
SELECT order_date, COUNT(*), SUM(amount)
FROM silver_orders
GROUP BY order_date;Now let's check the result.
SELECT * FROM gold_daily_sales ORDER BY order_date;| order_date | orders | revenue |
|---|---|---|
| 2026-06-01 | 2 | 65.00 |
| 2026-06-02 | 1 | 15.00 |
June 1 has 2 orders and 65.00 in revenue (orders 1 and 2), and June 2 has 1 order and 15.00 (order 4). A dashboard can read this table as it is, with no cleaning and no grouping. In a real warehouse, gold is where the star schemas, aggregates and data marts live.
How the names map
The medallion names come from lakehouse platforms, but the same layering exists in classic warehouses under different names.
| Classic warehouse | Medallion |
|---|---|
| staging area | bronze |
| integrated warehouse (cleaned, conformed) | silver |
| data marts, star schemas, aggregates | gold |
Classic staging is roughly bronze, the cleaned and integrated warehouse layer is silver, and the marts and reporting tables are gold.
Follow-up questions interviewers ask
Why keep bronze if silver is cleaner?
So silver can be rebuilt from the original data when a cleaning rule changes or was wrong.
What happens to rows that fail validation?
They go to a quarantine (reject) table and are reported, not silently dropped.
Is medallion only for lakehouses?
The names come from lakehouse platforms, but the same layering exists in classic warehouses as staging, integration and marts.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #38 - Staging area and medallion architecture
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q38; SET search_path TO dwh_q38;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS gold_daily_sales;
DROP TABLE IF EXISTS silver_orders;
DROP TABLE IF EXISTS bronze_orders;
-- BRONZE: raw, exactly as it landed (all text, plus load metadata)
CREATE TABLE bronze_orders (
order_id VARCHAR(20),
order_date VARCHAR(20),
customer VARCHAR(50),
amount VARCHAR(20),
_file VARCHAR(30)
);
INSERT INTO bronze_orders VALUES
('1', '2026-06-01', ' sara khan ', '25.00', 'orders_0601.csv'),
('2', '2026-06-01', 'OMAR ALI', '40.00', 'orders_0601.csv'),
('2', '2026-06-01', 'OMAR ALI', '40.00', 'orders_0601.csv'),
('3', '2026-06-02', 'Lina Park', NULL, 'orders_0602.csv'),
('4', '2026-06-02', 'Sara Khan', '15.00', 'orders_0602.csv');
-- SILVER: cleaned, typed, deduplicated
CREATE TABLE silver_orders (order_id INT PRIMARY KEY, order_date DATE, customer VARCHAR(50), amount DECIMAL(10,2));
-- GOLD: business-ready aggregate
CREATE TABLE gold_daily_sales (order_date DATE PRIMARY KEY, orders INT, revenue DECIMAL(12,2));
-- Step 1: Bronze to silver: cast types, trim and standardize, remove duplicates, reject bad rows
INSERT INTO silver_orders
SELECT DISTINCT
CAST(order_id AS INT),
CAST(order_date AS DATE),
UPPER(TRIM(customer)),
CAST(amount AS DECIMAL(10,2))
FROM bronze_orders
WHERE amount IS NOT NULL;
-- check the result
SELECT * FROM silver_orders ORDER BY order_id;
-- Step 2: Rows rejected on the way to silver (send them to a quarantine table)
SELECT order_id, customer, _file
FROM bronze_orders
WHERE amount IS NULL;
-- Step 3: Silver to gold: aggregate for the business
INSERT INTO gold_daily_sales
SELECT order_date, COUNT(*), SUM(amount)
FROM silver_orders
GROUP BY order_date;
-- check the result
SELECT * FROM gold_daily_sales ORDER BY order_date;
Key points
- Staging: land data as-is, then transform
- Decouples the source; loads can be re-run
- Bronze: raw, as landed, with history
- Silver: clean, typed, deduplicated
- Gold: business-ready stars and aggregates
Question 39 — What is a data mart? What is a data mart, and how is it different from a data warehouse?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.