Snowflake Data Modeling

A data model is the blueprint of a database. Good blueprints make queries simple, fast, and trustworthy. Poor blueprints produce confusing tables, duplicate numbers, and endless debates about which figure is correct. Snowflake handles many modeling styles well, and the star schema remains the most popular for reporting.

The Shop Receipt Comparison

A store receipt lists what was sold, when, and for how much. Descriptive details such as product names, customer addresses, and store locations live in separate catalogs. The receipt stores short codes that point to those catalogs. A star schema follows the same design. A central fact table records events, and surrounding dimension tables describe them.

STAR SCHEMA
===========

            +----------------+
            |  DIM_DATE      |
            |  date_key      |
            |  year, month   |
            +-------+--------+
                    |
 +----------------+ |  +-------------------+
 |  DIM_PRODUCT   | |  |  DIM_CUSTOMER     |
 |  product_key   | |  |  customer_key     |
 |  name, brand   | |  |  name, city       |
 +-------+--------+ |  +---------+---------+
         |          |            |
         |   +------+------------+--+
         +---+      FACT_SALES      +---+
             |  date_key             |
             |  product_key          |
             |  customer_key         |
             |  store_key            |
             |  quantity, amount     |
             +-----------+-----------+
                         |
                 +-------+--------+
                 |  DIM_STORE     |
                 |  store_key     |
                 |  city, region  |
                 +----------------+

Facts and Dimensions

TABLE TYPE   HOLDS                         EXAMPLES                  SIZE
-----------  ----------------------------  ------------------------  --------
Fact         Measurable events, numbers    Sales, clicks, payments   Very large
Dimension    Descriptions of those events  Product, customer, date   Small

Fact columns:       keys to dimensions + numeric measures
Dimension columns:  one key + many descriptive attributes

Decide the Grain First

The grain states what one row in the fact table means. Examples include one row per order line, one row per daily account balance, or one row per website click. Every column must fit that grain. Mixing grains in one table causes double counting. Write the grain sentence before building anything.

Building the Tables

CREATE TABLE dim_product (
  product_key  NUMBER AUTOINCREMENT PRIMARY KEY,
  product_code STRING NOT NULL,
  product_name STRING,
  brand        STRING,
  category     STRING
);

CREATE TABLE dim_customer (
  customer_key NUMBER AUTOINCREMENT PRIMARY KEY,
  customer_id  STRING NOT NULL,
  full_name    STRING,
  city         STRING
);

CREATE TABLE fact_sales (
  date_key     NUMBER NOT NULL,
  product_key  NUMBER NOT NULL REFERENCES dim_product(product_key),
  customer_key NUMBER NOT NULL REFERENCES dim_customer(customer_key),
  quantity     NUMBER,
  amount       NUMBER(12,2)
);

Surrogate keys, such as product_key, are system-generated numbers that stay stable even when business codes change.

Constraints in Snowflake

Snowflake records PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints for documentation and for tools that read them. Snowflake enforces only NOT NULL. Loading jobs must guarantee uniqueness and valid references through their own checks.

Querying a Star Schema

SELECT d.category,
       c.city,
       SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_product  d ON f.product_key  = d.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.category, c.city
ORDER BY revenue DESC;

Every report follows the same pattern: join the fact table to dimensions, group by descriptive columns, and sum the measures.

Handling Changing Dimensions

Customers move and products change brands. Two common patterns manage that history.

• Type 1: overwrite the old value. History disappears, and the table stays simple.
• Type 2: add a new row with start and end dates. History stays intact, and reports can show values as they were at any date.

TYPE 2 EXAMPLE
==============
customer_key  customer_id  city     valid_from   valid_to     is_current
------------  -----------  -------  -----------  -----------  ----------
101           C-77         Delhi    2024-01-01   2026-03-31   FALSE
102           C-77         Mumbai   2026-04-01   9999-12-31   TRUE

Layers in a Modern Warehouse

RAW LAYER       Exact copy of source data, never edited
   |
   v
CLEAN LAYER     Fixed types, removed duplicates, standard names
   |
   v
MODEL LAYER     Star schema facts and dimensions
   |
   v
SERVING LAYER   Views and aggregates for dashboards

Teams often store each layer in its own schema or database. Raw data stays untouched, so any later layer can rebuild from it.

Other Modeling Styles

• Snowflake schema: dimensions split into smaller linked tables. Storage shrinks and joins multiply.
• Data Vault: hubs, links, and satellites for large enterprises with many source systems.
• Wide flat tables: one denormalized table per use case. Snowflake's columnar storage handles wide tables comfortably.

A Practical Scenario

A subscription company stores billing events, customer records, and plan details in separate raw tables. Analysts write long queries that disagree on monthly revenue. A modeler designs a fact table with one row per invoice line and dimension tables for customer, plan, and date. Every report now joins the same fact table, so every team reads the same revenue figure. New questions need only a new grouping column, not a new data pipeline.

Key Points

• A star schema places a fact table at the center of dimension tables.
• Fact tables hold numbers; dimension tables hold descriptions.
• State the grain before designing columns.
• Surrogate keys keep joins stable.
• Snowflake enforces only NOT NULL, so loading logic must protect uniqueness.
• Type 2 dimensions preserve history; layered schemas keep raw data safe.