A Data Pipeline Interview Exercise With Late Events

TL;DR

• In this fictional pipeline exercise, late store transactions can revise an earlier daily sales total.
• Separate event time from processing time and define when results are provisional or revised.
• Use event identity and correction rules to avoid double counting, then reconcile the visible total through replay and failure cases.

Put the late event on a timeline

Use this fictional data-pipeline interview: stores upload transactions to a daily sales dashboard. Most arrive promptly, but one store loses connectivity and sends yesterday's transactions this morning. The interviewer asks whether those records belong in yesterday's total or today's total.

Start by defining the business question. A sales-by-transaction-date report differs from an ingestion-volume report. Neither should silently borrow the other's clock. Ask which timestamp is authoritative, how it is recorded and what happens when it is missing or implausible.

The example concerns a reporting design, not an accounting policy. In a real organisation, the owner of the metric must approve how late and corrected records affect published figures. Your interview answer should expose that dependency while proposing a testable data flow.

Keep event time and processing time separate

For the exercise, record the transaction's event time and the time the pipeline received it. Do not overwrite one with the other to simplify the schema. The difference helps explain lateness and operational behaviour.

Choose explicit time-zone and day-boundary rules. A store's local business day may not match the server's calendar day. Describe the intended grouping rather than assuming a timestamp string can be truncated safely.

Define provisional and revised results

A dashboard may need a timely preliminary total before all events arrive. Explain that tradeoff to the reader rather than presenting the first value as immutable truth. A visible freshness or completeness state can help users interpret revisions.

One candidate design keeps recent periods open for automatic updates and handles older arrivals through a defined reconciliation path. The chosen boundary depends on observed lateness and business needs; do not invent a universal number of hours.

Ask what consumers do with the figure. A manager glancing at activity may tolerate revisions differently from a downstream process that triggers an irreversible action. The same aggregate can require different serving contracts for different uses.

Prevent duplicate and corrected records from inflating totals

An offline uploader may retry the same transaction. Use stable event identity and an explicit rule for versions or corrections so that reprocessing does not simply add the amount again. Distinguish a duplicate delivery from a new transaction that happens to have the same amount.

If a transaction changes, decide whether the system replaces its contribution or applies a traceable adjustment under the approved metric definition. The aggregate update needs to remain consistent with the authoritative event state, including failures between storing an event and updating the projection.

Do not promise exactly-once business results merely because a processing framework offers a particular delivery guarantee. Explain the relevant storage and replay boundaries in the system you are proposing.

Reconcile the visible total

Create a reconciliation method that compares an aggregate with its eligible source records for a defined period and version. This can reveal missing updates, duplicates or inconsistent filters. Keep the comparison's time and policy assumptions explicit.

When a late batch arrives, decide how revised totals become visible and whether affected consumers need a notification or correction record. A silent overwrite may be fine for a provisional dashboard and inappropriate for an exported report people have already relied on.

Our system design frameworks guide can help you draw the separation between raw events, processed state, aggregates and serving views. Each layer should have a clear replay and correction role.

A processing watermark is also not a business declaration that every possible event has arrived. The Apache Beam programming guide discusses watermarks and late data. If you use such a mechanism, connect its estimate and configured handling to the reporting policy, including what happens to records that arrive beyond the normal processing window. Preserve a deliberate reconciliation path for those exceptions.

Rehearse the failure cases

Have a practice partner introduce an event with an impossible future timestamp, a duplicate batch and a correction arriving after the normal update boundary. Walk each through validation, identity handling and reconciliation. Do not simply drop inconvenient records without a visible exception process.

Also restart the worker halfway through a replay. Explain how the system resumes without changing the meaning of the total. If some records remain unresolved, the dashboard should not imply that all data is complete.

Finish with the metric's clock, the late-arrival policy and the correction path. Use the mock interview strategy to practise saying which decision belongs to the business owner and which belongs to the pipeline design. A strong answer makes time, identity and revision semantics explicit before choosing a processing engine.