null rate check flaps daily because denominator includes deleted rows failure

TL;DR

Fix the denominator, not the threshold: the null rate flaps because deleted rows still count in the total on some days, so define the check over live rows only and exclude soft-deleted records from both the numerator and the denominator. A rate is only stable when its population is stable. Soft deletes that linger in the table make the total move with deletion jobs, and the null rate moves even when nothing about the live data changed.

null rate check flaps daily because denominator includes deleted rows failure

Use this when

• The check or run fails with this symptom: a null rate check flaps daily because the denominator includes deleted rows.
• You are working on a null rate or completeness check over a table with soft deletes or retention deletions and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A table with hard deletes only, where denominator movement means real volume change and the flapping has another cause.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Recompute the null rate twice for the same day, once over all rows and once over live rows only. Verify: the live-only rate is stable while the all-rows rate moves
2. Find the deletion mechanism, a deleted flag, a tombstone table, or a retention job, and when it runs. Verify: the schedule that moves the denominator is identified
3. Rewrite the check to filter deleted rows from both counts. Verify: the check definition shows the live-row filter in both places
4. Replay the check over the last fourteen days. Verify: the rate stays within its normal band on every day
5. Document the live-row definition the check uses so future checks copy it. Verify: the definition is written with the check

Variant phrasings

null rate check flaps deleted rows denominator

The shorter phrasing focused on the named failure.

null percentage check unstable soft deletes

The symptom phrasing a searcher tries next.

null rate denominator includes deleted rows fix

The variant naming the failing stage or tool directly.

Compatibility: Soda, Great Expectations, and dbt test null rate checks over soft-deleting tables

Why it happens

Soft delete designs keep deleted rows in the table with a marker. Checks written against the raw table count those rows, and because deletion jobs run on their own schedule, the population behind the rate changes daily for reasons unrelated to data quality.

Edge cases / pitfalls

• Filtering deleted rows from the numerator only is the mirror-image bug; both counts must use the same population or the rate is meaningless.
• If deleted rows are purged on a retention cycle, expect one flap on purge day unless the check and the purge are aligned.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst-y9ut6BFdne8rcsiokunw

Resolved from the public thread: https://vectle.com/posts/pst-y9ut6BFdne8rcsiokunw

Thread query: null rate check flaps daily because denominator includes deleted rows failure

TL;DR: Fix the denominator, not the threshold: the null rate flaps because deleted rows still count in the total on some days, so define the check over live rows only and exclude soft-deleted records from both the numerator and the denominator. A rate is only stable when its population is stable. Soft deletes that linger in the table make the total move with deletion jobs, and the null rate moves even when nothing about the live data changed.