Architecting Enterprise Medallion Architecture with Microsoft Fabric and Azure Databricks
Enterprise data platforms fail less often because of bad dashboards and more often because the layers underneath are unclear. Raw files get overwritten. Schemas drift quietly. Duplicate events inflate metrics. Bad records disappear into logs. A production Medallion Architecture solves these problems by making data quality, lineage, and ownership explicit from ingestion to business consumption.
A strong design with Microsoft Fabric and Azure Databricks gives teams the best of both platforms. Fabric provides OneLake, Lakehouse, Warehouse, Data Factory pipelines, Power BI integration, and a unified SaaS experience. Azure Databricks brings mature Spark engineering, Delta Lake controls, scalable notebooks, jobs, and advanced table maintenance.
This guide focuses on how to build the Bronze, Silver, and Gold layers using Microsoft Fabric, Azure Databricks, Medallion Architecture, Delta Lake, PySpark patterns that hold up in production.
Build the foundation on ADLS Gen2 and OneLake
The Bronze layer starts with ingestion, but production design starts one step earlier: storage layout. ADLS Gen2 remains a common landing zone for enterprise data on Azure because it supports hierarchical namespaces, fine-grained access controls, and scalable analytics workloads. Microsoft Fabric OneLake can then act as the unified data lake experience across workspaces and domains.
A practical enterprise layout keeps raw data immutable and easy to trace:
```text
/landing/source_system/entity/ingestion_date=2026-09-14/batch_id=...
/bronze/source_system/entity/
/silver/domain/entity/
/gold/domain/data_product/
```
The landing area receives files exactly as delivered. Bronze stores Delta tables built from those files, enriched only with technical metadata. This separation protects auditability.
Add these ingestion fields during the Bronze write:
`ingestion_timestamp`
`source_file_name`
`source_system`
`batch_id`
`record_hash`
`load_type`
For batch ingestion, Fabric Data Factory pipelines can collect files from storage accounts, SFTP locations, APIs, and databases. Databricks Workflows can handle Spark-heavy ingestion jobs, especially where Auto Loader patterns, schema tracking, or custom parsing are needed.
For streaming or near-real-time workloads, send events through Event Hubs or managed event pipelines, then land them into Bronze Delta tables. The same rule still applies: do not clean the data too early. Keep the Bronze layer close to source reality.
A good enterprise Bronze table answers these questions:
Question | Why it matters |
Where did this row come from? | Supports audit and incident analysis |
When was it ingested? | Helps replay and recovery |
Which batch or event stream produced it? | Makes failed loads easier to isolate |
Has the source changed format? | Detects schema drift before it reaches users |
Access control also starts here. Grant only platform engineers and approved data owners write access to landing and Bronze paths. Most analysts should never query raw Bronze tables directly, except for controlled investigation.
Implement Delta Lake as the contract between layers
Delta Lake is the storage and transaction layer that makes the Medallion pattern production-ready. It adds ACID transactions, schema enforcement, time travel, scalable metadata handling, and safe concurrent writes on top of cloud object storage.
In this architecture, every curated layer should be stored as Delta:
```python
df.write.format("delta") \
.mode("append") \
.option("mergeSchema", "false") \
.saveAsTable("bronze.crm_customer")
```
Schema evolution deserves careful handling. In development, teams often enable automatic schema merging to move quickly. In production, that can hide source changes. A safer pattern is to fail the load, quarantine the affected records or files, then approve schema changes through version control and deployment pipelines.
Use Delta expectations at the design level even if enforcement happens in PySpark:
Bronze accepts technical validity.
Silver enforces business validity.
Gold enforces consumption contracts.
For example, Bronze may accept a customer row where `email` is null if the source sent it. Silver may reject or quarantine it if email is mandatory for a specific business process. Gold may expose only active, deduplicated customers with trusted attributes.
Delta also supports recovery. If a bad job overwrites a table, time travel can help identify a previous version. In regulated environments, combine this with retention policies, table history checks, and approval gates for destructive operations.
A sound naming pattern helps across Fabric and Databricks:
```text
bronze.erp_sales_order_raw
silver.sales_order_clean
gold.revenue_daily_summary
```
Avoid hiding critical semantics in notebook names. Put the meaning in the table name, metadata, and catalogue description.
Validate schemas and quality rules with PySpark
PySpark schema validation is the first serious gate between raw data and usable data. The goal is not only to catch malformed rows. It is to create predictable outcomes when source systems change.
Start with explicit schemas for known sources:
```python
from pyspark.sql.types import StructType, StructField, StringType, TimestampType, DecimalType
customer_schema = StructType([
StructField("customer_id", StringType(), False),
StructField("email", StringType(), True),
StructField("created_at", TimestampType(), True),
StructField("credit_limit", DecimalType(18, 2), True)
])
```
Read incoming files with the expected schema rather than inferring it each time:
```python
raw_df = spark.read \
.schema(customer_schema) \
.option("header", "true") \
.csv(input_path)
```
Then apply validation rules before writing to Silver:
```python
from pyspark.sql.functions import col, lit, current_timestamp
valid_df = raw_df.filter(
col("customer_id").isNotNull() &
col("created_at").isNotNull()
)
invalid_df = raw_df.subtract(valid_df) \
.withColumn("error_reason", lit("Missing mandatory customer_id or created_at")) \
.withColumn("quarantine_timestamp", current_timestamp())
```
In production, avoid one giant validation expression. Build a reusable rule framework where each rule has:
A unique rule ID
A severity level
A target column or entity
A clear error message
A business owner
A remediation path
This matters when support teams need to explain why a row failed. “Invalid record” is not enough. “Rule CUST_004 failed because `customer_id` was null” is useful.
Schema drift should be handled separately from data quality failures. A new column from the source is not the same as a null mandatory field. Route schema drift alerts to engineering teams. Route business rule failures to data owners or operations teams.
For enterprise workloads, add validation metrics to a control table:
```text
pipeline_name
batch_id
source_table
records_read
records_valid
records_quarantined
validation_status
started_at
completed_at
```
Fabric notebooks, Databricks jobs, and orchestration pipelines can all write to the same operational monitoring tables. This gives platform teams a reliable view of data health without scanning logs.
Use MERGE INTO for deduplication and change handling
Deduplication is one of the main reasons the Silver layer exists. Source systems resend files. Event streams replay messages. APIs return overlapping windows. Without a clear dedupe strategy, Gold metrics become unreliable.
A common pattern uses a business key plus a record hash. The business key identifies the entity. The hash identifies whether the content changed.
```python
from pyspark.sql.functions import sha2, concat_ws
staged_df = valid_df.withColumn(
"record_hash",
sha2(concat_ws("||", *valid_df.columns), 256)
)
```
Create a temporary view for the incoming batch:
```python
staged_df.createOrReplaceTempView("staged_customers")
```
Then use `MERGE INTO` against the Silver Delta table:
```sql
MERGE INTO silver.customer AS target
USING staged_customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND target.record_hash <> source.record_hash THEN
UPDATE SET
target.email = source.email,
target.created_at = source.created_at,
target.credit_limit = source.credit_limit,
target.record_hash = source.record_hash,
target.updated_at = current_timestamp()
WHEN NOT MATCHED THEN
INSERT (
customer_id,
email,
created_at,
credit_limit,
record_hash,
updated_at
)
VALUES (
source.customer_id,
source.email,
source.created_at,
source.credit_limit,
source.record_hash,
current_timestamp()
)
```
This pattern handles inserts and updates in one transaction. For delete handling, avoid physical deletes unless required. Soft-delete flags preserve history and reduce recovery risk:
```sql
is_deleted = true,
deleted_at = current_timestamp()
```
For high-volume tables, deduplicate within the incoming batch before the merge. Use window functions to keep the latest record by event time or ingestion time:
```python
from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, desc
w = Window.partitionBy("customer_id").orderBy(desc("updated_at"), desc("ingestion_timestamp"))
deduped_df = staged_df.withColumn("rn", row_number().over(w)) \
.filter("rn = 1") \
.drop("rn")
```
Define the winner rule with the business team. Latest timestamp works for many systems, but not all. Some systems send correction files where the file sequence is more reliable than event time.
Design quarantine handling as a first-class system
A quarantine strategy should not be an afterthought. Bad records are part of normal data operations. The platform must capture them, explain them, and support replay after correction.
A useful quarantine table includes:
Column | Purpose |
`batch_id` | Links the error to a specific run |
`source_system` | Identifies ownership |
`entity_name` | Shows which dataset failed |
`raw_payload` | Preserves the original record |
`error_code` | Enables grouping and reporting |
`error_message` | Explains the failure clearly |
`rule_id` | Links to the validation catalogue |
`quarantine_timestamp` | Supports SLA tracking |
`reprocess_status` | Tracks whether the row has been fixed |
Store quarantined data as Delta tables, not loose CSV files. This allows analytics on quality trends and makes reprocessing safer.
There are three common quarantine routes:
Route | When to use it |
Record-level quarantine | A small portion of rows fail validation |
File-level quarantine | The whole file has the wrong layout or encoding |
Batch-level quarantine | The dataset is incomplete or violates control totals |
Record-level quarantine works well when valid records can still move to Silver. File-level quarantine is better when parsing fails or the header changes. Batch-level quarantine protects downstream users when totals do not reconcile.
Set clear thresholds. For example, a pipeline may continue if less than a small agreed percentage of rows fail non-critical rules, but stop if any critical control rule fails. Avoid hard-coded thresholds inside notebooks. Store them in configuration tables so changes can go through review.
Alerting also needs care. Send one useful incident with counts, sample error codes, and affected datasets. Do not send thousands of row-level alerts. Databricks jobs, Fabric Data Activator, Azure Monitor, or enterprise incident tools can all participate, depending on the operating model.
Most teams miss the replay path. Build a controlled reprocess workflow that reads corrected quarantined records, reruns validation, and merges them into Silver. Keep the original error record for audit, then update `reprocess_status`.
Tune Gold tables for consumption and cost
Gold tables serve BI, data products, APIs, and machine learning features. They should be narrow, well-modelled, and aligned to business use cases. Do not let Gold become another dump of all Silver fields.
Common Gold designs include:
Star schemas for Power BI models
Aggregated tables for executive reporting
Feature tables for data science
Data products owned by domains
Secure views for restricted access
Performance tuning starts with file layout. Too many small files slow reads. Very large files can reduce parallelism. Delta maintenance helps keep query performance stable:
```sql
OPTIMIZE gold.revenue_daily_summary;
```
For Databricks workloads, `ZORDER BY` can improve data skipping when users often filter by the same columns:
```sql
OPTIMIZE gold.revenue_daily_summary
ZORDER BY (business_date, region_code);
```
Choose Z-ORDER columns carefully. Good candidates are frequently filtered columns with useful selectivity, such as `customer_id`, `business_date`, `store_id`, or `region_code`. Do not Z-ORDER every column. It adds maintenance cost.
Liquid Clustering is useful when table access patterns change or when static partitioning becomes painful. Instead of relying only on fixed folder partitions, Liquid Clustering lets Delta manage clustering at the table level for selected columns:
```sql
CREATE TABLE gold.customer_activity
CLUSTER BY (customer_id, activity_date)
AS
SELECT * FROM silver.customer_activity_clean;
```
Use Liquid Clustering for large, frequently queried Delta tables where query patterns are known but continue to evolve. Review platform support and runtime requirements before standardising it across teams.
Partitioning still has a place. For large fact tables, partition by stable, low-to-medium cardinality columns such as date. Avoid partitioning by high-cardinality fields like customer ID. That often creates too many small folders.
In Microsoft Fabric, keep Power BI consumption in mind. Build Gold tables that match semantic model needs. Use meaningful column names, documented measures, and role-based access. If a Power BI report needs complex transformations at refresh time, push those transformations upstream into Gold where they can be tested, versioned, and reused.
Production best practices that keep the architecture reliable
A production Medallion Architecture needs engineering discipline around the layers. The tools matter, but the operating model matters just as much.
Use Git-backed development for notebooks, SQL scripts, and pipeline definitions. Promote changes through development, test, and production workspaces. Keep environment-specific values in configuration, not code.
Create clear ownership:
Layer | Primary owner | Main responsibility |
Bronze | Platform or ingestion team | Reliable capture and traceability |
Silver | Data engineering and domain teams | Quality, deduplication, conformance |
Gold | Domain analytics or product teams | Trusted consumption and definitions |
Security should follow least privilege. Source-aligned teams may own specific Silver datasets. Wider business access usually belongs at Gold. Use masking, row-level security, and separate workspaces where required.
Observability is non-negotiable. Track row counts, latency, failure rates, schema changes, table versions, and cost indicators. Every critical dataset should have a freshness expectation and a named owner.
Finally, document the contract for each Gold table. Include grain, refresh frequency, source lineage, quality rules, and known limitations. A table called `gold.sales` is not enough. A documented table that states “one row per invoice line after cancellation adjustment” prevents expensive misunderstandings.
The best enterprise Medallion Architecture is not the most complex one. It is the one where every layer has a job, every failed record has a destination, every metric has lineage, and every production change can be traced. Start with clear storage zones, enforce Delta contracts, validate with PySpark, deduplicate with `MERGE INTO`, quarantine with intent, and tune the Gold layer for real query patterns. That is how Microsoft Fabric and Azure Databricks become a dependable production data platform rather than another collection of pipelines.
Comments