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

Computing a metric like daily active users per user's LOCAL day, correctly across a multi-region user base, requires storing timestamps in UTC alongside each user's timezone (captured at event time, not assumed from their current profile setting), and using the IANA timezone database, not a fixed offset, to convert to local dates, since only the IANA database correctly encodes historical daylight-saving and political timezone-rule changes.

Structured elaboration

• Users who travel: a user's "local day" should be computed using the timezone in effect at the moment of each individual event, not their profile's currently-set timezone, since a user who traveled between the event and when the aggregation runs would otherwise have their events misattributed to the wrong local day.
• Users with no stored timezone: define an explicit, documented fallback policy (a default timezone based on IP geolocation at event time, or a global default like UTC with an explicit "unknown timezone" flag) rather than silently guessing or silently excluding these users, since either of those silent choices distorts the aggregate without anyone knowing.
• Ambiguous and nonexistent local times around DST transitions: the "fall back" hour occurs twice in local time and needs a rule for which occurrence an event belongs to (usually resolved by the event's actual UTC timestamp, which is unambiguous, converted through the correct offset for that specific UTC instant); the "spring forward" hour never occurs in local time at all, so any local timestamp claiming to be in that gap is itself invalid and should be flagged.
• Testing strategy: unit tests for the core UTC-to-local-date conversion function using known DST transition dates in several different timezones; integration tests validating a full day's aggregation produces the correct DAU count across a synthetic dataset specifically constructed to include events crossing a DST boundary; and explicit boundary test cases (an event at exactly 1 second before and after a transition) to catch off-by-one-second errors at the edge.

Worked example

An event logged at 2026-11-01 06:30:00 UTC for a user in America/New_York: correctly converting this requires knowing that daylight saving ended that day, so events shortly before and after the transition, despite being close together in UTC, land on different sides of the DST boundary and must use the correct offset (EDT versus EST) in effect at each event's specific UTC instant, not a single fixed offset applied to the whole day. The America/New_York transition instant that day is exactly 2026-11-01 06:00:00 UTC, at which local clocks fall back from 2:00 AM EDT to 1:00 AM EST; converted with the correct IANA rule for each event's exact UTC instant, an event at 2026-11-01 05:59:59 UTC (one second before the transition) lands at 2026-11-01 01:59:59 EDT, while an event at 2026-11-01 06:00:00 UTC (one second later) lands at 2026-11-01 01:00:00 EST, an hour EARLIER in local time despite being only one second later in UTC. A naive converter using a single fixed offset for the whole day would get one of these two events wrong, either mislabeling the pre-transition event as EST or the post-transition event as EDT, and in either case would place it in the wrong local hour. The original anchor event at 06:30:00 UTC falls fully after the transition and converts unambiguously to 01:30:00 EST, which is why it alone does not illustrate the boundary crossing the way the 05:59:59/06:00:00 pair does.

Trade-offs and pitfalls

The recurring implementation mistake is using a fixed, cached UTC offset per user (computed once and reused) rather than looking up the correct offset fresh for each event's specific timestamp using the full IANA timezone database; a fixed offset is wrong immediately after any DST transition and stays wrong until someone notices the DAU numbers look subtly off around every clock change, which is a hard bug to trace back to its root cause without dedicated test coverage for exactly this scenario.

Direct answer

Canonicalize identifiers before joining or deduplicating anything: normalize case, strip formatting punctuation, and apply source-specific rules (Gmail dot-and-plus-address handling for email, E.164 digit-only normalization for phone numbers, standardized abbreviations for addresses). Skipping this step is the single most common reason a dedup or join pipeline silently under-matches, because two representations of the same real value never compare as equal in the first place.

Structured elaboration

• Email: lowercase the whole address, and be deliberate about provider-specific aliasing rules (Gmail ignores dots in the local part and truncates anything after a +, so j.smith+newsletter@gmail.com and jsmith@gmail.com are the same mailbox); most other providers do not have this behavior, so applying it universally is itself a bug.
• Phone numbers: strip all non-digit characters, normalize to a single format such as E.164 (+<country code><digits>), and resolve a default country code for numbers that arrive without one rather than guessing silently.
• Addresses: normalize casing and common abbreviations (St/Street, Ave/Avenue), and decide up front whether you are doing this with local rule-based normalization (cheap, imperfect) or calling an external address-standardization/geocoding API (more accurate, adds latency and cost); most teams use local rules for a fast first pass and only call an external API for records that will be used for something high-stakes like shipping.
• Always preserve the original raw value alongside the canonicalized one. Canonicalization is lossy and occasionally wrong, and an audit trail back to the source value is what lets you debug a bad merge or comply with a correction request later.

Worked example

Two source systems report the same customer as "J.Smith+work@Gmail.com" and "jsmith@gmail.com". A naive equality join sees these as different rows. Applying Gmail-specific canonicalization (lowercase, strip dots in local part, strip everything from + onward) reduces both to jsmith@gmail.com, and the join now correctly matches them. Applying the same dot-stripping rule to a Yahoo or Outlook address, where dots are meaningful, would incorrectly merge two genuinely different mailboxes, which is why the rule must be provider-aware rather than applied blanket.

Trade-offs and pitfalls

Canonicalization rules that are too aggressive (stripping too much, applying provider-specific behavior universally) create false merges; rules that are too conservative leave real duplicates unmatched. The fix is not to pick one extreme, but to version the canonicalization rules explicitly, measure their effect on downstream match rate when they change, and keep the raw value alongside the canonical one so a rule change can be re-applied retroactively rather than requiring a full re-ingestion.

Direct answer

Late-arriving records that have already been aggregated into a published daily figure require a deliberate backfill and reconciliation strategy: detect that a historical aggregate needs correcting, reprocess it without corrupting anything currently being read, preserve an audit trail of the correction, and decide explicitly when a metric stops being reprocessed and is considered final.

Structured elaboration

• Detection: compare the count or sum of records for a given historical partition now against what was originally published; a meaningful positive delta signals late arrivals that were missed the first time.
• Safe reprocessing: recompute the affected partition into a new location (a versioned table or a staging area) rather than updating the live, currently-queried table in place, then atomically swap it in once validated, so no consumer ever reads a partially-updated, inconsistent intermediate state.
• Audit trail: record what changed, when, and by how much for every backfilled partition, both for internal debugging and so a consumer who cached or exported the earlier figure can understand why it changed.
• Deciding "final enough": define an explicit close window (for example, a day's metric is finalized 48 hours after day-end and will not be silently revised after that point, only explicitly corrected via an incident process if a later problem is found) rather than leaving every historical number implicitly provisional forever, which makes reproducibility impossible for anyone building on it.

Worked example

A payments event arrives 3 days late due to a retry-queue backlog on the source system, after the daily revenue figure for that date has already been published and consumed by a downstream forecasting model. The reconciliation job detects the delta (source total now exceeds warehouse total for that date beyond tolerance), recomputes the affected day into a versioned staging table, validates the new total against the source, and atomically swaps it into the live table, along with a logged correction note ("2026-07-01 revenue revised from $142,300 to $142,850 due to late-arriving events, corrected 2026-07-04") that the forecasting team can see and account for. This correction lands 3 days, 72 hours, after publication, past the 48-hour close window described above, so it is explicitly routed through the incident-correction process, not treated as routine same-day reconciliation: the correction note itself is the artifact that process requires (what changed, by how much, and why), and the forecasting team's downstream model is expected to explicitly account for a post-close-window revision rather than assume the figure was final and stable, which is exactly the distinction the close-window rule exists to make legible to consumers.

Trade-offs and pitfalls

The tension is between correctness (waiting longer before finalizing catches more late arrivals) and reproducibility/trust (a number that keeps silently changing weeks after publication erodes confidence in every number the pipeline produces). The practical resolution is an explicit close window with an above-normal-effort exception process for anything after it, not an indefinitely-open "we'll fix it whenever we notice," which optimizes purely for correctness at the cost of anyone being able to trust a number as stable.

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.