Data Quality and Validation Questions

Direct answer

Define measurable Service-Level Objectives (SLOs) for a data-quality pipeline across at least freshness (how stale the data may be), completeness (what fraction of expected records must arrive), and accuracy (how much deviation from a reconciled figure is tolerable), each with a concrete Service-Level Indicator (SLI), a numeric target, and an explicit error budget that defines how much SLO violation is tolerated before it triggers a mandated response.

Structured elaboration

• SLI definitions: freshness SLI = time between an event occurring and it being queryable in the warehouse; completeness SLI = percentage of expected source records present at the SLO evaluation time; accuracy SLI = percentage of partitions passing a reconciliation check within tolerance.
• Targets: pick targets from actual business need, not aspiration (a dashboard used only for a weekly review does not need sub-hour freshness even if it is technically achievable).
• Error budget: an SLO of "99% of partitions freshness under 2 hours" implies a 1% error budget; when that budget is exhausted (too many partitions missed the freshness target this period), the response should be a defined, pre-agreed action (a freeze on non-critical pipeline changes, a mandated root-cause review), not an ad hoc decision made under pressure during the next incident.
• Reporting: report SLA (Service-Level Agreement) compliance, tracked via the SLO/SLI definitions above, on a recurring cadence to the teams who depend on it, and treat a consistently-exhausted error budget as a signal to invest in reliability work, not just as a metric to track. Concretely, this means a weekly SLA scorecard (a dashboard, or an automated email if the consuming teams do not regularly check a dashboard) showing each of the three SLOs' current attainment percentage and remaining error budget for the period, with a simple red/amber/green status per metric, sent to the consuming teams' leads; separately, an error-budget exhaustion (like the freshness example below) triggers an automatic escalation notice the moment it happens, rather than waiting for the next scheduled weekly report to surface it.

Worked example

A customer-analytics pipeline processing 50 million events per day sets: freshness SLI/SLO = 95% of daily partitions available within 3 hours of day-end; completeness SLI/SLO = row count within 2% of the trailing 7-day median; accuracy SLI/SLO = 99% of daily reconciliation checks pass within a 1% tolerance against the source system. If freshness misses its target on 8 of the last 100 partitions (an 8% miss rate against a 5% allowed miss rate), the error budget is exhausted and the team's pre-agreed response, for example pausing new feature rollouts on the ingestion pipeline until the root cause of the freshness misses is found, kicks in automatically rather than being negotiated in the moment. That same week's SLA scorecard reports it concretely: "Freshness: 92% attainment against a 95% target, RED, 160% of this week's freshness error budget used (8 misses against a 5-miss allowance)", alongside "Completeness: 99.6% attainment against a 98% target, GREEN" and "Accuracy: 99.4% attainment against a 99% target, GREEN", sent to the consuming teams' leads, with the RED freshness line being exactly what triggered the automatic escalation notice mid-week rather than waiting for this scheduled report to surface it.

Trade-offs and pitfalls

The most common mistake is setting SLO targets to match whatever the pipeline currently achieves, rather than what the business actually needs; this makes the SLO meaningless as a quality bar (it can never be missed) and defeats the purpose of having an error budget at all. A second common mistake is defining an accuracy SLO without first having a trustworthy reconciliation source to measure against, which produces a target that looks precise but cannot actually be verified.

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

For a pipeline ingesting on the order of a million events per minute, the concrete operational metrics to instrument are ingestion lag/freshness, row-count deltas against an expected baseline, per-column null-rate spikes, schema-change events, and duplicate rate, each with a starting severity tier that distinguishes a real regression from ordinary day-to-day variance.

Structured elaboration

• Ingestion lag: time between event occurrence and it landing queryably; alert if lag exceeds a threshold sustained for several consecutive minutes, not on a single spike, to avoid paging on a transient blip.
• Row-count delta: compare the current window's row count against a rolling baseline (a trailing 7-day median for the same time-of-day/day-of-week, not a flat average, since volume has strong seasonality); alert on a large relative deviation, for example more than 3x the typical day-to-day variance.
• Null-rate spike: track per-column null rate over time and alert when a critical column's null rate jumps materially above its historical baseline, since a sudden jump is a much stronger signal of a real upstream break than a stable-but-nonzero baseline null rate.
• Schema-change events: alert on any unexpected added, removed, or type-changed column, since even a seemingly benign addition can break a strict downstream consumer.
• Duplicate rate: track the rate of exact or near-duplicate records and alert on a sustained increase, which is usually a symptom of a retry storm or a broken idempotency guarantee upstream.

Worked example

At this ingestion volume, a severity tiering that works well in practice: freshness lag over 5 minutes = warning (page only if sustained 15+ minutes), row-count delta over 3x the typical day-of-week variance = page immediately, any unexpected schema change on a table with more than 5 known downstream consumers = page immediately, null-rate spike over 5 percentage points above the 7-day baseline on a required field = page, duplicate rate above 2x baseline = warning.

Trade-offs and pitfalls

The recurring failure mode at this scale is comparing against a flat, non-seasonal baseline: a naive "alert if today's count differs from yesterday's by more than X%" fires constantly on legitimate day-of-week seasonality (weekends are naturally lower volume for most consumer products) and trains the on-call team to ignore the alert channel entirely. A seasonality-aware baseline, even a simple trailing-median-by-day-of-week, removes most of that noise and is usually a bigger improvement to signal quality than tightening or loosening the raw threshold.

Direct answer

When a dashboard shows a sudden, unexplained metric change, the first move is not to guess at a business cause, it is to rule out an ingestion or pipeline problem with a short, ordered checklist: row counts and null rates for the affected date, join cardinalities compared to a known-good baseline, and any recent code, schema, or deployment change, because these are cheap to check and account for the large majority of "sudden metric change" incidents.

Structured elaboration

• Step 1, row counts and freshness: did the expected volume of data actually arrive for the affected period, and on time? A partial or missing partition is the single most common cause and the fastest to rule in or out.
• Step 2, null rates: did a required field's null rate spike compared to its historical baseline? A spike here often points directly at a specific upstream field or join key that broke.
• Step 3, join cardinality: has the row count after a join changed disproportionately to the row counts of its inputs? (Cardinality here just means the number of rows a join or query produces.) A cardinality blowup, far more output rows than either input table had, or a cardinality collapse, far fewer, is the signature of a join-key or fan-out bug: a blowup usually means the join key is not unique on one side and is silently duplicating matching rows, while a collapse usually means the join key stopped matching correctly (a type mismatch, a null-heavy key) and rows that should have matched are being dropped.
• Step 4, recent changes: what deployed, in code or schema, in the window immediately before the anomaly appeared? This is often the fastest path to root cause when steps 1-3 come back clean.
• Only after these four come back clean does the investigation shift toward "this might be a genuine business event," and even then, you should be able to name a plausible external cause (a promotion, a seasonal pattern, a competitor event) before concluding it is not a data problem.

Worked example

A metric drops 40% overnight. Step 1 (row counts) shows the expected volume arrived. Step 2 (null rates) shows nothing unusual. Step 3 (join cardinality) shows the join that produces the metric now returns roughly half the expected rows relative to its input tables, immediately narrowing the search to that specific join rather than the whole pipeline; a quick check of that join's key reveals a type mismatch introduced by a schema change deployed the previous evening (step 4 confirms the timing). The whole path from "metric looks wrong" to "specific broken join, specific deploy" takes minutes because each step either confirms or eliminates a whole class of causes before moving to the next.

Trade-offs and pitfalls

The failure mode this ordered checklist prevents is jumping straight to a plausible-sounding business explanation (a promotion ended, a competitor launched something) without first ruling out the much more common pipeline causes, which wastes time chasing a narrative that a five-minute row-count check would have disproven. Communicating an interim finding before the root cause is fully confirmed matters too: telling stakeholders "we have ruled out an ingestion gap and a null spike, and are now investigating a specific join" as you go is more useful than going silent until you have the complete answer.

Direct answer

For a small, nonzero fraction of NULLs in a required numeric field like sales_amount, the decision between dropping, imputing, quarantining, or alerting-and-leaving-as-is depends on three factors working together: the business criticality of the field (revenue-adjacent fields warrant more caution), whether the nulls are concentrated (suggesting a specific, findable cause) or diffuse (suggesting genuine noise), and how the downstream consumer would actually behave differently under each option; a billing amount and a marketing attribute sit at very different points on that criticality scale, so the same 1.2% gap resolves very differently for each.

Structured elaboration

• Dropping rows: appropriate only when the affected rows are a genuinely negligible fraction and there's evidence the missingness is close to random (not concentrated in one segment, one time period, or one upstream source), since dropping a non-random subset silently biases every aggregate that uses this table, not just the ones directly touching sales_amount.
• Imputing: appropriate for a low-stakes use case where a reasonable default (zero, or a segment-level median) is genuinely defensible and the imputation is clearly documented and flagged, never appropriate for a field feeding financial reporting without an explicit sign-off, since a silently-imputed revenue number is a serious integrity risk.
• Quarantining for review: the safest default for a field this important, hold the affected rows out of the published aggregate while a human investigates the specific cause, and re-include them once resolved rather than guessing.
• Alerting upstream and leaving untouched: appropriate when the root cause is clearly on the producer's side and the fix belongs there, not in your pipeline, publish the aggregate with a visible caveat noting the affected percentage rather than silently either including or excluding the nulls.
• Billing amount versus marketing attribute: if sales_amount feeds a billing or revenue-recognition system, the decision shifts hard toward quarantine or alert-upstream-and-leave-untouched; a silently imputed or dropped billing figure risks becoming an authoritative charge, refund, or reconciliation number that is wrong by construction, so imputation is rarely defensible without explicit finance sign-off, and the same 1.2% would typically trigger a mandatory audit trail and a hard SLA (service-level agreement) on upstream remediation time rather than a routine judgment call. If the field is instead a lower-stakes marketing attribute (for example a campaign-attribution tag), the same 1.2% gap is far lower-stakes: a documented default value, or simply dropping those rows from the specific report that uses it, is usually acceptable, since the downstream consumer is a directional trend dashboard rather than a financial statement, and the cost of being occasionally wrong is materially smaller.

Worked example

1.2% NULL on sales_amount first triggers a quick diagnostic: is the 1.2% spread evenly across all product categories and dates (suggesting genuine, low-stakes noise, perhaps from an edge-case checkout flow), or concentrated in one specific category or one specific day (suggesting a real, findable bug, perhaps a specific payment integration silently failing to log the amount)? If concentrated, that is strong evidence for quarantine-and-investigate rather than a blanket impute-or-drop decision, since concentration usually means there is a specific, fixable root cause worth finding rather than genuine background noise. If this same sales_amount field were instead feeding a customer's monthly invoice rather than a marketing dashboard, the same concentrated 1.2% would additionally trigger a hold on invoice generation for the affected customers specifically, rather than a caveat published alongside an otherwise-complete report, since an incorrect invoice has a direct customer-facing and legal consequence a delayed dashboard does not.

Trade-offs and pitfalls

The single most useful diagnostic step, checking whether the nulls are concentrated or diffuse, is also the one most commonly skipped in favor of jumping straight to a default policy ("we always impute nulls with zero" or "we always drop them"). A blanket policy applied without this check will sometimes silently mask a real, fixable upstream bug behind a mechanical default, converting a findable root cause into invisible, permanent noise in every future report, and applying the same blanket policy to a billing field that would be reasonable for a marketing field is the version of this mistake with the highest real-world cost.