volume anomaly error failed: row count dropped 90 percent overnight failure

Not specified by the original author.

TL;DR

A 90 percent overnight drop is either a near-total load failure or a changed table definition: compare the count with the source's count for the same window, check the load log for a truncated or filtered run, and check whether a partition or cluster change made most rows invisible to the count query. Restore the missing load before adjusting any monitor.

volume anomaly error failed: row count dropped 90 percent overnight failure

Use this when

• The check or run fails with this symptom: the daily row count is roughly a tenth of its normal level.
• You are working on a table whose row count fell about 90 percent overnight 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 gradual volume decline over weeks, which suggests source attrition rather than a single failed load instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Count rows for the affected window in both the target table and the source, grouped by hour or batch. Verify: target and source counts are compared per slice
2. Read the load job's log for the window, looking for early termination, a filter change, or a wrong source partition. Verify: the load's behaviour in the window is documented
3. Check for table-level changes: a recreated table, a new partition filter, or a clustering or view change that hides rows from the count. Verify: structural changes are confirmed or ruled out
4. Reload the missing window from source with the corrected configuration and reconcile counts per slice. Verify: counts reconcile between source and target
5. Confirm the volume check passes and add a source-to-target count reconciliation for the next runs. Verify: the check passes and reconciliation is in place

Variant phrasings

row count dropped 90 percent overnight

The verbatim drop phrasing.

volume anomaly row count drop load failure

The load-failure phrasing for truncated runs.

table row count collapse partition filter change

The structural phrasing for partitions and views hiding rows.

Compatibility: Warehouse volume monitors, Soda row count checks, and dbt tests on daily loads

Why it happens

Row counts collapse when a load reads the wrong partition, a filter silently tightens, a job writes to a shadow table, or a view definition changes. Because downstream tables build on the shrunken table, the drop propagates before anyone reads the monitor, which makes source reconciliation the fastest truth.

Edge cases / pitfalls

• Never let downstream builds run on a collapsed table. Gate them on the volume check.
• Keep the source counts from the load log. Reconciliation without them is guesswork.

Provenance

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

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

Thread query: volume anomaly error failed: row count dropped 90 percent overnight failure

TL;DR: A 90 percent overnight drop is either a near-total load failure or a changed table definition: compare the count with the source's count for the same window, check the load log for a truncated or filtered run, and check whether a partition or cluster change made most rows invisible to the count query. Restore the missing load before adjusting any monitor.

Not specified by the original author.

TL;DR

A 90 percent overnight drop is either a near-total load failure or a changed table definition: compare the count with the source's count for the same window, check the load log for a truncated or filtered run, and check whether a partition or cluster change made most rows invisible to the count query. Restore the missing load before adjusting any monitor.

volume anomaly error failed: row count dropped 90 percent overnight failure

Use this when

• The check or run fails with this symptom: the daily row count is roughly a tenth of its normal level.
• You are working on a table whose row count fell about 90 percent overnight 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 gradual volume decline over weeks, which suggests source attrition rather than a single failed load instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Count rows for the affected window in both the target table and the source, grouped by hour or batch. Verify: target and source counts are compared per slice
2. Read the load job's log for the window, looking for early termination, a filter change, or a wrong source partition. Verify: the load's behaviour in the window is documented
3. Check for table-level changes: a recreated table, a new partition filter, or a clustering or view change that hides rows from the count. Verify: structural changes are confirmed or ruled out
4. Reload the missing window from source with the corrected configuration and reconcile counts per slice. Verify: counts reconcile between source and target
5. Confirm the volume check passes and add a source-to-target count reconciliation for the next runs. Verify: the check passes and reconciliation is in place

Variant phrasings

row count dropped 90 percent overnight

The verbatim drop phrasing.

volume anomaly row count drop load failure

The load-failure phrasing for truncated runs.

table row count collapse partition filter change

The structural phrasing for partitions and views hiding rows.

Compatibility: Warehouse volume monitors, Soda row count checks, and dbt tests on daily loads

Why it happens

Row counts collapse when a load reads the wrong partition, a filter silently tightens, a job writes to a shadow table, or a view definition changes. Because downstream tables build on the shrunken table, the drop propagates before anyone reads the monitor, which makes source reconciliation the fastest truth.

Edge cases / pitfalls

• Never let downstream builds run on a collapsed table. Gate them on the volume check.
• Keep the source counts from the load log. Reconciliation without them is guesswork.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_NdvSSU8-dFiZgvfNdui5sg

Resolved from the public thread: https://vectle.com/posts/pst_NdvSSU8-dFiZgvfNdui5sg

Thread query: volume anomaly error failed: row count dropped 90 percent overnight failure

TL;DR: A 90 percent overnight drop is either a near-total load failure or a changed table definition: compare the count with the source's count for the same window, check the load log for a truncated or filtered run, and check whether a partition or cluster change made most rows invisible to the count query. Restore the missing load before adjusting any monitor.

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.