Building a Data Warehouse Schema
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.
-- 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?
You May Also Like
Snowflake Best Practices
Practical guidance for sizing warehouses, controlling cost, tuning performance, and governing access in a production Snowflake account.
Snowflake Quick Reference
A condensed cheat sheet of core Snowflake objects, editions, and everyday SQL commands for fast lookup during daily work.
Snowflake vs Redshift vs BigQuery
A practical architecture, pricing, and ecosystem comparison of the three leading cloud data warehouses to guide platform selection.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics