212 What is Medallion Architecture?
Medallion Architecture is a data design pattern used to logically organize data in a data lake or lakehouse (such as Databricks or Snowflake). It structures data storage in progressive layers—traditionally called Bronze, Silver, and Gold—to steadily improve the quality, structure, and usability of data as it flows through the pipeline.
Bronze Layer (Raw): Ingests raw data directly from source systems in its original format (JSON, CSV, CDC streams). Data is append-only and acts as a historical audit trail.
Silver Layer (Cleaned & Conformed): Cleanses, normalizes, deduplicates, and structures the data (often into relational tables or dimensional models). It provides an enterprise "single source of truth."
Gold Layer (Curated & Aggregated): Highly refined, business-level data optimized for consumption by BI tools, dashboards, data marts, and machine learning models (e.g., star schemas, fact/dimension tables, or aggregated rollups).
How to Do ETL and Data Modeling on Medallion Architecture
Implementing ETL and data modeling across the medallion layers follows a sequential transformation path:
Step 1: Extract and Load into the Bronze Layer (Raw Ingestion)
ETL Action: Pull raw data from operational databases, APIs, or event streams and land it into the Bronze layer storage (e.g., cloud object storage or raw lakehouse tables) with minimal transformation.
Data Modeling: None required. Maintain schema-on-read or raw append-only structures with metadata columns (e.g.,
ingestion_timestamp,source_file_name).
Step 2: Transform Bronze to Silver (Cleansing & Normalization)
ETL Action: Read from Bronze, parse payloads, handle missing values, filter out corrupt records, deduplicate rows, and apply enterprise data types.
Data Modeling:
Apply relational database principles (3NF or normalized structures) or stage data for dimensional modeling.
Implement Change Data Capture (CDC) tracking and handle Slowly Changing Dimensions (SCD Types 1 and 2).
Step 3: Transform Silver to Gold (Dimensional Modeling & Aggregation)
ETL Action: Read clean data from the Silver layer, perform enterprise joins, compute business metrics, and load into consumption-ready structures.
Data Modeling:
Build dimensional models (Star Schema or Snowflake Schema).
Design Fact Tables (at atomic or transaction-level grain, e.g.,
Policy_ID,Sale_Date,Customer_ID) and Dimension Tables (Customer,Agent,Branch).Create pre-aggregated summary tables or data marts for high-performance reporting.
