Bronze, silver, and gold data layers explained with an ecommerce order

Bronze, silver, and gold are three stages of the same data. Bronze keeps records as they arrived. Silver cleans, deduplicates, and matches them. Gold shapes them for business questions.

The easiest way to see it is to follow one order. Here's a made-up order, with a duplicate record and a refund that shows up three weeks later.

The pattern comes from the lakehouse world, where Databricks calls it the medallion architecture. It's a design pattern, and the Azure Databricks docs call it a recommended best practice, not a requirement. You can use it on any warehouse, and the layer names don't matter as much as the jobs.

The order

A customer places order 1001 on Sep 28: $80 of items, an $8 discount, $6 shipping, and $5.76 tax. On Oct 20, the store refunds $24 of it. The $24 is a discounted merchandise refund, excluding shipping and tax.

Bronze: keep what arrived

Bronze keeps records close to how they arrived, with minimal validation. An ingestion tool loads it, whether that's a managed connector, an export, or your own script. Nothing's fixed here.

Here's what the warehouse received for this order:

Record	Order	Updated at	Loaded at	Note
e1	1001	Sep 28, 14:02	15:00	First load
e2	1001	Sep 28, 14:02	16:00	Same version, loaded twice
e3	1001	Sep 29, 09:10	Sep 29, 10:00	Newer version
r1	1001	Oct 20, 13:00	Oct 20, 14:00	Refund, $24

Three order records for one order. Add up those three rows and you'd count $216 of items after discount, not $72. That's fine, because bronze isn't for reporting.

Its job is to let you start over. The Databricks docs describe bronze as keeping the raw state of the source data and retaining history for reprocessing and audit. If a rule turns out wrong, or a source changes a field, you can rebuild from it. Load time also tells you when you learned something, which can differ from when it happened.

The grain of bronze is one row per record received.

Silver: one clean version of each thing

Silver is where you clean, deduplicate, and standardize. The Databricks docs list deduplication, type casting, joins, and resolving out-of-order and late-arriving data as silver work.

This query keeps the newest version of each order:

with ranked as (
  select *, row_number() over (
    partition by order_id
    order by updated_at desc, loaded_at desc
  ) as version_rank
  from bronze_order_events
)
select order_id, subtotal - discount as item_sales_after_discount
from ranked
where version_rank = 1

I ran it on these rows in SQLite. Three records go in, and one comes out: order 1001 with $72 of item sales after discount.

The grain of silver orders is now one row per order, with order_id as the key. Refunds sit in their own table, at one row per refund. Keep them apart because they're different events on different dates.

Silver is also where you match records, like tying the order to a customer ID or a product to its cost. Those matches get their own tests, and the failures go to an exception list.

Silver should keep at least one detailed version of each record, even if you add aggregates.

Gold: answer a business question

Gold is shaped for people and reports. It applies the business rules, like what counts as net sales and which date a refund belongs to.

Here's where the late refund matters. The same $24 can land in two valid places:

Gold table	September	October
Daily sales, by refund date	$72	-$24
Order performance, tied back to the order	$48	$0

Neither is wrong. The first matches how Shopify's sales reports date reversals. The second is what you'd want for cohort LTV. Note that September in the second table changed after the month closed. That's restating, and your reports should say when a number can still move.

Gold tables have a grain too. fct_order_lines might be one row per order line. fct_daily_sales might be one row per day and channel. Naming the grain stops people from joining tables in ways that repeat rows. The Databricks docs note that some teams build more than one gold layer, such as one for finance and one for marketing.

Which tool does what?

These jobs often get blurred.

• Ingestion loads source data into bronze. That's the connector or script.
• dbt is commonly used to build silver and gold. It works alongside ingestion tools and transforms data already in your warehouse. It doesn't load your sources for you.
• Tests only help if they run. dbt's data tests, such as unique and not_null, have to be configured and run, whether by hand or on a schedule. A test on order_id in silver would have caught the duplicate if the query above were wrong.
• The warehouse stores all of it, and can be any platform that fits.

What gold doesn't fix

A gold table doesn't guarantee a correct AI answer. An assistant still has to choose the right table and metric, and "gold" doesn't tell it that two tables date refunds differently. A semantic layer can name those definitions, and it needs testing too.

Gold doesn't make access safe either. A clean table can still contain data a team shouldn't see. Set permissions on purpose. It's also a reason to keep people and assistants out of bronze, which can hold raw personal data.

Do you need all three?

Not always by those names. A small project might use staging, intermediate, and reporting folders, which is the same idea. What you shouldn't skip is a raw copy you can rebuild from, one clean version of each record, and business tables with a stated grain.

This order is sample data, checked locally, and a real source will have more edge cases. If your order data is hard to reconcile, the ecommerce data warehouse page shows how I'd start, and why reporting takes time covers what to expect.