Snowflake Data Models: Star Schema, Data Vault and Declared Keys

1.What a data model gets from declared keys

A dimensional star schema and a Data Vault both describe relationships between tables with primary and foreign keys. On Snowflake standard tables, those keys describe the model but do not police it, which is different from many other databases. The documentation says constraints on standard tables exist "primarily for data modeling purposes and compatibility with other databases", and also to support client tools that read them.

The metadata is still useful. Declared keys help a project team understand the schema design and see how the tables relate. Most BI and visualization tools also import foreign key definitions and build join conditions from them. That means each developer doesn't have to guess the joins for themselves, so joins are configured consistently.

CREATE OR REPLACE TABLE salespeople (
  sp_id INT NOT NULL UNIQUE,
  name VARCHAR DEFAULT NULL,
  region VARCHAR,
  constraint pk_sp_id PRIMARY KEY (sp_id)
);
CREATE OR REPLACE TABLE salesorders (
  order_id INT NOT NULL UNIQUE,
  quantity INT DEFAULT NULL,
  description VARCHAR,
  sp_id INT NOT NULL UNIQUE,
  constraint pk_order_id PRIMARY KEY (order_id),
  constraint fk_sp_id FOREIGN KEY (sp_id) REFERENCES salespeople(sp_id)
);

Nothing in this DDL stops a row in salesorders from pointing to an sp_id that doesn't exist in salespeople. On standard tables, primary-key/foreign-key relationships are informational. Of the declared constraints, only NOT NULL is enforced here. So a model built on standard tables relies on its load pipeline, not the database engine, for referential integrity. That trade-off runs through both models on this page: you get flexible, fast loading, and you take on responsibility for data quality.

Sources12

2.Star schema: a fact table surrounded by dimensions

A star schema puts a central fact table in the middle and joins it to dimension tables. Snowflake's own semantic-view modeling guidance recommends it as the place to start. The documentation's join examples use a small star: dim_products has one row per product, and fact_sales has one row per sale.

The benefit is simple join paths. Every query joins a fact to a dimension on the dimension's key, which is exactly the join a BI tool builds from declared foreign keys. Because the shape is so regular, the optimizer can also remove a dimension join that a query doesn't need. It only does this when you mark the keys as trustworthy with the RELY constraint property.

The limitation follows from the key concept. The star only gives correct answers if product_id is unique in dim_products and every fact_sales.product_id exists there. On standard tables Snowflake doesn't check either of those rules, so you are responsible for enforcing them. Your pipeline has to guarantee the star's integrity before you tell the optimizer to rely on it.

Sources34

3.Data Vault: hubs, links and satellites

Data Vault splits a model into three kinds of tables. Hubs hold business keys. In Snowflake's Data Vault example, the source system's untreated key stays with the descriptive data, and the treated business key goes to the hub table. Links hold relationships. They are designed for many-to-many (associative) cardinality, but the same link table can also hold mandatory 1:1 or 1:M relationships and optional ones. Satellites hold descriptive content, and a satellite is always the child of either a hub or a link. If the content is transactional, it usually goes in a link-satellite.

On Snowflake, a satellite can carry semi-structured data directly. Snowflake's guidance adds a Data Vault metadata column, dv_object, that stores the original document as a VARIANT. The document can be JSON, XML, an array or an object, and you can query it in place. This lets the vault grow without refactoring each time the source changes shape.

The drawback is performance: semi-structured content is slower to query, and nested content may need flattening before you can reach an attribute. To deal with this, profile the data and copy the critical data elements into strongly typed columns in the same satellite. In the example, those are the business event timestamp (used as the applied date), the business key and the source system's event ID. Typed columns can also improve pruning. Snowflake's table-design guidance also says to store dates and timestamps in DATE or TIMESTAMP columns rather than VARCHAR. Don't flatten nested content at staging. If the time from business event to analytical value is critical, let the dashboard flatten the content at query time.

Sources52

4.Hash keys or natural keys

In Snowflake's Data Vault example, the staging view builds the hub hash key with sha1_binary. It hashes the tenant ID, the business key collision code and the treated customer ID together. The alternative is to use natural keys with no hashing. In that case the natural keys must be kept in the satellite so it can join to every related hub. Teams choose this when they find hashing too costly or slow. The trade-off is that queries against the satellite need more columns to join on.

Pick one approach for the whole vault. A Data Vault should use either natural keys or hash keys, not a mix of the two. Mixing them forces users to treat each table differently and makes the vault inconsistent to work with. Collision codes and multi-tenant IDs add to the problem, because they have to appear in every join. If hashing cost is the worry, Streams and Tasks can reduce how much data is processed into the vault, which cuts the hashing work.

Natural business keys often span several columns. Snowflake supports multi-column keys, but they must be declared out-of-line. Like any key on a standard table, they describe the vault's structure and aren't enforced, so your load process has to keep hubs, links and satellites consistent.

Sources56

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Using hash keys for some hubs and natural keys for others is a fine way to balance hashing cost.Why is that wrong?

   Snowflake's Data Vault guidance says to build the vault on either natural keys or hash keys, not both. A mix makes users treat each table differently and makes the vault inconsistent.

   Covered in Hash keys or natural keys