Browse cheat sheets · Microsoft Fabric
Fabric Medallion Architecture
Design a practical Bronze, Silver, and Gold architecture in Microsoft Fabric with clear data contracts, replayability, incremental processing, quality gates, and serving responsibilities.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Keep Bronze source-aligned
- 2. Add ingestion metadata in Bronze
- 3. Keep raw source columns when parsing can fail
- 4. Move schema normalization into Silver
- 5. Validate required keys in Silver
- 6. Quarantine invalid records instead of deleting evidence
- 7. Deduplicate in Silver using deterministic rules
- 8. Use MERGE for mutable Silver entities
- 9. Keep Bronze append-only when possible
- 10. Use Silver for canonical reusable entities
- 11. Build Gold from trusted Silver, not directly from raw data
- 12. Put business metrics in Gold
- 13. Keep Gold grain explicit
- 14. Validate Gold uniqueness
- 15. Reconcile totals between Silver and Gold
- 16. Use incremental Bronze ingestion
- 17. Do not advance the watermark before data is durable
- 18. Make incremental Silver idempotent
- 19. Carry operational lineage across layers
- 20. Separate technical metadata from business columns
- 21. Use a Lakehouse-first medallion design for engineering-heavy workloads
- 22. Use Warehouse as Gold when SQL-first serving is required
- 23. Gold does not have to mean aggregation only
- 24. Do not mix source cleanup and consumer formatting
- 25. Use shortcuts when the data should not be copied
- 26. Keep domain ownership clear
- 27. Organize by domain first when scale grows
- 28. Use quality gates between layers
- 29. Record quality metrics
- 30. Avoid recomputing the same expensive Silver logic for every Gold consumer
- 31. Keep layer contracts versionable
- 32. Prefer explicit schemas for stable layers
- 33. Do not use full-table overwrite for every Silver refresh
- 34. Use full rebuilds intentionally for deterministic small Gold tables
- 35. Keep history when the business needs history
- 36. Keep late-arriving data in the design
- 37. Reprocess from Bronze when transformation logic changes
- 38. Keep business-time and ingestion-time separate
- 39. Use Gold tables to stabilize consumer contracts
- 40. Document every layer with four questions
- What the result means
- What to look for
- Bronze contains heavy business logic
- Silver tables are different for every report
- Gold still contains raw source codes and cleanup logic
- Rerunning a batch creates duplicates
- Data cannot be replayed after a bug
- Gold totals do not reconcile with Silver
- Many consumers independently clean the same fields
- Production notes
- Medallion architecture is logical, not mandatory physical separation
- Bronze retention has a cost
- Silver should be the main reusable engineering contract
- Gold can be implemented in Lakehouse or Warehouse
- Incremental processing must be end-to-end
- Quality checks should match the layer
- OneLake reduces the need for copies
- Common mistakes
- 1. Treating Bronze as a dumping ground with no metadata
- 2. Cleaning data destructively in Bronze
- 3. Using dropDuplicates() without deciding which record should survive
- 4. Putting all business logic directly in Gold
- 5. Making Silver consumer-specific
- 6. Rebuilding every large table from scratch every run
- 7. Advancing the watermark before the write is durable
- 8. Ignoring late-arriving data
- 9. Creating a Warehouse just because “Gold should be a warehouse”
- 10. Creating three layers with no actual contract differences
- Next step
- Related deep dives
Problem
You need to organize a Microsoft Fabric data platform so raw ingestion, cleansing, business transformation, and reporting do not become one large notebook or one fragile table.
A medallion architecture separates responsibilities into layers:
Bronze
→ source-aligned, replayable ingestion
Silver
→ validated, standardized, deduplicated data
Gold
→ business-ready serving models
The difficult part is not naming the layers.
The real design questions are:
- What exactly belongs in each layer?
- Should Bronze be mutable or append-only?
- Where should deduplication happen?
- Where should business rules live?
- When should Gold be Lakehouse vs Warehouse?
- How do incremental loads move through the layers?
- Where do data-quality failures go?
- How do you rerun safely without duplicating data?
- How do you avoid duplicating the same transformations in every consumer?
Quick answer
Use this responsibility model:
Bronze
- preserve source fidelity
- append or land source data
- keep ingestion metadata
- make replay possible
Silver
- standardize schema
- parse types
- deduplicate
- validate keys
- apply source-level business rules
- create trusted reusable entities
Gold
- shape data for consumption
- aggregate
- model business metrics
- build facts/dimensions or curated reporting tables
- optimize for BI/API/reporting use
A practical Fabric flow:
- Source systems
- Pipelines / shortcuts / notebooks
- Lakehouse Bronze
- Lakehouse Silver
- GoldLakehouse Gold or Fabric Warehouse
- Semantic models / Power BI / APIs / reports
Do not create a layer just because the architecture diagram says it should exist.
Each layer should remove a specific type of ambiguity or operational risk.
Code / commands
1. Keep Bronze source-aligned
Example source event:
{
"sale_id": "10001",
"merchant_id": "501",
"sale_time": "2026-10-04T15:30:00Z",
"amount": "125.50"
}
A Bronze representation should preserve the source meaning and add ingestion metadata rather than immediately reshaping everything into business aggregates.
Example PySpark:
from pyspark.sql import functions as F
bronze = (
source_df
.withColumn(
"_ingested_at_utc",
F.current_timestamp(),
)
.withColumn(
"_source_file",
F.input_file_name(),
)
.withColumn(
"_pipeline_run_id",
F.lit(pipeline_run_id),
)
)
Write:
(
bronze.write
.format("delta")
.mode("append")
.saveAsTable("bronze_sales")
)
Use Bronze to preserve enough evidence to reprocess data later.
2. Add ingestion metadata in Bronze
Useful metadata can include:
_ingested_at_utc
_pipeline_run_id
_batch_id
_source_file
_source_system
_source_modified_at
Example:
bronze = (
source_df
.withColumn(
"_source_system",
F.lit("transaction-api"),
)
.withColumn(
"_batch_id",
F.lit(batch_id),
)
.withColumn(
"_ingested_at_utc",
F.current_timestamp(),
)
)
This helps with:
- lineage
- replay
- duplicate investigation
- source reconciliation
- incident analysis
Do not add sensitive credentials or secrets as metadata.
3. Keep raw source columns when parsing can fail
Suppose Bronze contains:
amount = "$125.50"
sale_time = "10/04/2026 15:30"
In Silver, keep the original value while creating the typed value during validation:
silver_stage = (
bronze
.withColumn(
"amount_decimal",
F.regexp_replace(
F.col("amount"),
r"[\$,]",
"",
).cast("decimal(18,2)"),
)
.withColumn(
"sale_timestamp",
F.to_timestamp(
"sale_time",
"MM/dd/yyyy HH:mm",
),
)
)
Do not destroy the original source value before you know whether parsing succeeded.
4. Move schema normalization into Silver
Example:
silver = (
bronze
.select(
F.col("sale_id").cast("long").alias("sale_id"),
F.col("merchant_id").cast("int").alias("merchant_id"),
F.to_timestamp("sale_time").alias("sale_time"),
F.col("amount").cast("decimal(18,2)").alias("amount"),
"_ingested_at_utc",
"_pipeline_run_id",
)
)
Silver is where source-specific quirks should become a stable internal contract.
5. Validate required keys in Silver
Example:
invalid_keys = (
silver
.filter(
F.col("sale_id").isNull()
| F.col("merchant_id").isNull()
)
)
Count:
invalid_count = invalid_keys.count()
If required keys must always exist:
if invalid_count > 0:
raise RuntimeError(
f"Silver validation failed: "
f"{invalid_count} rows have missing required keys"
)
Whether to fail or quarantine depends on the source contract.
Do not silently drop invalid records without tracking them.
6. Quarantine invalid records instead of deleting evidence
Example:
valid = (
silver
.filter(
F.col("sale_id").isNotNull()
& F.col("merchant_id").isNotNull()
)
)
invalid = (
silver
.filter(
F.col("sale_id").isNull()
| F.col("merchant_id").isNull()
)
)
Write invalid rows separately:
(
invalid.write
.format("delta")
.mode("append")
.saveAsTable("quarantine_sales")
)
Use quarantine when bad rows need investigation rather than silent removal.
7. Deduplicate in Silver using deterministic rules
Example:
from pyspark.sql import Window
w = (
Window
.partitionBy("sale_id")
.orderBy(
F.col("source_updated_at").desc(),
F.col("_ingested_at_utc").desc(),
)
)
silver_deduped = (
silver
.withColumn(
"_rn",
F.row_number().over(w),
)
.filter(
F.col("_rn") == 1
)
.drop("_rn")
)
This is better than:
dropDuplicates(["sale_id"])
when the surviving row matters.
Define which row wins.
8. Use MERGE for mutable Silver entities
For changeable business entities:
from delta.tables import DeltaTable
target = DeltaTable.forName(
spark,
"silver_sales",
)
(
target.alias("t")
.merge(
silver_deduped.alias("s"),
"t.sale_id = s.sale_id",
)
.whenMatchedUpdateAll()
.whenNotMatchedInsertAll()
.execute()
)
Use MERGE when existing keys can receive corrections or later updates.
Do not blind-append mutable entities.
9. Keep Bronze append-only when possible
A strong default:
Bronze = append source history
Silver = reconcile current trusted state
This makes it easier to:
- replay transformations
- investigate source defects
- compare old/new logic
- repair downstream layers
There are valid exceptions, but mutating Bronze should be a deliberate decision.
10. Use Silver for canonical reusable entities
Example Silver tables:
silver_sales
silver_customers
silver_merchants
silver_products
silver_commissions
These should be reusable by multiple downstream consumers.
Avoid consumer-specific logic such as:
Power BI dashboard formatting
one-off report labels
API-specific column naming
inside shared Silver tables unless it is truly part of the enterprise business definition.
11. Build Gold from trusted Silver, not directly from raw data
Example:
gold_daily_sales = (
spark.table("silver_sales")
.groupBy(
"sale_date",
"merchant_id",
)
.agg(
F.sum("amount").alias("revenue"),
F.countDistinct("sale_id").alias("sales"),
)
)
Write:
(
gold_daily_sales.write
.format("delta")
.mode("overwrite")
.saveAsTable("gold_daily_sales")
)
Use a controlled incremental pattern for large Gold tables instead of always rebuilding them.
12. Put business metrics in Gold
Examples:
daily merchant revenue
customer lifetime value
commission summary
monthly active customers
conversion metrics
reporting facts and dimensions
Gold is the correct place to define business-ready semantics that should be consistent across consumers.
13. Keep Gold grain explicit
For every Gold table, document:
One row represents:
merchant + calendar date
or:
One row represents:
customer + reporting month
Example:
gold_merchant_daily_sales
grain = merchant_id + sale_date
This prevents downstream double counting.
14. Validate Gold uniqueness
If the grain is:
merchant_id + sale_date
validate it:
duplicates = (
gold_daily_sales
.groupBy(
"merchant_id",
"sale_date",
)
.count()
.filter(
F.col("count") > 1
)
)
if duplicates.limit(1).count() > 0:
raise RuntimeError(
"Gold grain validation failed"
)
A Gold table should enforce its intended logical grain operationally, even if the storage layer does not enforce a unique constraint.
15. Reconcile totals between Silver and Gold
Example:
silver_total = (
spark.table("silver_sales")
.filter(
F.col("sale_date") == "2026-10-04"
)
.agg(
F.sum("amount").alias("amount")
)
.first()["amount"]
)
gold_total = (
spark.table("gold_daily_sales")
.filter(
F.col("sale_date") == "2026-10-04"
)
.agg(
F.sum("revenue").alias("amount")
)
.first()["amount"]
)
Then compare:
if silver_total != gold_total:
raise RuntimeError(
"Silver-to-Gold reconciliation failed"
)
Use tolerances where floating-point arithmetic or valid transformation rules require them.
For financial data, prefer exact decimal types where practical.
16. Use incremental Bronze ingestion
Example pattern:
last processed watermark
→ source records after watermark
→ append Bronze
→ persist new watermark only after success
A watermark can be:
source_modified_at
incrementing ID
CDC sequence
file arrival time
batch ID
Choose one that matches the source semantics.
17. Do not advance the watermark before data is durable
Bad order:
1. update watermark
2. write Bronze
Safer:
1. read source
2. write Bronze
3. validate write
4. update watermark
Otherwise, a failure between steps can permanently skip data.
18. Make incremental Silver idempotent
Example:
Bronze batch
→ dedupe source
→ MERGE Silver by business key
This allows the same Bronze batch to be reprocessed safely.
Use stable business keys and update ordering.
19. Carry operational lineage across layers
Example columns:
_source_system
_batch_id
_pipeline_run_id
_ingested_at_utc
_source_updated_at
You do not need every metadata field in every Gold table.
But keep enough lineage to answer:
Where did this row come from?
Which load produced it?
Which source timestamp won?
20. Separate technical metadata from business columns
A clear convention helps:
Business columns
sale_id
merchant_id
amount
Technical columns
_ingested_at_utc
_batch_id
_pipeline_run_id
This makes operational fields obvious.
Use a naming standard consistently.
21. Use a Lakehouse-first medallion design for engineering-heavy workloads
A common Fabric implementation:
Lakehouse
bronze_*
silver_*
gold_*
Use this when:
- Spark is the primary engine
- Delta tables are the main storage
- engineering teams own transformation logic
- Power BI can consume curated Lakehouse data
This is often enough without adding a Warehouse.
22. Use Warehouse as Gold when SQL-first serving is required
Example:
Lakehouse Bronze
→ Lakehouse Silver
→ Fabric Warehouse Gold
Use this when the serving layer needs:
- full T-SQL DML
- stored procedures
- dimensional modeling
- relational serving
- multi-table SQL transactions
Do not copy data into Warehouse unless the SQL serving layer adds clear value.
23. Gold does not have to mean aggregation only
A Gold table can be:
fact table
dimension
wide reporting table
business entity
aggregate
API-serving dataset
The defining property is:
consumer-ready business semantics
not simply:
aggregated data
24. Do not mix source cleanup and consumer formatting
Example:
Silver:
country_code = "US"
Gold/report:
country_display = "United States"
Keep source normalization and consumer presentation separate when they serve different purposes.
This reduces coupling between data engineering and one specific report.
25. Use shortcuts when the data should not be copied
If external or cross-domain data already exists and does not require physical duplication:
Source location
→ OneLake shortcut
→ Bronze/Silver consumer
Use shortcuts when:
- ownership stays elsewhere
- freshness should remain tied to the source
- duplicating data adds little value
Do not shortcut blindly when you need:
- stable snapshots
- source isolation
- controlled retention
- reproducible replay
26. Keep domain ownership clear
Example:
Sales domain
- bronze_sales
- silver_sales
- gold_sales_daily
Customer domain
- bronze_customers
- silver_customers
- gold_customer_360
Avoid one giant workspace/table namespace where nobody knows who owns a dataset.
Medallion layers do not replace domain ownership.
27. Organize by domain first when scale grows
A scalable pattern can be:
Sales Lakehouse
Bronze
Silver
Gold
Commission Lakehouse
Bronze
Silver
Gold
instead of:
One enterprise Bronze Lakehouse
One enterprise Silver Lakehouse
One enterprise Gold Lakehouse
Either can work.
Choose based on:
- team boundaries
- security
- deployment
- data ownership
- workload isolation
Do not force one topology onto every organization.
28. Use quality gates between layers
Example Bronze → Silver checks:
required fields present
schema parsable
business key valid
timestamp valid
source duplicate rules applied
Example Silver → Gold checks:
grain uniqueness
metric reconciliation
reference joins valid
no orphan keys where disallowed
reporting date coverage
Promote data only when the layer contract is met.
29. Record quality metrics
Example:
metrics = {
"rows_in": bronze.count(),
"rows_valid": valid.count(),
"rows_invalid": invalid.count(),
}
For large workloads, avoid triggering multiple expensive scans unnecessarily.
Prefer one aggregated quality pass where practical.
Operational metrics should include:
rows read
rows written
rejected rows
duplicate keys
null required keys
batch ID
run ID
30. Avoid recomputing the same expensive Silver logic for every Gold consumer
Bad:
Gold report A
→ parse raw
→ dedupe
→ normalize
Gold report B
→ parse raw
→ dedupe
→ normalize
Better:
Bronze
→ shared Silver
├→ Gold report A
└→ Gold report B
Silver should remove repeated source-level transformation logic.
31. Keep layer contracts versionable
If Silver changes:
customer_id int
→ customer_id string
that can break downstream Gold consumers.
Treat important schema changes as contracts.
Use:
- versioned deployment
- compatibility checks
- schema validation
- coordinated downstream changes
Do not allow uncontrolled schema evolution to become architecture.
32. Prefer explicit schemas for stable layers
Example:
from pyspark.sql.types import (
StructType,
StructField,
LongType,
IntegerType,
DecimalType,
TimestampType,
)
sales_schema = StructType([
StructField("sale_id", LongType(), False),
StructField("merchant_id", IntegerType(), False),
StructField("amount", DecimalType(18, 2), False),
StructField("sale_time", TimestampType(), False),
])
Explicit schemas are especially useful at ingestion boundaries with known contracts.
33. Do not use full-table overwrite for every Silver refresh
For large mutable tables, prefer:
incremental source
→ dedupe
→ MERGE Silver
over:
read all Bronze history
→ rebuild all Silver
→ overwrite
Full rebuilds can be useful for backfills or small tables, but should not be the default if incremental state is practical.
34. Use full rebuilds intentionally for deterministic small Gold tables
Example:
(
gold_dimension.write
.format("delta")
.mode("overwrite")
.saveAsTable("gold_dim_date")
)
A full rebuild is reasonable when:
- table is small
- logic is deterministic
- cost is low
- atomic replacement is operationally acceptable
Not every table needs incremental complexity.
35. Keep history when the business needs history
For changing entities such as customer attributes, decide whether Silver represents:
current state only
or:
historical versions
Do not accidentally overwrite history if the business requires slowly changing dimensions or auditability.
This choice affects:
- Silver model
- Gold dimensions
- merge logic
- effective dates
36. Keep late-arriving data in the design
Example:
sale_date = 2026-10-01
arrives = 2026-10-04
If Gold is partitioned or aggregated by business date, the pipeline must be able to update prior periods.
Do not assume:
today's ingestion only affects today's Gold
Use business timestamps and late-arrival rules explicitly.
37. Reprocess from Bronze when transformation logic changes
One major benefit of Bronze retention is replay.
Example:
Bug in Silver normalization
→ fix logic
→ replay affected Bronze date range
→ rebuild/MERGE Silver
→ rebuild affected Gold
This is much safer than trying to reconstruct original source values from already-transformed Gold.
38. Keep business-time and ingestion-time separate
Example:
sale_time
→ when the business event happened
_ingested_at_utc
→ when Fabric received it
Both can matter.
Do not replace one with the other.
This distinction is important for:
- late arrivals
- SLA monitoring
- replay
- source latency analysis
39. Use Gold tables to stabilize consumer contracts
A report should not need to understand:
raw source codes
duplicate-source behavior
CDC sequencing
source-specific timestamps
temporary staging columns
Gold should expose stable business meaning.
That reduces downstream duplication and coupling.
40. Document every layer with four questions
For each table, document:
1. What does one row represent?
2. What is the business key?
3. How is the table loaded?
4. What quality rules must hold?
Example:
Table:
silver_sales
Grain:
one row per sale_id
Business key:
sale_id
Load:
incremental MERGE
Quality:
sale_id not null
merchant_id not null
amount >= 0 where business rules require it
latest source update wins
This is more useful than a diagram that only says Bronze / Silver / Gold.
What the result means
| Layer | Primary responsibility |
|---|---|
| Bronze | Preserve source data and replayability |
| Silver | Standardize, validate, deduplicate, and create trusted reusable data |
| Gold | Expose consumer-ready business semantics |
| Quarantine | Preserve invalid/rejected records for investigation |
| Warehouse Gold | SQL-first relational serving when needed |
| Lakehouse Gold | Delta/Spark/Direct Lake-oriented serving |
The most important distinction is:
Bronze = source truth
Silver = trusted operational truth
Gold = consumer truth
That is a design principle, not a universal product rule.
What to look for
Bronze contains heavy business logic
The raw layer is probably doing too much.
Ask whether those transformations belong in Silver.
Silver tables are different for every report
Silver is probably too consumer-specific.
Move reusable canonical logic into shared Silver entities.
Gold still contains raw source codes and cleanup logic
Gold is probably not consumer-ready.
Push source normalization upstream.
Rerunning a batch creates duplicates
The incremental path is not idempotent.
Check:
- append behavior
- business key
- batch ID
- merge logic
- watermark handling
Data cannot be replayed after a bug
Bronze retention or lineage is insufficient.
Preserve source-aligned data and execution metadata.
Gold totals do not reconcile with Silver
Check:
- join cardinality
- duplicate grain
- late-arriving data
- filters
- dimension joins
- aggregation logic
Many consumers independently clean the same fields
Silver is missing a canonical reusable contract.
Production notes
Medallion architecture is logical, not mandatory physical separation
Bronze, Silver, and Gold can live in:
- one Lakehouse
- multiple Lakehouses
- separate workspaces
- a mix of Lakehouse and Warehouse
Choose physical separation based on:
- security
- ownership
- deployment
- workload isolation
- operational scale
Do not equate one layer with one mandatory Fabric item.
Bronze retention has a cost
Keeping all raw history improves replayability but consumes storage.
Define retention based on:
- source replayability
- compliance
- audit needs
- recovery strategy
- storage cost
Silver should be the main reusable engineering contract
If every consumer bypasses Silver and reads Bronze directly, the architecture loses most of its value.
Restrict or discourage Bronze consumption where appropriate.
Gold can be implemented in Lakehouse or Warehouse
Choose based on serving needs.
Use Lakehouse Gold when:
- Delta/Spark/Direct Lake fits
- full T-SQL write semantics are unnecessary
Use Warehouse Gold when:
- SQL-first relational serving is important
- stored procedures or multi-table SQL transactions are required
Incremental processing must be end-to-end
It is not enough for only Bronze to be incremental.
Think through:
Source → Bronze → Silver → Gold
Each layer should define how reruns, late data, and corrections propagate.
Quality checks should match the layer
Bronze may tolerate malformed values to preserve evidence.
Silver should enforce stronger structural and business-key validity.
Gold should enforce consumer-facing grain and metric consistency.
OneLake reduces the need for copies
Use shortcuts and shared storage patterns where they support the ownership model.
Copy data only when the target layer needs:
- independent lifecycle
- schema isolation
- performance layout
- recovery boundary
- ownership boundary
Common mistakes
1. Treating Bronze as a dumping ground with no metadata
Without source/run metadata, replay and incident analysis become difficult.
2. Cleaning data destructively in Bronze
You can lose the evidence needed to understand source defects later.
3. Using dropDuplicates() without deciding which record should survive
Deduplication must be deterministic.
4. Putting all business logic directly in Gold
This forces every downstream consumer to depend on one large transformation path and makes reuse harder.
5. Making Silver consumer-specific
Silver should represent reusable trusted entities, not one report’s exact layout.
6. Rebuilding every large table from scratch every run
Use incremental patterns when the source and business keys support them.
7. Advancing the watermark before the write is durable
This can create permanent data gaps after failure.
8. Ignoring late-arriving data
Business-date aggregates may need to update historical periods.
9. Creating a Warehouse just because “Gold should be a warehouse”
Gold can remain in Lakehouse when that serves the consumers well.
10. Creating three layers with no actual contract differences
Renaming tables:
bronze_x
silver_x
gold_x
does not create architecture by itself.
Each layer needs a distinct responsibility.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.