Part 3 — Building the Data Lake: Bronze, Silver, and Gold Architecture

A well-designed data lake is the backbone of a modern analytics platform. Whether you ultimately use a warehouse like Snowflake, a lakehouse like Fabric or Databricks, or hybrid architectures across AWS and GCP, the lake must be structured in a way that preserves raw data, supports scalable transformations, and aligns cleanly to your dimensional model.

This part covers the complete medallion architecture — Bronze, Silver, and Gold — in full technical depth. You will learn how to lay out folder structures, choose the right table format for each layer, apply partitioning strategies, optimize file sizes, and build the transformation pipeline that feeds your dimensional models. All examples are cloud-agnostic and work in ADLS, S3, GCS, and OneLake.

1 The Medallion Architecture — Why Three Layers Beginner

The medallion architecture — originated by Databricks and now the industry standard for lake and lakehouse design — organizes data into three zones with distinct responsibilities. Each zone solves a specific problem that the previous zone creates.

The three-layer separation solves a fundamental tension: you want to preserve raw data for audit, replay, and debugging, but you also want clean, fast, business-ready data for analytics. Trying to serve both purposes from a single table leads to messy, unreliable pipelines. The medallion architecture separates these concerns cleanly into dedicated zones with clear contracts between them.

Responsibilities at Each Layer

2 Bronze Layer — Raw Ingestion Beginner

Bronze stores data exactly as received from source systems. No transformations. No deduplication. No type casting. No business rules. Full schema drift is allowed — if the source system adds a column, Bronze absorbs it without failing. Bronze is your source-of-truth archive: it allows you to replay pipelines from scratch, audit upstream source data, investigate data quality issues, and rebuild Silver or Gold whenever transformation logic changes.

What Bronze Is NOT

• Not a staging area for ad-hoc queries — it is an archive, not an analytics layer
• Not a place to apply business rules — that is Silver’s job
• Not a place to deduplicate — deduplication happens in Silver
• Not a place to merge or update records — Bronze is append-only

Typical Bronze Data Sources and Formats

• JSON payloads from API extracts, CDC streams, and webhook events
• CSV exports from ERP, CRM, and flat-file sources
• Parquet files from optimized bulk extracts
• Delta Lake raw tables in Databricks or Fabric for structured CDC streams

Data Arrives Via

• Fivetran — managed SaaS and database connectors
• Airbyte — open-source connectors
• Databricks Auto Loader — incremental file ingestion
• Snowpipe — auto-ingest to Snowflake
• AWS Glue Jobs — ETL on S3
• ADF or Fabric Data Pipelines — Azure and Fabric ingestion

3 Silver Layer — Clean and Conformed Beginner

Silver is where the lake begins to take shape as a usable data asset. Bronze is raw and unreliable — Silver fixes that. Every quality and conformance operation that prepares data for the dimensional model happens in Silver.

What Silver Does

• Type enforcement — cast strings to dates, integers, decimals with proper precision
• Deduplication — remove duplicate rows using ROW_NUMBER() or DISTINCT on natural keys
• Null handling — standardize nulls, apply default values where appropriate
• JSON flattening — extract nested structures into columns
• Name standardization — consistent column names across sources (snake_case, standard date format)
• Natural key conformance — standardize source IDs to a consistent format
• Business rule validation — flag or exclude records that violate basic rules

4 Gold Layer — Dimensional and Analytics-Ready Beginner

Gold tables are the dimensional models from Part 1. This is where you apply surrogate keys, SCD Type 2 history tracking, fact table grain enforcement, metric definitions, and conformed dimension logic. Gold is the layer that BI tools, semantic models, and SQL endpoints connect to.

For lakehouses running Fabric or Databricks, Gold tables are typically Delta Lake tables with ACID transactions — enabling reliable incremental updates, SCD Type 2 row management, and time travel. For warehouse-centric architectures, Gold tables are the final curated tables in Snowflake, BigQuery, Redshift, or Synapse that the warehouse serves directly to BI tools.

5 Folder Structure — Vendor-Neutral and Scalable Beginner

A clean folder design directly impacts query performance (through partition pruning), pipeline reliability (through clear data contracts between layers), and governance (through predictable access patterns). The structure below works in ADLS Gen2, Amazon S3, Google Cloud Storage, and Microsoft OneLake.

Why This Structure Works

• Source-scoped Bronze — each source system (crm, ecommerce) has its own folder, preventing naming collisions and making source-level access control straightforward
• Date partitioning in Bronze — year= / month= / day= Hive-style partitioning enables partition pruning in Spark, Athena, and BigQuery external tables
• Delta directories in Silver and Gold — Delta Lake tables are directories, not files. The .delta naming convention signals the format to all consumers
• dim/ and fact/ separation in Gold — clean separation for access control and monitoring — dimension tables and fact tables have very different update frequencies and access patterns

Platform-Specific Storage Mapping

6 Choosing File Formats: Parquet, Delta, and Iceberg Intermediate

The file format determines storage efficiency, query performance, and what operations are possible on your data. All three formats in the table below are columnar — they store data column-by-column rather than row-by-row, enabling analytical engines to scan only the columns a query uses.

Format Selection by Layer and Platform

7 Bronze Ingestion Examples — All Platforms Intermediate

Databricks Auto Loader — Recommended for Continuous File Ingestion

Auto Loader reads new files incrementally as they land in cloud storage. It handles schema inference, schema evolution, and tracks which files have been processed using a checkpoint. It is the recommended Bronze ingestion pattern for Databricks environments.

# Databricks: Auto Loader Bronze ingestion
# Reads JSON files continuously as they land in cloud storage
# Writes as Delta for downstream Silver processing
df = (
    spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/mnt/checkpoints/crm_customer_schema")
    .option("cloudFiles.inferColumnTypes", "true")
    .load("/mnt/raw/crm/customer")
)

(
    df.writeStream
    .format("delta")
    .option("checkpointLocation", "/mnt/checkpoints/crm_customer")
    .option("mergeSchema", "true")      # absorb schema changes automatically
    .trigger(availableNow=True)         # process all available files then stop
    .start("/mnt/bronze/crm/customer")
)

Snowpipe — Auto-Ingest to Snowflake Bronze

Snowpipe triggers automatically when files land in a cloud stage. Once configured it runs continuously without any scheduling infrastructure.

-- Snowflake: Snowpipe for continuous Bronze ingestion
-- Files land in S3/ADLS/GCS → event notification → Snowpipe triggers → Bronze table

-- Step 1: Create a file format for incoming JSON
CREATE OR REPLACE FILE FORMAT json_format
TYPE = 'JSON'
STRIP_OUTER_ARRAY = TRUE;

-- Step 2: Create a stage pointing to your cloud storage
CREATE OR REPLACE STAGE crm_raw_stage
URL = 's3://your-bucket/raw/crm/customer/'
CREDENTIALS = (AWS_KEY_ID = '...' AWS_SECRET_KEY = '...')
FILE_FORMAT = json_format;

-- Step 3: Create the Bronze target table
CREATE TABLE IF NOT EXISTS bronze.crm_customer (
    raw_data    VARIANT,        -- store raw JSON as VARIANT for full flexibility
    ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Step 4: Create the Snowpipe
CREATE OR REPLACE PIPE crm_customer_pipe
AUTO_INGEST = TRUE
AS
COPY INTO bronze.crm_customer (raw_data, ingested_at)
FROM (
    SELECT $1, CURRENT_TIMESTAMP()
    FROM @crm_raw_stage
)
FILE_FORMAT = (FORMAT_NAME = 'json_format');

AWS Glue Job — Bronze Ingestion on S3

# AWS Glue: read raw files from S3, write Parquet Bronze
import sys
from awsglue.context import GlueContext
from pyspark.context import SparkContext

sc = SparkContext()
glueContext = GlueContext(sc)

# Read raw JSON from S3
bronze_raw = glueContext.create_dynamic_frame.from_options(
    connection_type = "s3",
    connection_options = {"paths": ["s3://lake/raw/crm/customer/"]},
    format = "json"
)

# Write to Bronze Parquet with date partitioning
bronze_raw.toDF().write \
    .mode("append") \
    .partitionBy("year", "month", "day") \
    .parquet("s3://lake/bronze/crm/customer/")

Fabric Data Pipelines — Bronze Ingestion to OneLake

-- Fabric: use Copy Activity in a Data Pipeline to land files in OneLake Bronze
-- Source: HTTP connector, REST API, SQL Server, SharePoint, or any supported source
-- Destination: OneLake Files section of your Lakehouse (Bronze zone)

-- After landing files, use a Notebook activity to register as a Delta table:
-- (Run in Fabric Notebook attached to your Lakehouse)
from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Read landed files from OneLake Files (Bronze zone)
df = spark.read.format("json").load("Files/bronze/crm/customer/")

# Add ingestion metadata
from pyspark.sql.functions import current_timestamp, input_file_name
df = df.withColumn("_ingested_at", current_timestamp()) \
       .withColumn("_source_file", input_file_name())

# Write to Lakehouse Tables section as Delta (shows in SQL Analytics Endpoint)
df.write.format("delta").mode("append").saveAsTable("bronze_crm_customer")

8 Silver Transformation Examples — All Platforms Intermediate

Databricks Spark Silver Example

# Databricks: Bronze → Silver cleaning in PySpark
from pyspark.sql.functions import (
    col, lower, initcap, trim, when, current_timestamp,
    row_number
)
from pyspark.sql.window import Window

bronze = spark.read.format("delta").load("/lake/bronze/crm/customer")

# Deduplicate: keep the most recent record per customer_id
window = Window.partitionBy("customer_id").orderBy(col("updated_at").desc())

silver = (
    bronze
    .withColumn("rn", row_number().over(window))
    .filter(col("rn") == 1)
    .drop("rn")
    # Type enforcement and standardization
    .withColumn("email",         lower(trim(col("email"))))
    .withColumn("customer_name", initcap(trim(col("customer_name"))))
    .withColumn("customer_id",   col("customer_id").cast("bigint"))
    # Null handling
    .withColumn("city",          when(col("city").isNull(), "Unknown").otherwise(col("city")))
    .withColumn("_silver_loaded_at", current_timestamp())
    # Filter out records with missing natural key
    .filter(col("customer_id").isNotNull())
)

# Write to Silver as Delta with merge schema enabled
(
    silver.write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .save("/lake/silver/crm/customer")
)

Snowflake Silver Example

-- Snowflake: Bronze VARIANT → Silver typed table
CREATE OR REPLACE TABLE silver.customer AS
SELECT
    raw_data:id::BIGINT              AS customer_id,
    INITCAP(TRIM(raw_data:name::STRING))   AS customer_name,
    LOWER(TRIM(raw_data:email::STRING))    AS email,
    COALESCE(raw_data:city::STRING, 'Unknown') AS city,
    raw_data:state::STRING           AS state_province,
    raw_data:country::STRING         AS country,
    raw_data:loyalty_tier::STRING    AS loyalty_tier,
    raw_data:updated_at::TIMESTAMP   AS updated_at,
    CURRENT_TIMESTAMP()              AS silver_loaded_at
FROM bronze.crm_customer
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY raw_data:id::STRING
    ORDER BY raw_data:updated_at::TIMESTAMP DESC
) = 1                               -- deduplicate: keep latest per customer
WHERE raw_data:id IS NOT NULL;      -- exclude records without a natural key

Fabric Lakehouse Silver — SQL Analytics Endpoint

-- Fabric: create Silver view over Bronze Delta table
-- (run via Fabric Notebook or SQL Analytics Endpoint)
CREATE OR ALTER VIEW silver.Customer AS
SELECT
    CAST(customer_id AS BIGINT)          AS CustomerID,
    INITCAP(TRIM(customer_name))         AS CustomerName,
    LOWER(TRIM(email))                   AS Email,
    COALESCE(city, 'Unknown')            AS City,
    state                                AS StateProvince,
    country                              AS Country,
    loyalty_tier                         AS LoyaltyTier,
    updated_at                           AS UpdatedAt
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY updated_at DESC
           ) AS rn
    FROM bronze_crm_customer
    WHERE customer_id IS NOT NULL
) AS deduped
WHERE rn = 1;

BigQuery Silver Example

-- BigQuery: Bronze external table → Silver native table
CREATE OR REPLACE TABLE silver.customer AS
SELECT
    SAFE_CAST(customer_id AS INT64)          AS customer_id,
    INITCAP(TRIM(customer_name))             AS customer_name,
    LOWER(TRIM(email))                       AS email,
    COALESCE(city, 'Unknown')                AS city,
    state                                    AS state_province,
    country,
    loyalty_tier,
    CAST(updated_at AS TIMESTAMP)            AS updated_at,
    CURRENT_TIMESTAMP()                      AS silver_loaded_at
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY updated_at DESC
           ) AS rn
    FROM bronze.crm_customer
    WHERE customer_id IS NOT NULL
)
WHERE rn = 1;

9 Gold Layer Implementation Examples Intermediate

Gold tables implement the dimensional model from Part 1 using Silver as the source. The following examples show the initial Gold population — the full SCD Type 2 merge pattern that keeps Gold current on every pipeline run is covered in Part 6.

Gold DimCustomer — Initial Load

-- Gold DimCustomer: initial population from Silver
-- Works across: Snowflake, Fabric Warehouse, BigQuery, Redshift, Synapse
-- For Delta/Iceberg lakehouse platforms use equivalent Spark SQL

CREATE TABLE gold.DimCustomer AS
SELECT
    ROW_NUMBER() OVER (ORDER BY CustomerID) AS CustomerKey,
    CustomerID      AS CustomerNaturalKey,
    CustomerName,
    Email,
    City,
    StateProvince,
    Country,
    LoyaltyTier,
    CURRENT_DATE     AS EffectiveFrom,
    CAST('9999-12-31' AS DATE) AS EffectiveTo,
    1                AS IsCurrent
FROM silver.Customer;

Gold FctOrderLine — Joining to Dimension Surrogate Keys

-- Gold FctOrderLine: surrogate key lookup join
-- Critical: join to dimension using NaturalKey + IsCurrent = 1
-- This ensures facts link to the dimension version active at load time

CREATE TABLE gold.FctOrderLine AS
SELECT
    ROW_NUMBER() OVER (ORDER BY o.order_id, o.line_number) AS OrderLineKey,
    d.DateKey,
    c.CustomerKey,
    p.ProductKey,
    o.quantity,
    o.unit_price,
    o.quantity * o.unit_price - COALESCE(o.discount_amount, 0) AS ExtendedAmount,
    CURRENT_TIMESTAMP() AS LoadedAt
FROM silver.orders         o
JOIN gold.DimDate           d ON d.FullDate           = o.order_date
JOIN gold.DimCustomer       c ON c.CustomerNaturalKey  = o.customer_id
                              AND c.IsCurrent           = 1
JOIN gold.DimProduct        p ON p.ProductNaturalKey   = o.product_id
                              AND p.IsCurrent           = 1
WHERE o.order_id IS NOT NULL
AND   o.order_date IS NOT NULL;

10 Partitioning Strategy Intermediate

Partitioning divides large tables into smaller physical segments based on column values. When a query filters on a partitioned column, the engine reads only the relevant partitions rather than scanning the entire table. For large Silver and Gold tables this can reduce scan size by 90% or more, dramatically reducing query time and cost.

Partitioning Guidelines

• Partition by date in Bronze. Hive-style year= / month= / day= partitioning enables date-range pruning and is supported by every engine (Spark, Athena, BigQuery external tables, Synapse serverless).
• Partition fact tables by date in Gold. Order date, event date, or session date is almost always the most common filter column in analytical queries. Date partitioning gives the largest pruning benefit on fact tables.
• Do not over-partition. Avoid partitioning on high-cardinality columns like customer ID or product ID. Too many small partitions create excessive metadata overhead and actually slow down queries. A partition that contains fewer than 128 MB of data is generally too small.
• Target file sizes of 128 MB to 1 GB per partition. Below 128 MB you have a small-file problem. Above 1 GB per file you lose parallelism benefits.

-- Delta Lake: partition fact table by order date
-- Databricks / Fabric Spark
spark.sql("""
    CREATE TABLE gold.fct_order_line
    USING DELTA
    PARTITIONED BY (order_date_partition)
    AS
    SELECT
        *,
        DATE_FORMAT(order_date, 'yyyy-MM') AS order_date_partition  -- month-level partition
    FROM silver.orders_prepared
""")

-- Snowflake: no explicit partitioning needed
-- Micro-partitions are automatic; use clustering keys for large tables
ALTER TABLE gold.FctOrderLine CLUSTER BY (OrderDateKey);

-- BigQuery: partition by date column
CREATE TABLE gold.fct_order_line
PARTITION BY DATE(order_date)
CLUSTER BY customer_key, product_key
AS SELECT * FROM silver.orders_prepared;

-- Check partition statistics in BigQuery
SELECT partition_id, total_rows, total_logical_bytes / POW(1024,3) AS gb
FROM gold.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'fct_order_line'
ORDER BY partition_id DESC LIMIT 20;

11 File Compaction and Optimization Advanced

Small files are one of the most common performance problems in lakehouses. Every Spark task that reads a small file incurs the same planning and scheduling overhead as reading a large file — but does a fraction of the work. A table with 10,000 files of 1 MB each performs far worse than the same data in 100 files of 100 MB each. This problem grows over time as incremental pipelines append small files with each run.

Databricks Delta OPTIMIZE and ZORDER

-- Compact small files and apply Z-ordering (Databricks)
-- OPTIMIZE merges small files into larger ones (target ~1 GB)
-- ZORDER co-locates related data to improve file-skipping on filter columns

OPTIMIZE delta.`/lake/silver/crm/customer`;

-- With Z-ordering on frequently filtered columns
OPTIMIZE gold.fct_order_line
ZORDER BY (customer_key, order_date_key);
-- Z-order on multiple columns creates a locality-preserving space-filling curve
-- Queries filtering on customer_key OR order_date_key will skip more files

-- Schedule OPTIMIZE weekly or after large batch loads
-- For streaming tables: run OPTIMIZE after each micro-batch or nightly

Delta VACUUM — Remove Old File Versions

-- Remove old file versions to reclaim storage
-- Default retention: 7 days (168 hours) -- do not reduce below 7 days
-- Lower retention prevents time-travel queries to older versions

VACUUM silver.customer_data RETAIN 168 HOURS;  -- 7 days default
VACUUM gold.fct_order_line  RETAIN 168 HOURS;

-- Check table size before and after vacuum
DESCRIBE DETAIL delta.`/lake/gold/fct_order_line`;

Fabric Lakehouse — Automatic Optimization

-- Fabric automatically optimizes Delta tables in the background
-- You can trigger manual optimization from a Fabric Notebook:
from delta.tables import DeltaTable

dt = DeltaTable.forName(spark, "gold.fct_order_line")
dt.optimize().executeCompaction()   # compact small files

# V-Order optimization (Fabric-specific -- improves Power BI Direct Lake read speed)
spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true")
spark.conf.set("spark.microsoft.delta.optimizeWrite.binSize", "1073741824")  # 1 GB bins

AWS Glue Compaction for Parquet

# AWS Glue: compact small Parquet files into larger ones
# Read all small files, coalesce to fewer larger files, overwrite
from pyspark.context import SparkContext
from awsglue.context import GlueContext

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session

df = spark.read.parquet("s3://lake/silver/crm/customer/")
target_file_count = max(1, int(df.count() / 500000))  # ~500K rows per file

(
    df.coalesce(target_file_count)
    .write
    .mode("overwrite")
    .parquet("s3://lake/silver/crm/customer/")
)

12 Workshops

References

• Databricks — Medallion Architecture
• Delta Lake Documentation
• Apache Iceberg
• Apache Parquet
• Databricks Auto Loader
• Databricks — Z-Order Optimization
• Databricks — Delta File Management
• Snowflake — Snowpipe
• Snowflake — External Tables
• Microsoft Fabric — Lakehouse Overview
• Microsoft Fabric — OneLake Shortcuts
• Microsoft Fabric — Lakehouse Optimization
• AWS Glue Documentation
• Google BigLake
• Delta Lake — Best Practices for File Sizing