4.1 Dimensional Modeling: Star vs. Snowflake Schemas

4.1 Dimensional Modeling: Star vs. Snowflake Schemas

Designing high-performance analytical data architectures in Snowflake requires a clear understanding of dimensional modeling principles. While Ralph Kimball's dimensional modeling techniques were originally developed for legacy on-premises databases with disk spindle constraints, their application within Snowflake's cloud-native, columnar, micro-partitioned architecture introduces fundamentally different trade-offs.

For the SnowPro Advanced: Architect exam, you must evaluate how Fact tables, Dimension tables, Star schemas, Snowflake schemas, and Third Normal Form (3NF) interact with Snowflake's query compiler, cost-based optimizer (CBO), and micro-partition pruning mechanics.

Dimensional Modeling Foundations: Star vs. Snowflake vs. 3NF

Dimensional modeling organizes data around business processes into two foundational table categories:

1. Fact Tables: Contain quantitative measurements, numeric metrics, and foreign keys referencing dimensions. Facts can be fully additive (e.g., sales revenue, quantity sold), semi-additive (e.g., account balances, inventory levels), or non-additive (e.g., unit price ratios, percentage discounts). Fact tables typically possess high row volumes, rapid ingestion rates, and append-heavy access patterns.
2. Dimension Tables: Contain descriptive context, textual attributes, classifications, and business hierarchies (e.g., customer demographics, store locations, product categories). Dimensions typically have significantly lower row counts than fact tables but feature wide rows with dozens of descriptive columns.

The Three Competing Schema Paradigms

-- Example Star Schema: Flat, denormalized dimension joined directly to Fact table
SELECT 
    d.region_name,
    d.product_category,
    SUM(f.sales_amount) AS total_revenue,
    COUNT(DISTINCT f.order_id) AS total_orders
FROM sales_dw.marts.fact_sales f
JOIN sales_dw.marts.dim_product_denorm d 
    ON f.product_key = d.product_key
WHERE d.region_name = 'North America'
GROUP BY d.region_name, d.product_category;

Columnar Storage & Micro-Partitioning: Redefining Denormalization

In legacy row-oriented databases (e.g., Oracle, SQL Server, PostgreSQL), wide denormalized tables introduce severe I/O penalties. Because row stores read entire rows from physical blocks into memory, querying a single attribute from a 100-column table incurs the I/O cost of reading all 100 columns. Consequently, legacy DBAs frequently normalized schemas into snowflake designs to minimize row width.

Snowflake completely eliminates this penalty through its proprietary columnar storage and micro-partitioning architecture:

1. Zero I/O Penalty for Unqueried Wide Columns

Snowflake divides tables into immutable micro-partitions ranging from 50 to 500 MB of uncompressed data. Within each micro-partition, data is organized strictly by column. When an analytical query projects 3 columns out of a 150-column denormalized dimension table, Snowflake's virtual warehouse scans only the micro-partition file offsets corresponding to those 3 columns. The remaining 147 columns are never transferred from cloud object storage (S3/Azure Blob/GCS) nor loaded into memory.

2. High-Ratio Columnar Compression

Denormalized dimensions frequently contain repeated textual values (e.g., repeating state names, country codes, or product category descriptions millions of times). Within Snowflake's columnar format, identical contiguous values compress exceptionally well using dictionary encoding, run-length encoding (RLE), and frame-of-reference compression. A denormalized dimension often consumes less than 15% of the raw uncompressed storage footprint, rendering storage redundancy concerns negligible.

3. Optimizer Join Elimination & Simplicity

Every join in an execution plan introduces query compilation overhead, state memory allocation, and potential data redistribution. By utilizing a Star Schema, the Snowflake Cost-Based Optimizer works with simpler join graphs, generates tighter cardinality estimates, and readily executes Broadcast Joins.

Join Performance Mechanics: Broadcast Joins vs. Repartitioned Joins

When executing joins between fact tables and dimension tables, Snowflake's execution engine dynamically chooses between two primary physical join strategies based on table cardinality and data distribution:

┌────────────────────────────────────────────────────────────────────────┐
│ Broadcast Join (Small/Medium Dimension)                                │
│                                                                        │
│  Fact Table (Multi-TB)               Dimension Table (< Millions Rows) │
│  [Node 1] [Node 2] [Node 3]                     │                      │
│     │        │        │                         ▼                      │
│     │        │        │               Replicated to ALL Nodes          │
│     ▼        ▼        ▼                         │                      │
│  Local    Local    Local   ◄────────────────────┘                      │
│  Join     Join     Join    (Zero network shuffling of Fact Table!)     │
└────────────────────────────────────────────────────────────────────────┘

┌────────────────────────────────────────────────────────────────────────┐
│ Repartitioned / Hash Join (Both Tables Large)                          │
│                                                                        │
│  Large Fact Table                    Large Dimension Table             │
│  [Node 1] [Node 2] [Node 3]          [Node 1] [Node 2] [Node 3]        │
│       │       │       │                   │       │       │            │
│       └───────┴───────┼───────────────────┴───────┴───────┘            │
│                       ▼                                                │
│          Network Shuffle by Hash(Join_Key)                             │
│          (Significant Network I/O and Memory Overhead)                 │
└────────────────────────────────────────────────────────────────────────┘

1. Broadcast Joins (Ideal for Star Schemas)

• Mechanism: The entire smaller table (the dimension) is broadcast across the network and loaded into the local memory cache of every compute worker node participating in the virtual warehouse.
• Fact Table Scan: Each node reads its assigned micro-partitions of the large fact table and performs the join entirely in local memory against its local replica of the dimension table.
• Performance Impact: The large fact table does not need to be redistributed across the network, which usually makes the join cheaper.
• Threshold: Snowflake does not publish a fixed broadcast threshold; the optimizer decides based on estimated sizes, so a small filtered dimension is the usual broadcast candidate.

2. Repartitioned / Distributed Hash Joins

• Mechanism: When both joining tables are large, broadcasting the entire table would exhaust node memory. Instead, Snowflake hashes the join key on both tables and shuffles rows across the network so that matching hash values land on the same compute node.
• Performance Impact: Introduces substantial network I/O, serialization overhead, and potential memory spilling to local SSD or remote storage if node memory limits are exceeded.

Runtime Micro-Partition Pruning: Join Filter Pushdown (Bloom Filters)

One of Snowflake's most powerful execution optimizations in dimensional models is Dynamic Partition Pruning (DPP) via runtime Bloom filters:

1. During query execution, the virtual warehouse scans the dimension table first, applying any WHERE clause filters (e.g., dim_store.region = 'EMEA').
2. The surviving surrogate keys are compiled into a compact probabilistic in-memory structure called a Bloom filter.
3. This Bloom filter is dynamically pushed down into the fact table scan operator before reading fact table micro-partitions.
4. Snowflake compares the Bloom filter against the column metadata (min/max values and dictionary filters) stored in the Cloud Services layer for each fact table micro-partition.
5. Micro-partitions or rows that cannot contain matching dimension keys are skipped. In Query Profile this appears as a JoinFilter operator and as fewer partitions scanned than the static pruning alone would allow.

Clustering Alignment for Dimensional Models

To maximize the effectiveness of Dynamic Partition Pruning:

• If a multi-billion-row fact table is queried primarily by transaction date and regional dimensions, the natural ingestion order often clusters by date.
• For massive tables where cross-dimensional filtering is the dominant workload, define an explicit clustering key combining the high-frequency dimension foreign keys:

-- Optimal clustering key on massive fact table: Date dimension + high-cardinality dimension
ALTER TABLE sales_dw.marts.fact_sales 
  CLUSTER BY (order_date_key, customer_region_key);

Architect Tip: Never cluster a table on a surrogate key that has purely random distribution (like raw UUIDs or unaligned hashes) if range pruning is required. Natural sequence numbers or composite keys matching temporal query patterns maximize partition pruning.

Slowly Changing Dimensions (SCD Type 1 vs. SCD Type 2)

In analytical environments, dimension attributes evolve over time. Managing these changes requires choosing an appropriate Slowly Changing Dimension (SCD) strategy:

SCD Type 1: In-Place Overwrites

• Behavior: Replaces existing attribute values with new values. Historical context is permanently lost.
• Use Case: Correcting typographical errors, updating non-historical attributes (e.g., correcting an employee's misspelled middle name).
• Snowflake Storage Impact: Executing an UPDATE in Snowflake does not modify physical bytes in place; it rewrites entire micro-partitions where the target rows reside, creating new micro-partitions and marking old ones for Time Travel retention.

SCD Type 2: Historical Versioning

• Behavior: Preserves historical context by inserting a new record for every attribute change while closing out the previous record using temporal tracking columns (valid_from, valid_to, is_current).
• Use Case: Tracking customer relocations, compensation history, product price tier revisions.

Production SCD Type 2 Implementation Pattern with Streams and MERGE

Executing row-by-row updates for SCD Type 2 in a cloud data warehouse is an anti-pattern. Instead, architects implement a two-phase set-based MERGE using Snowflake Change Data Capture (CDC) Streams:

-- Step 1: Establish a standard stream on the raw dimension staging table
CREATE OR REPLACE STREAM sales_dw.staging.stg_customer_stream 
  ON TABLE sales_dw.staging.stg_customer;

-- Step 2: Set-based SCD Type 2 transformation using MERGE
MERGE INTO sales_dw.marts.dim_customer target
USING (
    -- Subquery: Identify records requiring closure AND prepare new version inserts
    SELECT 
        s.customer_id,
        s.customer_name,
        s.city,
        s.postal_code,
        s.updated_at AS effective_start_date,
        '9999-12-31 00:00:00'::TIMESTAMP_NTZ AS effective_end_date,
        TRUE AS is_current_flag,
        'INSERT' AS dml_operation
    FROM sales_dw.staging.stg_customer_stream s
    JOIN sales_dw.marts.dim_customer c
      ON s.customer_id = c.customer_id 
     AND c.is_current_flag = TRUE
    WHERE s.city <> c.city OR s.postal_code <> c.postal_code

    UNION ALL

    -- Records to close out (expire existing current record)
    SELECT 
        s.customer_id,
        NULL AS customer_name,
        NULL AS city,
        NULL AS postal_code,
        NULL AS effective_start_date,
        s.updated_at AS effective_end_date,
        FALSE AS is_current_flag,
        'UPDATE' AS dml_operation
    FROM sales_dw.staging.stg_customer_stream s
    JOIN sales_dw.marts.dim_customer c
      ON s.customer_id = c.customer_id 
     AND c.is_current_flag = TRUE
    WHERE s.city <> c.city OR s.postal_code <> c.postal_code
) source
ON target.customer_id = source.customer_id 
AND target.is_current_flag = TRUE 
AND source.dml_operation = 'UPDATE'
WHEN MATCHED THEN 
    -- Phase 1: Expire old active record
    UPDATE SET 
        target.effective_end_date = source.effective_end_date,
        target.is_current_flag = FALSE
WHEN NOT MATCHED AND source.dml_operation = 'INSERT' THEN 
    -- Phase 2: Insert new active record version
    INSERT (
        customer_id, customer_name, city, postal_code, 
        effective_start_date, effective_end_date, is_current_flag
    )
    VALUES (
        source.customer_id, source.customer_name, source.city, source.postal_code, 
        source.effective_start_date, source.effective_end_date, source.is_current_flag
    );

Modern Alternative: Declarative SCD with Dynamic Tables

With Snowflake Dynamic Tables, architects can express SCD tracking declaratively using window functions (such as LEAD() or QUALIFY) driven by an automated lag target (TARGET_LAG = '10 minutes'), completely offloading pipeline orchestration and stream offset tracking to Snowflake's serverless engine.