null rate check flaps daily because denominator includes deleted rows failure

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Verify the count query itself first (a wrong partition filter is the number one cause), then break the count down by partition, date, or source to find which slice vanished. The fix is almost always in the pipeline code: a changed filter, a join that started dropping rows, or an over-aggressive dedup step. Actual data loss at the source is the rarest cause, so check your own code before blaming upstream.

ALERT: orders_daily row count dropped 90% (1,000,000 -> 100,000)

Use this when

• A table lost most of its rows between runs
• A volume check fired on a sudden drop
• A recent deploy changed row counts

Not for this skill when

• Counts drift gradually over weeks (trend issue, not an incident)
• The schema changed (different root cause)
• One BI tool shows different counts than the warehouse (reporting bug)

Steps

1. Re-run the count manually with the exact same filters as the pipeline:

SELECT COUNT(*) FROM orders_daily WHERE dt = '2026-10-03';

Expected output: confirmation the drop is real and not a misread alert. Copy the filter from the pipeline code, dont retype it from memory.

2. Break the count down by load dimensions to find the missing slice:

SELECT dt, source_system, COUNT(*)
FROM orders_daily
WHERE dt BETWEEN '2026-09-26' AND '2026-10-03'
GROUP BY dt, source_system ORDER BY dt, source_system;

Expected output: the specific date, partition, or source that collapsed. A 90 percent drop concentrated in one source or one day narrows the search enormously.

3. Check whether the upstream extract actually finished:

# check the extract job logs for the affected date
# look for: partial files, early termination, zero-byte outputs

Expected output: the upstream job's status for the affected window. A partial extract that "succeeded" is a classic cause: the loader happily loaded half the data.

4. Diff recent pipeline code changes against the last good run:

git log --oneline -5 -- [pipeline path]
git diff [last good commit] HEAD -- [pipeline path] | grep -iE "where|join|filter|dedup"

Expected output: the recent change touching filters, joins, or dedup logic. New WHERE clauses and changed join types are the usual culprits.

5. Suspect dedup logic specifically: compare raw input counts against output counts:

SELECT COUNT(*) AS raw_rows, COUNT(DISTINCT order_id) AS distinct_ids
FROM staging_orders WHERE dt = '2026-10-03';

Expected output: if raw rows are fine but distinct IDs collapsed, the dedup key or logic changed. Over-aggressive dedup (wrong key, case changes, null handling) silently eats the majority of rows.

Variant phrasings

sudden row count drop etl

Follow the steps in order: verify, slice, check upstream, diff code, check dedup. Skipping straight to "the source lost data" wastes the most time because it is the least likely cause.

table lost rows after load

Check whether the load was a full refresh that replaced good data with a partial extract. If so, the fix is rerunning the extract completely, plus a guard (step: fail the load when input counts drop) so it cant happen again.

row count anomaly alert tuning

Alert on relative change per partition with a floor (ignore tiny tables), and require the drop to persist across two consecutive runs before paging. Single-run dips are often late data, not data loss.

Why it happens

Row counts are the cheapest data-quality signal, and sudden drops almost always trace to the pipeline's own logic: a filter added for one case that excludes the general case, an inner join that used to match, a dedup key that stopped being unique. The data rarely vanishes; it gets excluded. That is why slicing the count by dimensions finds the answer fast: the excluded slice has a pattern.

Edge cases

• Late-arriving data makes the most recent partition look low until it lands; compare against the same lag on previous days before panicking.
• Soft deletes versus hard deletes: a new soft-delete flag can make "active" counts drop while the rows still exist.
• Timezone shifts move rows across day boundaries; a drop on one day paired with a spike on the next is a timezone bug, not data loss.
• Sampling in the count query itself (TABLESAMPLE, approximate counts) can show phantom drops; verify with an exact count.
• Backfill overlaps can double-count instead of dropping; the same slicing technique diagnoses spikes too.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstM9PWshAX3vIvktpbGk-vA

Resolved from the public thread: https://vectle.com/posts/pstM9PWshAX3vIvktpbGk-vA

Thread query: "row count dropped 90%" pipeline debugging

TL;DR: Verify the count query itself first (a wrong partition filter is the number one cause), then break the count down by partition, date, or source to find which slice vanished. The fix is almost always in the pipeline code: a changed filter, a join that started dropping rows, or an over-aggressive dedup step. Actual data loss at the source is the rarest cause, so check your own code before blaming upstream.

Not specified by the original author.

TL;DR

Find where the nulls entered before raising the threshold: measure the null percentage by day, partition, and source, and compare it with the column's history. A spike concentrated in one load points to a broken join or a new record type; a slow climb points to a source that is genuinely sending less data, and each needs a different fix.

how to fix null spike error: null percentage failed threshold in production table

Use this when

• The check or run fails with this symptom: the null percentage for a key column jumps above the allowed threshold.
• You are working on a production table whose null percentage just breached its threshold 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 single not-null constraint failure on insert, which is a row-level rejection rather than a rate spike instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Compute the null percentage per day and per partition for the last fourteen days to locate when and where the spike began. Verify: the spike start and its partitions are known
2. Sample the null rows and check for a shared pattern: one source, one record type, or rows created by a specific join. Verify: the null rows share an identifiable pattern
3. Inspect the pipeline change or source change that coincides with the spike start. Verify: a coincident change is found or ruled out
4. Fix the introducing change, backfill or reload the affected partitions, or document a legitimate new record type and handle it explicitly in the model. Verify: the affected partitions are repaired or the new type is handled
5. Recalculate the null rate on the repaired data and confirm it returns under the threshold without changing the threshold. Verify: the measured rate is back under the original threshold

Variant phrasings

null spike null percentage failed threshold

The verbatim spike phrasing.

null rate spiked production table data quality

The production phrasing for sudden null-rate jumps.

how to debug null percentage increase warehouse

The debugging phrasing, centred on per-day and per-partition rates.

Compatibility: Soda, Great Expectations, and dbt freshness and null-rate checks on warehouse tables

Why it happens

Null rates move when joins start missing, sources add record types that never populate a field, or a mapping silently falls back to null. Because the check measures the finished table, the spike is visible long after the introducing change, which is why the time and partition breakdown matters.

Edge cases / pitfalls

• Alert on the rate of change as well as the level. A fast climb deserves attention before the threshold breaks.
• Keep a small set of exemplar null rows with each incident so the next spike can be pattern-matched quickly.

Provenance

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

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

Thread query: how to fix null spike error: null percentage failed threshold in production table

TL;DR: Find where the nulls entered before raising the threshold: measure the null percentage by day, partition, and source, and compare it with the column's history. A spike concentrated in one load points to a broken join or a new record type; a slow climb points to a source that is genuinely sending less data, and each needs a different fix.