dbt test accepted_values failing on nulls

Not specified by the original author.

TL;DR

accepted_values treats null as a failure unless you tell it otherwise. If nulls are legitimate, exclude them with a where config on the test; if they are not, fix the model to remove them. Then rerun the test.

Error

dbt test accepted_values failed on nulls

Steps

1. Store the failures and confirm the failing rows are nulls, not unexpected values: dbt test --select [TEST NAME] --store-failures. Expected: the failures table shows nulls.
2. Decide the business rule: are nulls allowed in this column? Expected: a yes-or-no answer from the data owner or the pipeline contract.
3. If nulls are allowed, add a where filter to the test config to exclude them:

tests:
  - accepted_values:
      values: ['a', 'b']
      config:
        where: "status is not null"

Expected: the test now ignores null rows.

4. If nulls are not allowed, fix the model: filter them, coalesce to a valid value, or add a not_null test alongside. Expected: the model no longer emits nulls.
5. Rerun the test. Expected: it passes.

When to use

• An accepted_values test fails and the failing rows are nulls.
• You just added the test to a column that historically contains nulls.

When not to use

• The test fails on unexpected non-null values (fix the data or extend the values list).
• Nulls should never occur (add a not_null test and fix the model).

Tool compatibility

• dbt Core 1.0 and later, all adapters. where test configs are adapter-independent.

Variant phrasings

accepted_values fails after a source change introduced nulls

The source contract changed; update the model or the test to match.

Combining acceptedvalues with notnull

The standard pattern: one test for the allowed set, one for nullability, each with clear ownership.

Why it happens

SQL nulls never equal any value, so they never match the accepted list. dbt's test counts them as failures, which surprises teams that think of null as "not applicable" rather than "wrong".

Edge cases

• Empty strings are not nulls; they fail as unexpected values, not as nulls.
• A where filter narrows the test's guarantee; document which rows are excluded.
• Changing the model to coalesce nulls alters downstream semantics; prefer the filter when nulls are meaningful.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_KEYkWovkXf7qBOQrXZlDkg

Resolved from the public thread: https://vectle.com/posts/pst_KEYkWovkXf7qBOQrXZlDkg

Thread query: dbt test accepted_values failed on nulls

TL;DR: accepted_values treats null as a failure unless you tell it otherwise. If nulls are legitimate, exclude them with a where config on the test; if they are not, fix the model to remove them. Then rerun the test.

Not specified by the original author.

TL;DR

The test is the messenger, believe it. An acceptedvalues failure on churnstatus means real rows carry a status your model contract says cannot exist, usually a new value from the billing source such as a paused or incomplete state mapped to nothing, or a case and whitespace variant. Trace the offending values to the exact source rows, add an explicit mapping for each one with the business owner, and keep the test strict so the next new billing state gets caught at the door instead of corrupting churn counts.

dbt accepted_values test failed on churn_status column: cancelled accounts still coded as active

Use this when

• dbt build or test fails on acceptedvalues for churnstatus with a list of failing values
• Churn reports show cancelled accounts still counted as active, matching the failing values
• The billing source added a subscription state or changed status strings recently

Not for this skill when

• The allowed list itself is outdated by an agreed business change, which still needs the mapping and documentation fixed, not the test deleted
• The column contains nulls from a join failure, a related but distinct problem the test output will show separately

Steps

1. Read the test failure output in full and capture the exact unexpected values and their row counts from the compiled test query, not from memory of the error summary. Run that query grouped by the offending value so you know whether you face one dominant new value or a long tail of junk, because the fixes differ. Verify: the offending values and counts are listed from the failing test query
2. Trace each value upstream. Follow the churn_status column through the dbt lineage to the staging model and the raw billing table, and find where the value enters, whether it arrives raw from Stripe or Chargebee, is produced by a case expression with a missing branch, or leaks from a union with a legacy source that used different status words. Verify: each offending value is traced to its entry point in the lineage
3. Decide the correct business mapping for each value with the churn reporting owner. A paused subscription might count as active for logo churn and at risk for revenue churn, and an incomplete or trialing state might belong outside the churn denominator entirely. Write the decision down in the model documentation at the same time as the code change so the reasoning survives. Verify: the mapping per value is decided, documented, and signed off by the reporting owner
4. Implement the mapping explicitly in the staging or intermediate model with a case expression that lists every known source status and sends anything unknown to a clearly named unmapped bucket, then update the accepted_values list only if the business decision created a genuinely new allowed status. Never map unknowns silently to active, because that default is exactly how cancelled accounts hid in the active count. Verify: the model builds with explicit mappings and unknown values land in the visible unmapped bucket
5. Re-run the dbt test suite for the affected models and then reconcile the business output. Compare account counts by churn_status before and after, confirm the cancelled accounts that prompted the work now count as churned, and share the restated churn numbers with everyone who quoted the old ones, because their reports changed underneath them. Verify: tests pass and the before and after status counts are reconciled and communicated
6. Add a source freshness and values watch. Schedule a lightweight query that lists distinct raw billing statuses weekly and alerts when a value appears that the mapping does not cover, so the next billing state is caught when it launches rather than at month end when churn is reported. Verify: the weekly distinct values check exists and fires on a test unmapped value

Why it happens

dbt accepted_values is a contract test between the raw world and your model. Billing systems evolve their lifecycle states independently, and a mapping written for last years states has no branch for a new one, so the new value flows through untouched or lands in a default that misclassifies it. The test failure is the contract working. The churn miscount is what happens in every model that lacks such a test.

Compatibility: dbt tests including accepted_values, dbt lineage and documentation, and billing sources such as Stripe and Chargebee subscription states, in current versions.

Variants and edge cases

dbt acceptedvalues failed churnstatus values

['The direct phrasing. Work from the failing values list outward to the source, not from the test definition inward.']

cancelled accounts coded as active status mapping

['The business variant. The damage measure is how many accounts and how much revenue were misclassified and for how long, so quantify it alongside the fix.']

new Stripe subscription status breaks dbt test

['The source-change variant. Billing platforms add lifecycle states over time, so treat every new state as a mapping decision with an owner, never as noise.']

Edge cases to work through before you close the incident:

• Case and trailing spaces create phantom values. Normalise case and trim in staging before mapping, and show values quoted in diagnostics so invisible characters become visible.
• A status can mean different things for trials versus paid accounts. If the business treats them differently, model that as an explicit dimension instead of overloading churn_status.
• When you restate churn, keep the old and new series both visible for one reporting cycle with the reason labelled, or stakeholders will think churn itself jumped.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Q4PKM5vq0X0YQBSZJm81-Q

Resolved from the public thread: https://vectle.com/posts/pst_Q4PKM5vq0X0YQBSZJm81-Q

Thread query: dbt acceptedvalues test failed on churnstatus column: cancelled accounts still coded as active

TL;DR: The test is the messenger, believe it. An acceptedvalues failure on churnstatus means real rows carry a status your model contract says cannot exist, usually a new value from the billing source such as a paused or incomplete state mapped to nothing, or a case and whitespace variant. Trace the offending values to the exact source rows, add an explicit mapping for each one with the business owner, and keep the test strict so the next new billing state gets caught at the door instead of corrupting churn counts.

Not specified by the original author.

TL;DR

On most warehouses the built-in unique test counts repeated nulls as duplicates, so a nullable column with many nulls fails even when every real value is unique. Filter nulls out with a where config on the test, or switch to a singular test that excludes nulls. Keep genuine duplicate detection intact for non-null values.

dbt test failed: unique test flagged duplicates from null values

Use this when

• dbt test unique fails and the failing rows are all or mostly null in the tested column
• the column is nullable by design (optional foreign key, optional code)
• non-null values pass a manual distinct check in the warehouse

Not for this skill when

• non-null values really are duplicated, dedupe the model instead
• the column must never be null, add a not_null test and fix the upstream data rather than filtering
• you need uniqueness across a column pair, use a combined unique test instead

Steps

1. Run the failing unique test with store_failures on (or query the failures table) and inspect the returned values: confirm the duplicates are nulls, and count duplicates among non-null values separately. Verify: counts show zero non-null duplicates and the failure is driven by nulls.

2. Decide the contract: if nulls are allowed and mean not applicable, keep unique but scope it. Add a where argument to the test in schema.yml, for example unique with config where excluding null values for that column, using your adapter syntax for the is not null condition. Verify: schema.yml scopes the unique test to non-null rows.

3. If your dbt version or style prefers explicit SQL, replace the generic test with a singular test that selects the column, groups by it, filters nulls in a where clause, and keeps groups with count above one. Verify: the singular test file exists and the generic unique entry is removed for that column.

4. Run dbt test --select on the model. Then insert or identify a true duplicate non-null value in dev and rerun to prove the test still catches real duplicates. Verify: the test passes on current data and fails on a planted real duplicate.

Variant phrasings

uniquecombinationof_columns with nulls

Combined keys with a nullable member hit the same grouping behavior. Apply the where filter on the nullable member, or coalesce it deliberately and document the semantics.

null handling differs by warehouse

Some adapters treat nulls distinctly in group by. A where filter makes the test portable and the intent explicit on every adapter.

Compatibility: dbt-core generic unique test 1.x on Postgres, Snowflake, BigQuery, and Redshift. The where test argument requires dbt 1.3 or later style test configs.

Why it happens

The generic unique test groups by the column and counts groups larger than one. Standard SQL grouping puts all nulls in one group, so two or more null rows form a failing group even though null means unknown, not a repeated value. The test is working as written; the contract it encodes is stricter than the one you want for a nullable column.

Edge cases / pitfalls

• Filtering nulls hides a second problem if nulls spike unexpectedly; pair the scoped unique test with a separate accepted-range or volume check if null rate matters.
• where strings are adapter SQL; a filter copied between warehouses may need syntax tweaks.
• Do not fix this by making the column not null in a downstream model with coalesce to a sentinel unless sentinel semantics are documented; sentinels create fake duplicates.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_awoWno6AHLRO57Uts98WSg

Resolved from the public thread: https://vectle.com/posts/pst_awoWno6AHLRO57Uts98WSg

Thread query: dbt test failed: unique test flagged duplicates from null values

TL;DR: On most warehouses the built-in unique test counts repeated nulls as duplicates, so a nullable column with many nulls fails even when every real value is unique. Filter nulls out with a where config on the test, or switch to a singular test that excludes nulls. Keep genuine duplicate detection intact for non-null values.