Building a Data Warehouse Schema

Analogy categoryAnalogies

A well-designed Snowflake schema starts with a layered approach: a raw/landing layer that lands source data unmodified for lineage and replay, a staging layer that cleans and conforms types, and a presentation layer organized as a star schema of fact and dimension tables that business users and BI tools query directly. Because Snowflake has no foreign-key enforcement at runtime and cheap storage, the design should optimize for query clarity and pruning rather than normalization purity.

🏏

Cricket analogy: Like a team keeping raw match footage, then edited highlights, then a polished broadcast package, the raw-staging-presentation layering preserves the original while producing a clean product for viewers.

Star Schema: Facts and Dimensions

In the presentation layer, fact tables (e.g., order_line_fact) hold measurable events at a specific grain with foreign keys to dimensions and numeric measures, while dimension tables (e.g., dim_customer, dim_product, dim_date) hold descriptive attributes business users filter and group by. Because Snowflake has abundant cheap storage and columnar compression, mild denormalization inside dimensions (e.g., embedding product category and brand directly in dim_product rather than a separate snowflaked table) usually improves query simplicity and performance without meaningful cost penalty.

🏏

Cricket analogy: Like a scorecard's ball-by-ball log (fact table: runs, wickets, at what grain) referencing a separate players roster (dimension: batting style, team, country) for descriptive lookups.

Slowly Changing Dimensions and Grain

Choosing the fact table's grain correctly up front (one row per order line, not per order) is the single most important decision — getting it wrong forces a costly rebuild later. For attributes that change over time (a customer's address or a product's price tier), Type 2 slowly changing dimensions add effective_date, end_date, and is_current columns so historical facts still join to the dimension values that were true when the event occurred, which Snowflake's Streams and Tasks can automate incrementally.

🏏

Cricket analogy: Like recording a player's role change from opener to middle-order at a specific date rather than overwriting history, Type 2 SCDs preserve what was true when each match actually happened.

Loading and Refreshing the Warehouse

Data typically lands in the raw layer via Snowpipe (continuous, event-driven micro-batch loads from cloud storage) or bulk COPY INTO for scheduled batch loads, then Streams capture row-level inserts/updates/deletes on the raw table so a Task can incrementally merge only changed rows into staging and presentation tables rather than reprocessing everything nightly. Dynamic Tables are Snowflake's newer declarative alternative, letting you define the target transformation as a query and let Snowflake manage the incremental refresh pipeline automatically.

🏏

Cricket analogy: Like a stadium's ball-tracking system updating the scoreboard incrementally after every delivery rather than recalculating the whole match, Streams and Tasks process only what changed.

sql

-- Presentation-layer star schema DDL
CREATE TABLE dim_customer (
  customer_key      NUMBER AUTOINCREMENT PRIMARY KEY,
  customer_id       STRING NOT NULL,
  customer_name     STRING,
  segment           STRING,
  effective_date    DATE,
  end_date          DATE,
  is_current        BOOLEAN DEFAULT TRUE
);

CREATE TABLE fact_order_line (
  order_line_key    NUMBER AUTOINCREMENT PRIMARY KEY,
  customer_key      NUMBER REFERENCES dim_customer(customer_key),
  product_key       NUMBER REFERENCES dim_product(product_key),
  date_key          NUMBER REFERENCES dim_date(date_key),
  quantity          NUMBER,
  net_amount        NUMBER(12,2)
) CLUSTER BY (date_key);

-- Incremental capture and merge with Streams + Tasks
CREATE STREAM raw_orders_stream ON TABLE raw_orders;

CREATE TASK merge_orders_task
  WAREHOUSE = etl_wh
  SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
MERGE INTO fact_order_line f
USING raw_orders_stream s ON f.order_line_key = s.order_line_key
WHEN MATCHED THEN UPDATE SET f.net_amount = s.net_amount
WHEN NOT MATCHED THEN INSERT (order_line_key, customer_key, product_key, date_key, quantity, net_amount)
  VALUES (s.order_line_key, s.customer_key, s.product_key, s.date_key, s.quantity, s.net_amount);

Snowflake does not enforce primary key, foreign key, or unique constraints at write time by default (they exist for documentation and query-optimizer hints only) — bad joins or duplicate keys will not be blocked, so data quality checks must be built into the ETL/ELT pipeline itself.

Layer the warehouse as raw (landing), staging (cleaned/conformed), and presentation (star schema) for lineage and clarity.
Fact tables hold measurable events at a defined grain; dimension tables hold descriptive attributes for filtering and grouping.
Choosing the correct fact grain up front is the highest-leverage design decision in the schema.
Use Type 2 slowly changing dimensions (effective_date/end_date/is_current) to preserve historically accurate joins.
Snowflake's cheap storage favors mild denormalization inside dimensions over strict third-normal-form snowflaking.
Streams capture row-level changes and Tasks merge them incrementally instead of full nightly reprocessing.
Constraints in Snowflake are not enforced by default, so data quality validation belongs in the pipeline, not the schema.

Practice what you learned

1. What is the most important decision when designing a fact table?

2. What problem does a Type 2 slowly changing dimension solve?

3. Does Snowflake enforce primary key and foreign key constraints at write time by default?

4. What is the purpose of a Snowflake Stream in an incremental ELT pipeline?

Was this page helpful?

Snowflake vs Redshift vs BigQuery

Snowflake Interview Questions

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Programming

PowerShell Study Notes

Programming · 30 topics

Topics covered

#Programming#SnowflakeStudyNotes#BuildingADataWarehouseSchema#Building#Data#Warehouse#Schema#SQL#StudyNotes#SkillVeris

Building a Data Warehouse Schema