Prompt and context
A payment system reports 1,000 orders yesterday, but the warehouse report has 998. Design a pipeline that finds differences across the source, landing layer, transformation models, and report.
Do not anchor the answer to dbt, Airflow, or a specific warehouse. The goal is a repeatable evidence chain that distinguishes missing, duplicate, mismatched, late, and differently defined records.
What the interviewer is testing
Reconciliation grain
Define the business key, time semantics, and monetary precision before choosing order, merchant, day, or batch grain. A count-only check can hide offsetting errors.
Explainable checks
Check counts, amounts, status distributions, key uniqueness, and sampled details. Persist the rule version and input snapshot so every result is reproducible.
Closed-loop incidents
Classify differences, assign an owner, preserve evidence, support replay, and record closure. An email without a traceable case is not an operational loop.
Safe operations
Handle late data, idempotent reruns, partitions, schema changes, freshness, and audit history without making repairs create new discrepancies.
Clarifying questions to ask
- Which timezone and event-time definition does “yesterday” use?
- What is the order business key, and how do refunds and cancellations count?
- Can the source send late updates or deletes?
- Is the warehouse batch, streaming, or hybrid?
- Which currency and decimal precision apply?
- Should remediation be automatic, replayed, or manually approved first?
30-second answer framework
“I would freeze one time window and snapshot, reconcile counts, amounts, and statuses by order key, then drill into missing, duplicate, not-yet-arrived, and transformation errors. Each difference stores a rule version, source summary, warehouse summary, and owner. Watermarks and a retry window separate late data from true loss. An idempotent backfill or replay repairs approved cases, then the same snapshot is reconciled again. Inputs, outputs, alerts, and human actions go into an audit table.”
Step-by-step deep dive
Step 1: Freeze scope and snapshot
Record the source batch, event-time range, processing time, timezone, and snapshot ID. Results must point to the same input set instead of drifting while the source changes.
Step 2: Normalize keys
Normalize order ID, merchant ID, status mapping, currency, and amount precision. Keep a hash or summary of each side; retain only the minimum sensitive data needed for diagnosis.
Step 3: Run layered checks
Compare count and total amount first, then group by merchant, date, and status. Follow with key uniqueness, null, duplicate, tolerance, and detail anti-join checks. Equal totals do not prove equal details.
Step 4: Separate lateness from revision
Use event time and a watermark to distinguish not-yet-arrived data from missing data. Keep records pending inside a backfill window; escalate only after it closes. Recompute updates, refunds, and deletes using version or change time.
Step 5: Classify and remediate
Rank cases by amount, order count, business impact, and duration. Automatic repair is limited to idempotent backfill or replay. Financial-definition changes require approval and before/after evidence.
Step 6: Rerun and audit
Run idempotently by snapshot ID, partition, and rule version. Store input scope, query version, metrics, detail differences, alerts, owner, retries, and close time for review.
Model high-quality answer
“I would generate a snapshot_id for every source batch and freeze a UTC event-time window. A normalization layer aligns order IDs, statuses, currencies, and amount precision. We compare counts, amounts, and status distributions, then use primary-key anti-joins to find source-only, warehouse-only, duplicate, and amount-mismatch records.
A watermark and a two-hour backfill window classify late events as pending; only unresolved differences after the window escalate. The difference table stores rule version, both record summaries, amount delta, owner, and evidence. An approved replay is idempotent on snapshot_id plus business key. After repair I rerun the same snapshot and write the input range, code version, alerts, and closure time to the audit table.”
Common mistakes
- Total-count-only comparison → offsetting errors stay hidden → use business-key anti-joins and persist detail differences.
- Processing time used as event time → late or cross-timezone records shift windows → define event time, processing time, and timezone separately.
- Refund, cancel, update, and delete semantics omitted → each layer explains a different number → version the status mapping and treatment rules.
- Floating-point money comparison → decimal noise becomes a false incident → use integer minor units, currency, and an explicit tolerance.
- Every late record alerts immediately → noisy incidents trigger bad repairs → use a watermark and backfill window with a pending state.
- Non-idempotent repair → reruns duplicate rows → upsert idempotently by snapshot and business key.
- No owner, evidence, or closure state → incidents cannot be assigned or reviewed → add a case and audit record.
- Only final numbers retained → the result cannot be reproduced → store input snapshot, rule version, and code version.
Follow-up questions and responses
Follow-up 1: What if totals match but records differ?
Use business-key anti-joins, duplicate-key checks, and grouped distributions to find offsetting additions and removals. Persist the detail differences for review.
Follow-up 2: How do you avoid false late-data alerts?
Use event time, a watermark, and an explicit backfill window. Mark cases pending inside the window and escalate unresolved cases after it, recording the window version.
Follow-up 3: How can repair safely rerun?
Use snapshot ID, partition, and business key as idempotency keys; upsert or deduplicate writes; compare counts and amounts before and after each retry.
Follow-up 4: How do you test the reconciliation rules?
Create fixtures for missing, duplicate, late, refund, and currency cases. Test rule versions, tolerances, boundary dates, and timezones, then monitor false-positive rates.
Follow-up 5: How do you route alerts and ownership?
Route by amount, count, duration, and business severity. Link each alert to a batch, evidence, and repair action; closure requires a reason and remains auditable.
Source 1: dbt sources and source tests
The dbt Developer Hub sources documentation describes declaring sources, building lineage, attaching data tests, and measuring freshness—useful governance patterns for reconciliation inputs.
Source 2: Freshness and SLA windows
The dbt Developer Hub source-freshness guide shows loaded-at fields, warning/error thresholds, and snapshot results for freshness management, which informs late-data windows and escalation.
Source 3: Data-quality checks
dbt Labs’ data-quality guide covers uniqueness, relationships, null checks, and freshness, illustrating how automated checks should connect to versioned models and alerts.