SQL-Based Data Cleaning and Anomaly Detection Questions

Replace a full scan with a random or stratified sample, compute the metric on the sample plus a confidence interval, and only pay for a full scan when the sampled estimate is close enough to your threshold that the uncertainty itself matters.

Approach

WITH sampled AS (
  SELECT * FROM clicks TABLESAMPLE BERNOULLI(1) -- roughly 1% sample (some engines, e.g. DuckDB, require BERNOULLI(1%))
)
SELECT partition_day,
  COUNT(*) AS sample_rows,
  COUNT(*) - COUNT(DISTINCT (user_id, session_id)) AS sample_dup_rows,
  (COUNT(*) - COUNT(DISTINCT (user_id, session_id))) * 1.0 / COUNT(*) AS sample_dup_rate
FROM sampled
GROUP BY partition_day;

Stratifying by partition (here, by day) rather than sampling uniformly across the whole table avoids a sample that's accidentally skewed toward a few large partitions; report the sampled rate alongside a confidence interval (derived from the sample size and observed proportion via a standard proportion-confidence formula) so the estimate is presented honestly as a range, not a false-precision point value. Note that a multi-column DISTINCT needs the row-constructor form, COUNT(DISTINCT (col1, col2)), not COUNT(DISTINCT col1, col2); the latter is not valid syntax in PostgreSQL or DuckDB and will fail with a function-not-found error.

Worked example

A 100-billion-row table sampled at roughly 1% (about 1 billion rows) still gives a duplicate-rate estimate with a narrow confidence interval, at a small fraction of the cost of scanning every row; if that estimate sits comfortably below your alert threshold with the interval fully below it too, you can trust the sampled result without a full scan.

Trade-offs and pitfalls

When the sampled estimate's confidence interval STRADDLES your threshold (you can't tell if the true rate is above or below the line you care about), that's exactly the situation that justifies paying for a full, expensive scan rather than the sample being "good enough"; the sample's job is to save you from unnecessary full scans, not to force a decision when the uncertainty is genuinely too high to trust. An incremental, watermark-based check (only re-examining rows updated since the last successful run, tracked via a stored watermark column, and designed to be idempotent across retries) is a complementary technique for keeping routine checks cheap, distinct from sampling: it reduces the SCOPE of what's scanned rather than the FRACTION sampled within that scope, and the two combine well on a genuinely enormous, frequently-updated table.

Referential integrity means every foreign key in the child table either matches a real primary key in the parent, or is NULL; an orphan is a non-NULL foreign key with no matching parent row. Find orphans by asking "which child rows have no partner", which `LEFT JOIN ... WHERE parent.id IS NULL` and `NOT EXISTS` both express, just from opposite directions.

Both forms (verified)

```sql
-- LEFT JOIN form
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL AND o.customer_id IS NOT NULL;

-- NOT EXISTS form
SELECT o.order_id, o.customer_id
FROM orders o
WHERE o.customer_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);
```
Both return identical results on correctly-modeled data; `NOT EXISTS` is often preferred because its intent reads directly ("no matching customer exists") and most optimizers turn it into an efficient anti-join without materializing the full joined row.

Worked example (verified by execution)

Given `customers(1), customers(2)` and `orders` referencing customers 1, 2, 3, and NULL: both queries correctly return only the order referencing customer 3 (the true orphan), correctly excluding the order with a NULL customer_id, since a NULL foreign key represents "not yet assigned" rather than "broken reference".

Trade-offs and pitfalls

The explicit `o.customer_id IS NOT NULL` guard matters: without it, a NULL foreign key would incorrectly show up as an "orphan" in the LEFT JOIN form, because it never equals any customer_id and the join naturally produces NULL on the customer side. The same pattern extends to self-referential relationships (an employee table's manager_id pointing back into the same table) without any structural change to the query.

Compute a canonicalized row hash so two logically identical rows always produce the identical hash regardless of things that shouldn't matter, like column order or how NULLs and floating-point values happen to be represented.

Approach

```sql
SELECT order_id,
md5(concat_ws('|', order_id::VARCHAR, COALESCE(amount::VARCHAR, 'NULL'), COALESCE(status, 'NULL'))) AS row_hash
FROM orders_source;
```
Using an explicit separator and an explicit NULL sentinel (rather than letting `concat` silently absorb NULLs, which would make `('a', NULL, 'b')` hash identically to `('a', 'b')`) keeps two genuinely different rows from ever colliding on the same hash purely by NULL-handling accident.

Worked example (verified by execution)

Two rows, one with a real amount and status, one with a NULL status, produce distinct MD5 hashes, correctly reflecting that they are different rows, whereas a naive `concat(order_id, amount, status)` without a NULL sentinel would make the second row's hash indistinguishable from a hypothetical row that simply omitted the status field from the concatenation entirely.

Trade-offs and pitfalls

Floating-point values need special care: the same logical amount can be represented with different trailing precision (`100.0` versus `100.00`) depending on how a column was cast, producing different hashes for values a human would call identical; round to an explicit, agreed precision before hashing, rather than hashing the raw floating-point representation. Beyond a single row hash, the same technique extends to a per-day TABLE-level checksum (aggregating the row hashes, for example with a sum or XOR that is itself insensitive to row order) so you can compare "did this whole day's data change" in one number, and further to computing full row-level diffs between two snapshots (added, removed, and changed rows with old-value/new-value detail per column) when a table-level mismatch needs to be narrowed down to specific rows; for very large tables, sampling a subset via anti-joins is a lighter-weight regression check between a pre- and post-transformation snapshot than a full row-level diff every time.

Compare today's count against a rolling baseline (like the trailing 28-day median for the same weekday) rather than yesterday's value alone, since a single prior day can itself have been unusual, and flag a tolerance-band violation rather than any deviation at all.

Approach (verified by execution)

```sql
WITH baseline AS (
SELECT d, row_count,
MEDIAN(row_count) OVER (ORDER BY d ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING) AS baseline_median
FROM daily_counts
)
SELECT d, row_count, baseline_median,
ROUND(row_count * 100.0 / NULLIF(baseline_median, 0), 1) AS pct_of_baseline,
(row_count < 0.7 * baseline_median OR row_count > 1.3 * baseline_median) AS is_alert
FROM baseline;
```

Worked example (verified by execution)

Over a week of daily row counts holding steady around 980-1050, a day with only 300 rows computes to 30% of the trailing baseline median, well under the 70% floor, correctly triggering the alert; the other days, all within roughly 95-105% of baseline, correctly stay quiet.

Trade-offs and pitfalls

A single global tolerance band (70%-130%) works reasonably for a stable metric but is too rigid for one with a naturally wide day-to-day range; consider a same-weekday-last-week baseline as a simpler alternative when strong weekly seasonality exists, or a rolling median-ratio rule (flagging a spike at 3x the trailing 14-day median and a drop under a third of it) which is a more robust variant of the same idea. When several such rules run together (a row-count rule, a null-rate rule, an ingestion-lag rule), compute and report all of their statuses in one query with severity labels rather than as separate isolated checks, so a genuine incident that trips several rules at once is immediately visible as one correlated event rather than three disconnected alerts. At the scale of petabytes, swap the exact row count for an approximate distinct-count algorithm like HyperLogLog to keep the check itself cheap to run daily.

Define the duplicate as "same entity and value, arriving within N minutes of each other" and detect it with a rolling comparison to the PREVIOUS row in the same partition, not a fixed time bucket, because a fixed bucket can miss duplicates that straddle a bucket boundary.

The bucket pitfall (verified)

A naive approach buckets each row into a fixed 5-minute wall-clock window and groups by `(user_id, amount, bucket)`. Two transactions 3 minutes apart, at 10:03 and 10:06, land in different fixed buckets (10:00-10:05 and 10:05-10:10) and are missed entirely, even though they are well within the 5-minute tolerance.

Correct rolling-tolerance form

```sql
WITH ordered AS (
SELECT *, LAG(occurred_at) OVER (PARTITION BY user_id, amount ORDER BY occurred_at) AS prev_ts
FROM txn
)
SELECT txn_id, occurred_at, prev_ts,
(prev_ts IS NOT NULL AND occurred_at - prev_ts <= INTERVAL '5 minutes') AS is_duplicate
FROM ordered ORDER BY txn_id;
```
`LAG` looks at the immediately preceding row for the same `(user_id, amount)`, so the gap is measured against the actual neighbor, not a wall-clock boundary.

Worked example (verified by execution)

For transactions at 10:03, 10:06, and 10:20 for the same user and amount: the fixed-bucket approach misses the 10:03/10:06 pair (different buckets). The LAG-based query correctly flags the 10:06 row as a duplicate of 10:03 (180-second gap, within tolerance) and correctly leaves the 10:20 row unflagged (840-second gap from its predecessor, outside tolerance).

Trade-offs and pitfalls

The rolling form is chained: if three transactions occur at 0, 3, and 6 minutes, each 3 minutes apart, only the second is compared to the first and the third to the second, so both are flagged as duplicates of their immediate neighbor even though the first and third are 6 minutes apart. Decide upfront whether that chaining behavior is the definition you want, or whether you need duplicates measured against the FIRST event in a cluster instead. When picking which row survives, keep the earliest arrival as canonical, since it is the one least likely to be the retried copy.