Data Modeling on Snowflake: Kimball, Data Vault, and SCD Type 2 That Actually Performs

Most Snowflake performance and cost problems we are called in to fix are not tuning problems. They are modeling problems wearing a tuning problem's coat: a 900-column "one big table" rebuilt nightly, a Data Vault that nobody can query without six joins to a satellite, or a dimension that lost its history because somebody wrote an UPDATE.

Snowflake changes some modeling rules — no indexes, cheap storage, micro-partitions, no real enforced foreign keys — but it does not abolish modeling. This tutorial covers the decisions that actually matter on Snowflake, with SQL you can run.

1. What Snowflake changes, and what it doesn't

Classic assumption	On Snowflake
Indexes make joins fast	No indexes. Pruning (micro-partitions + clustering) and the join order the optimizer picks decide everything
Storage is expensive, so normalize	Compressed columnar storage is cheap. Wide, denormalized tables are affordable — compute is the bill
Constraints are enforced	Only NOT NULL is enforced. PK/UK/FK are metadata — but they are useful metadata (see §5)
Updates are expensive, avoid them	MERGE rewrites whole micro-partitions. Large scattered updates are still the most expensive thing you can do
Aggregate tables everywhere	Often replaced by Dynamic Tables, materialized views, or just a well-clustered fact table

The durable rules that survive: model for the questions users ask, keep grain explicit, and never lose history you might be asked to reproduce.

2. Default to a star schema

For the presentation layer — the thing BI tools, Cortex Analyst semantic views, and analysts touch — dimensional modeling is still the best default on Snowflake. It gives you few joins, readable names, and a natural home for semantic definitions.

-- Conformed dimension
CREATE OR REPLACE TABLE mart.dim_customer (
  customer_key    NUMBER      NOT NULL,   -- surrogate
  customer_id     STRING      NOT NULL,   -- natural / business key
  customer_name   STRING,
  segment         STRING,
  country         STRING,
  valid_from      TIMESTAMP_NTZ NOT NULL,
  valid_to        TIMESTAMP_NTZ,
  is_current      BOOLEAN     NOT NULL,
  CONSTRAINT pk_dim_customer PRIMARY KEY (customer_key) RELY
);

-- Fact at order-line grain
CREATE OR REPLACE TABLE mart.fct_order_line (
  order_line_id   STRING      NOT NULL,
  order_date      DATE        NOT NULL,
  customer_key    NUMBER      NOT NULL,
  product_key     NUMBER      NOT NULL,
  quantity        NUMBER(12,2),
  net_amount      NUMBER(18,2),
  CONSTRAINT pk_fct_order_line PRIMARY KEY (order_line_id) RELY,
  CONSTRAINT fk_fol_cust FOREIGN KEY (customer_key) REFERENCES mart.dim_customer (customer_key) RELY
)
CLUSTER BY (order_date);

Three Snowflake-specific notes:

• Cluster facts on the column you filter by, almost always a date. Snowflake already prunes on natural load order; an explicit CLUSTER BY (order_date) is worth it when data arrives out of order or the table is re-written by MERGE.
• Do not cluster small dimensions. Under a few hundred megabytes, clustering costs more in maintenance credits than it returns.
• Snowflake-managed clustering costs credits. Check AUTOMATIC_CLUSTERING_HISTORY after you add a key; if the table is rewritten wholesale each night, drop the key and rely on insert order instead.

3. Surrogate keys without sequences

Sequences work in Snowflake but they are a coordination point, they are not reproducible across environments, and they break when you reload a table. Two better options:

Hash keys (deterministic, environment-independent, ideal for anything you may need to rebuild):

-- Stable across dev/prod and across full reloads
SELECT
  SHA1_BINARY(CONCAT_WS('||', 'CUSTOMER', UPPER(TRIM(customer_id)))) AS customer_hk,
  ...
FROM staging.customers;

Normalize before hashing — trim, upper-case, and use a delimiter that cannot appear in the data. CONCAT without _WS is the classic bug: 'AB' || 'C' and 'A' || 'BC' collide.

Identity columns when you want compact numeric keys and a single writer:

ALTER TABLE mart.dim_product ADD COLUMN product_key NUMBER IDENTITY(1,1);

Rule of thumb: hash keys in the integration layer (they are reproducible), numeric surrogates in the mart (they are small and join fast), and never expose either to end users — expose the business key.

4. SCD Type 2 two ways

With MERGE (full control)

The pattern that avoids the common "close the row and open the new one in one statement" impossibility: use a two-step, or use a single MERGE with a union trick. The clear version is two statements inside a transaction.

BEGIN;

-- Rows whose tracked attributes changed
CREATE OR REPLACE TEMPORARY TABLE _chg AS
SELECT s.*
FROM staging.customers s
JOIN mart.dim_customer d
  ON d.customer_id = s.customer_id
 AND d.is_current
WHERE HASH(s.customer_name, s.segment, s.country)
   <> HASH(d.customer_name, d.segment, d.country);

-- 1. Close the outgoing versions
UPDATE mart.dim_customer d
   SET valid_to = CURRENT_TIMESTAMP(), is_current = FALSE
  FROM _chg c
 WHERE d.customer_id = c.customer_id
   AND d.is_current;

-- 2. Insert new versions plus brand-new customers
INSERT INTO mart.dim_customer
  (customer_key, customer_id, customer_name, segment, country, valid_from, valid_to, is_current)
SELECT
  HASH(s.customer_id, CURRENT_TIMESTAMP()),
  s.customer_id, s.customer_name, s.segment, s.country,
  CURRENT_TIMESTAMP(), NULL, TRUE
FROM staging.customers s
LEFT JOIN mart.dim_customer d
  ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL          -- new
   OR s.customer_id IN (SELECT customer_id FROM _chg);   -- changed

COMMIT;

Use a change hash over exactly the attributes you want to version. Comparing every column means a new dimension row every time an upstream system touches a comment field.

With Dynamic Tables (less code)

If your source keeps a change feed — a stream, a CDC landing table, or an append-only staging table with a load timestamp — a Dynamic Table can derive the history declaratively and refresh incrementally:

CREATE OR REPLACE DYNAMIC TABLE mart.dim_customer_scd2
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT
  customer_id,
  customer_name, segment, country,
  loaded_at AS valid_from,
  LEAD(loaded_at) OVER (PARTITION BY customer_id ORDER BY loaded_at) AS valid_to,
  LEAD(loaded_at) OVER (PARTITION BY customer_id ORDER BY loaded_at) IS NULL AS is_current
FROM (
  SELECT *, HASH(customer_name, segment, country) AS attr_hash,
         LAG(HASH(customer_name, segment, country))
           OVER (PARTITION BY customer_id ORDER BY loaded_at) AS prev_hash
  FROM raw.customers_cdc
)
WHERE prev_hash IS NULL OR attr_hash <> prev_hash;

Trade-off: window functions over the full history often force a full refresh rather than an incremental one. Check SHOW DYNAMIC TABLES / DYNAMIC_TABLE_REFRESH_HISTORY for refresh_action. If it says FULL and the table is large, go back to MERGE.

5. RELY constraints are not decoration

Snowflake does not enforce PK/UK/FK, but with RELY the optimizer will trust them and eliminate joins it can prove are unnecessary — the classic win being a BI tool that joins five dimensions when the query only selects fact measures.

ALTER SESSION SET JOIN_ELIMINATION_USE_RELY_CONSTRAINTS = TRUE;  -- verify current default for your account

The catch: if the constraint is a lie, you get wrong results, not an error. So only mark RELY on keys you actively test. Test them cheaply with a Data Metric Function:

ALTER TABLE mart.dim_customer
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (customer_key);

ALTER TABLE mart.dim_customer SET DATA_METRIC_SCHEDULE = 'USING CRON 0 6 * * * UTC';

6. When Data Vault earns its keep

Data Vault 2.0 (hubs, links, satellites) is a real answer to a real problem: many source systems, changing schemas, auditability requirements, and multiple teams loading in parallel. Hash keys make hub and link loads insert-only and order-independent, which suits Snowflake very well.

It earns its keep when you have several of:

• More than a handful of source systems that describe the same entities differently
• Regulatory need to reproduce any report as of any date, including late-arriving corrections
• Multiple teams loading concurrently, with no shared release train
• Sources whose schemas change often enough that a remodel per change is intolerable

It is the wrong choice when you have two or three stable sources and a small team. The cost is real: 3–5x the object count, and every user-facing query needs a presentation layer on top. If you build Vault, budget for that layer from day one — usually views or Dynamic Tables producing a star schema.

-- Hub: business keys only, insert-only
CREATE OR REPLACE TABLE rv.hub_customer (
  customer_hk   BINARY(20) NOT NULL,
  customer_id   STRING     NOT NULL,
  load_ts       TIMESTAMP_NTZ NOT NULL,
  record_source STRING     NOT NULL,
  CONSTRAINT pk_hub_customer PRIMARY KEY (customer_hk) RELY
);

-- Satellite: descriptive attributes, versioned by hashdiff
CREATE OR REPLACE TABLE rv.sat_customer_crm (
  customer_hk   BINARY(20) NOT NULL,
  load_ts       TIMESTAMP_NTZ NOT NULL,
  hashdiff      BINARY(20) NOT NULL,
  customer_name STRING, segment STRING, country STRING,
  record_source STRING NOT NULL
);

A pragmatic middle path we deploy often: raw layer as-is, a thin insert-only integration layer with hash keys and hashdiffs (Vault-ish, without the full ceremony), and dimensional marts on top. You get auditability and reloadability without 400 objects.

7. One Big Table: when denormalizing is right

Snowflake's columnar storage means a 300-column wide table costs little to store and prunes columns you do not select. OBT is genuinely good for:

• A single event stream consumed by one analytic use case
• Feeding ML feature pipelines or a Streamlit app
• Datasets where joins would always be on the same keys anyway

It is bad when multiple teams need different grains, when attributes need history, or when a single upstream change forces a full rebuild of a multi-terabyte table. "We will just make one big table" typically becomes an expensive nightly full refresh within a year.

8. A modeling checklist for a Snowflake account

1. Grain of every fact table is written down in the table comment.
2. Every dimension states its SCD type; anything Type 2 has a tested change hash.
3. Keys: hash in integration, numeric surrogate in mart, business key exposed to users.
4. CLUSTER BY only on large, filtered tables — and verified against AUTOMATIC_CLUSTERING_HISTORY.
5. PK/UK/FK declared, RELY only where a DMF checks it.
6. Presentation layer stable enough to build semantic views on; marts are the contract, raw is not.
7. Rebuild path documented: can you reproduce every mart from raw? If not, you have history living only in a mutable table.

Where teams get stuck

The two failure modes we see most: a Data Vault built without a presentation layer (correct, auditable, unusable) and a star schema whose dimensions are loaded with UPDATE (fast, tidy, historically worthless). Both are cheap to avoid at design time and expensive to fix after two years of loads.

PowderInsights architects Snowflake data models — dimensional, Data Vault, and the pragmatic hybrids in between — and rebuilds the ones that are costing you credits. Tell us about your model and we will review the grain, keys, and history strategy.