Testing Data Transformations

Suppose a revenue model joins orders to a product dimension, and someone reloads the dimension so that 0.5% of product keys now appear twice. Every order for those products is duplicated by the join, so monthly revenue of $1.2 billion is reported as roughly $1.206 billion if those products carry a proportional share of sales. The $6 million error sits well inside the range any volume or distribution check would accept. No row is malformed, no column is null, and the schema is unchanged. The transformation is wrong only in a way a test of its logic would catch.

Checks on production data, the subject of expectations-assertions-and-placement, ask whether today's data looks right. Tests of a transformation ask whether the code does what it claims for inputs chosen to expose mistakes, before it runs on real data. The two are complements, and pipelines that have only the first discover logic errors after publication.

Fixture-based unit tests

The direct approach mirrors application unit testing: a small, hand-written input table, the transformation under test, and the exact expected output. dbt added native unit tests in version 1.8 in exactly this given/expect form: mock rows for each ref or source the model reads, and the rows the model must produce (dbt Labs, Unit tests documentation). They run against static fixtures, so dbt recommends running them in development and CI rather than in production, and aiming them at complex logic: regular expressions, date arithmetic, window functions, long CASE expressions.

Good fixtures are chosen adversarially. For the join above, a fixture with one duplicated dimension key and an assertion on the output row count catches the fan-out in milliseconds. The cases that repay a fixture are the ones production data rarely exhibits on any given day: null join keys (which an inner join silently drops, because NULL = NULL is not true), an event exactly at a window or day boundary, a customer with no orders, an empty input partition, a timestamp on a daylight-saving transition.

The weakness is the one fixtures always have. A test encodes the author's expectations, and the author who wrote the bug usually shares the misunderstanding that caused it.

Property-based tests

Property-based testing replaces hand-chosen examples with generated ones and replaces exact expected outputs with invariants that must hold for every input. The idea comes from QuickCheck (Claessen & Hughes, ICFP 2000). In Python it is usually done with Hypothesis, which also shrinks a failing input to a minimal counterexample (MacIver, Hatfield-Dodds et al., 2019, Hypothesis: A new approach to property-based testing, JOSS 4(43)).

Data transformations have unusually crisp invariants. Writing \(T\) for the transformation and \(x\), \(y\) for input tables:

Idempotence for deduplication or upsert logic, \(T(T(x)) = T(x)\). A rerun must not change the result.

Conservation for any step that should neither create nor destroy value, \(\sum_{r \in T(x)} r.\text{amount} = \sum_{r \in x} r.\text{amount}\), and for joins against a dimension that must be unique, \(|T(x)| = |x|\).

Partition additivity for incremental models, \(T(x \cup y) = T(x) \oplus T(y)\) when \(x\) and \(y\) cover disjoint partitions and \(\oplus\) is the model's merge. This is precisely the assumption an incremental build makes, and it fails for distinct counts, medians and window functions that reach across partition boundaries.

Order independence, \(T(\pi(x)) = T(x)\) for any row permutation \(\pi\), which catches logic that depends on physical order, such as "first row wins" without an explicit ordering.

A generator producing random order tables with nulls, duplicates and boundary timestamps will find the fan-out, the null-key drop and the non-additive distinct count without anyone having thought of them. The cost is writing generators that respect the schema's real constraints, and a slower test suite.

Reconciliation: testing the run, not the code

Code tests cannot see a bad input or a partial run. Reconciliation compares a transformation's output with its input on every run, at a level where the two must agree:

where \(K\) is the set of business keys and \(\triangle\) the symmetric difference, the keys present on one side only. Totals catch value created or lost; the key diff says which records. The tolerance \(\varepsilon\) must be justified, for currency rounding or known late arrivals, because a generous one hides exactly the $6 million above.

A related technique runs a proposed change and the current production code over the same input snapshot and diffs the outputs row by row before merging. Any row that differs is either the intended change or a regression, and a reviewer can tell which.

Practitioners disagree about the mix. One camp holds that SQL unit tests with mocked inputs are brittle, re-encode the implementation and rot as schemas change, and that production-snapshot diffs catch more real bugs per hour. The other replies that snapshot diffs only exercise cases present in today's data, which is exactly how boundary bugs escape. The dbt guidance to reserve unit tests for complex logic sits between them.

When it breaks

Fixtures drift from the schema. A new non-null column upstream makes fixtures unrepresentative while tests keep passing, because the mock never included it.

Engine semantics differ. Tests run on DuckDB or a local engine can pass while the warehouse differs on null ordering, integer division, timestamp precision or collation, so the test verifies a different program.

Properties can be vacuous. A conservation check over a generator that never produces duplicate keys proves nothing about fan-out. Generators need coverage checks of their own.

Reconciliation shares the bug. If source and target totals are both computed through the same faulty view, they agree perfectly. Reconcile against the system of record, not a derived table.