Data Engineering Interview: How Do You Design Data Quality SLOs for a Critical Pipeline?

Prompt and Applicable Context

A daily factordersettlements dataset processes about 10 million order events from 12 source systems. Finance starts its close at 07:00 UTC. The orchestrator currently reports only whether jobs finished, so a green run can still publish a late partition, omit one source, duplicate an order version, or disagree with source totals.

Design the data quality SLOs and the controls that enforce them. The source systems provide a control manifest containing record count and gross amount by source, currency, and business date. Raw events remain replayable for 30 days, corrections may arrive for 24 hours, and two consumers have different needs: finance requires certified data, while analysts may use a clearly marked preliminary view.

Cover SLIs, objectives, rule placement, release gates, ownership, alert routing, error-budget actions, incident recovery, and rollout validation. The 10 million events, 12 sources, deadlines, retention, and proposed targets are interview assumptions. Production targets require consumer agreement and historical measurements. This is a data question because its core is an operable quality contract for a data product. The work begins before an incident and continues through certification and policy review.

What the Interviewer Is Evaluating

First, can the candidate translate “high-quality data” into observable consumer outcomes? Freshness, completeness, validity, consistency, accuracy, and uniqueness describe different failure modes. One blended quality score can conceal a zero-tolerance duplicate behind several passing checks.

Second, can the candidate choose a trustworthy denominator? Comparing today's row count with yesterday's catches large anomalies but cannot prove completeness. A source manifest, change-log offset range, or independently controlled ledger supplies stronger evidence of what should have arrived.

Third, can the candidate separate an SLI specification from its implementation? “Finance receives certified data before close” expresses the outcome. Measuring a scheduler's finish time is an implementation that misses publication, catalog, permission, and downstream-read failures. Strong answers measure near the consumer boundary and document blind spots.

Fourth, does each failure cause a predetermined action? A hard invariant needs a publication block or quarantine. A warning needs an owner and review path. A page should represent urgent consumer impact. “Alert on every failed rule” transfers design work to the on-call engineer and creates noise.

Finally, can the candidate make ownership executable? Producers, platform engineers, dataset owners, and finance may share responsibility, but each rule, incident, and approval still needs one directly responsible owner and an escalation path.

Clarifying Questions Before Answering

• What does “ready” mean to finance? If close requires certified data at 07:00, measure when finance can query the certified version, not when the last transformation exits. If a preview is useful, publish it under a separate status and contract.
• What independent evidence defines completeness and correctness? A source manifest supports count and amount reconciliation. Without it, use offsets, source snapshots, or ledger totals and label statistical volume checks as anomaly detection rather than proof.
• What is the business key and update model? Uniqueness may apply to (sourceid, orderid, version), while the latest business state may require one winning version per order. Late corrections change both the check and the certification lifecycle.
• Which failures may degrade and which must block? Missing keys, duplicate versions, or ledger mismatches can corrupt close and should block certification. An optional marketing attribute may be quarantined while certified financial fields continue.
• How many quality events exist in the SLO window? A daily pipeline has too few observations for a meaningful monthly 99.9% objective. A rolling 60-business-day target such as 59 timely certifications has understandable granularity.
• Who may waive a failed gate? An exception needs an approver, affected consumers, expiry time, reason, and audit record. The on-call engineer should not silently redefine a financial contract during an incident.
• What recovery is possible? Thirty-day raw replay supports deterministic rebuilds. If source history is mutable or incomplete, recovery and evidence requirements must change.

30-Second Answer Framework

“I would start from finance's decision, then define separate SLIs for certified freshness, manifest reconciliation, business-key completeness, validity, and uniqueness. I would measure the certified table at its consumer boundary and keep zero-tolerance financial invariants separate from budgeted timeliness.

Schema checks run before deployment, row checks run at ingestion, business checks run after transformation, and end-to-end reconciliation gates certification. Finance reads only a versioned certified view; analysts can opt into a labeled preliminary view. Each rule has an action, owner, and runbook. I would canary the contract on one source, compare detections with known incidents, tune false alarms, rehearse replay, and let an agreed error-budget policy change engineering priorities when the freshness objective is exhausted.”

Step-by-Step Deep Dive

Step 1: Define the Data Product and Consumer Outcomes

Write one contract for the certified settlement dataset, not a catalog of every possible check. Name the dataset version, business date, consumers, owner, certification deadline, correction policy, and evidence retained for audit. The two consumer modes should be explicit:

• preliminary: available early, may contain declared late sources, never used for financial close;
• certified: immutable for the stated version, all hard gates passed, with a manifest and quality result ID;
• superseded: replaced by a correction version while previous evidence remains discoverable.

This state model prevents a green orchestration status from becoming an accidental business guarantee. Finance queries only certified; exploratory consumers can trade freshness for certainty without weakening the finance contract.

Step 2: Write Each SLI as Good Events over Eligible Events

Use consumer-visible outcomes and a defined unit. For the scenario, a starting freshness SLI is:

freshness_sli =
  business-day partitions certified and queryable by 06:30 UTC
  / eligible business-day partitions

starting_slo = at least 59 good partitions in a rolling 60-business-day window

The threshold leaves 30 minutes before finance close and one late certification in the example window. Both values are assumptions to negotiate with finance and validate against history. Measure from a consumer query or catalog state that confirms the version is readable; the scheduler's completion timestamp is only a diagnostic signal.

Give other dimensions their own success criteria:

• Completeness: every expected source manifest is present; record count and critical-key coverage reconcile.
• Uniqueness: zero duplicate (sourceid, orderid, version) tuples in a certified partition.
• Validity: required keys exist and contract-controlled fields use permitted types, domains, and ranges.
• Consistency: counts and amounts reconcile by source, currency, and business date before aggregation.
• Correction timeliness: accepted corrections become a newly certified version within an agreed window.

Accuracy is harder to infer from internal shape. A valid, unique amount can still be wrong. Source-controlled totals, ledger reconciliation, sampled business review, and downstream outcome checks provide stronger evidence than a format rule.

Step 3: Choose Hard Invariants, SLOs, and Diagnostics Deliberately

Three classes keep actions understandable:

1. Hard invariants: any violation blocks certification, such as missing business keys, duplicate versions, or unreconciled financial totals.
2. Budgeted objectives: occasional misses are tolerated within a written policy, such as the certification deadline or correction turnaround.
3. Diagnostics: signals that help investigation but do not define consumer success, such as a 20% day-over-day volume change.

Do not convert hard correctness requirements into “allowed bad rows” merely to fit an error-budget formula. One duplicated high-value order can matter more than thousands of harmless optional nulls. Keep each objective and its consequence separate; an overall dashboard score may summarize trends but cannot override a blocking rule.

Step 4: Put Checks at the Earliest Useful Boundary

Run schema compatibility and contract examples in producer and transformation CI. At ingestion, validate parseability, required envelope fields, source identity, offset continuity, and idempotency; quarantine identifiable bad records with reason codes. After transformation, test business keys, referential relationships, version selection, currency rules, and source-level slices. At publication, perform manifest and ledger reconciliation and verify that the exact certified version is queryable.

The same rule need not run everywhere. A non-null unit test with five fixtures validates code paths but says little about a production source. A production aggregate can detect a failure but may arrive too late to protect intermediate consumers. Place a cheap prevention check before change, an operational check where data enters, and an end-to-end check where the promise is consumed.

The following is illustrative, tool-neutral pseudo-YAML rather than a product-specific schema:

dataset: finance.fact_order_settlements
owner: finance-data
consumer: daily-close
certification_deadline_utc: "06:30"
rules:
  - name: required_business_key
    dimension: completeness
    scope: row
    pass_ratio: 1.0
    action: block_and_quarantine
  - name: unique_order_version
    dimension: uniqueness
    scope: [source_id, order_id, version]
    pass_ratio: 1.0
    action: block_certification
  - name: source_manifest_reconciliation
    dimension: consistency
    scope: [source_id, currency, business_date]
    pass_ratio: 1.0
    action: block_certification_and_page_owner

Step 5: Use Independent Reconciliation and Slice the Result

Join the received aggregates to all expected manifest rows, including sources that delivered nothing. A left join from received data alone makes an absent source invisible. Compare counts and amounts in their original currency before any lossy conversion. Record manifest ID, source watermark, transformation version, table snapshot, and rule results together.

Slice failures by source, region, currency, event type, and pipeline stage. A global 99.99% pass rate can hide a complete outage for a low-volume source. Conversely, one known late source should not generate identical pages from ingestion, transformation, and publication. Route the first actionable broken boundary and suppress derivative alerts while preserving diagnostics.

Historical bands are useful for volume anomaly detection, but seasonality, promotions, and newly launched sources can move them legitimately. Treat an anomaly as evidence to investigate until an independent control total confirms loss.

Step 6: Make Gates, Exceptions, and Ownership Executable

Each rule should declare severity, action, direct owner, backup owner, escalation target, runbook, and maximum acknowledgement time. A practical split is:

• source schema or manifest failure: producer owns correction; ingestion owner contains impact;
• transport, deduplication, or orchestration failure: data platform owns recovery;
• semantic transformation or reconciliation failure: dataset owner owns diagnosis and rebuild;
• business-definition dispute: finance data steward decides the contract, with an auditable change review.

A waiver creates a new explicitly degraded version or keeps the dataset preliminary. It records who approved it, why the business accepts the risk, which consumers are affected, and when the exception expires. Never overwrite a failed result or silently point the certified alias at an unverified partition.

Step 7: Define an Error-Budget Policy That Changes Decisions

For the example freshness SLO, one late partition is the rolling 60-business-day budget. Track consumption and burn rate. A single late day consumes the entire budget; an incorrect certified financial partition triggers the incident policy regardless of remaining freshness budget.

Agree on actions before a miss. When a burn-rate forecast shows that the sole allowed miss is at risk, review the top cause and validate recovery capacity. At exhaustion, pause risky pipeline changes, prioritize reliability work, and require an owner-approved exit criterion. This policy turns the SLO into a decision tool. A dashboard without a consequence is only reporting.

Revisit the SLI when detections do not correlate with consumer incidents or pages have low precision. Move measurement closer to finance, add missing slices, or relax a noisy diagnostic. Do not loosen a hard invariant to silence operational noise; fix the implementation or routing.

Step 8: Canary, Rehearse Recovery, and Prove Coverage

Shadow-evaluate rules without gating on one source and a representative 14-to-30-day historical window. Seed controlled failures: omit a source, duplicate an order version, break a schema, delay publication, and change an amount while preserving row count. Verify the expected rule, owner, and runbook activate once.

Then gate one low-risk source, expand by source criticality, and finally protect certification. Acceptance criteria include detection coverage for seeded failures, no incorrect certified partition, bounded false-page rate, consumer-readable evidence, deterministic replay from raw events, and recovery before the finance deadline. Measure rule cost and scan only changed partitions where correctness permits; a quality system that routinely misses its own deadline undermines the pipeline.

After rollout, review incidents that consumers found manually and alerts that produced no impact. Those two sets reveal missing coverage and low precision. Version contract changes with the data model, require producer and consumer review, and retain the exact rule set used for every certification.

High-Quality Sample Answer

“I would define the service from finance's point of view: a specific settlement partition is certified, queryable, reconciled, and available before close. A finished DAG is a diagnostic, so I would probe the published version and retain its manifest, snapshot, code version, and quality result.

For freshness, I might propose 59 certified partitions by 06:30 over a rolling 60-business-day window, then calibrate that with finance and historical performance. Correctness has separate hard gates. Every source manifest must arrive; counts and original-currency amounts must reconcile by source and business date; required business keys must be complete; and (sourceid, orderid, version) must be unique. I would never average these into a score that lets one blocking failure pass.

Checks run at several boundaries. CI catches incompatible schema and transformation cases. Ingestion checks envelopes, offsets, and idempotency and quarantines identifiable failures. Transformation checks semantic and referential rules. Publication performs independent reconciliation and confirms the exact version is readable. Finance receives only the certified alias; analysts may use a preliminary view carrying source and quality status.

Every failed rule maps to an action and one owner. A producer handles a broken source contract, the platform team handles transport, and the dataset owner handles transformation and certification. A waiver is time-limited, consumer-visible, and approved by the finance steward. An incorrect certified partition is an incident even when the freshness budget remains.

I would shadow the rules on historical partitions, inject missing-source, duplicate-key, schema, delay, and same-count-wrong-amount failures, then gate one source. I would expand only when detection, routing, replay, false-page rate, and rule runtime meet acceptance criteria. If the single allowed late day is consumed, the agreed policy moves capacity from feature work to reliability until the exit criteria are met.”

Common Mistakes

• Monitoring only job success → A completed job can publish incomplete or unreadable data → Measure the certified object at the consumer boundary.
• Using yesterday's row count as proof of completeness → Legitimate demand changes and silent source loss look similar → Reconcile against manifests, offsets, snapshots, or another controlled total.
• Collapsing all checks into one score → Many passing optional rules can mask one financial invariant → Give every critical dimension its own threshold and action.
• Setting 99.9% on one daily event per month → The denominator cannot express that precision → Choose a window and unit with meaningful granularity.
• Giving every bad row an error budget → Row count ignores business value and hard integrity → Keep zero-tolerance invariants outside budgeted timeliness.
• Paging on every failed rule → One source break creates a cascade of unactionable alerts → Page the first owned boundary and suppress downstream derivatives.
• Letting on-call waive a gate silently → Consumers cannot assess risk and audit evidence disappears → Use an approved, expiring, consumer-visible exception.
• Testing only the happy path → The team learns that recovery is nondeterministic during close → Inject representative failures and rehearse replay before enforcement.
• Adding expensive full-table scans to every run → Quality checks consume the freshness margin they protect → Use incremental checks plus periodic full reconciliation where safe.

Follow-Up Questions and Responses

Follow-up 1: What if the sources cannot provide manifests?

Rank substitutes by independence. Source snapshots, database log sequence ranges, broker offsets, file inventories with checksums, and a separately produced ledger are stronger than the target table's own row count. Use historical bands to detect anomalies, disclose that they cannot prove completeness, and make manifest or control-total delivery part of the producer contract for critical sources.

Follow-up 2: What if finance demands zero late partitions forever?

An implicit 100% target leaves no mechanism for prioritizing reliability work and may be operationally indefensible. Quantify redundancy, replay time, source dependencies, and cost required to approach the request. Keep financial correctness as a hard gate, propose a measurable freshness target plus a stricter aspirational target, and obtain explicit business, engineering, and operations agreement on the response policy.

Follow-up 3: How do you handle a correction after a partition was certified?

Create a new immutable certification version, link it to the prior version and correction manifest, rerun all affected reconciliation rules, and atomically move the certified alias only after approval. Preserve the old evidence for audit. Measure correction turnaround separately because the original freshness SLI cannot describe how quickly known errors are repaired.

Follow-up 4: How do you prevent alert fatigue across hundreds of datasets?

Tier datasets by consumer impact, require a direct owner before paging, and page only on actionable SLO threat or hard-gate failure. Group related rule failures under the earliest broken dependency, route warnings to review queues, and periodically compare pages with actual consumer incidents. Retire or redesign rules with persistently low precision.

Follow-up 5: What changes for a streaming pipeline?

Replace one daily certification opportunity with event-time windows or consumer minutes. Define freshness from event time to queryable state, completeness from closed watermark or source offsets, and correction behavior for late events. Use burn-rate alerts over short and long windows, while retaining hard per-record or per-key invariants that must quarantine data immediately.

Follow-up 6: How would you evaluate a data quality tool?

Start with contract fit rather than feature count. Verify row and aggregate rules, composite-key uniqueness, incremental execution, failed-record evidence, versioned results, orchestration gates, ownership metadata, APIs, alert routing, and cost at production scale. Run the seeded-failure suite through each candidate; a polished dashboard cannot compensate for missing end-to-end reconciliation or an unenforceable release gate.