Section C · Critical

Data Quality Advanced

Beyond not-null and unique: statistical detection, CI/CD for data, SLOs, and incident frameworks. This is how you keep trust at scale and prove rigor in a quality-focused interview round.

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.

First-hire calibration

Start with cheap z-score + null-rate + distinct-count monitors on your few trusted metrics. Reach for PSI/KS when you have model features or you've been burned by a silent distribution shift. Don't buy a platform to do what 20 lines of SQL + a schedule can.

Anomaly detection approaches (ranked by effort)

Rule + threshold — cheapest, brittle. Good for hard invariants (revenue ≥ 0).
Rolling z-score / percent-change bands — adapts to trend; the everyday workhorse.
Seasonal baselines — compare this Monday to prior Mondays; decompose trend/seasonality so weekends don't page you.
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.

dbt unit test (yaml)

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.

Quality is easier on well-shaped data. Go deep on modeling: 06 then 06b — Advanced →

✓ Check yourself

Answer from memory before opening each one.

The mean of a status-derived score has been steady for a month, yet the share of rows with status 'unknown' rose from 2% to 30%. Which monitors would catch this, and why did the mean not?

Show answer
Null-rate, distinct-count and category-set monitors catch it, as does a bucket-proportion comparison such as PSI. The mean held because the other values shifted to offset the new ones, so a single summary statistic hid a change in the distribution. See distribution drift.
A pull request changes a CASE expression in the billing model. All data tests pass. What else do you require before merging, and why are the data tests not enough?

Show answer
A unit test with fixed input rows and expected output, and a data diff of the old and new outputs. Data tests assert properties of real data, such as non-null; they do not show the CASE logic is right. A diff proves the change altered only what you intended. See unit testing transforms.
Metered usage and invoices differ by $120 on $4 million. Is that a failure? What do you set up so you can tell?

Show answer
It depends on a tolerance you define up front for rounding and floating point. Store each run's discrepancy as a time series: a stable small gap is rounding, while a gap that grows run after run is a leak. See reconciliation.
One connector fails and twenty downstream models fail with it, producing twenty-one alerts. How do you redesign the alerting?

Show answer
Alert on the root cause and suppress its dependents, grouped by lineage. Route by tier, and put what broke, the impact and a first step in each message. People ignore alerts that arrive in bursts without context. See alert design.
Your SLO is that the daily revenue model is fresh by 8am on 99% of business days. With about 250 business days a year, how many misses fit, and what do you do after two in one week?

Show answer
One percent of 250 is about 2.5 misses a year, so two in a week uses nearly the whole budget. Pause feature work and invest in reliability until you recover. That is the point of an error budget. See SLOs and data downtime.
The CEO shows you two dashboards that report different revenue. Would you write more SQL, and what do you do?

Show answer
Not first. Find out whether the two definitions differ, such as gross against net or different cut-off dates. Then publish one definition in a semantic layer that both dashboards read, and mark the certified dataset. See semantic consistency.

Takeaway

Fixed thresholds miss gradual drift, so track null rate, distinct count and category sets against a baseline; add PSI or KS when features justify them.
Match the method to the metric: a rule, a rolling z-score, a seasonal baseline, or a forecast residual, and no forecast model where a 3-sigma rule works.
Reconciliation matters most for billing: same-grain aggregates, row-level full outer joins, control totals, a stated tolerance and a trended gap.
Data tests check properties of data, unit tests check logic on fixed input, and a data diff proves a refactor changed nothing; use all three in CI.
An SLI is measured, an SLO is the target, and the error budget tells you when to stop feature work.
Alert on the root cause, route by tier and make every alert actionable.
Most quality complaints are definition disputes; settle them with one metric definition in one place.

Checks are easier on well-shaped data, so the next chapter is how to model it. → Modeling Judgement