SQL-Based Data Cleaning and Anomaly Detection Questions

Turn the orphan check from a one-off query into a small monitoring habit: run it daily, keep the count as a time series, and alert on a genuine trend break rather than the presence of any single orphan (which will happen occasionally in any live system with some latency between writes).

Approach

```sql
INSERT INTO orphan_check_history (check_date, orphan_orders, orphan_payments)
SELECT CURRENT_DATE,
(SELECT COUNT() 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)),
(SELECT COUNT() FROM payments p WHERE p.order_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id = p.order_id));
```
Running both the orphan-orders and orphan-payments check in one scheduled job is natural since they share the exact same anti-join pattern against different parent/child pairs.

Worked example

If the orphan-orders count has held steady around 2-3 per day for months (routine timing noise from an async write path) and suddenly jumps to 400 in one day, that jump, not the baseline noise, is the signal worth paging on; a naive "alert if orphans > 0" rule would have been firing every single day and would have been ignored long before the real incident.

Trade-offs and pitfalls

Preventing new orphans is usually cheaper than repeatedly detecting and cleaning them: on the producer side, that means writing the child row inside the same transaction as (or after confirming) the parent write; on the consumer side, it means the ingestion job should reject or quarantine a child row whose parent reference doesn't yet resolve, rather than writing it and hoping a later reconciliation catches it.

Attempt each expected format in turn with a safe parse function, and treat anything that fails every attempt, or parses to an implausible date, as a failure worth surfacing rather than silently coercing.

Approach

```sql
SELECT ts_string,
COALESCE(
TRY_STRPTIME(ts_string, '%Y-%m-%d'),
TRY_STRPTIME(ts_string, '%m/%d/%Y'),
TRY_STRPTIME(ts_string, '%d %b %Y')
) AS parsed_date
FROM raw_logs
WHERE COALESCE(
TRY_STRPTIME(ts_string, '%Y-%m-%d'),
TRY_STRPTIME(ts_string, '%m/%d/%Y'),
TRY_STRPTIME(ts_string, '%d %b %Y')
) IS NULL
OR COALESCE(...) NOT BETWEEN DATE '2000-01-01' AND CURRENT_DATE;
```
Each `TRY_STRPTIME` attempt returns NULL on a format mismatch rather than erroring, so `COALESCE` naturally picks the first format that actually parses, and a row that fails every attempt surfaces as NULL, the same signal used for the impossible-date check.

Worked example

"2024-10-05" parses cleanly on the first attempt; "10/05/2024" fails the first %Y-%m-%d attempt and succeeds on the second; a genuinely malformed value like "sometime last week" fails all three and surfaces as a parse failure requiring manual review.

Trade-offs and pitfalls

"05/06/2024" is genuinely ambiguous between May 6th and June 5th depending on the producer's locale convention, and no amount of clever parsing resolves that ambiguity from the string alone; the honest fix is determining the PREVALENT format for a given source (by checking which interpretation produces plausible, in-range dates across many rows from that source) rather than guessing per row, and flagging genuinely ambiguous rows for manual disambiguation instead of silently picking one interpretation.

Compute each user's own historical mean and standard deviation using window functions restricted to PRIOR rows only, so a transaction is judged against what was normal for that user before it happened, not including itself in its own baseline.

Approach (verified by execution)

```sql
WITH stats AS (
SELECT transaction_id, user_id, amount, occurred_at,
AVG(amount) OVER (PARTITION BY user_id ORDER BY occurred_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS running_mean,
STDDEV_SAMP(amount) OVER (PARTITION BY user_id ORDER BY occurred_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS running_std,
COUNT(*) OVER (PARTITION BY user_id ORDER BY occurred_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prior_count
FROM transactions
)
SELECT transaction_id, user_id, amount,
(prior_count >= 5 AND running_std > 0 AND amount > running_mean + 3 * running_std) AS is_anomalous
FROM stats;
```
The frame `ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING` excludes the current row from its own baseline, which matters: including the current (possibly huge) transaction in the mean and standard deviation used to judge it would pull the threshold up and could make even a genuine anomaly look "within range."

Worked example (verified by execution)

For a user with five prior transactions around 9-13 and a sixth transaction of 500: the running mean and standard deviation at that sixth row (computed only from the first five) are roughly 11 and 1.58, so the threshold is about 15.7, and 500 correctly flags as anomalous. A second user with only two prior transactions is correctly excluded from flagging entirely, since `prior_count >= 5` isn't met.

Trade-offs and pitfalls

Excluding users with too little history avoids a specific false-positive trap: a second transaction that happens to differ even slightly from a single prior one can produce a technically enormous z-score, since the standard deviation of two points is a poor estimate. The threshold of "5 prior transactions" is a reasonable, defensible floor, not a universal constant; some teams require significantly more history before trusting a per-user baseline at all. Communicate to business stakeholders up front that any fixed-sigma rule WILL produce some false positives by design (that's the nature of a statistical threshold), and pair the flag with a simple mechanism for a reviewer to mark a specific alert as "not actually anomalous," feeding that feedback back into future threshold tuning rather than silently living with noise.

Give each check a name and turn its result into an explicit PASS/FAIL row rather than a bare number, so the output reads like a test report a human can scan in seconds.

Approach

```sql
SELECT 'row_count_reconciliation' AS check_name, 10000::TEXT AS expected, COUNT()::TEXT AS observed,
CASE WHEN COUNT() = 10000 THEN 'PASS' ELSE 'FAIL' END AS status FROM orders
UNION ALL
SELECT 'null_rate_order_date', '0', COUNT() FILTER (WHERE order_date IS NULL)::TEXT,
CASE WHEN COUNT() FILTER (WHERE order_date IS NULL) = 0 THEN 'PASS' ELSE 'FAIL' END FROM orders
UNION ALL
SELECT 'referential_integrity_customers', '0', COUNT()::TEXT,
CASE WHEN COUNT() = 0 THEN 'PASS' ELSE 'FAIL' END
FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);
```
Each check is a self-contained `SELECT` stitched together with `UNION ALL`, so adding a sixth check is adding one more branch, not restructuring the whole thing.

Worked example

A run with a correct row count, zero null order_dates, but 2 orders referencing a missing customer produces three result rows: two `PASS` and one `FAIL` with `observed = '2'`, immediately pointing at referential integrity as the specific thing that broke this load.

Trade-offs and pitfalls

The "expected" value for row-count reconciliation shouldn't be a hardcoded constant in practice; it should come from the prior day's count (or a rolling baseline) computed dynamically, or every legitimate day-to-day change in volume will register as a false FAIL. This tabular check-results format is also naturally what you'd persist into a `DQ_results` history table (run_id, check_name, status, observed_value, run_time), turning a one-off validation query into the seed of a proper trend-monitoring dataset.

Situation: I was reviewing a weekly BI dashboard when I noticed a metric that had held steady for months suddenly moved sharply overnight, with no corresponding announcement of a business change.

Task: I needed to determine, quickly and credibly, whether this was a genuine business shift worth reporting to stakeholders or a data-quality defect that would embarrass the team if reported as real.

Action: I started with the cheapest checks first: re-ran the metric's definition independently to rule out a dashboard-rendering bug, then checked whether the underlying row counts for that day looked normal, then checked whether a recent schema or pipeline deploy coincided with the timing of the shift. That last check surfaced a recent change to an upstream join that had started silently duplicating a subset of rows. I documented each check and its result as I went, including the ones that came back clean, so the investigation was reproducible by someone else.

Result: I traced the anomaly to the join change, worked with the pipeline owner to fix it and backfill the affected days, and proposed a lightweight regression test on that join's row-multiplication behavior so a similar defect couldn't ship silently again. I also shared a short writeup with stakeholders explaining what happened, what looked wrong initially, and what the corrected numbers actually were, rather than quietly fixing it and letting the earlier report stand uncorrected.

Trade-offs and pitfalls

The value in this story isn't just "I found the bug," it's the discipline of checking cheap-to-expensive and documenting assumptions along the way, since that's what makes the investigation defensible and repeatable rather than a one-off act of intuition. Being transparent with stakeholders about the earlier (wrong) numbers, rather than silently correcting them, is what actually builds trust in the data over time.