Testing A Datavault4dbt Project

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 +      # 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) |

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: ScalefreeCOM
• Source: ScalefreeCOM/datavault4dbt-agent-skills
• License: Apache-2.0
• Homepage: https://datavault4dbt.com

Install and usage instructions live in the source repository linked above.