are likely to turn out to be tougher to belief as they develop in scope, and so they definitely turn out to be tougher 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 it, no person is sort of positive which model of the info will 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 drawback. It divides a knowledge platform into three layers, normally known as bronze, silver, and gold. On the boundary of every layer, there ought to be a transparent, documented description of the info contained in that layer. That is very true of the bronze layer, as that’s the place preliminary ingestion of your knowledge takes place, so that you’ll wish 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 typically it’s loaded, and so forth.
In a great world, the info in every layer will get there utilizing instruments equivalent 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 a knowledge and AI firm whose cloud platform helps organisations course of, handle and analyse giant datasets utilizing applied sciences equivalent to Apache Spark and Delta Lake.
Databricks describes the medallion construction as a multi-layered sample wherein knowledge high quality improves progressively as knowledge strikes by way of the three layers.
Sometimes, the bronze degree is used to retailer uncooked, unfiltered knowledge because it arrives from the supply. Information are usually immutable and append-only.
Silver accommodates a cleaned-up model of the info in bronze. For instance, null information, invalid dates, lacking fields, and so forth., could be remedied or eliminated earlier than being saved right here.
Gold typically accommodates specialised, mixture datasets outlined as SQL (materialised) views derived from Silver that align with enterprise guidelines. For instance, knowledge dashboards and administration studies are normally constructed from knowledge within the Gold layer as a result of the info is appropriate, tends to be smaller, and results in higher accuracy and decrease processing occasions.
In fact, techniques like this have been round so long as knowledge has. Most database engineers can have used a “staging” space to deliver knowledge 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. Observe that in real-life techniques, the gold, silver, and bronze layers normally correspond to completely different schemas inside a contemporary database or knowledge warehouse.
Bronze
Bronze is a document of what arrived from the supply. Helpful bronze knowledge may additionally embody ingestion metadata alongside the supply fields equivalent 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 kinds of knowledge points at this layer stage is necessary.
For unhealthy and/or lacking knowledge values, these ought to be retained as-is and quarantined on the silver degree if required. If a knowledge load fails half-way by way of, due to a community failure, for instance, the load ought to be marked as failed or outdated and re-loaded as a brand new batch.
If further or late knowledge arrives, append it as one other batch and document 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 method is taken, ingestion ought to be idempotent. Processing the identical supply supply greater than as soon as shouldn’t create duplicate information or in any other case change the ensuing state.
Silver
Silver applies guidelines to the bronze layer knowledge set that make information reliable and correct sufficient to be usable. Typical transformation work contains,
- parsing and imposing knowledge varieties
- standardising dates, currencies, nation codes and models
- deduplicating information
- quarantining duff knowledge
- becoming a member of reference knowledge
Silver ought to normally retain business-level element. It’s the clear, foundational knowledge 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 kind. An order_id ought to have a documented uniqueness rule. If a row fails these guidelines, the pipeline wants an express final result, e.g insertion right into a quarantine desk, slightly than a silent omission.
Gold
Gold is organised round explicit enterprise use circumstances and processes. Gold sometimes contains:
- Summarised and aggregated knowledge units equivalent to totals and counts by day, month, or area (e.g., complete gross sales, lively customers).
- Star schemas or knowledge marts constructed for quick queries with fewer joins.
- Tailor-made, separate knowledge units for particular groups like finance, advertising and marketing, or operations.
Tying the whole lot collectively here’s a diagram of what a typical, quite simple, Medallion system may appear to be.
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 simply normally implement a medallion structure utilizing some sort of database, knowledge 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. It will work on something from SQLite in your native laptop computer to an AWS Redshift knowledge lake on an enormous cloud-based cluster or AWS S3/Azure Blob/Google Cloud Storage.
Particularly for cloud primarily based object storage you’ll additionally want to consider the open desk format that you simply wish to use. The three most typical are Hudi, Apache Iceberg and Delta tables.
By way of the software program tooling for use, I see the medallion sample as simply one other a part of common knowledge engineering (DE). So, the instruments that knowledge engineers use of their day-to-day jobs are the identical ones used to arrange and preserve medallion techniques. SQL will likely be your fundamental go-to, and do not forget 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 primarily based structure you may additionally use instruments particular to that platform. I primarily use AWS, so I might in all probability be utilizing AWS Athena for knowledge querying, AWS Glue for pipeline improvement work and Step for orchestration.
Observe 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 unfavorable quantities have to be rejected. The pipeline runs in a single day in order that operations has paid and refunded gross sales totals, cut up by area and forex, by 07:00.
The pipeline has 5 phases:
- Retailer every CSV import unchanged within the append-only Bronze desk.
- Convert the fields to the proper varieties, validate the values and take away duplicate orders in Silver.
- Transfer rejected rows right into a quarantine desk for investigation.
- Combination the accepted orders into every 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 when you want it to.
Our mission structure will likely be much like this.
retail-medallion/
├── knowledge/
│ └── 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 surroundings 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 knowledge. Reserve it as a file known as orders_2026-07-19.csv beneath the info/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 great check to make sure our pipeline copes when knowledge is unhealthy.
Our pipeline code
Save the next code to pipeline.py within the mission’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 'unfavorable 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 fundamental(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")
fundamental(Path(sys.argv[1]))Run it utilizing this command.
python3 pipeline.py knowledge/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 | unfavorable 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 unhealthy dates, unfavorable quantities or repeated order IDs don’t make it that far. They’re saved 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, when 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, knowledge reloads into your bronze layer are one thing you’ll must cater for too. It’s not usually as massive a deal on your silver and gold layers, as these ought to at all times be reproducible out of your bronze layer knowledge, so when you get that proper, the whole lot else ought to fall into place.
You may examine the medallion layers immediately 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 - Day by 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 | unfavorable quantity |
+------------+---------------------------+----------+--------------------+
GOLD - Day by 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 knowledge engineers, we hear discuss of the Medallion sample in ETL jobs on a regular basis, and truthfully, you’ve in all probability already carried out at the very least a cut-down model of it many occasions. What I attempted to do on this article is offer you 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 knowledge and an area database, however the rules you would wish for a much bigger, productionised system are in place.
For manufacturing, you’ll have to determine whether or not you wish 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 think about whether or not you want a pipeline orchestration software equivalent to Airflow or Dagster.
And I’ve not even talked concerning the kinds 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 necessary level is to grasp the fundamentals of the medallion sample and recognise how and the place it may possibly match into your new or current ETL pipelines.
The medallion structure works as a result of it makes distinctions in your knowledge seen. Knowledge obtained isn’t the identical as knowledge validated, and knowledge validated isn’t routinely prepared for a specific enterprise choice.
