9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design

9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design

DP-750 Exam Focus: Master the design principles, data fidelity standards, schema evolution rules, and compute mapping for the Medallion (Multi-Hop) Lakehouse architecture. Understand the specific operational boundaries of Bronze (raw append-only), Silver (cleansed, deduplicated, conformed), and Gold (curated dimensional models and aggregates) layers, including replayability mechanics, audit compliance, and compute tier selection.

---

1. Architectural Foundations of the Medallion Architecture

In modern data engineering, building robust data platforms requires balancing competing requirements: low-latency ingestion, regulatory auditability, historical reprocessing capabilities, high-performance analytics, and data quality guarantees. Traditional single-tier architectures (such as monolithic data warehouses or uncurated data swamps) force tradeoffs between raw fidelity and downstream query usability.

The Medallion Lakehouse Pattern (also known as the Multi-Hop Architecture) is the reference design pattern for building scalable, reliable lakehouses on Azure Databricks. By organizing data into distinct logical layers—Bronze, Silver, and Gold—organizations establish a stepwise quality refinement pipeline that decouples source-aligned ingestion from consumer-aligned data delivery.

+---------------------------------------------------------------------------------------------------------+
|                                 MEDALLION LAKEHOUSE MULTI-HOP DATA FLOW                                  |
+---------------------------------------------------------------------------------------------------------+
|                                                                                                         |
|  +-------------------+        +-------------------+        +-------------------+        +------------+  |
|  |   SOURCE DATA     |        |   BRONZE LAYER    |        |   SILVER LAYER    |        | GOLD LAYER |  |
|  |                   |        |   (Raw Ingest)    |        | (Cleansed/Conform)|        | (Curated)  |  |
|  | - IoT Telemetry   | ===>   | - Append-only     | ===>   | - Deduplicated    | ===>   | - Star     |  |
|  | - Azure EventHubs | Ingest | - Raw schema/JSON | Enrich | - Schema-enforced | Model  |   Schemas  |  |
|  | - OLTP Databases  |        | - Rescued data    | & Clean| - Type-casted     | & Agg  | - BI Hubs  |  |
|  | - Files (ADLS Gen2|        | - Audit metadata  |        | - Domain Lookups  |        | - ML Feats |  |
|  +-------------------+        +-------------------+        +-------------------+        +------------+  |
|                                         |                            |                         |        |
|  COMPUTE PROFILE:               Automated Job /              Automated Job /            Serverless SQL  |
|                                Serverless Lakeflow          Serverless Lakeflow           Warehouse     |
+---------------------------------------------------------------------------------------------------------+

Why Multi-Hop Design Matters

1. Replayability & Resiliency: If downstream business logic changes or a bug is identified in business rules, engineers can recompute Silver and Gold layers directly from the immutable Bronze historical record without re-extracting data from production source systems.
2. Granular Quality Gates: Data quality checks, deduplication routines, and type validations occur progressively. Bronze never fails due to malformed payloads, Silver isolates bad data, and Gold remains pristine for executive consumption.
3. Performance Optimization: Storage formats, clustering strategies, and indexing can be optimized specifically for each layer's query patterns rather than compromising across ingestion and analytical workloads.
4. Unified Governance: Unity Catalog applies fine-grained access control across all three layers, ensuring data scientists, BI developers, and compliance auditors only access appropriate tiers.

---

2. Bronze Layer: Raw Append-Only Ingestion

The Bronze layer (often referred to as the raw table or landing zone) acts as the initial landing repository for data arriving from upstream operational systems, cloud object stores, message queues, and external APIs.

Core Bronze Design Principles

• Append-Only Ingestion: Bronze tables are strictly append-only. Incoming records are inserted as-is without in-place updates (UPDATE or DELETE operations are avoided, with the exception of regulatory compliance requests such as GDPR Right-to-be-Forgotten).
• High Data Fidelity: Data is preserved in its rawest possible state. If a source provides a semi-structured JSON payload or unstructured text, the payload is captured verbatim to prevent information loss.
• Minimal Transformation: Only technical metadata columns are appended during ingestion. Business transformations, type conversions, and filtering must never be performed in the Bronze layer.
• Schema Preservation & Rescued Data: Ingestion engines (such as Databricks Auto Loader) capture new or unexpected columns automatically, routing schema mismatches into the _rescued_data column rather than failing the stream.

-- Example: Bronze Ingestion Table Definition
CREATE TABLE bronze.crm.customer_events (
    raw_payload STRING,
    source_file_name STRING,
    source_file_modification_time TIMESTAMP,
    ingest_timestamp TIMESTAMP,
    _rescued_data STRING
)
USING DELTA
PARTITIONED BY (DATE(ingest_timestamp));

Essential Bronze Metadata Columns

To guarantee full traceability and auditability, enterprise Bronze tables should always append the following metadata attributes:

Exam Tip: On the DP-750 exam, any question asking where raw, unparsed JSON strings or historical audit copies of raw event streams belong should immediately point to the Bronze layer.

---

3. Silver Layer: Cleansed, Conformed, & Enterprise-Standardized

The Silver layer (also called the curated, cleaned, or conformed layer) represents the enterprise single source of truth. It takes raw records from Bronze and applies systematic data cleansing, enrichment, structural normalization, and domain validation.

                             SILVER REFINEMENT STAGES

  +------------------------+      +------------------------+      +------------------------+
  | 1. Structural Parsing  | ===> | 2. Quality Validation  | ===> | 3. Conformance & SCD   |
  | - Parse JSON payloads  |      | - Deduplicate by PK    |      | - Resolve Master IDs   |
  | - Extract nested fields|      | - Handle NULL values   |      | - Apply SCD Type 1 / 2 |
  | - Enforce strict schema|      | - Cast verified types  |      | - Join Reference Dictionaries
  +------------------------+      +------------------------+      +------------------------+

Core Silver Design Principles

• Strict Schema Enforcement & Typing: Raw strings are parsed into strongly-typed primitives (INT, BIGINT, DECIMAL(18,2), TIMESTAMP, BOOLEAN).
• Deduplication: Ingested records are deduplicated based on enterprise business keys (e.g., order_id, transaction_id) using deterministic window ranking (ROW_NUMBER()) or dropDuplicates().
• Null Imputation & Handling: Missing values are replaced with standardized defaults, coalesced from secondary sources, or quarantined.
• Data Conformance & Normalization: Categorical values are normalized to enterprise standards (e.g., converting ['NY', 'New York', 'ny', 'N.Y.'] to 'NY'), and foreign key references are resolved against master data dimensions.
• Change Data Capture (CDC) & History: Source updates are merged into Silver tables using Delta MERGE INTO or Lakeflow APPLY CHANGES INTO, maintaining current state (SCD Type 1) or full versioned history (SCD Type 2).

-- Example: Silver Table Curation via MERGE INTO
MERGE INTO silver.sales.orders AS target
USING (
    SELECT 
        CAST(get_json_object(raw_payload, '$.order_id') AS BIGINT) AS order_id,
        CAST(get_json_object(raw_payload, '$.customer_id') AS BIGINT) AS customer_id,
        TO_TIMESTAMP(get_json_object(raw_payload, '$.order_time'), 'yyyy-MM-dd HH:mm:ss') AS order_timestamp,
        CAST(get_json_object(raw_payload, '$.amount') AS DECIMAL(10,2)) AS order_amount,
        UPPER(TRIM(get_json_object(raw_payload, '$.status'))) AS order_status,
        ingest_timestamp
    FROM bronze.sales.raw_orders
    WHERE raw_payload IS NOT NULL
) AS source
ON target.order_id = source.order_id
WHEN MATCHED AND source.order_timestamp > target.order_timestamp THEN
    UPDATE SET 
        target.customer_id = source.customer_id,
        target.order_timestamp = source.order_timestamp,
        target.order_amount = source.order_amount,
        target.order_status = source.order_status,
        target.updated_at = current_timestamp()
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, order_timestamp, order_amount, order_status, created_at, updated_at)
    VALUES (source.order_id, source.customer_id, source.order_timestamp, source.order_amount, source.order_status, current_timestamp(), current_timestamp());

---

4. Gold Layer: Business Aggregation, Dimensional Models, & Feature Stores

The Gold layer (often called the consumption, curated reporting, or presentation layer) provides project-specific, business-aligned data structures optimized for read-heavy consumption, executive reporting, business intelligence (Power BI), and machine learning.

Core Gold Design Principles

• Business-Level Aggregations: Pre-calculates Key Performance Indicators (KPIs), daily sales summaries, customer lifetime value (LTV), churn scores, and rollups across common reporting hierarchies.
• Dimensional Modeling (Kimball Star & Snowflake Schemas): Structures data into Fact tables (e.g., fact_daily_financial_transactions) and Dimension tables (e.g., dim_customer, dim_product, dim_date).
• Optimized for High-Concurrency BI: Uses Delta Lake performance features such as Liquid Clustering (CLUSTER BY), file compaction (OPTIMIZE), and predictive caching to ensure sub-second dashboard query latency.
• Strict Access Boundaries: Business users, financial analysts, and Power BI service principals are typically granted SELECT access exclusively on Gold tables, preventing ad-hoc queries against raw or partially cleansed data.

-- Example: Gold Aggregated Dimensional Fact Table
CREATE OR REPLACE TABLE gold.analytics.fact_daily_regional_sales
CLUSTER BY (sale_date, region_code)
AS
SELECT 
    CAST(o.order_timestamp AS DATE) AS sale_date,
    c.region_code,
    c.customer_segment,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(o.order_amount) AS gross_revenue,
    AVG(o.order_amount) AS average_order_value
FROM silver.sales.orders o
INNER JOIN silver.crm.dim_customer c
    ON o.customer_id = c.customer_id
WHERE o.order_status = 'COMPLETED'
GROUP BY 1, 2, 3;

---

5. Medallion Layer Comparison Matrix

The following matrix summarizes the technical differences across the three layers for the DP-750 exam:

---

6. Compute Selection Across Medallion Layers

Matching the correct Azure Databricks compute engine to each medallion layer is critical for cost efficiency, scalability, and performance optimization.

+-----------------------------------------------------------------------------------------+
|                         COMPUTE MAPPING ACROSS MEDALLION TIERS                          |
+-----------------------------------------------------------------------------------------+
|                                                                                         |
|  1. BRONZE INGESTION LAYER                                                              |
|     - Workload: Continuous streaming or micro-batch ingestion (Auto Loader, Event Hubs) |
|     - Optimal Compute: Automated Job Clusters (Single-Node or Multi-Node) or            |
|       Serverless Lakeflow Pipelines. Billed at lowest DBU tier.                         |
|                                                                                         |
|  2. SILVER TRANSFORMATION LAYER                                                         |
|     - Workload: Heavy distributed joins, CDC merges, window deduplication, cleansing     |
|     - Optimal Compute: Automated Multi-Node Job Clusters or Lakeflow Declarative        |
|       Pipelines with Autoscaling and Photon enabled.                                    |
|                                                                                         |
|  3. GOLD CONSUMPTION LAYER                                                              |
|     - Workload: High-concurrency SQL queries, Power BI DirectQuery, ad-hoc BI analytics |
|     - Optimal Compute: Serverless SQL Warehouses (or Pro SQL Warehouses) with Photon,    |
|       Predictive I/O, and multi-cluster horizontal auto-scaling.                        |
+-----------------------------------------------------------------------------------------+