Medallion Architecture cheat sheet

Bronze, silver, and gold layer contracts — what belongs in each, naming, testing, and where teams get it wrong.

ArchitectureIntermediate6 sections

Layer contracts

Bronze: raw, append-only, source-shaped: Land data exactly as received plus ingestion metadata. No renaming, no casting, no business logic — ever.
Silver: cleaned, typed, deduplicated, conformed: One row per business entity at a declared grain. Joins to reference data happen here, not in bronze.
Gold: aggregated, business-facing, BI-ready: Facts and dimensions or wide serving tables. This is the only layer most consumers should ever query.
Every layer rebuildable from the one below: The property that makes the architecture worth the extra tables. If gold cannot be rebuilt from silver, the layering is decorative.

_ingested_at, _source_file, _batch_id: Ingestion metadata on every bronze row. Without it, tracing a bad value back to its file is impossible.
Store payloads as VARIANT or a raw string column: Schema-on-read in bronze means an upstream schema change lands data instead of failing the pipeline.
Partition by ingestion date, not by business date: Ingestion date is known at write time and never changes. Business date arrives late and would force rewrites.
No updates, no deletes: Append-only makes bronze an auditable log. CDC deletes become rows with an operation flag, not physical deletes.

qualify row_number() over ( partition by order_id order by _ingested_at desc ) = 1: Deduplication is the defining silver transformation. Pick the latest version per natural key.
Declare the grain in the model description: One sentence, such as one row per order line per day. Nearly every double-counting bug is an undeclared grain.
Cast types explicitly and fail loudly: Silver is where a string that should be a number becomes an error. Coercing silently to null moves the bug downstream.
Surrogate keys from a stable hash: md5 over the business keys. Never depend on a source system's auto-increment ID surviving a migration.
SCD2 for dimensions that need history: valid_from, valid_to, is_current. Decide per dimension; not everything needs history.

fct_ and dim_ prefixes: Facts hold measures at one grain; dimensions hold attributes. Consumers should be able to guess the shape from the name.
Pre-aggregate what dashboards scan repeatedly: A daily rollup queried a thousand times a day beats a live aggregate over the raw fact every time.
One metric definition, one place: If revenue is computed in three gold tables, the three will disagree within a quarter. Centralize the calculation.
Grant read access at the gold schema level: Consumers get gold only. Access to silver invites bypass queries that fork business logic.

analytics.bronze / analytics.silver / analytics.gold: Schemas per layer inside one database. Simpler to grant and clone than a database per layer.
models/staging, models/intermediate, models/marts: The dbt folder convention that maps onto the layers. staging equals silver entry, marts equals gold.
stg_ / int_ / fct_ and dim_ prefixes: Prefixes make lineage readable in a list. int_ models are private implementation details, never exposed.
One folder per source in staging: staging/salesforce, staging/stripe. Keeps ownership obvious when a source breaks.

Business logic in bronze: The most common mistake. Once bronze is transformed, you can no longer replay history after a logic fix.
Skipping silver: Bronze straight to gold means every gold model re-implements dedup and typing, and they will drift apart.
Gold tables reading other gold tables: Creates a hidden dependency web. Push shared logic down into silver or an intermediate model instead.
A layer with no tests: Silver needs uniqueness and not-null on its keys at minimum. An untested layer boundary is not a contract.
Medallion for a five-table pipeline: Three layers on a trivial pipeline is ceremony. Adopt layers when multiple sources and consumers actually exist.

From DataLane — tutorials at/blog, practice SQL live in theplayground.