Data Quality and Validation Questions

Direct answer

A data-quality rule documentation template needs, at minimum: a plain-language description, the detection logic (the actual query or code implementing the check), severity, an owner, remediation steps, and worked examples of both a passing and a failing record, stored somewhere discoverable and versioned so it stays accurate as the underlying pipeline evolves.

Structured elaboration

• Description: a plain-language statement of what the rule checks and why it matters, written for someone who is not the rule's author.
• Detection logic: the literal, runnable check (SQL, a framework expectation, a script), not a paraphrase of it, so anyone can verify exactly what is being tested rather than relying on the description alone, which can drift out of sync with the actual implementation.
• Severity and owner: an explicit escalation path, who gets paged, and how urgently, when this rule fails.
• Remediation steps: what to actually do when the rule fires, not just what the rule detects; a rule with no documented remediation path is only half-useful.
• Worked examples: a concrete passing record and a concrete failing record, since an abstract description of "what counts as a violation" is often ambiguous at the edges in a way a real example resolves immediately.
• Discoverability and staying current: store the template alongside the pipeline code itself (versioned in the same repository) rather than in a separate wiki that drifts out of sync, and treat a change to the detection logic as requiring an update to the documentation in the same code review, not as a separate, easily-forgotten step.

Worked example

A rule for "duplicate order detection": description = "flags orders with the same (customer_id, order_total, order_date) appearing more than once, which usually indicates a retry-induced duplicate insert rather than a genuine repeat purchase"; detection logic, inlined rather than just referenced, so the documentation and the runnable check are the same artifact:

WITH ranked AS (
  SELECT order_id, customer_id, order_total, order_date, created_at,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id, order_total, order_date
           ORDER BY created_at
         ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn > 1;

Every row this returns has rn > 1, meaning it is not the first-created row within its (customer_id, order_total, order_date) group, so it is flagged as a probable duplicate rather than the original; severity = medium, does not block the pipeline, routes to a daily digest rather than paging; owner = the orders-pipeline team; remediation = "confirm via the source system's request ID whether this is a genuine retry duplicate, and if so, mark the later record for exclusion in the next reconciliation pass"; worked examples = one clearly-duplicate pair and one legitimate same-day repeat purchase that should NOT be flagged, illustrating exactly where the boundary of the rule lies.

Trade-offs and pitfalls

The template's biggest practical risk is documentation drift: a rule's underlying SQL gets updated during an incident fix, but the corresponding documentation is not, and six months later the documented detection logic no longer matches what actually runs, misleading whoever reads it next. Requiring the documentation update as part of the same pull request that changes the detection logic, enforced by convention or by a lightweight CI check, is what actually keeps the two in sync rather than relying on someone remembering to update a separate wiki page.

Direct answer

An automated reconciliation system compares an aggregate computed from the source-of-truth system (for example the OLTP, online transaction processing, orders table) against the same aggregate as it lands in the analytics warehouse, on a schedule, flags any difference beyond an explicit tolerance, and either alerts or blocks downstream consumption depending on how business-critical the aggregate is.

Structured elaboration

• Tolerance: use a relative (percentage) tolerance rather than an absolute one for anything whose scale varies day to day (a $5 absolute difference is nothing on a $1M day and significant on a $100 day); pick the tolerance empirically from historical natural variance, not an arbitrary round number.
• Localizing a discrepancy without a full re-scan: precompute partition-level checksums (a hash or a simple count/sum) on both sides and compare checksums per partition rather than row by row; only partitions whose checksums disagree need a deeper row-level investigation, which turns an expensive full comparison into a cheap checksum comparison plus a targeted deep-dive only where needed.
• Late-arriving records: define an explicit "close" window (for example, a day's numbers are not reconciled until 24 hours after the day ends) so the check does not fire false alarms on data that is simply still arriving.
• Blocking versus alerting: a discrepancy on a table feeding executive financial reporting should typically block downstream publication until resolved; a discrepancy on a lower-stakes internal metrics table can alert and let an analyst triage on their own schedule.

Worked example

Comparing oltp_orders (raw orders) against warehouse_daily_revenue (a transformed daily aggregate) for two days, with a 1% tolerance rule, run against a DuckDB fixture: July 1st has a source total of $150.00 against a warehouse total of $150.00 (no discrepancy, correctly not flagged), while July 2nd has a source total of $75.00 against a warehouse total of $70.00, a $5.00 absolute and 6.67% relative difference, correctly flagged by the query since it exceeds the 1% tolerance (verified by execution):

WITH src AS (SELECT order_date, SUM(amount) AS src_total FROM oltp_orders GROUP BY order_date)
SELECT s.order_date, s.src_total, w.total_amount,
  ROUND(ABS(s.src_total - w.total_amount) / NULLIF(s.src_total,0) * 100, 2) AS pct_diff
FROM src s JOIN warehouse_daily_revenue w ON s.order_date = w.order_date
WHERE ABS(s.src_total - w.total_amount) / NULLIF(s.src_total,0) > 0.01;

Trade-offs and pitfalls

A tolerance set too tight generates alert fatigue from normal rounding and timing noise; set too loose, it misses genuine data-quality regressions. The checksum-based partition localization only works if partitions are stable and comparable on both sides (the same partitioning scheme, or at least a deterministic mapping between them); if the source and warehouse partition differently, you lose the cheap-localization benefit and are back to a full comparison, which is the detail teams most often get wrong when this design is first implemented.

Direct answer

For both a training dataset and the corresponding production input data feeding a deployed model, monitor completeness, null rate, uniqueness/duplicate rate, cardinality, basic distributional statistics (mean, standard deviation), and a stability metric such as Population Stability Index (PSI), because each catches a distinct failure mode a model can silently degrade from, and each needs its own reasonable starting alert threshold since a threshold that is right for one metric is meaningless for another.

Structured elaboration

• Completeness and null rate: catches missing or partially-broken upstream data before it silently reduces the model's effective feature signal, or before an unexpected NULL propagates into a feature pipeline that was not built to handle it. A reasonable starting threshold is to block training on any required field exceeding a 1% null rate, and in production, to alert if a field's null rate more than doubles relative to its own trailing 7-day baseline, since a fixed absolute threshold alone would miss a meaningful jump between two values that both still look individually low.
• Uniqueness/duplicate rate: catches duplicated training examples (which can bias a model toward whatever pattern the duplicated rows represent) and, in serving, duplicated inference requests that can distort aggregate monitoring metrics if not deduplicated. A reasonable starting threshold is to block training if the exact-duplicate rate exceeds 0.5% of rows, and to alert (without blocking) if the production duplicate-request rate deviates by more than 3x from its own trailing baseline.
• Cardinality: a sudden jump in the number of distinct values for a categorical feature (a new, previously-unseen category flooding in) is often the first visible symptom of an upstream encoding or join-key bug, well before it manifests as a model performance drop. A reasonable starting threshold is to alert on any single-day increase of more than 20% in the number of distinct values relative to the cardinality observed at last training time.
• Distributional statistics and PSI: catch the case where individual records are all individually valid, but the population as a whole has shifted, exactly the class of problem raw validity checks cannot see, since every single value can pass its own range check while the aggregate distribution has moved meaningfully. The standard, widely-used starting bands are a soft warning at PSI above 0.1 and a hard alert at PSI above 0.25, treating anything below 0.1 as normal variation. PSI is computed by first splitting the feature's range into the same fixed buckets for both the baseline (training) and current (production) distributions, then, for each bucket, taking the difference between the current and baseline percentage of rows in that bucket, multiplying it by the natural log of their ratio, and summing across buckets: $PSI = \sum_i (\text{current}_i - \text{baseline}_i) \times \ln\left(\frac{\text{current}_i}{\text{baseline}_i}\right)$. Concretely, for a feature split into 4 buckets where the baseline (training) distribution is 30%, 25%, 25%, 20% and the current (production) distribution has shifted to 20%, 30%, 25%, 25%: the per-bucket contributions are $(0.20-0.30)\ln(0.20/0.30) \approx 0.0405$, $(0.30-0.25)\ln(0.30/0.25) \approx 0.0091$, $(0.25-0.25)\ln(1) = 0$, and $(0.25-0.20)\ln(0.25/0.20) \approx 0.0112$, summing to $PSI \approx 0.061$, below the 0.1 soft-warning band despite a visible shift in where the mass of the distribution sits.
• Schema and type violations: unlike the rate-based metrics above, these warrant zero tolerance as a starting threshold, since a type mismatch or a missing required column is an unambiguous break rather than a rate to tune.
• The overall gate: schema, null/type, range, and duplicate checks should run and block before data is used to train, using the zero-tolerance and rate thresholds above, and a lighter-weight subset of drift checks (cardinality and PSI) should run continuously against production inputs, using the same starting thresholds, to catch degradation between training runs.

Worked example

A recommendation model's training data has a categorical device_type feature with 12 known values at training time; in production, cardinality monitoring detects it has grown to 40 distinct values within a month, a 233% increase that clears the 20% starting-threshold by a wide margin, most of them new device-model strings from a hardware vendor's recent product launch that the training encoding never saw. This is caught by the cardinality check well before it would show up as a measurable model-quality regression, giving the team time to retrain or add a fallback encoding for unseen categories before performance actually degrades.

Trade-offs and pitfalls

The recurring mistake is monitoring only the training dataset once, at training time, and never continuously monitoring the production input stream against that same baseline; a model that was trained on genuinely high-quality data can still degrade in production purely because the input distribution has drifted, which is invisible unless the same quality and drift checks that gated training are also run continuously, on a schedule, against live production inputs. A second common mistake is treating every starting threshold above as permanent rather than a first cut to be tuned against the specific feature's observed false-positive rate once real alert history accumulates.

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.

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.