SQL-Based Data Cleaning and Anomaly Detection Questions

An exact `COUNT(DISTINCT email) = COUNT(*)` check requires a full scan and a large in-memory or spill-to-disk distinct set, which doesn't hold up at billions of rows; approximate cardinality algorithms trade a small, bounded error for a check that runs in a fraction of the time and memory.

Approaches

• HyperLogLog / APPROX_COUNT_DISTINCT: gives an estimated distinct count with typically well under 2% error using a small fixed-size sketch, regardless of table size; compare it against the total row count to estimate a duplication rate without ever materializing the full distinct set.
• Bloom filters: build one incrementally as you scan; a bloom filter answers "have we seen this exact value before" with no false negatives (only a tunable false-positive rate), letting you flag CANDIDATE duplicates cheaply and confirm the smaller candidate set exactly.
• Partitioned verification: if the table is partitioned, verify uniqueness within each partition cheaply and only check across-partition uniqueness for keys that could plausibly repeat (for example if the partition key is unrelated to the uniqueness key).

Worked example

On a 5 billion row table, `APPROX_COUNT_DISTINCT(email)` typically returns an estimate within roughly 1-2% of the true distinct count while scanning the same data far faster than an exact `GROUP BY`, because it never needs to hold the full set of distinct values in memory.

Trade-offs and pitfalls

An approximate check can tell you "roughly how much duplication exists" but cannot point to which specific rows are the duplicates; if you need to actually remediate, you still need an exact pass, just possibly limited to the range the approximate check flagged as suspicious. The same underlying sketch techniques (HyperLogLog for cardinality, t-digest for quantiles) generalize well beyond uniqueness checks, into any large-scale distribution comparison where an exact answer is too expensive to compute routinely.

SQL uses three-valued logic: every comparison against NULL (including `NULL = NULL`) evaluates to UNKNOWN, not TRUE or FALSE, so a row is only kept when a condition evaluates to TRUE. That is why `col = NULL` never matches anything, no matter what `col` holds, and why you must use `IS NULL` / `IS NOT NULL`, which are defined to test for NULL directly rather than compare against it.

How NULL propagates

• `col != 'x'` silently drops every row where `col` is NULL, because `NULL != 'x'` is UNKNOWN, and `WHERE` only keeps TRUE rows. A validation query that assumes this filter caught "everything except x" will silently under-count.
• `COALESCE(col, default)` returns the first non-NULL argument, so `COALESCE(country, 'unknown')` turns a NULL into a labeled default without disturbing real values.
• `NULLIF(a, b)` returns NULL when `a = b` and `a` otherwise, useful for turning a sentinel value (like `-1` or `'N/A'`) back into a proper NULL before further processing.
• Aggregates treat NULL differently by design: `COUNT(*)` counts every row regardless of NULLs, `COUNT(column)` counts only non-NULL values in that column, and `SUM`/`AVG` simply ignore NULLs (an `AVG` over one NULL and one 10 is 10, not 5).

Worked example

$$\text{orders}(id, amount) = \{(1, 10), (2, \text{NULL}), (3, 20)\}$$

`COUNT(*)` = 3, `COUNT(amount)` = 2, `SUM(amount)` = 30, `AVG(amount)` = 15 (30 divided by the 2 non-NULL rows, not 3).

Trade-offs and pitfalls

Whether to store missing text as NULL or as an empty string is a warehouse-wide policy decision, not a one-off choice: mixing the two conventions across tables means a null-rate check on one table silently misses the "missingness" that another table represents as `''`. Pick one convention, document it, and have every validation check test for both until legacy data is migrated. A related, often-overlooked pattern is grouping by a nullable column directly: `GROUP BY event_type` will produce a genuine NULL group in most engines, but downstream tools (dashboards, BI exports) frequently render that group as blank or drop it, so it's often safer to wrap it as `COALESCE(event_type, 'UNKNOWN')` before grouping, so the missing-category count stays visible.

Join the parent aggregate to the sum of its children and flag any pair whose difference exceeds a small floating-point tolerance, treating a strict equality check as almost always the wrong choice for numeric data that's passed through any arithmetic at all.

Approach (verified by execution)

```sql
SELECT o.order_id, o.total_amount, SUM(oi.amount) AS items_sum,
ABS(o.total_amount - SUM(oi.amount)) AS diff
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING ABS(o.total_amount - SUM(oi.amount)) > 0.01;
```

Worked example (verified by execution)

An order recorded with `total_amount = 55.00` whose line items sum to `49.99` produces a diff of `5.01`, well past the 0.01 tolerance, correctly surfacing as a violation; a second order whose total matches its line items exactly does not appear in the result at all.

Trade-offs and pitfalls

The same invariant-checking shape (a recorded total versus a computed sum of its parts) generalizes to checking that a day's hourly metric values sum to the recorded daily total, and to checking that a dimension's full set of expected values still appears in a daily aggregate even on a day with zero activity for one of them (so a category doesn't silently vanish from a report rather than showing a legitimate zero). A related but distinct check worth running alongside this one is completeness of PAYMENT against an order's expected total, which needs its own comparison (partial or missing payments) rather than being folded into the same query, since "the order total doesn't match its line items" and "the order hasn't been fully paid" are different failures with different remediation paths even though both compare a recorded amount to an expected one.

Reconciliation matters because a warehouse is a derived copy of a source system, and anything that can silently diverge between "what the source recorded" and "what the warehouse reflects" (a dropped batch, a duplicate replay, a partial load) will otherwise go unnoticed until someone downstream trusts a wrong number.

The standard checks

• Row counts: does the warehouse have the same number of records as the source for a given window?
• Sums: does a key numeric total (revenue, transaction count) match between the two?
• Min/max: do the boundary values (earliest/latest date, smallest/largest amount) agree?
• Hash totals: does an aggregate hash over the row set match, a cheap way to catch a mismatch that row counts and sums alone might miss (two errors that happen to cancel out in a sum).

Worked example

A source system reports 10,000 orders for yesterday; the warehouse shows 9,998. That two-row gap is small enough to miss in a dashboard glance but large enough, if it recurs daily, to represent a systematic under-count worth investigating rather than dismissing as noise.

Trade-offs and pitfalls

The investigative process, once a mismatch is found, should move from cheap to expensive: first confirm the mismatch is real and not a timing artifact (are both sides measured over the exact same window, accounting for timezone and any in-flight late-arriving data), then narrow by partition or day to find WHEN the divergence started, then sample specific rows to find WHY. Jumping straight to inspecting individual rows before confirming the mismatch is genuine and narrowing its scope wastes time chasing what might be a boundary-condition artifact rather than a real defect.

Combine several independent anomaly rules against the same table in one pass, since a group can be anomalous for entirely different reasons, and reporting them together avoids running (and someone reading) three separate near-identical queries.

Approach (verified by execution)

```sql
WITH cust_avg AS (SELECT customer_id, AVG(amount) AS avg_amt FROM transactions GROUP BY customer_id),
median_of_avgs AS (SELECT MEDIAN(avg_amt) AS med FROM cust_avg)
SELECT DISTINCT t.customer_id,
(t.amount < 0) AS negative_amount,
EXISTS(SELECT 1 FROM transactions t2 WHERE t2.invoice_id = t.invoice_id GROUP BY t2.invoice_id HAVING COUNT() > 1) AS duplicate_invoice,
(ca.avg_amt > 10 * m.med) AS far_above_peer_median
FROM transactions t
JOIN cust_avg ca ON ca.customer_id = t.customer_id
CROSS JOIN median_of_avgs m
WHERE t.amount < 0 OR ca.avg_amt > 10 * m.med
OR EXISTS(SELECT 1 FROM transactions t2 WHERE t2.invoice_id = t.invoice_id GROUP BY t2.invoice_id HAVING COUNT() > 1);
```

Worked example (verified by execution)

Across four customers, one with a negative total amount, one with the same invoice ID appearing twice, one who is entirely normal, and one whose average daily amount is far above the peer median: the query correctly surfaces the three anomalous customers, each tagged with WHICH rule they tripped (a customer can trip more than one), and correctly excludes the normal one.

Trade-offs and pitfalls

Tagging WHICH rule fired (rather than a single combined boolean) matters, because "negative amount" and "10x the peer median" call for very different follow-up actions, one is often a data-entry or refund-handling defect, the other might be a genuinely large, legitimate customer. The "10x the median" threshold is a coarse heuristic that will flag some genuinely large legitimate accounts; pair it with a manual review step rather than an automatic block, and revisit the multiplier periodically as the customer base's natural spread changes. This same shape (several independent rules, one combined query, each result tagged with which rule fired) extends naturally to a genuinely more complex scenario with suspected corruption across several related tables sharing a composite key, where you'd add one EXISTS-based rule per table-pair relationship you need to validate.