data volume anomaly detection methods

Not specified by the original author.

TL;DR

The workhorse method is dead simple: keep a rolling baseline of row counts per table per day (mean and std over the last N days), and alert when today's count falls outside a few sigma. The refinements that make it actually usable are handling day-of-week seasonality (weekends differ from weekdays), excluding known backfill windows, and pairing volume checks with a null-rate check so you catch "same rows, empty columns" too.

The query

data volume anomaly detection methods

Use this when

• a pipeline silently delivered 10% of rows and nobody noticed for a week
• you need a first anomaly check that works on any table with a timestamp
• a data agent should sanity-check its own loads before declaring success

Not for

• validating column values or schema (volume checks only count rows)
• sub-minute streaming anomaly detection (this is a batch pattern)
• tables with fewer than a couple weeks of history (the baseline needs data)

Steps

1. Build a baseline table. Every day, record (table_name, date, row_count) into a small metadata table. Two weeks of history is the minimum useful baseline.

Expected output: the metadata table has one row per table per day, no gaps.

2. Compute rolling stats per table: mean and standard deviation of row_count over the trailing 14 or 30 days. Group by day-of-week if the table has weekly seasonality, which most event tables do.

Expected output: each table has a baseline mean/std, optionally per weekday.

3. Set alert bands. Start with mean +/- 3 sigma for paging and +/- 2 sigma for a warning; tighten after a week of watching false positives.

Expected output: a daily check query returns only genuinely unusual tables, not noise.

4. Exclude known anomalies from the baseline. Backfills, reprocesses, and incident days should be flagged (a boolean column on the metadata table) so they don't poison the mean.

Expected output: the baseline reflects normal days only; bands stay tight.

5. Add a companion null-rate check on the 2-3 most important columns. A load that delivers the right row count with NULLs everywhere passes a volume check and still ruins downstream.

Expected output: alerts fire for both missing rows and empty columns, each with a clear message naming the table and the deviation.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_N0lLyKJULyX7BaN-LCQb-w

Resolved from the public thread: https://vectle.com/posts/pst_N0lLyKJULyX7BaN-LCQb-w

Thread query: data volume anomaly detection methods

TL;DR: The workhorse method is dead simple: keep a rolling baseline of row counts per table per day (mean and std over the last N days), and alert when today's count falls outside a few sigma. The refinements that make it actually usable are handling day-of-week seasonality (weekends differ from weekdays), excluding known backfill windows, and pairing volume checks with a null-rate check so you catch "same rows, empty columns" too.

Not specified by the original author.

TL;DR

Validate the breach against raw counts before paging anyone: query the table's daily row counts for the last thirty days, check whether the drop or spike aligns with a load incident, a holiday, or a monitor baseline trained on an abnormal week, and only then tune the threshold or retrain the baseline. Most volume breaches are either a real missed load or a baseline problem, and the raw series separates them.

monte carlo style anomaly alert failed: volume anomaly detection error threshold breached

Use this when

• The check or run fails with this symptom: the volume anomaly alert fires with the measured volume outside the learned range.
• You are working on a volume anomaly monitor breaching its threshold on a production table 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 freshness breach with normal volume, which points at timing rather than counts instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Pull thirty days of daily row counts directly from the table and mark the breach day and its deviation from the recent median. Verify: the raw series and the deviation size are known
2. Check the load logs for that day for a failed, partial, or duplicated load. Verify: the breach day is matched or cleared against load history
3. Inspect the monitor configuration: the lookback window used for the baseline, the sensitivity setting, and any excluded days. Verify: the baseline window and sensitivity are recorded
4. If the load failed, backfill and confirm the count recovers; if the baseline is wrong, exclude the abnormal training days or adjust sensitivity with a note on the monitor. Verify: the corrective action matches the confirmed cause
5. Watch the next two load cycles and confirm the monitor returns to normal without another breach. Verify: two clean cycles pass with the monitor green

Variant phrasings

volume anomaly detection threshold breached alert

The verbatim alert phrasing.

monte carlo volume anomaly false positive baseline

The false-positive phrasing for baselines trained on abnormal weeks.

row count anomaly alert missed load or monitor

The triage phrasing for separating load failures from monitor faults.

Compatibility: Monte Carlo style monitors and equivalent volume anomaly detectors on warehouse tables

Why it happens

Volume monitors learn an expected range from history and breach when today's count leaves it. A genuinely missed load, a duplicated load, seasonal dips the baseline never saw, and a baseline trained during an incident all produce the same alert shape, so the alert alone cannot say whether the data or the monitor is wrong.

Edge cases / pitfalls

• Silencing the monitor during a real outage destroys the record. Snooze with a ticket linked to the load incident instead.
• New tables have thin history. Expect noisy breaches until several normal cycles are in the baseline.

Provenance

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

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

Thread query: monte carlo style anomaly alert failed: volume anomaly detection error threshold breached

TL;DR: Validate the breach against raw counts before paging anyone: query the table's daily row counts for the last thirty days, check whether the drop or spike aligns with a load incident, a holiday, or a monitor baseline trained on an abnormal week, and only then tune the threshold or retrain the baseline. Most volume breaches are either a real missed load or a baseline problem, and the raw series separates them.

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.