What you will be able to do
- Explain the roles of hub, link and satellite tables and the cardinalities a link can hold
- Generate hash keys for a Data Vault load and explain why not to mix hash-key and natural-key designs
- Design satellites for semi-structured data: keep VARIANT, pre-parse critical elements, and decide when to flatten
- Serve a Data Vault to BI users through information mart views and a business vault
1.Data Vault table types and why you'd choose them
Snowflake doesn't require one modeling methodology. You choose the model that fits what you are building. Data Vault is one option. Snowflake's Data Vault guidance gives the reason to load data into a vault: the auditable history, the business context, and the batch-oriented analysis that the data can then serve.
A vault uses three table types:
- Hub: holds the business key for a business object, such as a customer. The *treated* business key (trimmed, upper-cased, defaulted) goes to the hub. - Link: records relationships between hubs. Links are designed for many-to-many (associative) cardinality. The same link table can also hold mandatory 1:1 or 1:M and optional 0..1:1..M relationships. - Satellite: holds the descriptive, changing attributes. A satellite is always the child of either a hub or a link. Transactional content usually goes into a link-satellite.
These relationships matter for the same reason they do in any schema: declared primary and foreign keys let your team understand the design and see how the tables relate.
| Table type | Holds |
|---|---|
| Hub | Treated business key of a business object |
| Link | Relationships between hubs; M:M, 1:1, 1:M and optional cardinalities |
| Satellite | Descriptive attributes and their history; child of a hub or a link |
Checkpoint 1 of 7· Check yourself
Clickstream events describe transactions between a customer and an invoice. Which table should be the parent of the satellite that stores these events' descriptive content?
A satellite's parent is a hub or a link. Transactional content usually leads to a link-satellite.
“If the content is transactional, we are likely going to deploy a link-satellite table.”Source: www.snowflake.com
2.Generating hash keys, or using natural keys
Hubs, links and satellites join on keys, and the vault's staging step generates them. In Snowflake's example, a staging view over landed JSON does four things. It treats the business key (contid, trimmed, upper-cased, and defaulted to '-1'). It concatenates the key with a tenant ID and a business-key collision code. It hashes the result with sha1_binary to produce the hub hash key. It stamps load metadata: a load date, an applied date taken from the business event timestamp, and a record source.
create or replace view staged.stg_casp_clickstream as
select
value as dv_object
, coalesce(upper(to_char(trim(value:contid::varchar(50)))), '-1') as customer_id
, value:contid::varchar(50) as contid
, 'default' as dv_tenantid
, 'default' as dv_bkeycolcode_hub_customer
, sha1_binary(concat(dv_tenantid, '||', dv_bkeycolcode_hub_customer, '||', coalesce(upper(to_char(trim(customer_id))), '-1'))) as dv_hashkey_hub_customer
, current_timestamp() as dv_loaddate
, value:timestamp::timestamp as dv_applieddate
, 'lake_bucket/landed/casp_clickstream.json' as dv_recsource
, value:eventID::varchar(40) as eventID
, value:eventName::varchar(50) as eventName
from landed.casp_clickstreamYou could skip hashing and build the vault on natural keys. Hashing costs time and compute, so that can be tempting. The cost is that the natural keys must then be carried in every satellite, and queries need more columns to join on. Whichever you choose, apply it everywhere: a vault should be built entirely on hash keys or entirely on natural keys, never a mix. If you choose hash keys, you can reduce the hashing cost with Streams and Tasks, which pass only new data into the vault.
On standard Snowflake tables, the database doesn't enforce primary-key and foreign-key relationships. Your loading pattern, not Snowflake, has to keep hub, link and satellite keys consistent.
Checkpoint 2 of 7· Exam question
An executive dashboard in Snowsight reads the same 12 columns from joins across six normalized tables. Data refreshes once each night, about 500 people open the dashboard each morning, and join cost dominates the query time. What should the analyst do?
Correct answer: D — Build a pre-joined wide table with just those 12 columns, rebuilt after the nightly load, and point the dashboard at it
- A. A Data Vault splits entities into more tables, so it adds joins rather than removing them. It is built for integration and auditability, not for fast single-dashboard reads.
- B. Clustering helps partition pruning on filters and joins but never removes a join from the plan. The six-way join would still run for each viewer.
- C. UNION ALL stacks rows and does not combine columns across tables, so it cannot reproduce the join result. It also needs compatible column lists that these tables do not have.
- D. For a fixed, read-heavy dashboard on data that changes once a day, a flattened table pays the join cost once during the refresh. Every viewer then scans a single table.
Checkpoint 3 of 7· Check yourself
A team wants hash keys for most hubs but natural keys for two high-volume hubs, to save hashing cost. What does the guidance say?
A mixed design forces users to know which pattern each table uses, which makes the vault inconsistent to query. Reduce hashing cost with Streams and Tasks instead.
“A Data Vault should be produced as either natural-key or hash-key based and not a mix of the two.”Source: www.snowflake.com
3.Satellites for semi-structured and streaming data
Source systems that change their schemas often send semi-structured data. A vault can absorb that without refactoring. Add a metadata column, dv_object, that stores the original document as VARIANT, so the source content can change without affecting the satellite table. Semi-structured content is slower to query, though. During staging, persist the critical data elements as strongly typed columns in the same satellite, such as the applied timestamp, the business key and the event ID. These columns speed up queries and can improve pruning. Cast timestamps to a real timestamp type, as the staging view does with dv_applieddate: Snowflake stores DATE and TIMESTAMP more efficiently than VARCHAR.
Streaming events are immutable: they are always new. They can therefore use non-historized satellites or links that simply INSERT. These skip the standard loader's change check and don't need a hashdiff (record-hash) column. If the source is *overloaded*, meaning too many business processes are packed into one feed, the preferred fixes are ranked.
Checkpoint 4 of 7· Put it in order
Put the remedies for an overloaded source in order, from most preferred to least preferred
- 1.Solve it using a business vault
- 2.Solve it at the source
- 3.Solve it in staging, filtering out the essential content
Fixing at the source is best. Staging adds a maintenance point for the analytics team. A business vault adds more tables to maintain and join.
“Solve it using a business vault: This is always the least preferred option.”Source: www.snowflake.com
Checkpoint 5 of 7· Check yourself
In a satellite fed by an immutable event stream, which column can be dropped?
Events are always new, so the loader doesn't compare them against the current record. That makes the hashdiff unnecessary.
“the target satellite table will also not need to have a record-hash (hashdiff) column”Source: www.snowflake.com
4.Serving BI from a Data Vault
A raw vault is built for auditability, not for analysts. Snowflake's guidance describes two ways to put a friendlier consumption layer on top of it. Data loaded into the vault is immediately available to information mart views built on the vault. Alternatively, it can be processed further into a business vault, which can, for example, flatten nested content so business users never see the querying complexity. Because information marts are views, you can grant each role access to just the portion of the data it needs.
Nested arrays can also be queried in place with LATERAL FLATTEN. If latency between a business event and its analytical value is critical, leave flattening to the dashboard at query time rather than doing it in staging.
with flatten_content as (
select s.customer_id
, s.eventid
, s.eventname
, s.dv_applieddate
, flt.value:lineItem::int as lineItem
, flt.value:InvoiceLine::int as InvoiceLine
from datavault.sat_nh_casp_clickstream s
, lateral flatten(input => dv_object:invoiceDetails) flt
)
select customer_id
, dv_applieddate
, lineItem
, sum(InvoiceLine) as InvoiceLine
from flatten_content
group by rollup (customer_id, dv_applieddate, lineItem)
order by customer_id, dv_applieddate, lineItem;Checkpoint 6 of 7· Exam question
In a Data Vault 2.0 model implemented in Snowflake, which table type stores the descriptive attributes of a business entity together with their change history?
Correct answer: C — Satellite table, keyed by the parent hub or link hash key plus a load date, with a new row inserted for each change
- A. Hubs store only the business key, its hash key, load date and record source. Descriptive columns that change over time belong in satellites, so putting them in a hub loses the history.
- B. Links capture relationships between hubs, such as customer to order. Descriptive attributes of the entities themselves are held in satellites hanging off the hubs.
- C. Satellites carry descriptive context and are insert-only, so each change adds a row identified by the parent hash key and load date. This is how Data Vault keeps full history.
- D. Overwriting values in place discards history, which Data Vault is designed to preserve. Reference tables hold code lookups and are not the home of entity attributes.
Checkpoint 7 of 7· Check yourself
Business users find the vault's nested satellite content too complex to query. Which option does the guidance give for hiding that complexity?
A business vault can flatten nested content for end users while the raw satellite keeps the original VARIANT.
“use a business vault to flatten out the content so that you hide this querying complexity from the business end-user.”Source: www.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Using natural keys for some hubs and hash keys for others is a sensible cost optimisation.Why is that wrong?
A vault should use one key strategy throughout. Mixing makes every table a special case. Reduce hashing cost with Streams and Tasks instead.
Covered in Generating hash keys, or using natural keys
2.Semi-structured data for a vault should be fully flattened in staging before the satellite is loaded.Why is that wrong?
Keep the original content as VARIANT and pre-parse only the critical elements into typed columns. Flattening nested content at ingestion delays the load.
Covered in Satellites for semi-structured and streaming data
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Primary and foreign keys enable your project team to understand the schema design and see the relationships between the tables and their columns.”
↩︎ Data Vault table types and why you'd choose them“When they are created on standard tables, referential integrity constraints, as defined by primary-key/foreign-key relationships, are informational; they are not enforced.”
↩︎ Generating hash keys, or using natural keys“Snowflake stores DATE and TIMESTAMP data more efficiently than VARCHAR, resulting in better query performance.”
↩︎ Satellites for semi-structured and streaming data - 2.https://www.snowflake.com/en/blog/handling-semi-structured-dataSecondary source
“link tables are designed to serve many-to-many/associative (M:M) cardinality”
↩︎ Data Vault table types and why you'd choose them“the auditable history, business context, and the subsequent batch-oriented analysis that the data could serve”
↩︎ Data Vault table types and why you'd choose them“queries running on this satellite will essentially need to use more columns to join on.”
↩︎ Generating hash keys, or using natural keys“These could also be columns that can improve pruning performance on Snowflake.”
↩︎ Satellites for semi-structured and streaming data“The data is immediately available to the information mart view based on Data Vault, or it can be further processed into a business vault”
↩︎ Serving BI from a Data Vault“If the business event to analytical value latency is critical, avoid flattening nested content in staging”
↩︎ Serving BI from a Data Vault“We can mitigate the cost of hashing by using Snowflake features such as Streams and Tasks”
↩︎ Exam trap 1“persist those key-pairs as strongly typed structured columns within the same satellite table”
↩︎ Exam trap 2“If the content is transactional, we are likely going to deploy a link-satellite table.”
↩︎ Checkpoint“A Data Vault should be produced as either natural-key or hash-key based and not a mix of the two.”
↩︎ Checkpoint“Notice how we did not flatten the nested content—doing this at ingestion time causes a delay in loading the satellite table.”
↩︎ Prediction“Solve it using a business vault: This is always the least preferred option.”
↩︎ Checkpoint“the target satellite table will also not need to have a record-hash (hashdiff) column”
↩︎ Checkpoint“use a business vault to flatten out the content so that you hide this querying complexity from the business end-user.”
↩︎ Checkpoint - 3.
“Views allow you to grant access to just a portion of the data in a table(s).”
↩︎ Serving BI from a Data Vault