Snowflake Architecture · Topic 1 of 18

Data Models, Data Vault, Star Schema and Constraints

Compare star schema, snowflake schema, 3NF and Data Vault on Snowflake and learn how ENABLE, VALIDATE and RELY constraint properties really behave.

  • 5 min
In a nutshell
  • Star schema: simple, fast for BI. Snowflake (normalized) schema: less redundancy, more joins. Data Vault: hubs, links and satellites for auditable, source-agnostic history.
  • Only NOT NULL and CHECK are enforced on standard tables; primary, unique and foreign keys are informational.
  • RELY lets the optimizer trust a constraint to eliminate joins; default is NORELY. Set it on both keys of a pair.

What it is

A data model is the way tables are shaped and related: how much is normalized, where history lives, and which tables answer which questions. The exam does not ask you to draw an entity diagram. It describes a business need, such as "auditable history from many source systems" or "fast dashboards for analysts", and asks which model fits and what its limits are.

Snowflake is neutral about modeling. It runs a normalized third normal form (3NF) model, a star schema, a snowflake schema or a Data Vault equally well, because storage is columnar and compute scales separately. The architect chooses the model for the workload, not for the engine.

Source systemsmany, changingloadRaw vaulthubs, links, satellitesbusiness rulesBusiness vaultderivedpublishStar schemafacts and dimensionsserveBI tools

How it works

The common models

ModelShapeStrengthLimitation
3NF (normalized)Many narrow tables, little duplicationLow redundancy, good for OLTP style integrityMany joins for analytics; harder for business users
Star schemaOne central fact table, denormalized dimensionsSimple, fast joins, easy for BI toolsDimension changes need history handling; duplication in dimensions
Snowflake schemaStar with normalized (split) dimensionsLess dimension redundancyMore joins, more complex queries
Data VaultHubs, links and satellitesAuditable history, easy to add sources, parallel loadsMany tables and joins; not friendly to query directly
Wide denormalized tableOne flat tableFewest joinsRedundancy, expensive updates
Watch out

A Snowflake schema in the modeling sense (normalized dimensions) is not the same thing as the Snowflake product. Do not choose it because of the name.

Star schema

A star schema has one fact table holding measures (amount, quantity) and foreign keys, surrounded by dimension tables holding descriptive attributes (customer, product, date). Analysts join the fact to a few dimensions, filter on dimension columns and aggregate the measures.

It suits reporting and BI. Snowflake handles star joins well, and a clustering key on a large fact table, usually by a date column, helps pruning. Slowly changing dimensions (type 2 rows with start and end dates) keep history in a dimension.

Data Vault

Data Vault separates three kinds of data.

  • Hubs hold unique business keys (customer number) plus a load date and record source.
  • Links hold relationships between hubs (customer buys product).
  • Satellites hold descriptive attributes and their history, attached to a hub or link, with a new row for each change.

Because every source adds satellites without changing hubs, new systems are easy to add and the full history is auditable. The cost is a large number of tables and joins, so Data Vault is normally the integration layer and a star schema or views sit on top for consumption.

Hub customerbusiness keylinked byLink customer productrelationshiptoHub productbusiness keyHub customerdescribed bySatellitehistory of attributes

Key and column constraints

Snowflake supports PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK syntax, plus three Snowflake properties: ENABLE or DISABLE, VALIDATE or NOVALIDATE, and RELY or NORELY.

On standard tables the enforcement is limited:

ConstraintEnforced on standard tables?
NOT NULLYes
CHECKYes
PRIMARY KEY, UNIQUE, FOREIGN KEYNo, informational (metadata only)

Informational constraints still matter. BI and modeling tools read them to discover relationships, and they document the model. The properties work like this:

PropertyMeaningDefault
ENABLE / DISABLEIncluded for compatibility with other databasesDISABLE for primary, unique and foreign keys
VALIDATE / NOVALIDATEWhether existing data is checked when the constraint is createdNOVALIDATE for primary and foreign keys
RELY / NORELYWhether the optimizer trusts a NOVALIDATE constraint during query rewriteNORELY

For primary, unique and foreign keys, asking for the non-default ENABLE or VALIDATE at creation means the constraint is not created. RELY is the exception: it does not stop creation.

RELY is the one that changes performance. If a key is declared with RELY, the optimizer may remove a join that adds no rows or columns the query needs (join elimination). For related primary and foreign keys, set RELY on both.

ALTER TABLE dim_customer ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id) RELY;
ALTER TABLE fact_sales ADD CONSTRAINT fk_sales_customer
  FOREIGN KEY (customer_id) REFERENCES dim_customer (customer_id) RELY;
Watch out

With RELY on a standard table, you are the enforcer. If duplicate keys exist, queries can return wrong results and DML or CTAS statements can insert incorrect data.

Hybrid tables are different: they enforce primary key, unique and foreign key constraints, and you cannot mark those as not enforced. They suit operational, row-level workloads, not warehouse style scans.

Constraintdeclared with RELYread byOptimizerquery rewritecan removeUnneeded joinfaster query

Common mistakes

Believing a primary key prevents duplicates. On a standard table it does not. Deduplicate in the load logic, for example with MERGE or QUALIFY ROW_NUMBER().

Putting RELY on data that is not clean. The optimizer trusts you. A violated key leads to wrong results, not an error.

Querying a raw Data Vault directly. The many joins are expensive for analysts. Build information marts, often as a star schema or views, on top.

Choosing a model for the engine. Snowflake does not require a star schema. Pick by audit needs, source volatility and consumer skills.

What to remember for the exam

  • Star schema: simple, fast for BI. Snowflake (normalized) schema: less redundancy, more joins. Data Vault: hubs, links and satellites for auditable, source-agnostic history.
  • Only NOT NULL and CHECK are enforced on standard tables; primary, unique and foreign keys are informational.
  • RELY lets the optimizer trust a constraint to eliminate joins; default is NORELY. Set it on both keys of a pair.
  • Defaults: DISABLE, NOVALIDATE (for keys) and NORELY. A non-default ENABLE or VALIDATE on a key means no constraint is created.
  • Hybrid tables enforce keys; standard tables do not.

Check yourself

  • Which Data Vault object stores the history of descriptive attributes?

    The satellite. Hubs hold business keys and links hold relationships.

  • Why declare a foreign key with RELY on a standard table?

    So the optimizer can eliminate unnecessary joins. The architect must guarantee the data really satisfies the key.

  • A primary key is added to a standard table and duplicate rows are inserted anyway. Why?

    Primary keys are informational on standard tables and not enforced. Only NOT NULL and CHECK are enforced.

Topic 1 of 18 in Snowflake Architecture

Test yourself on Snowflake Architecture

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.