Designing the Bronze Layer — Raw Data

Purpose

Capture raw data exactly as it arrives.

Characteristics

• Minimal transformation
• Raw schema
• Raw files or raw Delta tables
• High volume
• Append‑only

Best Practices

• Store raw files in /Files/Bronze
• Convert to Delta for consistency
• Add ingestion metadata (timestamp, source)
• Avoid business logic
• Use Pipelines for ingestion
• Use Event Streams for real‑time

Common Bronze Sources

• APIs
• Databases
• ERP systems
• CRM systems
• IoT devices
• Logs
• S3/ADLS shortcuts

Bronze is your “source of truth.”

Designing the Silver Layer — Cleaned & Conformed

Purpose

Transform raw data into clean, standardized, analytics‑ready tables.

Best Practices

• Use PySpark notebooks
• Use Delta Lake merges
• Apply schema enforcement
• Remove duplicates
• Standardize column names
• Add surrogate keys
• Partition large tables

Common Silver Tasks

• Remove nulls
• Standardize date formats
• Convert strings to numeric types
• Join reference tables
• Apply business logic
• Flatten nested JSON

Designing the Gold Layer — Business‑Ready

Purpose

Provide BI‑optimized, business‑friendly tables.

Best Practices

• Use SQL endpoint for modeling
• Build fact + dimension tables
• Use surrogate keys
• Use incremental loads
• Partition fact tables
• Use aggregation tables

Common Gold Tables

• FactSales
• DimCustomer
• DimProduct
• FactInventory
• FactFinance
• KPI tables

Gold is your “analytics layer.”

Introduction — Why Medallion Architecture Became the Standard

Modern analytics systems must handle massive data volumes, real‑time ingestion, complex transformations, and business‑ready modeling — all while maintaining governance, performance, and reliability. Traditional ETL pipelines often collapse under this pressure, producing messy data, inconsistent schemas, duplicated logic, slow refresh cycles, and unreliable BI outputs.

The Medallion Architecture solves this problem. Originally popularized by Databricks and now deeply embedded into Microsoft Fabric, the Medallion pattern organizes data into Bronze → Silver → Gold layers, each with a clear purpose, clear quality expectations, and clear transformation boundaries.

1. What Is Medallion Architecture?

Medallion Architecture is a layered data design pattern that organizes data into three quality tiers:

Bronze — Raw Data

• Ingested from source systems
• Minimal transformation
• Stored as files or raw Delta tables
• Schema may be messy
• Used for traceability and reprocessing

Silver — Cleaned & Conformed

• Standardized schemas
• Deduplicated
• Type‑casted
• Enriched
• Joined
• Business logic applied

Gold — Business‑Ready

• Aggregations
• Dimensional models
• Star schemas
• KPI tables
• BI‑optimized structures

2. Why Medallion Architecture Matters

• Clarity — Each layer has a defined purpose.
• Scalability — Large datasets become manageable.
• Reliability — Silver and Gold layers are stable and predictable.
• Governance — Purview can classify and label each layer differently.
• Performance — Gold tables are optimized for BI and Direct Lake.
• Collaboration — Engineers, analysts, and BI developers work at the right layer.
• Reprocessing — Bronze allows full replay of ingestion.
• Real‑Time — Event Streams → Bronze → Silver → Gold → Direct Lake.

Medallion is not optional — it is the backbone of modern analytics.

Medallion Architecture in Fabric — The Perfect Fit

Fabric’s unified platform makes Medallion architecture seamless. Every component fits naturally into the Bronze → Silver → Gold flow.

• OneLake — All layers stored in one unified lake.
• Delta Lake — ACID transactions, schema evolution, partitioning.
• Lakehouses — Bronze, Silver, Gold stored as Delta tables.
• Pipelines — Ingest raw data into Bronze.
• Notebooks — Transform Bronze → Silver → Gold.
• SQL Endpoint — Query Silver/Gold directly.
• Direct Lake — Power BI reads Gold tables instantly.
• Purview — Govern each layer centrally.

Pipelines — The Ingestion Engine

Fabric Pipelines handle enterprise‑grade ingestion into Bronze.

Capabilities

• Scheduled ingestion
• Copy activities
• Parameterization
• Error handling & retry logic
• Monitoring & logging
• Notifications

Common Pipeline Patterns

• Full load
• Incremental load
• CDC load
• Metadata‑driven load
• Multi‑source ingestion

Notebooks — The Transformation Engine

PySpark notebooks transform Bronze → Silver → Gold.

Common Transformation Patterns

• Incremental loads
• Merge operations
• Partition pruning
• Schema evolution
• Delta optimization (Z‑Order, file compaction)

Source → Pipeline → Bronze → Silver → Gold Flow

A clean Medallion flow in Fabric looks like this:

1. Ingest raw data into Bronze using Pipelines.
2. Transform into Silver using PySpark notebooks.
3. Model Gold tables for business consumption.
4. Build semantic models using Direct Lake.
5. Publish Power BI dashboards.

Pipelines feed the Lakehouse. Notebooks shape the data. Gold delivers the value.

Medallion + Direct Lake — Real‑Time BI

Direct Lake reads Gold tables directly from OneLake — no refresh, no duplication, no latency.

Benefits

• No refresh cycles
• No data duplication
• Real‑time dashboards
• Lower storage cost
• Higher performance

Requirements for Direct Lake

• Gold tables must be Delta
• Gold tables must be optimized
• Gold tables must be partitioned
• Gold tables must be business‑ready

Medallion + Direct Lake = real‑time enterprise BI.

Medallion Governance — Purview Integration

Purview governs each layer differently:

Bronze

• Classified as raw
• Lower sensitivity
• Broad access

Silver

• Classified as cleaned
• Medium sensitivity
• Controlled access

Gold

• Classified as business‑critical
• High sensitivity
• Strict access

Governance becomes structured and predictable across every layer.

Medallion Dev/Test/Prod Strategy

Best Practices

• Separate workspaces for Dev, Test, and Prod
• Use deployment pipelines
• Parameterize pipelines
• Version control notebooks
• Promote Gold tables carefully
• Validate Silver transformations before promoting

Medallion architecture thrives with a proper workspace strategy. Each environment mirrors the same Bronze → Silver → Gold structure, but with environment-specific data and permissions.

End-to-End Architecture — How Everything Fits Together

1. Ingest raw data into Bronze using Pipelines.
2. Transform into Silver using PySpark notebooks.
3. Model Gold tables for business consumption.
4. Build semantic models using Direct Lake.
5. Publish Power BI dashboards.
6. Add real‑time streams for operational insights.
7. Govern everything through Purview.
8. Deploy across Dev/Test/Prod workspaces.
9. Monitor performance and optimize Delta.
10. Scale seamlessly as data grows.

Conclusion — Medallion Is the Backbone of Fabric

Medallion architecture is not just a pattern — it is the foundation of modern analytics. Fabric makes it natural, scalable, governed, and real‑time.

• Bronze → Silver → Gold
• Raw → Clean → Business‑Ready
• Ingest → Transform → Model
• Pipelines → Notebooks → SQL → Direct Lake

This is the future of enterprise analytics.

Introduction — Why Ingestion Is the Hardest Part of Analytics

Data ingestion is the foundation of every analytics system. Without reliable ingestion, everything collapses: pipelines break, dashboards fail, ML models drift, and business decisions become unreliable.

Fabric solves ingestion with two powerful tools:

• Pipelines — enterprise‑grade orchestration
• Dataflows Gen2 — low‑code transformation for business users

Together, they form the ingestion backbone of Fabric.

1. Pipelines — The Enterprise Ingestion Engine

Fabric Pipelines are built for enterprise‑grade orchestration.

Capabilities

• Scheduled ingestion
• Copy activities
• Data movement
• Parameterization
• Error handling
• Retry logic
• Monitoring
• Logging
• Notifications

Why Pipelines Matter

• They replace ADF for Fabric workloads
• They integrate directly with OneLake
• They support medallion architecture
• They support shortcuts
• They support Delta Lake

Pipelines are the backbone of Bronze ingestion.

2. Dataflows Gen2 — Low‑Code Transformation

Dataflows Gen2 allow business users to build transformations without writing code.

Capabilities

• Power Query transformations
• Direct write to OneLake
• Delta Lake output
• Scheduled refresh
• Parameterization
• Reusable logic

Why Dataflows Matter

• Empower business users
• Reduce engineering workload
• Standardize transformations
• Integrate with Lakehouses
• Support medallion architecture

Dataflows Gen2 are perfect for Silver transformations.

Pipeline Architecture — How It Works

Components

• Activities
• Data sources
• Data destinations
• Parameters
• Variables
• Control flow
• Monitoring

Common Pipeline Patterns

• Full load
• Incremental load
• CDC load
• Metadata‑driven load
• Multi‑source ingestion
• Multi‑layer ingestion

Pipeline → Lakehouse Flow

Source → Pipeline → Bronze → Silver → Gold

Pipelines vs Dataflows — When to Use What

Feature	Pipelines	Dataflows Gen2
Code	Yes	No
Complexity	High	Medium
Audience	Engineers	Analysts
Use case	Ingestion	Transformation
Output	Files/Delta	Delta
Scheduling	Yes	Yes

Use both — they complement each other.

Building Bronze Ingestion with Pipelines

Best Practices

• Use copy activity
• Add ingestion metadata
• Store raw files in /Files/Bronze
• Convert to Delta
• Use incremental logic
• Use parameterized pipelines

Common Bronze Sources

• SQL databases
• APIs
• ERP systems
• CRM systems
• S3/ADLS shortcuts
• Event Streams

Bronze ingestion must be reliable above all else.

Building Silver Transformations with Dataflows Gen2

Best Practices

• Standardize column names
• Deduplicate
• Type‑cast
• Flatten JSON
• Join reference tables
• Validate data quality

Dataflows Gen2 make Silver accessible to business users — no Spark required.

Metadata‑Driven Pipelines — The Enterprise Pattern

Metadata‑driven pipelines use configuration tables to control ingestion — no hard‑coded logic, just parameters.

Benefits

• No hard‑coded logic
• Easy to add new sources
• Easy to modify ingestion
• Easy to scale
• Easy to govern

Metadata Examples

• Source system
• Table name
• Incremental column
• Destination path
• Partitioning rules

Metadata‑driven ingestion is the enterprise standard — it scales infinitely without touching pipeline code.

Error Handling & Monitoring

Best Practices

• Use retry logic
• Use failure notifications
• Use logging tables
• Use monitoring dashboards
• Use pipeline run history

Reliable ingestion requires strong monitoring. Without it, silent failures go undetected until dashboards are already broken.

Dev/Test/Prod Strategy

Best Practices

• Separate workspaces for each environment
• Parameterize pipelines for environment switching
• Use deployment pipelines
• Use version control
• Validate transformations before promoting

Ingestion must be stable across environments. Dev pipelines that work only in Dev are not production-ready.

Conclusion — Pipelines & Dataflows Are the Ingestion Backbone

Fabric Pipelines and Dataflows Gen2 unify ingestion and transformation across the entire Medallion stack:

• Pipelines → Bronze
• Dataflows → Silver
• Notebooks → Gold
• Direct Lake → BI

Together they cover every ingestion scenario — from enterprise-scale batch loads to low-code business user transformations.

Key Takeaways

• Use Pipelines for Bronze ingestion — they handle complexity, retries, and orchestration.
• Use Dataflows Gen2 for Silver — they empower analysts without burdening engineers.
• Use metadata-driven patterns to scale without touching pipeline code.
• Monitor everything — silent failures are the enemy of reliable analytics.
• Separate Dev/Test/Prod — stability across environments is non-negotiable.

This is the future of enterprise ingestion in Microsoft Fabric.