ScalefreeCOM Testing a datavault4dbt project
Turn the Primary-Key and Foreign-Key assumptions of Data Vault 2 into dbt tests. In DV2 these are soft constraints — implemented as dbt tests that warn (and let you investigate) rather than hard database constraints that would silently drop "bad and ugly" raw data you still want to capture.
Default to soft constraints (dbt tests). Only use hard database constraints if you accept the risk of losing non-conforming raw data and have an Error Mart to catch it.
Where these tests live
Add data_tests in the model's YAML file (colocated with the .sql). Generic tests unique and
not_null are built in; relationships covers FK integrity. For multi-column uniqueness (satellites)
use dbt_utils.unique_combination_of_columns (add the dbt_utils package) or an equivalent.
The technical test matrix
| Entity | Column(s) | Test |
|---|---|---|
| Hub | hashkey | not_null, unique |
| Link | link hashkey | not_null, unique |
| Link | each foreign hashkey | relationships to its connected hub's hashkey |
| Satellite v0 | hashkey + load date | unique combination of columns |
| Satellite v0 | hashkey | relationships to parent hub/link |
| Non-historized link | link hashkey | not_null, unique |
| Non-historized link | each foreign hashkey | relationships to connected hubs |
| Non-historized satellite v0 | hashkey | not_null, unique, relationships to parent NH-link |
| Multi-active satellite v0 | hashkey + load date + multi-active key(s) | unique combination of columns |
| Multi-active satellite v0 | hashkey | relationships to parent hub/link |
| Record-tracking satellite | hashkey + load date | unique combination of columns |
| Record-tracking satellite | hashkey | relationships to parent hub/link |
| Reference hub | reference key(s) | unique, not_null |
| Reference satellite v0 | reference key(s) + load date | unique combination of columns |
(Authoritative source: dbt_packages/datavault4dbt/docs/26_general-usage-notes/40_testing-a-data-vault.)
Examples
Hub — hashkey is the PK:
models:
- name: customer_h
columns:
- name: hk_customer_h
data_tests: [unique, not_null]
- name: CUSTOMER_ID
data_tests: [not_null]
Link — hashkey unique + each FK references its hub:
models:
- name: contract_customer_l
columns:
- name: hk_contract_customer_l
data_tests: [unique, not_null]
- name: hk_contract_h
data_tests:
- not_null
- relationships: {to: ref('contract_h'), field: hk_contract_h}
- name: hk_customer_h
data_tests:
- not_null
- relationships: {to: ref('customer_h'), field: hk_customer_h}
Satellite v0 — hashkey + load date is the grain; hashkey references the parent:
models:
- name: customer_s_v0
data_tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns: [hk_customer_h, ldts]
columns:
- name: hk_customer_h
data_tests:
- not_null
- relationships: {to: ref('customer_h'), field: hk_customer_h}
A satellite hashkey is not unique on its own (it has history). Test uniqueness on
hashkey + ldts(and the multi-active key[s] for MA-sats), never the hashkey alone.
Running
dbt build --select <model>+ # build a model and run its tests, downstream too
dbt test --select tag:raw_vault # run only the raw-vault tests
Common mistakes
| Mistake | Fix |
|---|---|
unique on a satellite hashkey |
Satellites are historized — test hashkey + ldts uniqueness instead |
Skipping link→hub relationships |
Add a relationship test per foreign hashkey; this catches orphaned links |
| Hard DB constraints on the RDV | Use soft constraints (tests); hard constraints can drop raw data |
| Forgetting the multi-active key in MA-sat uniqueness | Grain is hashkey + ldts + multi_active_key(s) |
Using unique_combination_of_columns without dbt_utils |
Install dbt_utils (or use an equivalent multi-column uniqueness test) |