The Late Arrival

An easy AI Coding interview practice problem on DataDriven.

Domain
AI Coding
Difficulty
easy
Seniority
L4
Asked in
Shopify

Problem

Finance reports the daily orders dashboard is missing roughly 3% of revenue versus the source system of record. Reproduce by running tests/test_late_events.py. Fix the pipeline so the test passes; keep any currently-passing tests green. Be ready to walk an interviewer through what changed and how a re-run on the next hour does or does not double-count.

The clock was lying.

Worked solution and explanation

The clock was lying

Finance says 3% of revenue vanished, but this isn't an arithmetic bug, it's a late-arriving data problem wearing a dashboard costume. Every event carries two clocks: event_time (when the order was placed on the device) and ingest_time (when the broker actually received it). The starter windows on event_time alone. Order o3 was placed at '10:55' but a mobile buffer held it until '11:18'. The '11:00' run queried [10:00, 11:00) and couldn't see o3 (not ingested yet); the '12:00' run queried event_time [11:00, 12:00) and skipped it too ('10:55' is before '11:00'). No run ever claims it, so it's lost forever, not late.

Why the strict window drops o3

Step 1: Trace when o3 becomes visible

At the '11:00' run, o3 has ingest_time '11:18', still in the future. No query can return a row the broker hasn't received. To the pipeline, o3 does not exist yet.

Step 2: Watch the event_time window move past it

By '12:00' the run advances to event_time [11:00, 12:00). But o3's event_time is '10:55', now below the lower bound. The one run that could finally see o3 refuses to match it. The event falls through the crack.

Step 3: Switch the predicate to ingest_time

Query ingest_time_gte=start and ingest_time_lt=end. The '12:00' run's ingest window [11:00, 12:00) contains '11:18', so o3 lands here, and only here.

etl/aggregate.py
ALLOWED_LATENESS = timedelta(minutes=30)  # mobile buffer bound; see docs/contracts/mobile-buffering.md

def build_daily_orders(start: datetime, end: datetime) -> int:
    """Aggregate events INGESTED in [start, end) into the run's window.

    Windowing on ingest_time (not event_time) means every event is
    claimed by exactly one run, the one live when the broker received
    it, so a straggler buffered on a device is picked up by the run
    after it finally lands, and never counted twice.
    """
    events = read_events(
        topic="orders.events",
        ingest_time_gte=start,
        ingest_time_lt=end,
    )
    rows = []
    for e in events:
        rows.append({
            "order_id": e["order_id"],
            "user_id": e["user_id"],
            "amount_cents": e["amount_cents"],
            "event_time": e["event_time"],
            # Output stays keyed on the run's LOGICAL window so a retried
            # run upserts over itself instead of writing a second row.
            "window_start": start,
            "window_end": end,
        })
    return upsert_daily_orders(rows)

Window on ingest_time; keep the output key on the logical window.

Because each event has exactly one ingest_time, the half-open ingest windows tile the timeline with no gaps and no overlaps, so o3 is written by the '12:00' run and by no other. Keep the output keyed on the run's logical window_start and window_end, not on the event's own time, so a retried '12:00' run upserts over itself rather than minting a second row.

Widen the `event_time` read
Read event_time_gte=start - ALLOWED_LATENESS. The '12:00' run now sees o3 (10:55 >= 10:30), but it ALSO re-reads o2 (10:30), already counted by the '11:00' run under a different window key. Same order, two windows: a cross-window duplicate the (window_start, window_end) upsert cannot dedupe.
Filter on `ingest_time`
Read ingest_time_gte=start. Each order is claimed by the single run live at ingestion. o3 counted once, o2 never re-read, nothing to dedupe. The trade-off you should name: o3's revenue books to the ingest hour, not the event hour.

Common follow-up questions