Data Quality and Validation Questions

Direct answer

Three common statistical methods for flagging outliers in a numeric column are the z-score (how many standard deviations from the mean a value is), the interquartile-range (IQR) fence (flag anything outside 1.5x the IQR beyond the 25th/75th percentile), and a robust alternative based on the median absolute deviation (MAD), which is far less sensitive to the very outliers you are trying to detect than either of the first two.

Structured elaboration

• Z-score: assumes an approximately normal distribution; both the mean and standard deviation used in its own formula are themselves distorted by the outlier being measured, which weakens its sensitivity exactly when outliers are large or numerous (a masking effect). Computational cost: cheapest of the three, the mean and standard deviation are both computed in a single pass over the data, O(n) time and O(1) extra memory, and this single pass is trivially streamable (both can be updated incrementally as new values arrive) even on a huge table.
• IQR fence: distribution-free and robust to a small number of extreme outliers since it is based on percentiles, but the fixed 1.5x multiplier is a heuristic that can flag too aggressively on a genuinely skewed (not just outlier-contaminated) distribution. Computational cost: needs the 25th and 75th percentiles, which in the exact case requires a sort or a selection algorithm, O(n log n) for a full sort or O(n) on average with a quickselect-style approach; at very large scale this is usually done with an approximate-percentile data structure (a sketch) rather than an exact sort, trading a small amount of precision for bounded memory.
• MAD-based modified z-score: uses the median and median absolute deviation instead of the mean and standard deviation, both of which are far more robust to outliers, making it the most reliable of the three for a data-quality context where you specifically expect the sample to contain what you're testing for. Computational cost: needs the median twice, once for the data itself and once for the absolute deviations from that median, so it costs roughly the same as the IQR fence (a sort or selection step, generally O(n log n) or O(n) with quickselect), done twice rather than once, and more than the single-pass z-score. MAD itself can also mislead: on a sample with many tied or duplicate values, or a very small or coarsely-discretized sample, the median absolute deviation can collapse toward zero (more than half the values sitting exactly at the median), which makes the modified z-score's denominator tiny and inflates every deviation into an apparent extreme outlier, the opposite failure mode from the mean/std masking effect above but just as capable of misleading.

Worked example

For the sample [12, 14, 13, 15, 12, 14, 13, 90, 15, 13, 12, 14] (one clear outlier, 90), computed by execution: mean = 19.75, std = 22.15, giving the outlier a z-score of only 3.17, barely over the conventional threshold of 3, because the outlier itself has inflated the mean and standard deviation used to judge it. The IQR fence (Q1=12.75, Q3=14.25, fence = [10.5, 16.5]) correctly flags 90 as outside the fence. The MAD-based modified z-score uses the formula $0.6745 \times \frac{|x - \text{median}|}{\text{MAD}}$ (the constant 0.6745 rescales MAD so it is comparable to a standard deviation under a normal distribution); plugging in median=13.5, MAD=1.0, and x=90 gives $0.6745 \times \frac{|90 - 13.5|}{1.0} = 0.6745 \times 76.5 \approx 51.6$, dramatically clearer than the z-score's 3.17, because the median and MAD are barely moved by a single extreme value. This gap between 3.17 and 51.6 for the exact same outlier is the concrete illustration of why the z-score is fragile in exactly the situation it is meant to catch.

Trade-offs and pitfalls

Z-score is misleading on a small sample or a sample with more than one or two outliers, since the mean and standard deviation degrade fastest exactly when you need them most; IQR is safer but its 1.5x multiplier is a heuristic, not a statistically-derived constant, and can over-flag a genuinely skewed (e.g. revenue, latency) distribution that is not actually anomalous, just non-normal. A practical rule: default to the MAD-based method for automated production monitoring, and reserve the z-score for exploratory, one-off analysis where a human is looking at the output.

Direct answer

For a small, nonzero fraction of NULLs in a required numeric field like sales_amount, the decision between dropping, imputing, quarantining, or alerting-and-leaving-as-is depends on three factors working together: the business criticality of the field (revenue-adjacent fields warrant more caution), whether the nulls are concentrated (suggesting a specific, findable cause) or diffuse (suggesting genuine noise), and how the downstream consumer would actually behave differently under each option; a billing amount and a marketing attribute sit at very different points on that criticality scale, so the same 1.2% gap resolves very differently for each.

Structured elaboration

• Dropping rows: appropriate only when the affected rows are a genuinely negligible fraction and there's evidence the missingness is close to random (not concentrated in one segment, one time period, or one upstream source), since dropping a non-random subset silently biases every aggregate that uses this table, not just the ones directly touching sales_amount.
• Imputing: appropriate for a low-stakes use case where a reasonable default (zero, or a segment-level median) is genuinely defensible and the imputation is clearly documented and flagged, never appropriate for a field feeding financial reporting without an explicit sign-off, since a silently-imputed revenue number is a serious integrity risk.
• Quarantining for review: the safest default for a field this important, hold the affected rows out of the published aggregate while a human investigates the specific cause, and re-include them once resolved rather than guessing.
• Alerting upstream and leaving untouched: appropriate when the root cause is clearly on the producer's side and the fix belongs there, not in your pipeline, publish the aggregate with a visible caveat noting the affected percentage rather than silently either including or excluding the nulls.
• Billing amount versus marketing attribute: if sales_amount feeds a billing or revenue-recognition system, the decision shifts hard toward quarantine or alert-upstream-and-leave-untouched; a silently imputed or dropped billing figure risks becoming an authoritative charge, refund, or reconciliation number that is wrong by construction, so imputation is rarely defensible without explicit finance sign-off, and the same 1.2% would typically trigger a mandatory audit trail and a hard SLA (service-level agreement) on upstream remediation time rather than a routine judgment call. If the field is instead a lower-stakes marketing attribute (for example a campaign-attribution tag), the same 1.2% gap is far lower-stakes: a documented default value, or simply dropping those rows from the specific report that uses it, is usually acceptable, since the downstream consumer is a directional trend dashboard rather than a financial statement, and the cost of being occasionally wrong is materially smaller.

Worked example

1.2% NULL on sales_amount first triggers a quick diagnostic: is the 1.2% spread evenly across all product categories and dates (suggesting genuine, low-stakes noise, perhaps from an edge-case checkout flow), or concentrated in one specific category or one specific day (suggesting a real, findable bug, perhaps a specific payment integration silently failing to log the amount)? If concentrated, that is strong evidence for quarantine-and-investigate rather than a blanket impute-or-drop decision, since concentration usually means there is a specific, fixable root cause worth finding rather than genuine background noise. If this same sales_amount field were instead feeding a customer's monthly invoice rather than a marketing dashboard, the same concentrated 1.2% would additionally trigger a hold on invoice generation for the affected customers specifically, rather than a caveat published alongside an otherwise-complete report, since an incorrect invoice has a direct customer-facing and legal consequence a delayed dashboard does not.

Trade-offs and pitfalls

The single most useful diagnostic step, checking whether the nulls are concentrated or diffuse, is also the one most commonly skipped in favor of jumping straight to a default policy ("we always impute nulls with zero" or "we always drop them"). A blanket policy applied without this check will sometimes silently mask a real, fixable upstream bug behind a mechanical default, converting a findable root cause into invisible, permanent noise in every future report, and applying the same blanket policy to a billing field that would be reasonable for a marketing field is the version of this mistake with the highest real-world cost.

Direct answer

A rate computation like conversion_rate = clicks / impressions needs an explicit, documented answer for three distinct edge cases, not just a blanket "avoid division by zero": a NULL numerator should propagate to a NULL result, a zero denominator with a zero numerator is a legitimate rate of 0.0 (nothing happened, but nothing anomalous either), and a zero denominator with a nonzero numerator is a data anomaly (more clicks than impressions logged) that should surface as NULL or a flag, not be silently computed as infinity or coerced to zero.

Structured elaboration

A robust expression handles the three cases explicitly with a CASE statement rather than relying on a bare NULLIF, which only protects against the divide-by-zero error and does not distinguish the "0/0 is fine" case from the "nonzero/0 is broken" case.

SELECT campaign_id,
  CASE
    WHEN clicks IS NULL THEN NULL
    WHEN impressions = 0 AND clicks = 0 THEN 0.0
    WHEN impressions = 0 THEN NULL              -- anomaly: clicks without impressions
    ELSE clicks * 1.0 / impressions
  END AS conversion_rate,
  (impressions IS NOT NULL AND impressions > 0) AS has_data
FROM campaign_stats;

The added has_data column exists specifically to reconcile the 0.0 result above with the requirement that a currently-empty group render as an explicit no-data state rather than a misleading zero: impressions = 0 AND clicks = 0 is mathematically a valid rate of 0.0 (nothing is undefined), but a campaign with zero logged impressions has not actually run yet, it is an empty group, not a group that measurably converted at 0%. Carrying has_data alongside conversion_rate lets every downstream layer distinguish the two: render "no data" when has_data is false, and render the numeric conversion_rate (including a genuine 0%) only when has_data is true and impressions were actually served but nothing converted.

Worked example

Run against a DuckDB fixture with four campaigns, (1, clicks=10, impressions=100), (2, clicks=0, impressions=0), (3, clicks=NULL, impressions=50), (4, clicks=5, impressions=0), this query returns (1, rate=0.1, has_data=true), (2, rate=0.0, has_data=false), (3, rate=NULL, has_data=true), (4, rate=NULL, has_data=false) (verified by execution). Campaign 4 is the interesting case: 5 clicks against 0 logged impressions cannot mathematically be a valid rate, and the query correctly returns NULL rather than either erroring or silently treating it as 0.0, which would hide a real tracking bug. Campaign 2 is the other interesting case, and the one the has_data column exists for: it mathematically computes to 0.0, but has_data=false correctly flags that this is an empty campaign with no impressions served at all, not a campaign that ran and converted at a genuine 0%, so a consumer reading has_data alongside conversion_rate renders "no data" for campaign 2 rather than a misleading 0%.

Trade-offs and pitfalls

The same defensive pattern must be applied consistently across every layer that touches the number, not just the SQL: if the SQL correctly returns NULL for the anomalous case but the application code or the dashboard layer coerces NULL to 0 before displaying it (a common default in many charting libraries), the anomaly is silently hidden again one layer up. Application code needs its own explicit handling, distinct from the dashboard layer: in the API or service layer that reads this query's result, keep the conversion_rate field genuinely null (absent, or explicitly null, in the JSON response) rather than coercing it to 0 before it ever reaches a client, and have any downstream code check for null (and for has_data) explicitly, if (rate == null) return "no data", instead of relying on 0 ever being treated as a stand-in for "no data." This matters even when the dashboard layer downstream is implemented correctly, because a second consumer of the same API (a different internal service, a scheduled report) that does not go through that specific dashboard's rendering code would otherwise re-introduce the same coercion bug independently. A senior answer treats "guard against division by zero" as a three-layer problem (SQL, application code, visualization), not a one-line SQL fix, and explicitly decides whether an anomalous zero-denominator case should render as blank/no-data versus an explicit warning icon, rather than letting the visualization layer's default behavior make that decision implicitly.

Direct answer

A silent numeric error discovered weeks or months after the fact requires an incident-response structure with five distinct phases, run roughly in this order but with real overlap: scope the blast radius, quantify business impact, design and validate a backfill, communicate to stakeholders, and put a systemic (not just local) prevention in place.

Structured elaboration

• Scoping: determine exactly which date range, which tables, and which downstream consumers (dashboards, models, exports) were affected. This is often harder than the fix itself, because a bug that has been running for weeks has usually propagated into multiple derived tables, not just the one where it originated.
• Business impact: quantify the actual magnitude, not just "revenue was undercounted," but by how much, on which days, and whether any downstream decision (a forecast, a board number, a customer-facing calculation) was made using the bad data.
• Backfill design and validation: reprocess the affected range from the earliest untouched upstream source, not from the already-corrupted derived tables, and validate the backfilled numbers against an independent source (a finance export, a source-system total) before republishing, never trust the backfill just because the code change looks correct.
• Communication: tell stakeholders and, where financial or compliance-relevant, auditors, what the discrepancy was, its magnitude and duration, and what was corrected, before they discover the historical numbers changed on their own.
• Systemic prevention: the specific bug (a bad join, a rounding error) gets fixed, but the durable prevention is a class of automated check, for example a schema-assertion test or a reconciliation check running continuously, that would have caught this specific class of silent error within hours rather than weeks.

Worked example

A currency-conversion rounding bug accumulates a small error on every transaction, drifting a consolidated reporting total by $50,000 over several months before anyone notices, because each individual transaction's error is too small to trip any single-record validity check. The fix is not just correcting the rounding logic going forward, it requires backfilling every affected historical aggregate using the corrected logic, reconciling the backfilled totals against the audited books, and explicitly disclosing the correction to whoever relied on the pre-correction numbers, including, in this case, external auditors.

Trade-offs and pitfalls

The biggest risk in this whole process is moving too fast on the backfill: a corrected pipeline pushed live before its output has been validated against an independent source can introduce a second, different silent error on top of the first, and because trust in the numbers is already shaken, a second undetected error here is much more damaging than the original bug. Validate before you republish, every time, even under pressure to fix the dashboard quickly.

Direct answer

For a data feed onboarding into an analytics platform, the minimum required checklist covers required-field presence, type and cardinality checks, allowed-value (enum) checks, and referential checks against existing dimensions, each explicitly marked as blocking or warning-only, before any consumer is allowed to build on the feed.

Structured elaboration

• Required-field presence: which columns must never be NULL, and at what threshold does a partial-NULL column become a blocking failure rather than a monitored warning? Recommendation: BLOCK ingestion if any genuinely required field's null rate in the incoming batch exceeds a small fixed threshold, for example 0.5%, since that level of nulls in a supposedly-required field usually signals an upstream break rather than isolated bad rows; WARN (log and continue) below that threshold, since a handful of nulls in an otherwise-healthy batch is more likely isolated source-system noise than a systemic problem.
• Type and cardinality: does each column match its declared type, and are cardinality expectations (a country column should have on the order of 200 distinct values, not 50,000) sanity-checked, since a cardinality blowup is often the first visible symptom of a join key or encoding bug upstream? Recommendation: BLOCK on any outright type mismatch (a column declared numeric that arrives as unparseable text), since that is unambiguous and zero-tolerance; WARN on a cardinality jump (a country column suddenly showing 500 distinct values instead of the expected ~200), since a cardinality jump is a signal worth investigating but is not on its own proof of corruption, it could be a legitimate new set of values.
• Allowed-value checks: for categorical/enum fields, does every value fall within the documented set, and is there a defined action (block, quarantine, or log-and-continue) for a legitimate new category value versus a typo or data-corruption event? Recommendation: WARN and quarantine just the specific rows carrying the unrecognized value, rather than blocking the whole load, since one unexpected category should not hold up every other, valid row in the same batch; escalate to a blocking failure only if the fraction of rows failing the allowed-value check crosses a threshold high enough to suggest the whole column, not just a handful of rows, is affected.
• Referential checks: do foreign keys in the new feed resolve against dimensions the platform already trusts? Recommendation: BLOCK if the dimension a foreign key is supposed to resolve against is entirely missing or empty (a sign the reference data itself failed to load, not a problem with the new feed), since nothing in the feed can be trusted to join correctly in that state; WARN on a small number of individually-orphaned rows (a foreign key value with no matching dimension row) rather than blocking the whole batch, since a handful of not-yet-resolved references is common for a genuinely new but valid entity that simply has not been onboarded into the dimension yet.
• Finally: what is the minimum data-quality issue rate for the past N loads, or minimum sample size, required before the feed is marked production-ready for consumers to build dashboards on?

Worked example

A new third-party CSV lands with a status column expected to be one of {'active','cancelled','pending'}. Row-level enum validation finds 40 rows with status = 'ACTIVE' (wrong case) and 2 rows with status = 'churned' (a genuinely new category the vendor introduced without notice). The checklist treats these differently: the casing issue is a validity bug to fix with a normalization step, while the new category is a schema-drift event that should trigger a review of whether 'churned' needs to be added to the accepted enum and propagated to downstream consumers, not silently dropped or silently accepted.

Trade-offs and pitfalls

The most common failure in practice is treating this checklist as a one-time gate at onboarding and never re-running it: a feed that passed validation on day one can drift (new unexpected values, a type silently changing) on day 200 without anyone noticing, because the checklist was never wired into the ongoing pipeline as a recurring check. The checklist's value is in what it becomes after onboarding, a standing validation suite that runs on every subsequent load, not just a one-time acceptance test.