Data Quality Advanced

Distribution drift detection

Hard thresholds miss gradual change. Detect when a column's distribution shifts, not just when a value breaks a bound.

• Summary-stat monitoring: track mean, stddev, null-rate, distinct-count, min/max per run; alert on z-score deviation from the trailing baseline (the SQL is in 03b #10).
• Population Stability Index (PSI): bucket a column and compare bucket proportions now vs a reference period; PSI > 0.2 ≈ significant shift. Common for feature/segment drift.
• KS test: compares two continuous distributions for "are these the same distribution?" Good for detecting a changed upstream calculation.
• Categorical drift: new/disappeared categories (a new GPU type appears; a status value vanishes) — track the value set over time.

Anomaly detection approaches (ranked by effort)

1. Rule + threshold — cheapest, brittle. Good for hard invariants (revenue ≥ 0).
2. Rolling z-score / percent-change bands — adapts to trend; the everyday workhorse.
3. Seasonal baselines — compare this Monday to prior Mondays; decompose trend/seasonality so weekends don't page you.
4. Forecast residuals — fit a simple model (e.g. moving-average / Prophet-style), alert when actuals leave the prediction interval. For mature, high-value metrics.

The judgement is matching method to metric: don't put a forecast model on a metric a 3-sigma rule covers.

Reconciliation, deeply

The highest-value quality work for a usage/billing business — proving two systems agree. Patterns:

• Aggregate reconciliation: sum a measure on both sides at the same grain, compare within tolerance (metered vs invoiced — 03 #12).
• Row-level reconciliation: FULL OUTER JOIN on key, surface rows present on one side only and mismatched values — catches both leakage and over-charge directionally.
• Control totals: carry a count/sum from source through each layer; assert it's preserved (or changes only by documented logic). A classic finance-grade check.
• Tolerance & rounding: define acceptable epsilon (floating point, rounding rules) so you alert on real breaks, not noise.
• Cadence: run reconciliation every load, store results as a time series, and trend the discrepancy — a slowly growing gap is itself a signal.

CI/CD for data

Quality enforced at merge time, before bad logic ships. The modern analytics-engineering standard.

• Slim CI: on a PR, build and test only modified models and their downstream (dbt state:modified+) against a sample — fast feedback without rebuilding everything.
• Tests gate the merge: schema tests, unit tests, and reconciliation run in CI; red build blocks merge.
• Write-audit-publish at deploy (chapter 04b): build to staging, run audits, atomically swap on pass.
• Environments: PR builds in an isolated schema; nothing touches prod until merged and audited.
• Data diff in CI: show the reviewer how the PR changes output rows, not just SQL (datafold-style / EXCEPT diff). Reviewing data changes, not just code, is a maturity signal.

Unit testing transforms

Tests that don't depend on production data — fixed inputs, asserted outputs. They catch logic bugs (a wrong CASE, an off-by-one window) deterministically.

unit_tests:
  - name: test_interruptible_discount
    model: fct_billing
    given:
      - input: ref('stg_usage')
        rows:
          - {rental_type: 'interruptible', gpu_seconds: 3600, rate: 1.00}
          - {rental_type: 'on_demand',     gpu_seconds: 3600, rate: 2.00}
    expect:
      rows:
        - {rental_type: 'interruptible', cost: 1.00}
        - {rental_type: 'on_demand',     cost: 2.00}

Distinguish clearly in the interview: data tests assert properties of real data (is it null?); unit tests assert the transform logic given synthetic input. You want both.

Regression testing via data diff

When refactoring a model, prove the output is unchanged (or changed exactly as intended) with the EXCEPT-both-ways diff from 02b. Make "0 unexpected differing rows" a release gate. This is how you refactor a metric definition without breaking trust.

SLOs / SLIs & data downtime

• SLI (indicator): a measured property — freshness lag, % tests passing, reconciliation discrepancy.
• SLO (objective): the target — "daily revenue model fresh by 8am 99% of business days."
• Data downtime: time data was wrong/missing/late. A useful north-star metric for your function: drive it down over time.
• Error budgets: if you blow the SLO, you pause feature work and invest in reliability — a principled way to balance new models vs trust.

Publishing SLOs as the first data hire sets expectations and protects you from being blamed for best-effort Tier-3 data being treated as Tier-1.

Alert design (fighting fatigue)

• Severity routing: Tier-1 (revenue/billing) pages; everything else goes to a channel. An alert that doesn't require action shouldn't page.
• Actionable alerts only: each alert names what broke, the impact, and a first step. "Test failed" with no context trains people to ignore alerts.
• Deduplicate & group: one upstream break cascades into 20 downstream failures — alert on the root, not all 20.
• Tune thresholds: a noisy check is worse than no check — it erodes trust in alerting itself. Treat alert fatigue as a quality failure.

RCA frameworks

• 5 Whys: drill from symptom to root cause ("revenue dropped" → "usage table half-empty" → "connector silently failed" → "API token expired" → "no expiry alert"). Fix the deepest practical cause.
• Blameless post-incident review: timeline, impact, root cause, action items — focused on the system, not the person.
• Every incident yields a test/monitor so the same failure can't recur silently. This is how a one-person team compounds reliability.
• Track MTTD/MTTR (mean time to detect/resolve) — improving detection time is often higher leverage than prevention.

Semantic consistency (the metrics layer)

The most common "data quality" complaint isn't broken pipelines — it's two dashboards showing different "revenue." Defend against it:

• Single definition per metric in a metrics/semantic layer (dbt Semantic Layer, Cube, LookML) — defined once, consumed everywhere.
• Certified vs draft datasets: mark which models are blessed sources of truth so people stop querying random tables.
• Definition reviews: when "active customer" is ambiguous, the fix is a written, agreed definition — not more SQL. Often the highest-leverage quality work.

PII, lineage, access

• PII handling: identify it, minimize it (drop/mask at ingest where possible), and restrict access. Know the difference between masking, tokenization, and hashing.
• Column-level lineage: trace a metric back to source columns — essential for impact analysis and audits. dbt + a catalog gets you most of the way.
• Access control: least-privilege roles; sensitive marts gated. As first hire you set this precedent.
• Retention & compliance: know retention requirements and deletion (GDPR/CCPA right-to-be-forgotten) — deletes that must propagate through your models.