SQL-Based Data Cleaning and Anomaly Detection Questions

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.

When counts agree but sums don't, the row-level cardinality is right, so the defect is almost certainly in how a VALUE is computed or transformed on one side, not in which rows exist; that narrows the investigation to a specific, shorter list of candidate causes.

The prioritized checks

1. Currency conversion: confirm both sides are using the SAME exchange rate for the SAME date, not today's rate applied retroactively.
2. Rounding: check whether many small per-row roundings (to the nearest cent, say) are compounding into a systematic drift in one direction, rather than canceling out as random rounding would.
3. Late-arriving records: a record that arrived after one side's aggregation window closed but before the other's would explain a sum mismatch despite matching counts, if the late record's inclusion doesn't affect the row count comparison window the same way.
4. Transformation bug: a mis-applied business rule (a discount subtracted twice, a refund double-counted) changes a value without changing whether the row exists at all.

Worked example

A billing system reports a $2.4M total for the month while the analytics warehouse reports $2.376M, a roughly 1% gap, with row counts matching exactly on both sides; checking currency conversion first (cheapest to verify) and finding it's not the cause, then checking rounding (a known but usually negligible source) and finding the compounding is real but too small to explain the gap, points the investigation toward a transformation bug as the more likely remaining explanation.

Trade-offs and pitfalls

Systematic under-reporting from rounding or truncation is a subtler variant worth naming explicitly: if multiple source rows get collapsed or rounded during transformation in a way that consistently rounds DOWN rather than symmetrically, the resulting bias compounds across thousands of rows into a material total-level discrepancy, even though any single row's rounding error looks negligible in isolation. Deciding what tolerance is "acceptable" (a persistent but small mismatch, say around 0.1%) versus what demands a fix is itself a judgment call that should be made explicit as an error budget, not left as an ad-hoc threshold picked in the moment.

Quantify the bias by comparing the affected segment against a control segment that wasn't touched by the change, rather than trusting the overall aggregate, since a silent undercount for one cohort can be small enough in the total to hide behind normal day-to-day noise while still being large relative to that specific cohort.

Approach

Compare the affected cohort's metric trend before and after the suspected change against an unaffected control cohort's trend over the same period; a divergence that appears only in the affected cohort, and only after the change, is strong evidence the change itself caused the undercount rather than a coincidental broader shift.

Worked example

An instrumentation change duplicates events for roughly 5% of users in one specific country; the overall engagement metric barely moves (that country is a small share of total traffic), but a country-level breakdown shows engagement in that one country jumping in a way no other country shows, and the jump timing lines up exactly with the instrumentation deploy date, pinpointing both the cause and the affected scope.

Trade-offs and pitfalls

Remediation has to address both the historical data and the ongoing pipeline: backfilling or annotating the historically affected rows (rather than silently overwriting them, so the correction itself stays auditable) and re-instrumenting or reverting the change that caused the bias going forward. The preventive lesson is usually the more valuable output of this exercise: whatever check would have caught this sooner (a per-segment, not just an aggregate, monitoring rule) should be added, since the whole reason this class of defect is dangerous is that it hides behind an aggregate that looks fine.

The direct answer: for every column, report the total row count, the null count, and the null percentage in one pass, using conditional aggregation so a single scan produces the whole report rather than one query per column.

Structured approach

```sql
SELECT
COUNT() AS total_rows,
COUNT() - COUNT(email) AS email_nulls,
ROUND(100.0 * (COUNT() - COUNT(email)) / COUNT(), 2) AS email_null_pct,
COUNT() - COUNT(signup_date) AS signup_date_nulls,
ROUND(100.0 * (COUNT() - COUNT(signup_date)) / COUNT(), 2) AS signup_date_null_pct
FROM users;
```
This relies on `COUNT(column)` skipping NULLs while `COUNT()` counts every row, so the difference is exactly the null count per column, with no extra join or subquery. Whether an empty string or a whitespace-only value should count as "missing" alongside a true NULL is an assumption you should state explicitly, for example by also reporting `COUNT(*) FILTER (WHERE TRIM(email) = '')` as a separate signal rather than folding it silently into the NULL count.

Worked example

On a 100-row `users` table with 5 NULL emails and 100 non-NULL `signup_date` values, the query returns `total_rows=100, email_nulls=5, email_null_pct=5.00, signup_date_nulls=0, signup_date_null_pct=0.00`.

Trade-offs and pitfalls

Writing one `SELECT` per column does not scale past a handful of columns or tables. In practice, this check is generated programmatically: iterate the table's column list (from the catalog/information_schema) and template out one conditional-aggregation expression per column into a single generated query, so you get one profiling pass for the whole table, and this pattern is reused unchanged whether the table has 10 columns or 200. On very large tables, running this nightly against a fixed random sample (rather than a full scan) is usually accurate enough for a null-rate signal and far cheaper.

Try to safely cast the cleaned string and flag whatever doesn't survive the cast, rather than trying to enumerate every possible malformed pattern up front.

Approach

SELECT id, amount_text,
  CASE
    WHEN REGEXP_MATCHES(amount_text, '^\s*\(.*\)\s*$')
      THEN -1 * TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.]', '', 'g') AS DECIMAL(12,2))
    ELSE TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.\-]', '', 'g') AS DECIMAL(12,2))
  END AS amount_clean,
  (CASE
    WHEN REGEXP_MATCHES(amount_text, '^\s*\(.*\)\s*$')
      THEN -1 * TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.]', '', 'g') AS DECIMAL(12,2))
    ELSE TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.\-]', '', 'g') AS DECIMAL(12,2))
  END) IS NULL AS amount_error
FROM sales_raw;

Parenthesized amounts like '(500.00)' are detected up front by matching the whole string against a leading '(' and trailing ')', and are negated after stripping the parens themselves; anything else falls through to the original strip-and-cast path. Stripping everything except digits, the decimal point, and a minus sign handles common currency-symbol and thousands-separator noise ('$1,234.56' becomes '1234.56'); TRY_CAST (or SAFE_CAST, depending on dialect) returns NULL instead of erroring on anything that still isn't a valid number after cleaning, which is exactly the signal you want to flag as amount_error.

Worked example

"$1,234.56" cleans to "1234.56" and casts successfully; "(500.00)", the parenthesized-negative accounting convention, is detected by the leading/trailing-paren check and casts to -500.00 rather than silently losing its sign (verified: without this check the same string casts to a wrong, positive 500.00 with no error flag raised); "1.234,56" (a European locale convention) cleans to "1.23456" under this simple stripping rule and casts to the WRONG number rather than failing outright, which is the real danger of a purely mechanical strip-and-cast approach: it can silently produce a plausible-looking but wrong value instead of a clean failure.

Trade-offs and pitfalls

Because of that European-locale trap, a single global stripping rule is only safe once you've confirmed all your data uses ONE locale convention; if multiple locale conventions are genuinely mixed in the same column, you need to detect which convention a row is using (for example, by checking whether a comma or a period appears last) before choosing how to strip it, rather than applying one rule blindly to everything. The parenthesized-negative case is the same class of risk: any format convention the strip-and-cast approach doesn't explicitly detect will silently produce a plausible but wrong number rather than a clean failure, so before shipping this kind of check, enumerate every convention actually observed in a sample of the real data (currency symbols, thousands separators, parens, locale decimal/thousands swaps) and add an explicit branch for each one rather than assuming the stripping regex alone covers them. The same casting-and-flagging pattern reused here for currency strings applies unchanged to a column that changed storage type over time (VARCHAR with noise in old partitions, clean NUMERIC in new ones): identify the bad old-partition rows with the same TRY_CAST-is-NULL check before planning the backfill.