11.2 Medallion Architecture for Analytics

Introduction to Medallion Architecture

The Medallion Architecture is a design pattern developed by Databricks to logically organize data in a lakehouse as it flows through distinct refinement stages. Instead of maintaining monolithic data pipelines where raw data is transformed into reporting tables in a single complex process, the medallion architecture breaks data processing into three progressive layers: Bronze (raw ingestion), Silver (cleaned and curated), and Gold (business-ready aggregations).

By establishing clear boundaries between ingestion, validation, and analytical presentation, the medallion architecture guarantees data quality, enables auditability and replayability, isolates upstream breaking changes, and drastically improves query performance for end-user analytics in Databricks SQL and AI/BI Dashboards.

Bronze Layer: Raw Data Ingestion & Auditability

The Bronze Layer serves as the initial landing zone and immutable record of truth for all incoming data assets. Data enters the Bronze layer from diverse source systems, including operational databases (via Change Data Capture, or CDC), streaming event buses (such as Apache Kafka or Event Hubs), web cloud storage landing spots (S3, ADLS Gen2, GCS via Auto Loader), or external API payloads.

Key characteristics and design principles of the Bronze layer include:

1. Raw Payload Preservation: Data is stored in its raw format (often as unparsed JSON strings, raw CSV text, or binary payloads) without applying destructive structural transformations or business logic filtering.
2. Append-Only Ingestion: Tables are strictly append-only to maintain complete historical fidelity. If source records are deleted or updated, the Bronze layer records every state change over time.
3. Audit Metadata Enrichment: Ingested records are enriched with audit metadata columns, such as _ingest_timestamp (when the record arrived in Databricks), _source_file (the path of the source file), and _job_id.

-- Bronze Layer Table Definition in Databricks SQL
CREATE TABLE main.bronze.raw_store_transactions (
    raw_payload STRING,
    _ingest_timestamp TIMESTAMP,
    _source_file STRING,
    _job_id STRING
) USING DELTA;

Preserving raw data in Bronze ensures that if downstream business logic or parsing rules change in the future, data engineers can replay historical transformations from Bronze without re-querying external source systems.

Silver Layer: Data Cleansing, Conformance & Quality Enforcement

The Silver Layer transforms raw Bronze data into clean, validated, and normalized tables suitable for enterprise-wide ad-hoc analysis. The primary objective of the Silver layer is to establish an authoritative "single source of truth" for core business entities (such as customers, orders, products, and sensors).

Key transformations executed in the Bronze-to-Silver transition include:

1. Schema Parsing and Type Enforcement: Extracting fields from Bronze raw JSON payloads and casting them into typed tabular columns (e.g., converting text strings to INT, DECIMAL(10,2), or TIMESTAMP).
2. Data Cleansing and Standardization: Trimming whitespace, standardizing country/state codes, handling NULL values, and converting timezone offsets to UTC.
3. Deduplication and CDC Upserts: Merging change data streams (MERGE INTO) to eliminate duplicate event emissions and maintain current state snapshots.
4. Data Quality Validation: Applying Databricks Delta Expectations or SQL constraints to filter out corrupt or invalid rows (e.g., rejecting orders with negative purchase amounts).

-- Silver Layer Upsert & Cleansing via MERGE INTO
MERGE INTO main.silver.orders AS target
USING (
    SELECT 
        CAST(get_json_object(raw_payload, '$.order_id') AS INT) AS order_id,
        CAST(get_json_object(raw_payload, '$.customer_id') AS INT) AS customer_id,
        CAST(get_json_object(raw_payload, '$.amount') AS DECIMAL(10,2)) AS amount,
        CAST(get_json_object(raw_payload, '$.transaction_time') AS TIMESTAMP) AS transaction_time,
        _ingest_timestamp
    FROM main.bronze.raw_store_transactions
    WHERE get_json_object(raw_payload, '$.order_id') IS NOT NULL
) AS source
ON target.order_id = source.order_id
WHEN MATCHED AND source._ingest_timestamp > target._ingest_timestamp THEN
    UPDATE SET 
        target.customer_id = source.customer_id,
        target.amount = source.amount,
        target.transaction_time = source.transaction_time,
        target.updated_at = source._ingest_timestamp
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, amount, transaction_time, updated_at)
    VALUES (source.order_id, source.customer_id, source.amount, source.transaction_time, source._ingest_timestamp);

Gold Layer: Business Aggregations & Dimensional Analytics

The Gold Layer represents the refined presentation tier engineered specifically for business consumption, self-service analytics, executive BI dashboards, and AI/BI Genie spaces. While Silver tables maintain normalized entity structures, Gold tables assemble data into consumption-optimized formats—such as Star Schema dimensional models (facts and dimensions) or pre-aggregated summary tables.

Key characteristics of the Gold layer include:

1. Business-Level Aggregations: Computing Key Performance Indicators (KPIs), such as Monthly Active Users (MAU), Customer Lifetime Value (CLV), or daily revenue rollups by region.
2. Dimensional Modeling: Structuring cleaned Silver tables into Kimball star schema facts and dimensions linked by Unity Catalog constraints.
3. Performance Optimization: Applying Liquid Clustering, Z-Ordering, or materialized views to minimize scan times for dashboard queries.
4. Strict Security and Governance: Applying fine-grained access controls, row filters, and column masks so business stakeholders access only permitted datasets.

Data Flow & Streaming/Batch Integration Patterns

A major strength of the Medallion Architecture on Databricks is its unified support for both batch loading and real-time streaming pipelines under the Delta Lake engine. Through Structured Streaming and Delta Live Tables (DLT), data can flow continuously across Bronze, Silver, and Gold layers with sub-second or minute-level latency, or execute periodically in scheduled batch intervals.

Regardless of execution frequency, the logical isolation of layers ensures that streaming data ingestion in Bronze never locks or interferes with business queries running against Gold tables.

Analytical Consumption & Optimization across Layers

To maximize the utility of the Medallion Architecture for data analysts using Databricks SQL:

• Avoid Querying Bronze Tables directly for BI: Raw Bronze tables contain unparsed strings and duplicate change records, which result in slow, expensive queries and inaccurate metric calculations.
• Use Silver for Exploratory Data Science: Silver tables provide granular, row-level cleaned events, making them ideal for custom ad-hoc investigations and ML feature generation.
• Power Dashboards via Gold Tables: Always point Databricks AI/BI Dashboards and AI/BI Genie spaces to Gold tables or Gold Materialized Views to achieve sub-second query responsiveness.