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
- 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 NULLandCHECKare enforced on standard tables; primary, unique and foreign keys are informational. RELYlets the optimizer trust a constraint to eliminate joins; default isNORELY. 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.
How it works
The common models
| Model | Shape | Strength | Limitation |
|---|---|---|---|
| 3NF (normalized) | Many narrow tables, little duplication | Low redundancy, good for OLTP style integrity | Many joins for analytics; harder for business users |
| Star schema | One central fact table, denormalized dimensions | Simple, fast joins, easy for BI tools | Dimension changes need history handling; duplication in dimensions |
| Snowflake schema | Star with normalized (split) dimensions | Less dimension redundancy | More joins, more complex queries |
| Data Vault | Hubs, links and satellites | Auditable history, easy to add sources, parallel loads | Many tables and joins; not friendly to query directly |
| Wide denormalized table | One flat table | Fewest joins | Redundancy, expensive updates |
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.
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:
| Constraint | Enforced on standard tables? |
|---|---|
NOT NULL | Yes |
CHECK | Yes |
PRIMARY KEY, UNIQUE, FOREIGN KEY | No, 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:
| Property | Meaning | Default |
|---|---|---|
ENABLE / DISABLE | Included for compatibility with other databases | DISABLE for primary, unique and foreign keys |
VALIDATE / NOVALIDATE | Whether existing data is checked when the constraint is created | NOVALIDATE for primary and foreign keys |
RELY / NORELY | Whether the optimizer trusts a NOVALIDATE constraint during query rewrite | NORELY |
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;
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.
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 NULLandCHECKare enforced on standard tables; primary, unique and foreign keys are informational. RELYlets the optimizer trust a constraint to eliminate joins; default isNORELY. Set it on both keys of a pair.- Defaults:
DISABLE,NOVALIDATE(for keys) andNORELY. A non-defaultENABLEorVALIDATEon 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.