Data Quality and Validation Questions

Direct answer

Onboarding a new dataset needs checks at four layers, schema, record, referential, and business-logic, and each check needs an explicit placement decision: does it belong upstream at the source (cheapest, fastest feedback, but requires cooperation from a producer you may not control), during transform (a reasonable default for most checks), or as a post-load monitor only (for checks too expensive or too slow to run inline)?

Structured elaboration

• Schema-level (types, required fields, cardinality bounds): ideally enforced upstream if you control the producer, since catching a type error before it ever leaves the source is far cheaper than catching it after it has propagated; otherwise enforced during transform as a hard gate.
• Record-level (individual row validity: format, range, enum membership): usually belongs during transform, since it needs the data in hand to evaluate, and failing fast here (quarantine or reject) is cheap relative to letting bad records reach consumers.
• Referential (foreign keys resolving against existing dimensions): during transform if the referenced dimension is stable and available at load time; as a post-load monitor if the dimension itself is still catching up (a late-arriving dimension row is expected, not a bug).
• Business-logic (a computed aggregate matches an expected relationship, e.g. "monthly revenue equals sum of transactions for that month"): often too expensive to check on every single load inline, so this is a common candidate for a post-load monitor that runs on a schedule rather than blocking every load.
• The reasoning behind placement is a genuine cost/speed/impact trade-off: pushing everything upstream is ideal but you often do not control the source; pushing everything to post-load monitoring is cheap but lets bad data reach consumers before anyone notices.

Worked example

A new vendor-master feed arrives with a vendor_id that should reference an existing dim_vendor table. Schema and record-level checks (required fields present, vendor_id is a non-null string of expected length) run inline during transform and block the load on failure, since they are cheap and a failure here almost always means a genuinely broken batch: concretely, a row with vendor_id = NULL trips the not-null check immediately and is quarantined before it ever reaches a downstream table, at negligible cost since the check is a simple null test done in the same pass as the rest of the transform. The referential check against dim_vendor runs as a post-load monitor with a short grace window, since a small number of genuinely new vendors legitimately arrive before their dimension row is created, and treating every such case as a hard block would create constant false alarms: concretely, a row with vendor_id = 'V-4821' that has no matching row in dim_vendor yet is allowed through and flagged by the monitor rather than blocked, and if a matching dim_vendor row for V-4821 has not appeared by the end of the grace window, it escalates to a real referential-integrity issue instead of a routine timing gap.

The business impact differs meaningfully by check category, not just the cost and speed already covered above. A schema or record-level failure (the NULL vendor_id case) is cheap to catch and its business impact if missed is usually a hard, visible failure downstream, a load error or a broken report, which is exactly why it is worth blocking on immediately. A referential-check failure (the unresolved V-4821 case) has a different, quieter business impact: if it silently persisted past the grace window, invoices for that vendor would get coded against a missing or wrong vendor record, a finance-reporting error rather than a technical one, which is why the monitor escalates it rather than either ignoring it indefinitely or blocking the whole load over what is usually a benign timing gap. A business-logic failure (the monthly-revenue-reconciliation case) has the highest-stakes business impact of the four, a missed reconciliation mismatch can mean an executive dashboard silently shows the wrong revenue figure for a quarter, which is precisely why it still runs as a scheduled check even though it is too expensive to run inline on every load.

Trade-offs and pitfalls

The most common mistake is applying a single, uniform placement policy to every check ("everything blocks the load" or "everything is monitor-only") rather than reasoning about each check's cost and business impact individually. A blanket block-everything policy turns every minor, expected discrepancy into a production incident; a blanket monitor-only policy lets genuinely broken data reach consumers before anyone notices, defeating the purpose of validating at all.

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 referential-integrity suite for large, partitioned related tables needs to avoid a naive full-partition-scan join by using partition pruning, comparing only the relevant date/key ranges on each side, and needs explicit handling for two distinct violation types: orphaned child rows (referencing a parent that does not exist) and records referencing an impossibly future parent (a timestamp-ordering violation that a pure existence check would miss).

Structured elaboration

• Orphan detection with partition pruning: rather than joining the full child and parent tables, restrict the check to the specific date partitions relevant to the child rows being validated (a refund dated this month should only need to check against order partitions from this month and earlier, not the entire multi-year order history), which turns an expensive full-table join into a bounded, partition-pruned one.
• Impossibly-future-parent check: a referenced parent existing is necessary but not sufficient; a refund whose order_date reference is later than the refund's own date (or a session whose parent user's signup_date is after the session's start_ts) is a distinct, temporally-inconsistent violation that a bare existence-based LEFT JOIN would not catch, since the row it joins to does technically exist.
• Blocking versus alerting: a referential-integrity failure on a small number of records in a high-volume, low-stakes table is usually alert-only, since a handful of late-arriving parent rows is expected; a failure on a small, high-stakes table (a financial reconciliation table where every reference must resolve) should block downstream consumption until resolved, since even a single unresolved reference undermines the guarantee the table is meant to provide.

Worked example

For orders(order_id, order_date) and refunds(refund_id, order_id, refund_date), partitioned by their respective date columns, the check restricts the join to only the relevant partition range and adds the temporal-ordering guard:

SELECT r.refund_id, r.order_id
FROM refunds r
LEFT JOIN orders o ON r.order_id = o.order_id AND o.order_date <= r.refund_date
WHERE o.order_id IS NULL;

This flags both a genuinely orphaned refund (no matching order at all) and a refund whose order reference technically exists but with an order date after the refund date, a data-corruption signature the simpler existence-only check would silently miss.

Trade-offs and pitfalls

Partition pruning only delivers its performance benefit if the partitioning scheme on both sides is actually usable for this specific check, if refunds are partitioned by refund_date but the relevant comparison needs order_date on the other table, the query planner may not be able to prune effectively without an explicit date-range predicate added to the query itself, which is a detail teams frequently miss when they assume "it's partitioned" is enough on its own.

Direct answer

A statistical sampling plan for manually auditing a large dataset when validating every record is too costly needs an explicit sampling method (random versus stratified), a defined stratification scheme if the population is heterogeneous, a sample size computed for a target confidence level and margin of error, and a method for extrapolating sampled findings to estimate total error across the full population, with an honest accounting of the uncertainty in that extrapolation.

Structured elaboration

• Random versus stratified: simple random sampling is appropriate if the population is reasonably homogeneous with respect to the error being audited for; stratified sampling (sampling proportionally within known subgroups like transaction size or region) is more appropriate when the error rate plausibly varies across subgroups, since a random sample could, by chance, under-represent a subgroup with a meaningfully different error rate and bias the overall estimate.
• Choosing strata: pick strata based on dimensions you have reason to believe correlate with error likelihood, not arbitrary categories; for financial transactions, size (large transactions may warrant more scrutiny given their outsized impact) and region (differing local processes may introduce differing error patterns) are common, well-justified choices.
• Sample-size estimation: for a target confidence level and margin of error on an estimated proportion (like an error rate), the standard sample-size formula is $n = \frac{Z^2 \cdot p(1-p)}{E^2}$ where $Z$ is the z-score for the target confidence level (1.96 for 95%), $p$ is the assumed true proportion (use the conservative $p=0.5$, which maximizes $p(1-p)$ and therefore the required sample size, if no prior estimate exists), and $E$ is the target margin of error. For the 95% confidence, 2% margin-of-error target used in the worked example below: $n = \frac{1.96^2 \times 0.5 \times (1-0.5)}{0.02^2} = \frac{3.8416 \times 0.25}{0.0004} = \frac{0.9604}{0.0004} \approx 2{,}401$ records, meaning roughly 2,401 records need to be sampled (before any stratification split) to hit that target.
• Extrapolation and weighting: for stratified sampling, the sampled error rate within each stratum is weighted by that stratum's actual share of the total population when extrapolating to an overall error-rate estimate, not simply averaged across strata unweighted, since an unweighted average would implicitly and incorrectly treat every stratum as equally sized.

Worked example

Auditing a population of financial transactions stratified by size (small, medium, large) and region (three regions), sampling proportionally within each of the nine resulting strata to hit an overall 95% confidence, 2% margin-of-error target. If the large-transaction stratum, despite being only 5% of total transaction count, shows a meaningfully higher sampled error rate than the small and medium strata, the size-weighted overall estimate correctly reflects that this small-but-higher-error stratum contributes disproportionately to total dollar-value risk, an insight a simple unstratified random sample would likely have missed or underweighted entirely, since it might easily undersample the numerically-small large-transaction stratum by chance.

Trade-offs and pitfalls

The most common mistake in practice is computing a sample size formula correctly but then reporting the resulting error-rate estimate without also reporting its margin of error and confidence level, presenting a point estimate ("we found a 1.2% error rate") as if it were an exact measurement rather than an estimate with real, quantifiable uncertainty around it. The honest version of this finding always carries its confidence interval alongside the point estimate, and any decision made from it should account for the width of that interval, not just the point estimate itself.

Direct answer

A production-scale fuzzy-matching dedup pipeline has three stages: blocking (cheaply narrow candidate pairs so you never do full pairwise comparison), scoring (compute a similarity score per candidate pair across multiple fields), and thresholding with human review (auto-merge above a high-confidence threshold, route the middle band to manual review, and leave the rest as distinct).

Structured elaboration

• Blocking: group records into buckets that are cheap to compute (first 3 letters of last name plus zip code, or a phonetic code like Soundex on the name) so that true duplicates are very likely to land in the same bucket, and you only compare records within a bucket rather than the full N^2 pairs. This is what makes the approach scale: for 50 million records, full pairwise comparison is computationally infeasible, but blocking into buckets of a few hundred records each reduces the comparison work by several orders of magnitude.
• Similarity scoring: combine multiple field-level similarity metrics into one score, typically token-based similarity for names (handles reordering, e.g. "Smith, John" vs "John Smith") and edit-distance-based similarity for structured fields like phone/email. A blended score, rather than any single field, is what makes the match robust to one field being wrong.
• Address similarity: address needs its own approach rather than a raw string comparison, because a raw character-level edit distance is fragile to reordering and abbreviation in exactly the way name similarity is not, once you account for it. Parse the address into components first (street number, street name, city, state, postal code) and compare component-wise (exact or near-exact match on postal code and street number, fuzzy match on street name to absorb abbreviations like "St" vs "Street"), rather than diffing the two full strings as one blob.
• Threshold selection: pick the threshold empirically against a labeled sample of known-duplicate and known-distinct pairs, and report precision/recall at that threshold, not just "the match rate."
• Human-in-the-loop: pairs scoring in an ambiguous middle band go to a review queue rather than being auto-merged or auto-rejected; the reviewer's decisions can then be fed back to retune the threshold over time.

Worked example

Comparing a customer record ("John Smith", "john.smith@mail.com", "555-1234", "123 Main St, Springfield, IL 62704") against three candidates using a token-based name similarity, a character-based email similarity, and a character-based address similarity (0-100 scale):

(computed with rapidfuzz.fuzz.token_sort_ratio for names and fuzz.ratio for emails and addresses, verified by execution). Even a plain character-level fuzz.ratio on the full address string, as used above, is fragile to reordering in a way a component-wise or token-based comparison is not: comparing "123 Main St, Springfield, IL 62704" against the same address with its parts reordered, "Springfield, IL 62704, 123 Main St", scores only 62 with fuzz.ratio (verified by execution) even though it is the identical address, while a token-based comparison (fuzz.token_sort_ratio) correctly scores that same reordered pair at 97, which is why a production address-similarity step should compare parsed components (or at minimum use a token-based rather than a purely positional string metric), not raw full-string edit distance.) The second candidate, "Jon Smith", illustrates exactly why relying on a single field is fragile: the email string alone is only 91% similar, but combined with a matching phone number and a 95% name match, it clears a reasonable merge threshold with much more confidence than either signal alone.

Trade-offs and pitfalls

At 50 million rows versus a few thousand leads, the design does not change qualitatively, only the blocking key's selectivity and the infrastructure (single-machine pandas versus a distributed job) do: a blocking key that is fine-grained enough for a small CRM (say, exact last name) may create buckets too large to compare pairwise at 50 million rows, requiring a coarser key plus a second blocking pass. The most common failure mode at any scale is choosing a blocking key that is too coarse, silently missing true duplicates that land in different buckets, which is invisible unless you specifically measure recall against a labeled sample rather than only precision.