Tested Data Transformations with dbt

This is sample teaching content. Publication dates and transaction records are illustrative.

A transformation can run successfully and still contain duplicate transactions. dbt models describe transformations; data tests ask whether their output meets a contract. This example assumes an existing configured dbt project, adapter, and stg_transactions model. It uses current dbt data_tests syntax. Adapter setup is outside this exercise.

Describe the model dependency

Create models/transactions.sql. The input already has typed numeric amounts and one row per current transaction:

select
    transaction_id,
    transaction_date,
    amount
from {{ ref('stg_transactions') }}
where transaction_id is not null
  and amount is not null

ref resolves the upstream relation and records a dependency. Use it instead of a hard-coded database/schema name so environments can select their own relation names. Filtering missing fields is only acceptable if rejected rows are counted and investigated upstream.

Test the intended key

Create models/transactions.yml:

version: 2
models:
  - name: transactions
    columns:
      - name: transaction_id
        data_tests:
          - not_null
          - unique

Run the selected model and its tests through your project's dbt build command:

dbt build --select +transactions

The leading plus includes upstream dependencies. A unique test reports duplicate key values; it does not remove duplicates. The not_null test checks this output, not whether the source rejected a large fraction of rows. Add upstream rejection counts and domain-specific assertions when those are part of your contract.

Review incremental changes separately

Changing materialization to incremental introduces another contract. A unique_key configuration tells supported adapter strategies how to match destination rows. It is not a uniqueness constraint or source deduplicator. Validate source keys before merging.

A filter that only loads timestamps greater than the destination maximum can miss late corrections. Decide whether to reprocess a lookback window, read a source change log, or rebuild affected partitions. Test a correction arriving after its original event, plus a replay of the same batch. Expected row counts and values should remain stable.

Passing uniqueness says nothing about exchange rates, completeness, or whether a business definition changed. Keep those assumptions next to the model and compare results with a small hand-calculated fixture before treating a successful build as a release.