are inclined to change into more durable to belief as they develop in scope, and so they definitely change into more durable to run with out errors, to doc, and to debug.
A CSV arrives from one system, JSON comes from one other, a Parquet file from elsewhere. Weeks and months go previous, and earlier than you recognize it, no person is sort of positive which model of the information might be trusted, which guidelines have been utilized to it, or why an error occurred on yesterday’s dashboard.
The medallion structure is a sensible response to that downside. It divides an information platform into three layers, normally known as bronze, silver, and gold. On the boundary of every layer, there needs to be a transparent, documented description of the information contained in that layer. That is very true of the bronze layer, as that’s the place preliminary ingestion of your information takes place, so that you’ll need to write down as a lot info as you’ll be able to concerning the supply of information, who or which system masses it, when it was loaded, how usually it’s loaded, and many others.
In a super world, the information in every layer will get there utilizing instruments corresponding to SQL, Python, dbt and others.
The place did the medallion structure come from?
The bronze, silver and gold terminology was first proposed by Databricks. Databricks is an information and AI firm whose cloud platform helps organisations course of, handle and analyse giant datasets utilizing applied sciences corresponding to Apache Spark and Delta Lake.
Databricks describes the medallion construction as a multi-layered sample through which information high quality improves progressively as information strikes by means of the three layers.
Usually, the bronze degree is used to retailer uncooked, unfiltered information because it arrives from the supply. Information are usually immutable and append-only.
Silver accommodates a cleaned-up model of the information in bronze. For instance, null information, invalid dates, lacking fields, and many others., can be remedied or eliminated earlier than being saved right here.
Gold usually accommodates specialised, mixture datasets outlined as SQL (materialised) views derived from Silver that align with enterprise guidelines. For instance, information dashboards and administration studies are normally constructed from information within the Gold layer as a result of the information is right, tends to be smaller, and results in larger accuracy and decrease processing instances.
In fact, techniques like this have been round so long as information has. Most database engineers may have used a “staging” space to deliver information right into a system earlier than farming it out to the place it’s wanted lengthy earlier than they heard the time period “Medallion”. That’s a easy two-layer medallion system. Databricks simply added one other layer, gave it a flowery title and popularised it.
What belongs in every layer?
Let’s take a look in barely extra element at what every layer ought to ideally include. Notice that in real-life techniques, the gold, silver, and bronze layers normally correspond to completely different schemas inside a contemporary database or information warehouse.
Bronze
Bronze is a report of what arrived from the supply. Helpful bronze information may also embody ingestion metadata alongside the supply fields corresponding to
- supply system and supply file or occasion identifier
- ingestion timestamp and/or enterprise efficient date
- variety of information ingested
- batch or loading run identifier
The way you cope with errors and different varieties of information points at this layer stage is vital.
For dangerous and/or lacking information values, these needs to be retained as-is and quarantined on the silver degree if required. If an information load fails half-way by means of, due to a community failure, for instance, the load needs to be marked as failed or outmoded and re-loaded as a brand new batch.
If extra or late information arrives, append it as one other batch and report its supply, ingestion time and business-effective date.
If the identical supply is submitted twice, use a file hash, batch identifier or supply key to stop unintended duplication.
No matter strategy is taken, ingestion needs to be idempotent. Processing the identical supply supply greater than as soon as mustn’t create duplicate information or in any other case change the ensuing state.
Silver
Silver applies guidelines to the bronze layer information set that make information reliable and correct sufficient to be usable. Typical transformation work contains,
- parsing and implementing information sorts
- standardising dates, currencies, nation codes and items
- deduplicating information
- quarantining duff information
- becoming a member of reference information
Silver ought to normally retain business-level element. It’s the clear, foundational information that merchandise and downstream techniques can depend on.
Getting issues flawed at this degree can actually screw up your downstream techniques and processes. For instance, a silver order_total column ought to have an outlined forex and numeric sort. An order_id ought to have a documented uniqueness rule. If a row fails these guidelines, the pipeline wants an specific end result, e.g insertion right into a quarantine desk, moderately than a silent omission.
Gold
Gold is organised round explicit enterprise use instances and processes. Gold sometimes contains:
- Summarised and aggregated information units corresponding to totals and counts by day, month, or area (e.g., complete gross sales, energetic customers).
- Star schemas or information marts constructed for quick queries with fewer joins.
- Tailor-made, separate information units for particular groups like finance, advertising, or operations.
Tying all the pieces collectively here’s a diagram of what a typical, quite simple, Medallion system may seem like.
What instruments do I must implement a Medallion sample?
There’s no a method to do that, however as a starter, I’d say that you just normally implement a medallion structure utilizing some sort of database, information warehouse or cloud-based object storage the place your gold, silver, and bronze layers are sometimes completely different schemas in your database or folders in your object storage. This can work on something from SQLite in your native laptop computer to an AWS Redshift information lake on an enormous cloud-based cluster or AWS S3/Azure Blob/Google Cloud Storage.
Particularly for cloud based mostly object storage you’ll additionally want to consider the open desk format that you just need to use. The three commonest are Hudi, Apache Iceberg and Delta tables.
When it comes to the software program tooling for use, I see the medallion sample as simply one other a part of normal information engineering (DE). So, the instruments that information engineers use of their day-to-day jobs are the identical ones used to arrange and preserve medallion techniques. SQL can be your important go-to, and keep in mind that another instruments like dbt depend on SQL beneath the covers too. Apart from SQL, Python, Spark and different programming languages are sometimes used.
For cloud based mostly structure you may additionally use instruments particular to that platform. I primarily use AWS, so I’d in all probability be utilizing AWS Athena for information querying, AWS Glue for pipeline growth work and Step for orchestration.
Notice that, aside from being a consumer of the varied techniques and merchandise talked about on this article e.g DuckDB, I’ve no affiliation or industrial affiliation with any of them.
A working instance: retail orders with Python and DuckDB
For this instance, I’m utilizing the nightly CSV export from a small on-line retailer. The file wants some work earlier than it may be used for reporting. Orders could also be repeated, some dates fail to parse, and destructive quantities should be rejected. The pipeline runs in a single day in order that operations has paid and refunded gross sales totals, break up by area and forex, by 07:00.
The pipeline has 5 levels:
- Retailer every CSV import unchanged within the append-only Bronze desk.
- Convert the fields to the right sorts, validate the values and take away duplicate orders in Silver.
- Transfer rejected rows right into a quarantine desk for investigation.
- Mixture the accepted orders into each day regional gross sales figures in Gold.
- Prevents the identical supply file from being ingested twice.
Utilizing DuckDB as our database retains the instance small, however the layer contracts translate on to a bigger lakehouse should you want it to.
Our venture format can be just like this.
retail-medallion/
├── information/
│ └── incoming/
│ └── orders_2026-07-19.csv <= manually created by you
├── pipeline.py <= manually created by you
└── warehouse.duckdb <= this DB file is created by the pipelineCreate a digital atmosphere and set up DuckDB
D:projectsretail-medallion> python3 -m venv .venv
# Home windows PowerShell: ..venvScriptsActivate.ps1
# macOS/Linux: supply .venv/bin/activate
D:projectsretail-medallion> python3 -m pip set up duckdb pytz tabulate Creating an enter file
That is only a easy CSV, so open your favorite textual content editor and enter the next information. Reserve it as a file known as orders_2026-07-19.csv beneath the information/incoming folder.
order_id,ordered_at,customer_id,area,quantity,forex,standing
1001,2026-07-19T09:10:00Z,C001,North,125.50,GBP,paid
1002,2026-07-19T10:05:00Z,C002,South,89.99,GBP,paid
1002,2026-07-19T10:05:00Z,C002,South,89.99,GBP,paid
1003,not-a-date,C003,North,45.00,GBP,paid
1004,2026-07-19T11:42:00Z,C004,West,-10.00,GBP,paid
1005,2026-07-19T12:20:00Z,C005,North,210.00,GBP,refundedThe duplicate and invalid rows are deliberate and a superb take a look at to make sure our pipeline copes when information is dangerous.
Our pipeline code
Save the next code to pipeline.py within the venture’s house listing.
from __future__ import annotations
import hashlib
import sys
from pathlib import Path
import duckdb
DATABASE = Path("warehouse.duckdb")
def file_hash(path: Path) -> str:
digest = hashlib.sha256()
with path.open("rb") as supply:
for block in iter(lambda: supply.learn(1024 * 1024), b""):
digest.replace(block)
return digest.hexdigest()
def initialise(connection: duckdb.DuckDBPyConnection) -> None:
connection.execute("CREATE SCHEMA IF NOT EXISTS bronze")
connection.execute("CREATE SCHEMA IF NOT EXISTS silver")
connection.execute("CREATE SCHEMA IF NOT EXISTS gold")
connection.execute("""
CREATE TABLE IF NOT EXISTS bronze.ingestion_batches (
source_hash VARCHAR PRIMARY KEY,
source_file VARCHAR NOT NULL,
ingested_at TIMESTAMPTZ NOT NULL DEFAULT current_timestamp
)
""")
connection.execute("""
CREATE TABLE IF NOT EXISTS bronze.orders_raw (
order_id VARCHAR,
ordered_at VARCHAR,
customer_id VARCHAR,
area VARCHAR,
quantity VARCHAR,
forex VARCHAR,
standing VARCHAR,
source_file VARCHAR NOT NULL,
source_hash VARCHAR NOT NULL,
ingested_at TIMESTAMPTZ NOT NULL
)
""")
def ingest_bronze(connection: duckdb.DuckDBPyConnection, supply: Path) -> bool:
supply = supply.resolve()
digest = file_hash(supply)
already_loaded = connection.execute(
"SELECT 1 FROM bronze.ingestion_batches WHERE source_hash = ?", [digest]
).fetchone()
if already_loaded:
print(f"Skipping {supply.title}: this actual file has already been loaded")
return False
connection.start()
attempt:
connection.execute(
"""
INSERT INTO bronze.orders_raw
SELECT
order_id, ordered_at, customer_id, area, quantity,
forex, standing, ?, ?, current_timestamp
FROM read_csv(?, header = true, all_varchar = true)
""",
[source.name, digest, str(source)],
)
connection.execute(
"""INSERT INTO bronze.ingestion_batches (source_hash, source_file)
VALUES (?, ?)""",
[digest, source.name],
)
connection.commit()
besides Exception:
connection.rollback()
elevate
print(f"Loaded {supply.title} into bronze")
return True
def build_silver(connection: duckdb.DuckDBPyConnection) -> None:
connection.execute("""
CREATE OR REPLACE TEMP VIEW typed_orders AS
SELECT
trim(order_id) AS order_id,
try_cast(ordered_at AS TIMESTAMPTZ) AS ordered_at,
trim(customer_id) AS customer_id,
higher(trim(area)) AS area,
try_cast(quantity AS DECIMAL(18, 2)) AS quantity,
higher(trim(forex)) AS forex,
decrease(trim(standing)) AS standing,
source_file,
source_hash,
ingested_at,
row_number() OVER (
PARTITION BY trim(order_id)
ORDER BY ingested_at DESC, source_file DESC
) AS duplicate_rank
FROM bronze.orders_raw
""")
legitimate = """
order_id IS NOT NULL AND order_id <> ''
AND ordered_at IS NOT NULL
AND customer_id IS NOT NULL AND customer_id <> ''
AND quantity IS NOT NULL AND quantity >= 0
AND forex IN ('GBP', 'EUR', 'USD')
AND standing IN ('paid', 'refunded', 'cancelled')
AND duplicate_rank = 1
"""
connection.execute(f"""
CREATE OR REPLACE TABLE silver.orders AS
SELECT * EXCLUDE (duplicate_rank)
FROM typed_orders
WHERE {legitimate}
""")
connection.execute(f"""
CREATE OR REPLACE TABLE silver.orders_quarantine AS
SELECT
* EXCLUDE (duplicate_rank),
CASE
WHEN duplicate_rank > 1 THEN 'duplicate order_id'
WHEN ordered_at IS NULL THEN 'invalid ordered_at'
WHEN quantity IS NULL THEN 'invalid quantity'
WHEN quantity < 0 THEN 'destructive quantity'
WHEN forex NOT IN ('GBP', 'EUR', 'USD') THEN 'unsupported forex'
WHEN standing NOT IN ('paid', 'refunded', 'cancelled') THEN 'invalid standing'
ELSE 'lacking required worth'
END AS rejection_reason
FROM typed_orders
WHERE NOT ({legitimate})
""")
def build_gold(connection: duckdb.DuckDBPyConnection) -> None:
connection.execute("""
CREATE OR REPLACE TABLE gold.daily_sales_by_region AS
SELECT
forged(ordered_at AS DATE) AS order_date,
area,
forex,
depend(*) FILTER (WHERE standing = 'paid') AS paid_orders,
sum(quantity) FILTER (WHERE standing = 'paid') AS gross_sales,
depend(*) FILTER (WHERE standing = 'refunded') AS refunded_orders,
sum(quantity) FILTER (WHERE standing = 'refunded') AS refunded_value
FROM silver.orders
GROUP BY order_date, area, forex
ORDER BY order_date, area, forex
""")
def check_quality(connection: duckdb.DuckDBPyConnection) -> None:
duplicate_count = connection.execute(
"SELECT depend(*) - depend(DISTINCT order_id) FROM silver.orders"
).fetchone()[0]
null_key_count = connection.execute(
"SELECT depend(*) FROM silver.orders WHERE order_id IS NULL"
).fetchone()[0]
if duplicate_count or null_key_count:
elevate RuntimeError("Silver high quality contract failed")
def print_query(connection: duckdb.DuckDBPyConnection, question: str) -> None:
end result = connection.execute(question)
print(" | ".be part of(column[0] for column in end result.description))
for row in end result.fetchall():
print(" | ".be part of("NULL" if worth is None else str(worth) for worth in row))
def important(supply: Path) -> None:
with duckdb.join(str(DATABASE)) as connection:
initialise(connection)
ingest_bronze(connection, supply)
build_silver(connection)
check_quality(connection)
build_gold(connection)
print("nGold output")
print_query(connection, "SELECT * FROM gold.daily_sales_by_region")
print("nQuarantined information")
print_query(
connection,
"""SELECT order_id, ordered_at, quantity, rejection_reason
FROM silver.orders_quarantine""",
)
if __name__ == "__main__":
if len(sys.argv) != 2:
elevate SystemExit("Utilization: python pipeline.py path/to/orders.csv")
important(Path(sys.argv[1]))Run it utilizing this command.
python3 pipeline.py information/incoming/orders_2026-07-19.csvAnd the output?
Loaded orders_2026-07-19.csv into bronze
Gold output
order_date | area | forex | paid_orders | gross_sales | refunded_orders | refunded_value
2026-07-19 | NORTH | GBP | 1 | 125.50 | 1 | 210.00
2026-07-19 | SOUTH | GBP | 1 | 89.99 | 0 | NULL
Quarantined information
order_id | ordered_at | quantity | rejection_reason
1004 | 2026-07-19 12:42:00+01:00 | -10.00 | destructive quantity
1003 | NULL | 45.00 | invalid ordered_at
1002 | 2026-07-19 11:05:00+01:00 | 89.99 | duplicate order_idAfter the run, Gold has one row for every date, area and forex, with separate figures for paid and refunded orders. Rows with dangerous dates, destructive quantities or repeated order IDs don’t make it that far. They’re stored in silver.orders_quarantine desk to allow them to be checked.
In my instance, I elected to maintain issues easy and disallow reloads of the identical enter into the bronze layer utilizing a file hash. So, should you run the command a second time, you’ll see that the bronze ingestion half is skipped altogether as a result of the file hash already exists. In a manufacturing system, information reloads into your bronze layer are one thing you’ll must cater for too. It’s not typically as massive a deal to your silver and gold layers, as these ought to all the time be reproducible out of your bronze layer information, so should you get that proper, all the pieces else ought to fall into place.
You’ll be able to examine the medallion layers straight utilizing code like this.
import duckdb
from tabulate import tabulate
def show_table(
connection: duckdb.DuckDBPyConnection,
title: str,
question: str,
) -> None:
end result = connection.execute(question)
headers = [column[0] for column in end result.description]
print(f"n{title}")
print(tabulate(end result.fetchall(), headers=headers, tablefmt="psql"))
with duckdb.join("warehouse.duckdb") as connection:
show_table(
connection,
"BRONZE - Uncooked orders",
"""
SELECT
order_id,
ordered_at,
customer_id,
area,
quantity,
forex,
standing,
source_file
FROM bronze.orders_raw
ORDER BY order_id
""",
)
show_table(
connection,
"SILVER - Validated orders",
"""
SELECT
order_id,
ordered_at,
customer_id,
area,
quantity,
forex,
standing
FROM silver.orders
ORDER BY order_id
""",
)
show_table(
connection,
"SILVER - Quarantined orders",
"""
SELECT
order_id,
ordered_at,
quantity,
rejection_reason
FROM silver.orders_quarantine
ORDER BY order_id
""",
)
show_table(
connection,
"GOLD - Each day gross sales by area",
"""
SELECT *
FROM gold.daily_sales_by_region
ORDER BY order_date, area
""",
)Which ends up in the next output.
BRONZE - Uncooked orders
+------------+----------------------+---------------+----------+----------+------------+----------+-----------------------+
| order_id | ordered_at | customer_id | area | quantity | forex | standing | source_file |
|------------+----------------------+---------------+----------+----------+------------+----------+-----------------------|
| 1001 | 2026-07-19T09:10:00Z | C001 | North | 125.5 | GBP | paid | orders_2026-07-19.csv |
| 1002 | 2026-07-19T10:05:00Z | C002 | South | 89.99 | GBP | paid | orders_2026-07-19.csv |
| 1002 | 2026-07-19T10:05:00Z | C002 | South | 89.99 | GBP | paid | orders_2026-07-19.csv |
| 1003 | not-a-date | C003 | North | 45 | GBP | paid | orders_2026-07-19.csv |
| 1004 | 2026-07-19T11:42:00Z | C004 | West | -10 | GBP | paid | orders_2026-07-19.csv |
| 1005 | 2026-07-19T12:20:00Z | C005 | North | 210 | GBP | refunded | orders_2026-07-19.csv |
+------------+----------------------+---------------+----------+----------+------------+----------+-----------------------+
SILVER - Validated orders
+------------+---------------------------+---------------+----------+----------+------------+----------+
| order_id | ordered_at | customer_id | area | quantity | forex | standing |
|------------+---------------------------+---------------+----------+----------+------------+----------|
| 1001 | 2026-07-19 10:10:00+01:00 | C001 | NORTH | 125.5 | GBP | paid |
| 1002 | 2026-07-19 11:05:00+01:00 | C002 | SOUTH | 89.99 | GBP | paid |
| 1005 | 2026-07-19 13:20:00+01:00 | C005 | NORTH | 210 | GBP | refunded |
+------------+---------------------------+---------------+----------+----------+------------+----------+
SILVER - Quarantined orders
+------------+---------------------------+----------+--------------------+
| order_id | ordered_at | quantity | rejection_reason |
|------------+---------------------------+----------+--------------------|
| 1002 | 2026-07-19 11:05:00+01:00 | 89.99 | duplicate order_id |
| 1003 | | 45 | invalid ordered_at |
| 1004 | 2026-07-19 12:42:00+01:00 | -10 | destructive quantity |
+------------+---------------------------+----------+--------------------+
GOLD - Each day gross sales by area
+--------------+----------+------------+---------------+---------------+-------------------+------------------+
| order_date | area | forex | paid_orders | gross_sales | refunded_orders | refunded_value |
|--------------+----------+------------+---------------+---------------+-------------------+------------------|
| 2026-07-19 | NORTH | GBP | 1 | 125.5 | 1 | 210 |
| 2026-07-19 | SOUTH | GBP | 1 | 89.99 | 0 | |
+--------------+----------+------------+---------------+---------------+-------------------+------------------+Abstract
As database and information engineers, we hear discuss of the Medallion sample in ETL jobs on a regular basis, and truthfully, you’ve in all probability already applied not less than a cut-down model of it many instances. What I attempted to do on this article is provide you with a flavour of the way you may implement a sensible Medallion structure from first rules.
Don’t get me flawed. The instance I confirmed you was very a lot a toy instance. It used restricted enter information and a neighborhood database, however the rules you would wish for an even bigger, productionised system are in place.
For manufacturing, you’ll have to resolve whether or not you need to use an enterprise-level RDBMS like Postgres or Oracle or use cloud-based object storage like AWS S3. If the latter you’ll have to take into consideration what transactional desk storage format to make use of, hudi, delta tables or iceberg. You’ll additionally want to contemplate whether or not you want a pipeline orchestration instrument corresponding to Airflow or Dagster.
And I’ve not even talked concerning the varieties of automated checks you would wish for layer boundaries. Examples embody:
- bronze row counts and supply completeness
- silver key uniqueness, accepted-value checks and referential integrity
- gold reconciliation in opposition to silver totals
- freshness and quantity thresholds
- alerts for quarantine charges and schema drift.
However these are simply the toppings on the cake. The vital level is to grasp the fundamentals of the medallion sample and recognise how and the place it could possibly match into your new or present ETL pipelines.
The medallion structure works as a result of it makes distinctions in your information seen. Information obtained isn’t the identical as information validated, and information validated isn’t robotically prepared for a specific enterprise resolution.
