SQL-Based Data Cleaning and Anomaly Detection Questions

A denormalized table copies attributes from a source of truth at write time, so those copies can silently drift stale as the source changes; detect this by re-joining back to the current source and comparing, and separately watch for the case where the parent row itself has been marked deleted but is still being referenced.

Approach

```sql
SELECT f.order_id, f.customer_email AS denorm_email, c.email AS current_email
FROM orders_flat f
JOIN customers c ON f.customer_id = c.customer_id
WHERE f.customer_email IS DISTINCT FROM c.email;
```
`IS DISTINCT FROM` treats two NULLs as equal and a NULL-vs-value pair as different, avoiding the classic pitfall where a plain `!=` silently drops rows where either side is NULL.

Worked example

If a customer's email changes from "old@x.com" to "new@x.com" in the source table, but the denormalized `orders_flat.customer_email` still says "old@x.com" for their past orders, this query surfaces exactly those rows, letting you decide whether historical orders should reflect the email at order time (often correct) or be refreshed to the current value (occasionally needed for support tooling).

Trade-offs and pitfalls

Not every drift is a bug: a denormalized table often intentionally freezes an attribute AS OF the transaction date (the price or customer segment "as it was" when the order happened), so "stale" is sometimes the correct, desired behavior. The related but distinct failure is a soft-deleted parent (via a `deleted_at` timestamp) still being actively referenced weeks later; that pattern deserves its own check and its own remediation options (archive the child rows, reassign them, or simply flag them for review), since it usually signals a real upstream cleanup gap rather than expected historical framing.

Generate one row per column with its key profiling statistics, using conditional aggregation so a single pass over the table produces the whole report, and design it so the same shape can be templated across many tables rather than hand-written per table.

Approach

```sql
SELECT 'sale_amount' AS column_name,
COUNT() AS total_rows,
COUNT() - COUNT(sale_amount) AS null_count,
ROUND(100.0 * (COUNT() - COUNT(sale_amount)) / COUNT(), 2) AS null_pct,
COUNT(DISTINCT sale_amount) AS distinct_count,
MIN(sale_amount) AS min_val, MAX(sale_amount) AS max_val,
COUNT(*) FILTER (WHERE sale_amount < 0) AS invalid_count
FROM sales;
```
Extending this to every column in the table is a mechanical template expansion (one `UNION ALL` block per column, or generated programmatically from the catalog's column list), not a fundamentally different query per column.

Worked example

For a `sales` table with 10,000 rows, 50 NULL amounts, 9,800 distinct amounts, and 3 negative amounts, the report reads: `total_rows=10000, null_count=50, null_pct=0.50, distinct_count=9800, invalid_count=3`, giving a single-glance health summary a reviewer can act on immediately.

Trade-offs and pitfalls

Rolling several of these per-column signals (null rate, duplicate rate, distinct-count stability) into ONE normalized 0-100 score per table is a natural next step, but the weighting is a genuine judgment call, not a mechanical derivation, since a 5% null rate on a rarely-used optional field shouldn't count the same as a 5% null rate on a required key column; document the weighting rationale explicitly rather than treating the resulting number as objectively derived. The same profiling shape adapts directly to ranking ingestion batch FILES by error rate (rather than columns within one table) to quickly spot which specific files need reprocessing, and to measuring inter-annotator disagreement on a labeled dataset as a distinct but related data-quality signal, when the thing you're profiling is label consistency rather than column-level statistics.

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.

Route every observed value through a mapping table rather than a chain of CASE statements, so extending the mapping is a data change (insert one row) rather than a code change (deploy a new query).

Approach

```sql
CREATE TABLE gender_mapping(observed_value VARCHAR, canonical_value VARCHAR);
INSERT INTO gender_mapping VALUES ('M','Male'), ('Male','Male'), ('male','Male'), ('FEMALE','Female'), ('f','Female');

SELECT u.user_id, COALESCE(m.canonical_value, u.gender) AS normalized_gender,
(m.canonical_value IS NULL) AS unmapped
FROM user_profiles u
LEFT JOIN gender_mapping m ON u.gender = m.observed_value;
```
Any observed value with no matching mapping row comes through as `unmapped = true`, giving you a concrete, queryable to-do list of values the mapping table still needs to cover, rather than silently passing an unrecognized value through unchanged.

Worked example

Given observed values `'M', 'Male', 'FEMALE', 'nonbinary'`, the first three map cleanly to `'Male'`/`'Female'`; `'nonbinary'` comes through with `unmapped = true`, flagging it for a human to add a proper mapping row, rather than the query silently either dropping it or passing it through unmapped without anyone noticing.

Trade-offs and pitfalls

Store the mapping table as its own versioned, reviewable artifact, not embedded inline in a view definition, so a business analyst (not just an engineer who can edit SQL) can safely propose additions. The same pattern generalizes directly to normalizing legacy product SKUs to canonical product IDs before aggregating a sales report; the risk in both cases is the same, an aggregate built on top of un-normalized categories silently under- or over-counts a "canonical" bucket by however many rows are still using an unmapped variant.

Instead of re-scanning the whole history every time you suspect drift, track the CUMULATIVE difference day by day and find the first date where that running total crosses your tolerance, which pinpoints where an investigation should start rather than requiring you to eyeball a long time series.

Approach (verified by execution)

```sql
WITH diffs AS (
SELECT d, source_amt, target_amt, source_amt - target_amt AS daily_diff
FROM recon
),
cum AS (
SELECT d, daily_diff,
SUM(daily_diff) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_diff,
SUM(source_amt) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_source
FROM diffs
)
SELECT d, cum_diff, cum_source, ROUND(cum_diff * 100.0 / cum_source, 2) AS pct_cum_diff
FROM cum
WHERE cum_diff * 1.0 / cum_source > 0.02
ORDER BY d LIMIT 1;
```

Worked example (verified by execution)

Over five days with small, growing daily discrepancies (0, 1, 2, 50, 100 out of roughly 1000 per day), the cumulative percentage difference crosses a 2% threshold for the first time on day five (3.06%), even though day four's own daily discrepancy (50) was already the visibly larger jump; `LIMIT 1` on the ordered result correctly returns exactly that first crossing date rather than every subsequent day that also exceeds the threshold.

Trade-offs and pitfalls

A cumulative running total is sensitive to the STARTING point of the window: if you run this over "all history," a genuinely resolved discrepancy from months ago can still show up as inflating today's cumulative percentage, even though the two systems have agreed perfectly every day since; consider resetting the cumulative sum at a meaningful reconciliation checkpoint (like the last date both systems were confirmed to agree) rather than always summing from the dawn of time. Once you have the first-crossing date, that becomes the natural starting point for a narrower, partition-level investigation into what changed on or just before that specific day.