Add dbt unit tests to verify statistical correctness of profile measures

[stumelius]

Overview

The current integration tests verify that profile measures run without errors (mostly via not_null assertions), but they don't verify that measures compute correct values. dbt unit tests (introduced in dbt 1.8) can fill this gap by asserting precise expected outputs given known fixture data.

The gap in current test coverage

The YAML tests look like this:

- name: avg
  tests:
    - not_null:
        where: column_name not like 'string%' and column_name not like 'date%'

This only checks that avg doesn't crash — it wouldn't catch a regression where avg silently returns 0 or an incorrect value.

How unit tests would work here

dbt unit tests mock ref() inputs with fixture rows and assert the model output matches expected rows. For the profile model:

unit_tests:
  - name: test_profile_avg_and_nulls
    model: profile
    given:
      - input: ref('test_data_default')
        rows:
          - {id: 1, numeric_not_nullable: 10, numeric_nullable: 10, string_not_nullable: one, ...}
          - {id: 2, numeric_not_nullable: 20, numeric_nullable: null, string_not_nullable: two, ...}
    expect:
      rows:
        - {column_name: 'numeric_not_nullable', avg: 15.0, not_null_proportion: 1.0, distinct_count: 2}
        - {column_name: 'numeric_nullable',     avg: 10.0, not_null_proportion: 0.5, distinct_count: 1}

Constraints

The profile models use adapter.get_columns_in_relation() and run_query() inside the macro during compilation to introspect the schema and generate SQL dynamically. Unit tests can mock ref() inputs but not these schema introspection calls — so a real database connection is still required at compile time. This means unit tests don't provide fully offline testing, but they do provide value by asserting precise computed values rather than just non-nullness.

Fixture data must match the actual seed schema, which is manageable since the seeds change infrequently.

Suggested scope

Model	Value
profile	Assert exact values for all measures across all column types in test_data_default
profile_large_int	Assert specific std_dev_population / std_dev_sample values — turns the overflow regression test from "doesn't crash" into "computes the right number"
profile_no_rows	Assert all measures return null when the source table has no rows

References

• dbt unit tests docs
• dbt 1.8+ required