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 organization-wide data-quality program needs to cover people (roles like data stewards with clear accountability), process (policies, standards, and an incident-response playbook), and technology (a chosen tooling stack), rolled out in phases (pilot, scale, sustain) with measurable milestones, since attempting a company-wide rollout all at once, without a proven pilot, is the most common way this kind of initiative fails.

Structured elaboration

• Pilot phase (roughly months 1-2): select one or two high-visibility, high-pain pipelines as the pilot, implement the full program (stewardship, standards, tooling) end-to-end for just those, and use the pilot to prove the model works and generate a concrete before/after story to build momentum with.
• Scale phase (roughly months 3-4): expand from the pilot to the next tier of critical pipelines, using lessons learned (what tooling choices worked, what process friction showed up) to refine the approach before it becomes standard practice everywhere.
• Sustain phase (roughly months 5-6 and beyond): make the standards and tooling the default for new pipelines going forward, establish ongoing training and onboarding for new team members, and define success KPIs that are tracked continuously rather than only during the initial rollout.
• Tooling choices: the technology leg is not just "pick a tool," it is picking the tool whose friction matches how the organization already works. For a team already heavily invested in dbt-based SQL transformations, dbt tests (the built-in and dbt-native unique, not_null, accepted_values, relationships tests, plus custom SQL-based tests) are the lower-friction choice: they live alongside the existing models, run in the same dbt test invocation the team already uses, and require no new language or deployment surface. For an organization needing richer, cross-language validation (Python-based pipelines, non-dbt sources, or a need for a structured, queryable validation report rather than a pass/fail test run), Great Expectations is the better fit: it is a standalone Python framework that produces a structured "data docs" report and integrates with a wider range of data sources than a SQL-only tool can. The right call is decided by which cost the team can least afford, the friction of adopting a new tool outside their existing stack, or the limitation of a SQL-only tool when the pipeline is not SQL-only, not by which tool is abstractly "better."
• Driving adoption across teams that do not report to you: this is fundamentally an influence problem, not an authority one; the pilot's concrete, quantified success story (a real incident prevented, a real cost saved) is usually the most effective adoption lever, more so than a mandate from above, because it gives other team leads a self-interested reason to opt in rather than a compliance obligation to resent.

Worked example

A six-month program: months 1-2 pilot the full program on the two pipelines that generated the most incidents last quarter, producing a concrete result (for example, a measured 80% reduction in data-quality-related incident tickets for those two pipelines); months 3-4 use that result to recruit three more team leads to adopt the same standards and tooling voluntarily, refining the onboarding process based on friction the pilot teams reported; months 5-6 make the validated tooling the default for any new pipeline going forward and establish a recurring quarterly review of the program's KPIs (incident rate, time-to-detection, percentage of critical datasets with defined quality checks) to the leadership team.

Trade-offs and pitfalls

The failure mode this phased approach specifically avoids is a mandated, all-at-once rollout with no proven pilot: teams asked to adopt an unproven process purely by directive tend to comply minimally and revert as soon as attention moves elsewhere, whereas teams who see a peer team's concrete, quantified success story are far more likely to adopt genuinely and sustain it. The trade-off is speed, a pilot-first approach is slower to reach full coverage than a mandate, but it is far more likely to actually stick once it gets there.

Direct answer

When a full scan is too expensive, random, stratified, reservoir, and hash-based sampling each estimate data-quality metrics (null rate, mean, distinct count) with different trade-offs in bias risk and implementation complexity, and the choice between sampling and full-scan validation is a trade-off between cost and the risk of missing a rare-but-important issue that a sample, by definition, may not contain.

Structured elaboration

• Simple random sampling: unbiased for uniformly-distributed metrics, but requires knowing the total population size up front or an efficient way to sample uniformly, which is not always trivial on a distributed dataset without a full scan to begin with.
• Stratified sampling: samples proportionally within known subgroups (by region, by product category), which is important when the metric of interest varies meaningfully across strata, since plain random sampling could, by chance, under-represent a small-but-important stratum.
• Reservoir sampling: allows uniform random sampling from a stream of unknown total size in a single pass, without needing to know the population size in advance, well suited to a continuously-arriving pipeline rather than a static table.
• Hash-based sampling: deterministically sample by hashing a key (a user ID) and keeping records whose hash falls in a target range, which gives reproducible sampling (the same sample every run) and is efficient to compute in a distributed system without coordination.
• Sampling versus full scan trade-off: sampling dramatically reduces cost and lets you validate a much larger table on a tighter budget, but it fundamentally cannot guarantee catching a rare issue that affects a small fraction of rows, if the sample size is smaller than the reciprocal of the rare issue's rate, you have a real, non-negligible chance of missing it entirely, no matter how the sample is constructed.

Worked example

For estimating a null rate with 95% confidence and a 1% margin of error on a very large table, standard sample-size formulas for a proportion (assuming the true rate is near 0.5, the most conservative assumption for worst-case sample sizing) suggest roughly 9,600 rows regardless of the total population size, once the population is large enough that the finite-population correction becomes negligible (the finite-population correction is a small downward adjustment to the sample-size formula that only matters when the sample itself would be a large fraction of a small total population, for example sampling 5,000 rows out of a 6,000-row table; for a table with millions or billions of rows, the correction shrinks to essentially zero and the plain formula above already gives the right answer), a number the vast majority of practical dashboards or pipelines could compute far more cheaply than a full multi-billion-row scan.

Trade-offs and pitfalls

The pitfall most teams miss is assuming a sample-based estimate of an average rate (like an overall null rate) automatically extends to confidence about a rare, specific event (like "did any row have a catastrophically wrong value"), it does not; a random sample is well-suited to estimating aggregate statistics with a known confidence interval, but is poorly suited to guaranteeing detection of a rare anomaly, which needs either a much larger sample specifically sized for the rare event's expected rate, or a complementary full-scan check targeted narrowly at just that specific rare-event pattern rather than a general-purpose sample.

Guard the denominator explicitly and pick a deliberate default for the zero case, rather than letting the database either error or silently return NULL, since a silent NULL is easy to miss downstream in a dashboard that just shows a blank tile.

Approach

```sql
SELECT campaign_id, conversions, impressions,
CASE WHEN impressions = 0 THEN 0.0 ELSE ROUND(conversions * 100.0 / impressions, 2) END AS conversion_rate_pct,
(impressions = 0) AS zero_denominator
FROM camp;
```
Returning an explicit `0.0` plus a separate `zero_denominator` boolean flag is more honest than either erroring or silently returning NULL: a dashboard consuming this can choose to grey out the tile using the flag, rather than displaying a bare `0.00%` that looks identical to "the rate really is zero."

Worked example (verified by execution)

Given campaigns with (10 conversions, 1000 impressions), (0, 0), and (3, 0): the query returns `1.0%` with `zero_denominator = false` for the first, and `0.0%` with `zero_denominator = true` for the other two, correctly distinguishing "no data yet" from "a real, low rate."

Trade-offs and pitfalls

For a campaign with a genuinely tiny number of impressions (say 3 conversions out of 4 impressions), a raw ratio is technically defined but statistically meaningless; add-one (Laplace) smoothing, computing `(conversions + 1) / (impressions + 2)`, pulls small-sample estimates toward a more conservative middle value and avoids reporting a wildly overconfident 75% rate off of 4 data points. Whether smoothing is appropriate depends on the audience: a raw rate is more transparent for an engineer debugging a specific campaign, while a smoothed rate is safer for an automated ranking or alerting system that shouldn't be fooled by small-sample noise.

Direct answer

An anomaly-detection approach for a business metric across many segments (stores, regions) should combine simple, explainable statistical rules (a seasonally-adjusted rolling z-score, or STL seasonal decomposition to separate trend/seasonal/residual components) as the default detector, reserving a lightweight ML model only for cases the simple rules demonstrably cannot handle well, and must explicitly account for day-of-week and holiday seasonality, since raw statistical rules applied to naturally cyclical data generate constant false alarms otherwise.

Structured elaboration

• Seasonality-aware baselines: compare a segment's current value against its own historical distribution for the same day-of-week (or, for holiday-sensitive businesses, the same day-relative-to-holiday), not a flat trailing average, since a Monday is not comparable to a Saturday for most consumer businesses.
• Reducing false positives on noisy, low-volume segments: a small store with naturally high day-to-day percentage variance (because its absolute volume is low) will trip a fixed percentage-based threshold constantly if every store uses the same threshold; scale the sensitivity per segment based on its own historical variance, or apply a minimum-absolute-change floor alongside the percentage threshold so a tiny store's noise does not generate the same alert volume as a large store's genuine anomaly.
• Prioritizing and routing alerts: rank alerts by a combination of statistical significance and business impact (an anomaly in a high-revenue segment matters more than the same statistical anomaly in a negligible one), and route by that priority so an on-call analyst sees the highest-impact anomalies first rather than a flat, unranked list.

Worked example

Daily revenue across 100,000 stores: a naive fixed threshold of "flag anything more than 2 standard deviations from the trailing 7-day average" fires constantly on Sundays for stores that are naturally quieter that day, training the on-call rotation to ignore Sunday alerts entirely. Replacing the baseline with a same-day-of-week rolling average (comparing this Sunday against the last several Sundays, not the last 7 days flatly) removes the bulk of that noise, and adding a per-store variance-scaled threshold (rather than one global threshold) prevents small, naturally-noisy stores from dominating the alert volume relative to large, stable ones where the same percentage move is actually far more unusual.

Trade-offs and pitfalls

The design decision most teams get wrong is applying one global threshold across segments with very different natural variance, which simultaneously over-alerts on noisy small segments and under-alerts on large, stable ones where even a modest percentage move is genuinely significant. A second common failure is detecting "the metric is broken" (a join bug, a missing partition) using the same anomaly detector built for organic business anomalies; the detector should explicitly also watch for signatures specific to pipeline failure (a sudden hard drop to exactly zero, a partition that never landed at all) rather than relying purely on statistical distance from a historical baseline to catch those cases.