12.1 Medallion Architecture Design Patterns

Medallion Architecture Principles

The Medallion Architecture is a data design pattern used to logically organize data in a Lakehouse, with the goal of incrementally and progressively improving the structure and quality of data as it flows through each layer of the architecture (from Bronze to Silver to Gold).

The Bronze Layer (Raw Data)

The Bronze layer is the landing zone for all raw data. Its primary purpose is to capture data from external source systems in its original format without any modifications. This ensures that no data is lost and provides a historical archive that can be used for auditing or reprocessing if business logic changes.

Key Characteristics of Bronze

• Append-Only: Data is continuously appended. Updates and deletes from source systems are typically recorded as new events rather than mutating existing records.
• Schema Evolution: The schema is often flexible, capturing raw JSON, CSV, or Parquet files as they arrive.
• SLA Expectations: High throughput and low latency for ingestion. Data availability is the priority over query performance.

# Example: Ingesting raw JSON data into Bronze
(spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "json")
  .load("/mnt/source/telemetry/")
  .writeStream
  .format("delta")
  .option("checkpointLocation", "/mnt/bronze/_checkpoints/telemetry")
  .start("/mnt/bronze/telemetry"))

The Silver Layer (Cleansed and Conformed Data)

The Silver layer represents a cleansed, validated, and enriched version of the data. This layer acts as the single source of truth for the enterprise, providing a normalized view of core business entities (e.g., Customers, Orders, Products).

Key Characteristics of Silver

• Data Quality Rules: Missing values are handled, data types are cast, and invalid records are quarantined or dropped.
• Deduplication: Records are deduplicated, and upserts (MERGE operations) are used to handle late-arriving updates.
• SLA Expectations: Moderate latency. The focus is on data accuracy and consistency.

-- Example: Upserting data into Silver using Delta Lake MERGE
MERGE INTO silver.customers target
USING bronze.customers_updates source
ON target.customer_id = source.customer_id
WHEN MATCHED AND source.update_timestamp > target.update_timestamp THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *

The Gold Layer (Business-Level Aggregations)

The Gold layer is highly refined and aggregated, designed specifically for analytics, reporting, and machine learning. This layer is organized around business use cases rather than source systems.

Key Characteristics of Gold

• Read-Optimized: Data is structured in dimensional models (Star Schemas) or wide denormalized tables to maximize query performance for BI tools.
• Aggregations: Pre-calculated metrics (e.g., daily sales, monthly active users) are stored here to reduce compute costs at query time.
• SLA Expectations: High availability and extremely low latency for read queries. This layer must support concurrent BI user workloads.

Batch vs. Streaming Processing Boundaries

Designing the processing boundaries between layers requires balancing latency requirements with compute costs. Databricks Structured Streaming and Delta Live Tables (DLT) provide unified APIs for both paradigms.

Bronze to Silver (Streaming)

Moving data from Bronze to Silver is typically handled using streaming pipelines. This ensures that data is cleansed and available as quickly as possible. The low latency of streaming is beneficial for identifying data quality issues early and supporting operational reporting.

Silver to Gold (Batch)

Moving data from Silver to Gold is often done using scheduled batch jobs. Gold layer tables frequently involve complex aggregations, window functions, or large-scale joins that are computationally expensive to evaluate continuously in a streaming context. Running these on a schedule (e.g., hourly or daily) balances freshness with cost efficiency.

Dimensional Models vs. Wide Denormalized Tables

When designing the Gold layer in a distributed environment like Databricks, data engineers must choose between traditional dimensional models and wide denormalized tables.

Dimensional Modeling (Star Schema)

A Star Schema organizes data into central Fact tables (events/metrics) surrounded by Dimension tables (context). This is familiar to BI developers and efficiently manages updates to descriptive attributes (e.g., changing a customer's address).

However, in a distributed Lakehouse, joining massive Fact tables with large Dimension tables requires shuffling data across the cluster network, which can degrade query performance.

Wide Denormalized Tables

To optimize read performance in Databricks, engineers often create Wide Denormalized Tables. This approach pre-joins Fact and Dimension data into a single, massive table.

Advantages of Wide Tables:

• Zero Joins at Query Time: BI queries execute significantly faster because they only scan a single table, eliminating network shuffle.
• Optimized for Columnar Storage: Parquet/Delta formats are highly efficient at scanning wide tables, only reading the specific columns requested by the query.

Trade-offs:

• Data Duplication: The same dimensional attribute (e.g., store name) is repeated millions of times.
• Complex Updates: If a store changes its name, millions of records in the wide table must be updated (an expensive operation in Delta Lake) compared to a single row update in a Star Schema dimension table.

In modern Lakehouse architectures, a hybrid approach is common: maintaining a Star Schema for flexibility, while using Materialized Views to generate wide denormalized tables for specific, high-performance BI dashboards.