Data Engineering interview question · Most asked
Scenario: warehouse revenue is 4% higher than the payment provider's settlements. How do you investigate?
Summary
Reconcile day by day to localise the gap, then test the usual causes: join fan-out (order totals joined to lines or payments), duplicate events or rows from retries and reruns, refunds and cancellations counted as revenue, currency handling, and date basis (order date vs settlement date).
Longer Answer
Check uniqueness of every table's grain, count duplicate event IDs, compare gross and net revenue, and inspect days with the largest differences. Fix root causes with idempotent loads, deduplication and explicit grains, then add a daily reconciliation that blocks publication.
Key Points
- Localise with daily reconciliation.
- Check fan-out and duplicates.
- Refunds and date basis.
- Prevent with grains, idempotency and reconciliation.
Deep dive: ETL and Data Pipelines: Capstone: The Revenue Report That Didn't Match
Explanation
What interviewers look for
A systematic investigation and preventive controls.
More most-asked Data Engineering questions
- Batch or streaming: how do you decide?
- What is the difference between ETL and ELT, and when would you choose each?
- What is change data capture (CDC), and why is log-based CDC preferred over polling?
- What does it mean for a data pipeline to be idempotent, and how do you achieve it?
- How do you implement incremental extraction, and what can go wrong?
- How do you deduplicate events, and what does 'exactly-once' really mean?
- What is a star schema, and why is it used in data warehouses?
- What is the grain of a table, and what is join fan-out?