SQL-Based Data Cleaning and Anomaly Detection Questions

Compute each category's share of the total in two periods, and flag any category whose share changed by more than a threshold number of percentage points; for a more rigorous signal than a simple threshold, a chi-square-style test or KL-divergence can quantify how surprising the overall shift is.

Approach (verified by execution)

```sql
WITH totals AS (SELECT period, SUM(cnt) AS total FROM cat_period GROUP BY period),
shares AS (
SELECT c.period, c.category, c.cnt * 100.0 / t.total AS pct
FROM cat_period c JOIN totals t ON c.period = t.period
)
SELECT curr.category, prev.pct AS prev_pct, curr.pct AS curr_pct, curr.pct - prev.pct AS pct_point_change
FROM shares curr JOIN shares prev
ON curr.category = prev.category AND curr.period = 'current' AND prev.period = 'prior'
WHERE ABS(curr.pct - prev.pct) > 10;
```

Worked example (verified by execution)

A category whose share moved from 20% to 40% between two periods (while two other categories moved by exactly 10 percentage points, right at the boundary) is the one flagged by a strict ">10pp" rule, correctly distinguishing a material shift from ones sitting right at the threshold.

Trade-offs and pitfalls

A raw percentage-point threshold treats a shift from 1% to 11% (a 10x relative change) the same as a shift from 40% to 50% (a 25% relative change), even though the first is usually far more surprising statistically; a chi-square-style test (comparing observed counts against what would be expected if the distribution hadn't changed) captures that nuance, at the cost of extra aggregation steps and some real limitations doing significance testing purely in SQL (no readily available p-value function in most engines, so you typically compute the test statistic and compare it against a precomputed critical-value table). Kullback-Leibler divergence is a second, information-theoretic distance measure for the same comparison, useful when you want one summary number for "how different are these two distributions overall" rather than a per-category threshold check; it needs smoothing for any category with zero observed count in either period, since the KL formula divides by (and takes the log of) each category's probability. The same distribution-shift idea applies just as well to a continuous numeric metric using percentiles or median absolute deviation instead of category shares, when the thing that shifted is a number rather than a category label.

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.

Unnest every top-level key across all rows in one pass, and aggregate per key: its inferred type, how often it's present, and a small sample of actual values, so you understand the real shape of inconsistent JSON before designing a stricter schema for it.

Approach

```sql
WITH keys AS (
SELECT json_keys(payload) AS ks, payload FROM raw_events
),
unnested AS (
SELECT UNNEST(ks) AS key_name, payload FROM keys
)
SELECT key_name,
COUNT(*) AS non_null_occurrences,
MODE(json_type(json_extract(payload, '$.' || key_name))) AS inferred_type,
ARRAY_AGG(json_extract(payload, '$.' || key_name))[1:3] AS sample_values
FROM unnested
GROUP BY key_name
ORDER BY non_null_occurrences DESC;
```
`json_keys` lists every top-level key present in a given row's payload, so unnesting across all rows and grouping by key name gives you, in one pass, exactly which keys exist across the whole dataset and how consistently.

Worked example

Across 1,000 rows, a key like `user_id` might appear in all 1,000 (always typed as an integer), while a key like `promo_code` appears in only 40 rows and shows a MIXED inferred type (sometimes a string, sometimes null-typed because it's absent), immediately telling you `promo_code` is optional and needs type-coercion handling wherever it's consumed downstream.

Trade-offs and pitfalls

`MODE()` on the inferred type picks the MOST COMMON type per key, which can mask real type inconsistency within that key (a numeric field that's occasionally sent as a string); if type consistency itself is what you're validating, report the DISTINCT set of observed types per key, not just the mode, so a key with genuinely mixed types is visible rather than silently summarized away. This schema-inference query is a useful first pass before designing a stricter, declared validation (checking specific required keys exist and specific values cast correctly), which is a separate, narrower query once you know what "correct" should look like for this payload.

Encode each business rule as its own boolean condition, run them together, and report a count and sample offending rows per rule rather than a single pass/fail, so someone reading the output immediately knows which rule failed and by how much.

Approach

```sql
SELECT
COUNT() FILTER (WHERE amount NOT BETWEEN 0.01 AND 1000000) AS amount_violations,
COUNT() FILTER (WHERE currency NOT IN ('USD','EUR','GBP')) AS currency_violations,
COUNT(*) FILTER (WHERE transaction_date NOT BETWEEN '2000-01-01' AND now()) AS date_violations
FROM transactions;
```
Each `FILTER` clause is an independent rule; adding a fourth rule (say, a required non-negative sensor reading) is a one-line addition rather than a restructuring of the query.

Worked example

Given a table with one transaction of `amount = -5`, one with `currency = 'JPY'`, and one with a valid row: the query reports `amount_violations = 1`, `currency_violations = 1`, and a caller can then run a follow-up query with `WHERE amount NOT BETWEEN ...` to pull the specific offending rows for that rule.

Trade-offs and pitfalls

Choosing the threshold values themselves (why 0.01 to 1,000,000, why those three currencies) should be grounded in the actual business domain, not guessed; consult whoever owns the data and revisit the bounds periodically as the business grows, since a range that was generous last year can become too narrow this year. For a range that's meant to catch "physically impossible" readings (a sensor temperature outside a device's real operating range), be explicit that you're validating a HARD physical constraint, distinct from a softer business-plausibility bound like a KPI's expected range, which should be allowed to widen or shift over time without triggering the same alarm.

Compare two snapshots of the same table's column list and each shared column's null rate, so an added or dropped column, or a column whose behavior quietly changed, surfaces as data rather than something you'd only notice by chance.

Approach

-- Pass 1: structural diff (added / dropped / present) from the catalog
WITH t1_cols AS (SELECT column_name FROM information_schema.columns WHERE table_name='snapshot_t1'),
     t2_cols AS (SELECT column_name FROM information_schema.columns WHERE table_name='snapshot_t2')
SELECT COALESCE(a.column_name, b.column_name) AS column_name,
  CASE WHEN a.column_name IS NULL THEN 'added'
       WHEN b.column_name IS NULL THEN 'dropped'
       ELSE 'present' END AS status
FROM t1_cols a FULL OUTER JOIN t2_cols b ON a.column_name = b.column_name;

The catalog alone only tells you the column LIST changed; it has no idea what null rate a shared column actually has in the data, so a second, generated pass covers that half of the question. Take the columns that came back status = 'present' from pass 1 and template one null-rate comparison per shared column into a single UNION ALL (the same catalog-driven code-generation idea used for column-by-column profiling elsewhere in this topic):

-- Pass 2: null rate for each shared column, in both snapshots
-- (generated from the 'present' column list above; shown here for the shared
-- columns 'name' and 'email')
SELECT 'name' AS column_name, 't1' AS snapshot,
  ROUND(100.0 * (COUNT(*) - COUNT(name)) / COUNT(*), 2) AS null_pct
FROM snapshot_t1
UNION ALL
SELECT 'name', 't2', ROUND(100.0 * (COUNT(*) - COUNT(name)) / COUNT(*), 2)
FROM snapshot_t2
UNION ALL
SELECT 'email', 't1', ROUND(100.0 * (COUNT(*) - COUNT(email)) / COUNT(*), 2)
FROM snapshot_t1
UNION ALL
SELECT 'email', 't2', ROUND(100.0 * (COUNT(*) - COUNT(email)) / COUNT(*), 2)
FROM snapshot_t2;

Running both passes nightly against information_schema (or its engine-specific equivalent) gives you a structural diff plus a behavioral diff for everything that didn't structurally change, without ever needing to manually track what the schema "should" look like.

Worked example

A column renamed from user_id to userId between two snapshots shows up as one dropped (user_id) and one added (userId) row from pass 1, correctly surfacing the change even though, semantically, no data was actually lost; this is a case where the mechanical detection is correct but needs human judgment to interpret (a rename, not a genuine loss). For the columns that stayed shared, pass 2 might show name steady around 33.33% null in snapshot_t1 versus 25.00% in snapshot_t2, while email jumps from 33.33% null to 75.00% null, a swing worth investigating even though email itself was never added or dropped.

Trade-offs and pitfalls

The most dangerous version of this failure is a SILENT one: a column rename that isn't caught causes every downstream expression referencing the old name to quietly return NULL rather than erroring, and those NULLs then propagate into derived metrics that look plausible but are simply wrong. A shared column whose null rate jumps without any structural change is the other silent failure mode this check exists to catch, since pass 1 alone would report nothing wrong at all for that column. Feed this comparison's output into a sample alert payload (naming the specific column and the specific change type, structural or behavioral) so an on-call engineer gets an actionable signal rather than having to run this query manually after being tipped off that something seems wrong. This concrete, code-level schema-diff query is the counterpart to a higher-level DESIGN question about detecting schema drift as an ongoing process; both matter, but they answer different questions, one is "here's the query," the other is "here's the process around running it."