Data Quality and Validation Questions

Direct answer

Data quality is usually broken into six standard dimensions: completeness (are required values present), accuracy (do values reflect reality), consistency (does the same fact agree across systems), timeliness/freshness (is the data current enough to act on), validity (does a value conform to its expected type, format, or range), and uniqueness (is each real-world entity represented exactly once). When time is short on a newly-landed dataset, I would check completeness and validity first: they are cheap to test mechanically (null rates, type/range checks) and a failure in either one usually invalidates everything downstream before consistency or accuracy even become answerable questions.

Structured elaboration

• Completeness: percentage of required fields that are non-null, and whether entire expected rows/partitions are missing (a silent gap is worse than a visible null, because nothing complains).
• Accuracy: does the value match ground truth? Harder to test directly (you rarely have ground truth on hand), so it is usually inferred indirectly: reconciliation against an authoritative source, or a human spot-check.
• Consistency: the same fact (a customer's status, a revenue total) agrees across two systems or two computations of the same metric. Violations show up as "two dashboards disagree."
• Timeliness/freshness: how stale the data is relative to when a decision needs it. A perfectly accurate dataset that arrives a day late can still cause a bad decision.
• Validity: type, format, and domain-constraint conformance (an email column that is not a string, a percentage outside 0-100, a negative age).
• Uniqueness: no duplicate representation of the same real-world entity, which otherwise inflates counts and corrupts joins.

Prioritization is not fixed: a fraud-detection dataset weighs accuracy and timeliness heavily, while a monthly reporting warehouse weighs consistency and completeness (a late but correct number is usually fine; an inconsistent one is not).

Worked example

A product analytics table events(event_id, user_id, event_type, occurred_at, properties) lands with 2% NULL user_id. That is a completeness issue on a required field. If it also has 500 rows with event_type = 'pruchase' (a typo variant of purchase) sitting alongside 50,000 correctly-spelled purchase rows, that is a validity issue (an unexpected value outside the declared enum), and it will silently undercount purchases in any GROUP BY event_type query without ever violating a NOT NULL constraint.

Trade-offs and pitfalls

The most common mistake is treating "data quality" as a single pass/fail check rather than six independent, sometimes-conflicting dimensions: enforcing strict validity (rejecting anything slightly malformed) can hurt completeness (you lose real records), and chasing perfect accuracy can hurt timeliness (waiting for a slow reconciliation before publishing). A senior answer names the tension explicitly rather than presenting the six dimensions as if maximizing all of them simultaneously were free.

Direct answer

A robust date-normalization approach tries each recognized format in turn against every incoming value, converts a successful parse into a single canonical representation (an ISO 8601 UTC timestamp), and explicitly logs and flags any value that matches none of the recognized formats rather than silently dropping it or guessing.

Structured elaboration

• Maintain an explicit, ordered list of recognized formats (ISO 8601, MM/DD/YYYY, DD Mon YYYY, epoch seconds/milliseconds) and attempt each in turn; a value that matches none of them is logged with the original raw string and routed to a review queue rather than being silently dropped.
• The classic ambiguous case, 03/04/2020, could validly be March 4th (US convention) or April 3rd (many other locales), cannot be resolved by the parser alone; the only safe answer is to pick and document a single, explicit locale convention for the parser (for example, always assume US MM/DD/YYYY unless a source is explicitly known to use a different convention), and never silently guess based on the value itself, since guessing produces a wrong answer that looks exactly like a right one.

Worked example

A small Python normalizer trying ISO, %Y-%m-%d, %m/%d/%Y, %d %b %Y, and %m/%d/%Y %H:%M in order, run against six test values (verified by execution): '2023-12-31', '12/31/2023', and '31 Dec 2023' all correctly normalize to the same canonical 2023-12-31T00:00:00+00:00; '2024-01-02T15:04:05Z' correctly preserves its time component; 'not-a-date' is correctly flagged as unparseable rather than silently coerced to some default; and '03/04/2020', resolved under the assumed MM/DD/YYYY convention, normalizes to March 4th, an outcome that is only "correct" because the convention was explicitly documented and consistently applied, not because the code somehow figured out the right interpretation.

Trade-offs and pitfalls

The single most damaging mistake here is silently guessing at an ambiguous format rather than flagging it: a parser that silently assumes MM/DD/YYYY for every ambiguous date will silently mis-parse every genuinely European-formatted date it encounters (swapping day and month), producing wrong-but-plausible-looking dates that pass every downstream validity check (a valid date, just the wrong one) and are extremely hard to catch after the fact. Explicitly logging every value that required the ambiguous-case assumption, not just the outright unparseable ones, gives you an audit trail to catch this class of silent error if a source's actual convention turns out to differ from what you assumed.

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

Default to deterministic (exact-key) deduplication whenever a stable, trustworthy join key exists, because it is fast, fully explainable, and has zero false-positive risk. Reach for probabilistic (similarity/fuzzy) matching only when no reliable key exists across the records you need to merge, for example customer records arriving from systems with no shared identifier, because it trades perfect precision for the ability to catch real duplicates a key-based approach would simply miss.

Structured elaboration

• Deterministic dedup: match on an exact key (a primary key, a hashed and canonicalized email). Runtime cost is a GROUP BY/DISTINCT/window-function pass, it is trivially explainable to an auditor ("these two rows have the same customer_id"), and it never merges two genuinely different entities. Its failure mode is under-merging: two records for the same real person with typos in the key are left as separate rows.
• Probabilistic dedup: score candidate pairs on similarity across several fields (name, email, phone, address) using a blocking step to avoid comparing every row to every other row, then merge pairs above a tuned threshold. It catches the entity-resolution cases deterministic matching misses, at the cost of needing a labeled sample to tune the threshold, ongoing maintenance as data patterns shift, and a nonzero, tunable false-positive rate (two different people wrongly merged).
• Decision rule: use deterministic dedup as the default first pass on every table. Only add a probabilistic layer where a real business cost is attached to the residual duplicates a key-based pass leaves behind (e.g., double-counted customers inflating revenue-per-customer, or duplicate mail sent to the same household).

Worked example

A retailer discovers that dashboard "unique customers" is inflated because the same person signed up twice with j.smith@gmail.com and johnsmith@gmail.com. Deterministic dedup on customer_id or even canonicalized email finds nothing (the emails are genuinely different strings). A probabilistic pass scoring name similarity ("John Smith" vs "John Smith") at 100, combined with matching phone and shipping address, would surface this pair as a high-confidence merge candidate even though no single field ties them together exactly.

Trade-offs and pitfalls

The single biggest mistake is presenting probabilistic dedup results to stakeholders as if they were as certain as deterministic ones. A responsible answer states the expected false-positive and false-negative rate up front (measured against a labeled sample), keeps a human-in-the-loop review queue for borderline-confidence pairs rather than auto-merging everything, and never silently discards the pre-merge records: an auditable trail of what was merged into what, and why, is what lets a bad merge be reversed later.

Direct answer

A communication about a recurring data-quality issue to executive stakeholders needs a plain-language problem statement, the magnitude of impact, a root-cause summary, a remediation plan with timelines and named owners, and the residual risk that remains after the fix, deliberately leaving out implementation-level technical detail that does not change the decision the executive audience needs to make.

Structured elaboration

• Problem statement: one or two sentences, in business terms, no jargon, stating what happened and what it affects.
• Magnitude: a specific, quantified number (dollars, percentage, duration), not a vague qualifier like "significant," since a specific number is what lets an executive calibrate how much attention this genuinely deserves relative to everything else competing for their time.
• Root-cause summary: enough detail to establish that the cause is understood and the fix is targeted at the real problem, not a full technical post-mortem; the goal is to build confidence that this is being handled competently, not to teach the audience the underlying engineering.
• Remediation plan: concrete dates and named owners, not "we're working on it," since vague timelines are what erode confidence fastest with an executive audience.
• Residual risk: explicitly state what will NOT be fully resolved by this fix, and why, rather than implying the problem is entirely closed; an executive who later discovers an unmentioned residual risk on their own loses more trust than one who was told about it up front.

Worked example

"We identified a recurring issue in [pipeline] that has been undercounting [metric] by approximately 3% since [date], affecting [specific reports/decisions]. Root cause: [one-sentence summary]. We are deploying a fix by [date], owned by [name/team], and will backfill corrected historical figures by [date]. Residual risk: this fix addresses the specific bug found, but the underlying pipeline lacks automated regression testing for this class of error; we recommend a follow-up investment of [scope] to close that gap and prevent a similar issue recurring in a different form." This gives the executive everything needed to decide whether they are satisfied with the response or want to push for the follow-up investment, without requiring them to understand the technical root cause in detail.

Trade-offs and pitfalls

The instinct to lead with reassurance ("it's fixed, don't worry") rather than with the honest magnitude and residual risk is the most common way this kind of communication backfires: if a related issue resurfaces later, or if the residual risk that was omitted turns out to matter, the credibility cost of appearing to have understated the problem the first time is far higher than the short-term discomfort of stating it plainly up front.