Data Quality and Validation Questions

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

A focused unit test suite for a record-normalization transformation needs at minimum five cases: a normal well-formed input, a completely empty/zero-row input, a single-row input, a malformed or unparseable value, and an unexpected extra field, because a data-quality-focused test suite exists specifically to catch the edge cases that a demo on clean, well-formed data would never surface.

Structured elaboration

For each case, the assertion is different: the normal case asserts the output is correctly normalized (case-folded, formatted); the zero-row case asserts the function does not error on an empty input and returns an empty result rather than crashing; the single-row case asserts there is no logic that implicitly assumes multiple rows (a common latent bug: code that divides by len(df) or relies on a groupby producing more than one group); the malformed-value case asserts the function does not crash on genuinely bad input and instead handles it per its documented contract (null it out, flag it, or raise a specific, catchable error, whichever the contract promises); the extra-field case asserts the function does not silently drop or mishandle data it was not explicitly designed to know about.

Worked example

A toy normalize_users_row function (digit-stripping phone normalization, title-cased address normalization) tested against four cases, verified by execution: a normal input ("Alice Smith", "(555) 123-4567", "123 main st") normalizes correctly to phone="5551234567", address="123 Main St"; a missing-phone input correctly returns phone=None rather than erroring; a malformed too-short phone "12-3" is correctly treated as unparseable and returns None rather than propagating a garbage 2-digit "phone number"; and an input with extra whitespace and mixed casing normalizes correctly to "789 Elm St". Two more cases complete the five named in the direct answer: an actual zero-row input, calling normalize_users_row on an empty DataFrame (pd.DataFrame(columns=["name", "phone", "address"])), correctly returns an empty DataFrame with the same columns rather than raising an exception on an empty first row; and an actual extra-field input, a row carrying an unexpected middle_name key alongside the three known fields, is correctly passed through with middle_name preserved unchanged rather than being silently dropped or raising a KeyError, confirming the function does not assume it has seen every possible field the caller might send. All six assertions (normal, missing-phone, malformed, whitespace/casing, zero-row, extra-field) passed against the implementation; since each of the row-level cases (normal through extra-field) is itself a call on a single record, they jointly also exercise the single-row case named in the direct answer, so the worked example now demonstrates all five named cases, not four, confirming the test design actually exercises the behavior it claims to.

Trade-offs and pitfalls

The single-row case is the one teams skip most often, and it is a real, recurring bug source: code written and tested against multi-row sample data frequently has a latent assumption (an implicit .iloc[1], a division by row count minus one, a groupby that silently behaves differently with one group) that only surfaces once a real single-row edge case reaches production, often for a brand-new customer segment or a newly-onboarded, still-small data source. Writing this test explicitly, even though it feels like an unlikely case during development, is cheap insurance against a bug that is otherwise very hard to reproduce once it does occur in production.

Direct answer

The situation: a nightly ETL (extract, transform, load) job silently began casting an integer customer ID to a string partway through the pipeline, causing a downstream join to lose about 8% of matching rows and undercounting revenue for two weeks before anyone noticed the dashboard trend looked off. I discovered it by cross-checking a suspiciously flat week-over-week revenue trend against an independent finance export, which disagreed by exactly the missing 8%.

Structured elaboration

My investigative steps were: reproduce the discrepancy on a small sample first (compared row counts at each stage of the pipeline to isolate which transformation step introduced the loss), confirm the type mismatch by inspecting the schema of the intermediate table (the customer_id column had silently become VARCHAR two stages upstream of where I expected the bug), and then trace which specific commit or schema change introduced the cast. The remediation was a two-part fix: correct the transformation to preserve the original type, and backfill the two affected weeks by reprocessing from the raw source with the fix applied, validating the backfilled numbers against the finance export before republishing.

Worked example

The concrete detection signal was simple and reproducible: SELECT COUNT(*) FROM raw_customer_id_join versus SELECT COUNT(*) FROM string_customer_id_join on the same input differed by exactly the number of customer IDs whose numeric representation did not round-trip cleanly through a string cast (leading zeros and a handful of IDs that happened to collide after truncation), which is what let me confirm the type mismatch as the root cause rather than a more general data-loss bug.

Trade-offs and pitfalls

I communicated the two-week revenue undercount to stakeholders with the corrected numbers and an explicit note on the size and duration of the discrepancy, since silently republishing corrected historical numbers without flagging that a change occurred erodes trust more than the original bug did. The preventive measure I implemented afterward was a lightweight schema-assertion test in CI that fails the build if a table's declared column types change unexpectedly between deploys, so the same class of silent type-cast bug is caught before it ships rather than two weeks after.

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 high-throughput streaming pipeline (hundreds of thousands of events per second) with duplicates from retries, out-of-order delivery, and multiple producers, exact windowed stateful dedup (tracking seen keys within a bounded time window, evicting old keys via a watermark and TTL) is the correct default; approximate Bloom-filter-based dedup is a fallback only when the exact approach's memory footprint becomes genuinely prohibitive at your specific scale and retention window.

Structured elaboration

• Exact windowed dedup: maintain a keyed state store (an event ID or a composite fingerprint) with a time-to-live matching your maximum expected out-of-order lateness; a watermark advances the window and evicts state for keys old enough that a duplicate is no longer expected to arrive. This is exact (zero false positives or negatives within the window) but its memory cost scales directly with the number of unique keys held in the window at once.
• Approximate Bloom-filter dedup: a Bloom filter answers "have I seen this key before" using a fixed, much smaller memory footprint than storing every key explicitly, at the cost of a tunable false-positive rate (it can wrongly say "seen before" for a genuinely new key, causing you to drop a legitimate event; it never produces a false negative, so it will never let a true duplicate through).
• Sizing worked example: for 10 million active keys in the current window and a target false-positive rate of 0.1%, the standard Bloom-filter sizing formulas give roughly 144 million bits (about 17 MiB) and 10 optimal hash functions, verified by execution against the actual formula, which reproduces a 0.1% false-positive rate exactly at those parameters, a genuinely compact footprint for that many keys.
• Checkpointing for recovery: the state (exact key set or Bloom filter bits) must be checkpointed periodically to durable storage so that after a restart, the job resumes from the last checkpoint rather than either replaying duplicates it had already deduped, or losing its dedup state entirely and starting from an empty filter.

Trade-offs and pitfalls

The consequence of choosing the approximate approach is real and specific: a Bloom filter false positive silently drops a legitimate, never-before-seen event, treating it as a duplicate. For a metric where undercounting is more tolerable than double-counting, this can be an acceptable trade; for a use case (like a payments pipeline) where every legitimate event must be processed, false-positive event loss is unacceptable and the memory cost of exact dedup, even if higher, is the correct choice. Checkpointing frequency itself is also a trade-off: checkpointing too rarely risks reprocessing a large window of already-deduped events after a crash (duplicating work, and briefly reintroducing duplicates downstream until the state catches back up); checkpointing too frequently adds sustained I/O overhead to every processing cycle.