SQL-Based Data Cleaning and Anomaly Detection Questions

Trigram similarity (or edit distance) turns "how similar are these two strings" into a numeric score you can threshold, letting you find candidate duplicate pairs that don't share an exact key.

Approach

SELECT a.id, b.id, similarity(a.name, b.name) AS name_sim
FROM customers a
JOIN customers b ON a.id < b.id
WHERE similarity(a.name, b.name) >= 0.6
   OR similarity(a.email, b.email) >= 0.9;

a.id < b.id avoids comparing every pair twice and avoids self-matches. Before comparing at all, normalize both sides (lowercase, trim, strip punctuation) so the similarity score reflects genuine content differences rather than superficial formatting noise, and where a composite key is only partially available (say, only region_code but not the full country/region pair), fall back to a best-effort join on the part you do have plus a similarity score on the rest, rather than dropping the row.

Worked example

Measured directly against PostgreSQL's pg_trgm similarity() function: "Jon Smith" vs "John Smith" scores 0.615 (SELECT similarity('Jon Smith','John Smith') returns 0.61538464), "Jon Smith" vs "Jane Smith" scores 0.5, and an unrelated name like "Robert Jones" vs "Jon Smith" scores only 0.15. Two-word person names carry limited trigram overlap even when only one letter differs, so a threshold that sounds conservative, like 0.8, actually excludes the genuine typo pair entirely; a starting cutoff closer to 0.6 is what actually catches it (verified: the query above with >= 0.6 returns the Jon/John Smith pair and correctly excludes both Jane Smith and the unrelated name).

Trade-offs and pitfalls

A naive all-pairs comparison is O(n^2) and becomes infeasible past a few hundred thousand rows; reduce the candidate set first with a cheap "blocking" step (for example, comparing only within the same postal code or the same first three letters of the last name) before running the expensive similarity function. The threshold is a precision/recall dial, but calibrate it against measured similarity scores on a labeled sample from your own data rather than an assumed round number: raising it toward 0.8-0.9 misses genuine typos entirely (lower recall, as the worked example shows), while a lower value like 0.55-0.6 catches more true duplicates at the cost of flagging a few more unrelated people for manual review (lower precision); there is no universally correct number, only a number, verified against real examples, that matches your tolerance for manual review volume versus missed duplicates.

Extract each required key explicitly and test for its absence with a NULL check on the extraction result, since a naive containment check on the raw JSON text can test for the wrong thing entirely (a VALUE appearing anywhere, rather than a specific KEY existing).

Approach (verified by execution, after fixing a real bug)

```sql
SELECT event_id,
json_extract(payload, '$.user_id') IS NULL AS missing_user_id,
json_extract(payload, '$.order_value') IS NULL AS missing_order_value,
(json_extract(payload, '$.order_value') IS NOT NULL
AND TRY_CAST(payload->>'order_value' AS DOUBLE) IS NULL) AS order_value_not_numeric
FROM events;
```
A common early mistake here is reaching for a generic "does this JSON contain this string" function; that tests whether a given VALUE appears anywhere in the document, not whether a specific KEY exists, and produces wrong results (in one verified test, it incorrectly flagged every row as missing a key that most of them actually had). Extracting the specific path with `json_extract` (or the engine's equivalent `->`/`->>` operators) and testing THAT result for NULL is the correct, unambiguous way to check key presence.

Worked example (verified by execution)

Given three rows, one with both required keys present and a numeric `order_value`, one missing `order_value` entirely, and one with `order_value` present but set to the string `"abc"`: the query correctly reports the second row as `missing_order_value = true` and the third as `order_value_not_numeric = true`, with neither incorrectly flagged for the OTHER condition.

Trade-offs and pitfalls

Distinguish "the key is genuinely absent" from "the key is present but its value is NULL" if that distinction matters to your producers; `json_extract` returning NULL can mean either, depending on the payload and the engine, so if the difference matters, check key presence via the engine's key-listing function (like `json_keys`) rather than relying solely on extraction returning NULL. This validation query is the concrete counterpart to a broader schema-inference pass (discovering what keys exist and their typical types across the whole dataset); once you know the expected shape, THIS is the query that enforces it going forward.

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.

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

`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.

Flag any row where a critical column is NULL, and make the output self-explanatory by listing which specific columns are the problem for that row, plus a running total so the scale of the issue is obvious without a second query.

Structured approach

```sql
SELECT ,
CONCAT_WS(',',
CASE WHEN email IS NULL THEN 'email' END,
CASE WHEN signup_date IS NULL THEN 'signup_date' END
) AS null_columns,
COUNT() OVER () AS total_problem_rows
FROM users
WHERE email IS NULL OR signup_date IS NULL;
```
`CONCAT_WS` skips NULL arguments automatically, so a row missing only `email` reports `'email'`, a row missing both reports `'email,signup_date'`, and the window `COUNT(*) OVER ()` gives the total problem-row count on every row without a second aggregate query.

Worked example

Given 3 rows where row 1 has both fields, row 2 is missing `email` only, and row 3 is missing both: the query returns 2 rows (row 2 and row 3), with `null_columns` = `'email'` and `'email,signup_date'` respectively, and `total_problem_rows = 2` on both.

Trade-offs and pitfalls

Not every column deserves this treatment: a column with expected, legitimate missingness (an optional phone number, say) will drown out the genuinely critical failures if it's included in the same flag. A useful refinement is a magnitude threshold, only surfacing this as an actionable alert once a column's missing rate crosses something like 5% of recent rows, rather than treating any single NULL as worth a page. Also be explicit about whether an empty string should be treated the same as NULL here; silently missing that case understates the real problem count.