SQL-Based Data Cleaning and Anomaly Detection Questions

Data validation checks whether the data conforms to an expected shape: schema, types, required fields, and known business rules; anomaly detection checks whether the data's actual VALUES look statistically unusual relative to a baseline, even when everything conforms to the expected shape perfectly.

The distinction with examples

• Validation catches: a required field that's suddenly NULL, a column whose type changed, a foreign key that no longer resolves. These are checks against a known, static contract.
• Anomaly detection catches: a revenue number that's technically well-formed and passes every validation check, but is three times higher than any day in the past year. There's no schema violation here, just a statistical surprise.

Worked example

A dashboard's daily revenue field could be perfectly non-NULL, correctly typed as a decimal, and within its declared valid range (validation all passes), while still being a genuine anomaly because it's ten times the typical value for that day of week; conversely, a NULL creeping into a required field is a validation failure regardless of whether the resulting NULL count itself looks statistically unusual.

Trade-offs and pitfalls

The two are complementary, not competing: validation is cheap, deterministic, and should run on every load as a hard gate; anomaly detection is probabilistic, needs a baseline and tuning, and is better suited to alerting a human for judgment rather than blocking a pipeline outright, since a genuine anomaly might be a real, wanted business event rather than a defect. In production, you generally want validation failures to hard-fail a critical load, and anomaly-detection flags to soft-alert for review, reflecting the difference in how confident each signal is that something is actually wrong.

An outlier is a real, legitimate data point that happens to be extreme; an anomaly is a data point that exists because something went wrong. The two categories overlap in appearance but call for opposite treatment.

Distinguishing them

A customer whose spending is genuinely 50 times the median because they run a large business is a legitimate outlier: keep it in the data, since a model or report that silently drops it distorts reality by pretending large customers don't exist. A sensor reading of `-999` because a device reset to its sentinel default is an anomaly: it doesn't describe anything real and should be quarantined or corrected, not treated as valid signal.

Worked example

A single day's revenue spiking 5x during a well-publicized flash sale is an outlier (a real business event); the exact same 5x spike appearing because a batch job accidentally double-counted every transaction that day is an anomaly (a data-quality defect) with an identical statistical signature but an opposite correct response.

Trade-offs and pitfalls

Because the two can look statistically identical, the fastest reliable triage checklist is: check for known instrumentation failure modes first (a clock reset, a sentinel default value, a timestamp misalignment between two systems), since these have a distinctive, checkable signature (repeated identical sentinel values, an impossible timestamp) that a genuine business event won't share. Only once those specific failure modes are ruled out should you treat an extreme value as a probably-legitimate outlier. Getting this triage wrong in either direction is costly: quarantining a real outlier hides a genuine business signal from decision-makers, while keeping a genuine anomaly in the data corrupts every downstream aggregate that includes it.

Pick the cheapest method that actually clears your false-negative bar: exact-key matching is fast and safe but only catches literal duplicates; deterministic multi-field matching (for example, matching on a normalized combination of name, phone, and address) catches more real duplicates at a modest cost; fuzzy/probabilistic matching catches the most but is the most expensive and the hardest to tune.

Comparing the three

Worked example

A customer record for "John Smith, j.smith@co.com" and one for "JOHN SMITH, jsmith@co.com " look like two different people under an exact-key match on email, become the same person once you normalize casing and whitespace, and a fuzzy match would additionally catch "Jon Smyth, jsmith@co.com" as a likely duplicate via name-similarity scoring, at the cost of also occasionally flagging two genuinely different people with similar names.

Trade-offs and pitfalls

Once you decide two rows ARE duplicates, choosing which one survives is a separate decision: keeping the earliest-created row preserves history, keeping the most-recently-updated row preserves the freshest data, and merging non-conflicting fields from both preserves the most complete record but requires an explicit precedence rule for genuinely conflicting fields. In practice, teams run exact-key matching cheaply on every load and reserve fuzzy matching for periodic, human-reviewed batch runs, rather than running the expensive method on every write.

Z-score measures how many standard deviations a value sits from the mean; it assumes the underlying data is roughly symmetric and unimodal, and it degrades badly once that assumption breaks.

The three tools

$$z = \frac{x - \mu}{\sigma}$$

• Z-score: fast, one-pass, but the mean and standard deviation it depends on are themselves pulled by the very outliers you're trying to detect, so a single extreme value can inflate the denominator and mask itself (and other genuine outliers).
• IQR: uses the median-based Q1/Q3 instead of the mean/standard deviation, so it stays robust even when one value is extreme; the trade-off is needing a full sort (or an approximation) to compute quantiles, which is a bit more expensive than a mean and standard deviation.
• Percentile-based: simplest conceptually (flag anything outside, say, the 1st and 99th percentile), easy to explain to a non-technical stakeholder, but arbitrary in the sense that the cutoff percentiles are chosen rather than derived from the data's own spread.

Worked example

On five clean values plus one huge outlier, the z-score method can genuinely fail to flag the outlier, because the outlier itself inflates the standard deviation used to judge it; the IQR method, computed from the median-anchored quartiles, correctly flags it because a single extreme point barely moves Q1 or Q3.

Trade-offs and pitfalls

Prefer IQR (or median absolute deviation) over z-score specifically when you expect the data to already contain the outliers you're hunting for, which is nearly always true in a real anomaly-detection setting; z-score is better suited to data you're confident is already roughly clean and you're just watching for a NEW deviation. For genuinely heavy-tailed data (revenue, latency, anything with a long right tail even in normal operation), median absolute deviation (MAD) is often the most robust of the three, since it's anchored entirely on the median rather than any mean-based statistic.

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.