Late-Arriving Data

Three clocks: event, processing, ingestion

Almost every late-data bug traces back to conflating three timestamps that are quietly different. Get precise about them and half the confusion evaporates.

Late-arriving data is simply the gap between these clocks: an event whose event time is well before its ingestion time. If you always partitioned and aggregated by processing time you'd never see "late" data — but every daily number would be wrong, because a purchase at 23:58 that arrives at 00:04 would land on the wrong day.

Why data arrives late

Interviewers want to hear that you know late data is normal and structural, not a freak event. Concrete causes, roughly in order of how often they bite:

• Offline & mobile clients. A phone in airplane mode queues events for hours or days, then flushes them all with their original event timestamps when it reconnects. This is the classic multi-day straggler.
• Upstream retries & buffering. An at-least-once producer that failed and retried, a collector that batches, an SDK that flushes on a timer — all decouple event time from arrival.
• Out-of-order streams. Kafka only guarantees order within a partition. Across partitions (or after a rebalance / replay) records for the same key interleave, so a 12:00 event can be consumed after a 12:05 one.
• Batch cutoffs & extraction windows. Your 00:00 extract queried the source at 23:59:30; a transaction that committed at 23:59:45 is real, on-time, and simply missed the net — it shows up tomorrow.
• Timezone & clock skew. Source in UTC, another in local time, a device with a wrong clock. Mishandled, these masquerade as lateness and corrupt your day boundaries.
• Corrections upstream. A refund, a back-dated adjustment, a late-settling payment. The fact isn't just late — its value changed after you first saw it.

The design consequence: you cannot wait for "all" the data, because there is no bound on how late a straggler can be. You pick a completeness horizon you're willing to honor and accept a small, quantified error beyond it. That trade-off is the whole game.

Watermarks, allowed lateness & windows

Streaming systems (Flink, Spark Structured Streaming, Beam) formalize the trade-off with watermarks. A watermark is the engine's assertion: "I believe I've now seen all events with event time ≤ T." It's an estimate of completeness, usually computed as max(event_time_seen) − allowed_out_of_orderness. When the watermark passes a window's end, the window fires and emits its result.

• Bounded out-of-orderness is the lateness you budget for up front (e.g. 5 minutes). It sets how far the watermark trails the newest event, i.e. how long you hold a window open waiting for stragglers.
• Allowed lateness is an extra grace period after the watermark passes: the window stays in state and re-fires an updated result if a late record still trickles in, up to the limit.
• Beyond allowed lateness, records are dropped (or routed to a late/side output for separate handling) rather than silently corrupting a closed result.

The knob is the fundamental tension of this whole chapter:

Windows are how you bucket unbounded streams into finite results, and each interacts with lateness differently:

Batch pipelines don't say "watermark," but they make the exact same decision — the equivalent of the watermark delay is your lookback window, covered next.

Batch lookback & reprocessing

In a warehouse you don't hold windows open in memory; instead you re-read a trailing window of event-date partitions on every run and re-merge. Yesterday's run already wrote the 3rd; today's run re-scans the last N days of arrivals, finds any stragglers whose event time is the 3rd, and folds them into the 3rd's partition. Lookback is the batch watermark.

The non-negotiable property is idempotency: reprocessing the same window twice must not double-count. That means the load key is the event's natural/surrogate key, and you MERGE (or partition-overwrite) rather than blind-INSERT. Here's a lookback merge that routes each late fact into the partition of its own event date, not the run date:

-- Re-scan a trailing lookback window of ARRIVALS (ingestion time),
-- but write each row into the partition of its EVENT date.
-- Idempotent: re-running the same window converges to the same result.

MERGE INTO analytics.fct_orders AS tgt
USING (
    SELECT
        s.order_id,
        s.customer_id,
        s.amount,
        s.event_ts,
        CAST(s.event_ts AS DATE)  AS event_date,   -- partition key = EVENT time
        s.ingested_at,
        s.source_updated_at,
        -- if the same order_id was corrected multiple times in the window,
        -- keep only the newest version by source update time
        ROW_NUMBER() OVER (
            PARTITION BY s.order_id
            ORDER BY s.source_updated_at DESC
        ) AS rn
    FROM raw.orders_stream AS s
    -- lookback is on INGESTION time: catch anything that landed recently,
    -- regardless of how old the event itself is
    WHERE s.ingested_at >= DATEADD('day', -3, CURRENT_DATE)
) AS src
ON  tgt.order_id = src.order_id
AND src.rn = 1
WHEN MATCHED AND src.source_updated_at > tgt.source_updated_at THEN UPDATE SET
    tgt.customer_id       = src.customer_id,
    tgt.amount            = src.amount,
    tgt.event_ts          = src.event_ts,
    tgt.event_date        = src.event_date,
    tgt.source_updated_at = src.source_updated_at
WHEN NOT MATCHED AND src.rn = 1 THEN INSERT
    (order_id, customer_id, amount, event_ts, event_date, source_updated_at)
    VALUES
    (src.order_id, src.customer_id, src.amount, src.event_ts,
     src.event_date, src.source_updated_at);

What if a straggler arrives beyond your lookback horizon (a 9-day-late mobile flush when you look back 3)? You need a bounded escape hatch: a periodic wide reprocess (re-merge the last 30 days weekly), or a reconciliation loop (Chapter 06) that flags a partition whose source total drifted and triggers a targeted restatement. The lookback handles the 99% cheaply; the wide sweep handles the tail.

Late facts & late dimensions

So far the fact itself was late. Two subtler cases show up constantly in a dimensional warehouse, and interviewers use them to separate L2 from L3.

Late-arriving facts (dimension not ready yet)

A fact references a dimension member that hasn't loaded — an order for a customer_id your dim_customer hasn't seen. You have three options:

• Inferred / placeholder dimension. Insert a stub row for the unknown key (natural key filled, attributes marked -1/"Unknown", an is_inferred flag) so the fact can join now. When the real dimension arrives, update the stub in place — the surrogate key never changes, so no facts need rewiring. This is the standard, and usually right, answer.
• Park & retry. Route facts with unresolved keys to a quarantine/pending table and re-attempt the join on later runs. Cleaner referential integrity, but the fact is invisible until the dimension shows — bad when the fact is the revenue you're supposed to report.
• Never drop it. The one wrong answer is silently discarding the fact because its FK doesn't resolve. That's data loss disguised as a constraint.

Late-arriving dimensions (the attribute change is late)

The dimension update itself lands late — a customer's tier changed on the 1st but you learned on the 5th. If the dimension is SCD Type 2 (a new row per change with effective-dating), a late change means retroactively opening a version whose validity window is in the past, and re-closing the neighbours around it:

-- A tier change with a back-dated effective date arrives late.
-- Insert the new version at its true valid-from, and re-close the
-- version that was previously "current" so windows never overlap.

-- 1) Close the previously-open version where the late change begins.
--    Windows are half-open [valid_from, valid_to), as in the as-of join below.
UPDATE dim_customer AS d
SET valid_to    = :late_valid_from,
    is_current  = FALSE
WHERE d.customer_id = :customer_id
  AND d.is_current  = TRUE
  AND d.valid_from  <= :late_valid_from;   -- guard: only close an earlier-or-equal version

-- 2) Insert the late version with its TRUE (back-dated) effective window.
INSERT INTO dim_customer
    (customer_sk, customer_id, tier, valid_from, valid_to, is_current)
VALUES
    (:new_surrogate_key, :customer_id, :new_tier,
     :late_valid_from, DATE '9999-12-31', TRUE);

-- 3) Any facts in [late_valid_from, now] were joined to the OLD version's
--    surrogate key. Re-point them so as-of joins resolve to the right tier.
UPDATE fct_orders AS f
SET customer_sk = :new_surrogate_key
WHERE f.customer_id = :customer_id
  AND f.event_date >= :late_valid_from
  AND f.event_date <  DATE '9999-12-31';

Step 3 is the part people forget: a late SCD2 row is only half the fix. Facts already loaded during the back-dated window were stamped with the old surrogate key, so unless you re-point them (or resolve the dimension at query time via an as-of join, below) your history stays wrong. This is exactly why bitemporal thinking earns its keep.

Bitemporal modeling & as-of joins

The SCD2 example above tracks valid time — when a fact was true in the business world. But late data raises a second, orthogonal question interviewers love: what did we believe, and when did we believe it? That's system time (a.k.a. transaction time) — when a row was recorded in your system. Tracking both is bitemporal modeling.

Why you need both: valid time alone lets you answer "what was the customer's tier on the 3rd?" — but a late correction silently rewrites that answer. With system time too, you can also answer "what did the 3rd's month-end report show, run on the 5th, before the correction landed on the 8th?" That reproducibility is mandatory in finance, and it's the difference between "our numbers changed" and "our numbers are auditable."

An as-of join resolves a fact against the dimension version that was valid at the event's time rather than the version that's current now — so history stays stable even as dimensions evolve:

-- Resolve each order to the customer tier that was VALID at event time.
-- Optionally pin SYSTEM time too, to reproduce a report "as we knew it"
-- at a past reporting instant (:as_of_system_ts).

SELECT
    f.order_id,
    f.event_ts,
    f.amount,
    d.tier            AS tier_at_event_time
FROM fct_orders AS f
JOIN dim_customer AS d
  ON  d.customer_id = f.customer_id
  -- valid-time match: the version whose business window covers the event
  AND f.event_ts >= d.valid_from
  AND f.event_ts <  d.valid_to
  -- system-time match: only versions our system had recorded by the
  -- reporting instant, and not yet superseded as of then
  AND d.system_from <= :as_of_system_ts
  AND d.system_to   >  :as_of_system_ts;

Lambda, Kappa & safe restatements

At architecture scale, "how do we correct late data?" becomes a question about the shape of the whole pipeline.

• Lambda runs a fast speed layer (streaming, approximate, tolerates late/out-of-order data loosely) alongside a slow batch layer that reprocesses from immutable raw storage and produces the authoritative, complete result. Serving merges the two. It's correct-eventually and battle-tested, but you maintain the same logic twice and reconcile the layers — real operational tax.
• Kappa keeps a single streaming code path and treats correction as replay: to restate, you reprocess the retained log (Kafka/Kinesis) from an offset through the same job into a new output, then swap. One codebase, but it leans hard on log retention, deterministic reprocessing, and replay tooling.

LAMBDA                                  KAPPA

 raw ─┬─▶ [speed layer] ─▶ realtime      log ─▶ [stream job] ─▶ view v1
      │                       \                     │
      │                    serving-merge            └─(replay from offset)─▶ view v2 ─▶ swap
      │                       /
      └─▶ [batch layer] ─▶ authoritative   one code path; correctness via replay

 two code paths; batch overwrites speed  need: log retention + deterministic jobs

Whichever you pick, the discipline for correcting late data without breaking downstream is the same, and it's the part that matters most in the room:

• Immutable raw + reproducible transforms. Never mutate landed data; keep event time and ingestion time on every row. Any partition can be rebuilt from raw at any time, deterministically.
• Restate by partition, atomically. To fix the 3rd, rebuild only the 3rd's partition into a staging location and swap it in one metadata operation (partition-overwrite / Iceberg-Delta snapshot). No half-written state; readers see old or new, never both.
• Version, don't destroy. Table-format time travel (Iceberg/Delta snapshots) keeps the pre-restatement version queryable, so an auditor can see exactly what changed — this is bitemporality at the storage layer.
• Publish a completeness contract. Tell consumers "data older than T+3 days is final; newer is provisional and may be restated." A restatement inside the provisional window is expected, not an incident — and downstream can design for it instead of being surprised.

Restatement and reprocessing are where this chapter meets the next two: the mechanics of re-running just the affected slices without double-counting live in Chapter 05 · Incremental Processing & Backfills, and the loop that detects a partition needs restating lives in Chapter 06 · Reconciliation & Validation.

The leveling ladder

This is the topic where interviewers most reliably push "and then what?" until you run out of rungs. Have all four ready, and narrate the climb rather than jumping to the top.

Next, the machinery that makes all of this cheap enough to run every hour — and the backfills that let you rebuild history without double-counting: 05 · Incremental Processing & Backfills.

✓ Check yourself

Answer from memory before opening each one.

1. A team partitions daily revenue by the time their job ran. A purchase made at 23:58 reaches the pipeline at 00:04. Which day does it land in, and why does the team never see any "late" data?

   Show answer

   It lands on the next day, so the day it happened on is short and the next is long. Nothing looks late because processing time is stamped when the job runs, and by construction it is never behind itself. The error is hidden, not absent. Event time is the only axis for business questions, and lateness is the gap between it and arrival. See three clocks.

2. A phone flushes a week of queued events today. Your nightly job filters event_ts >= CURRENT_DATE - 3. What happens to those events, and how do you rewrite the filter?

   Show answer

   They are skipped, because their event time is outside the window even though they arrived today. Filter on ingestion time to decide what to reprocess, and partition on event time to decide where each row lands. Anything older than the lookback horizon needs a periodic wide sweep or a reconciliation-triggered restatement. See batch lookback.

3. A stream sets the watermark to the newest event time seen minus 5 minutes, with 10 minutes of allowed lateness. A record for a closed window arrives 20 minutes late. What does the engine do with it?

   Show answer

   The watermark has passed the window and the grace period is spent, so the record is dropped or sent to a side output instead of updating the result. Records within the grace period would have re-fired the window with a corrected value. Bigger delays catch more stragglers but make every result later. See watermarks and allowed lateness.

4. An order arrives for a customer who is not yet in dim_customer. Compare three ways to handle it and say which you ship.

   Show answer

   Insert an inferred stub row for the key with an inferred flag and update it in place when the real row arrives; the surrogate key never changes, so no fact needs rewiring. Parking the fact keeps integrity but hides revenue until the dimension shows. Dropping it is data loss. Ship the stub. See late facts and late dimensions.

5. On the 5th you learn a customer moved to a higher tier on the 1st. You insert a back-dated version into the SCD2 table. What else must happen before month-end reports are right?

   Show answer

   Close the previously open version at the new valid-from, and re-point facts from the 1st onward that were joined to the old surrogate key, or resolve the dimension at query time with an as-of join. Without that, loaded facts keep the old tier and history stays wrong. See late dimensions and as-of joins.

6. Finance restates revenue after a late settlement. The growth team's dashboard also moves. Which one needs bitemporal tables, and what do you give the other?

   Show answer

   Finance, because it must reproduce a past report as it was known on a given date and explain why it later changed, which needs both valid time and system time. The dashboard gets SCD2 valid time plus a published completeness rule, such as final after three days and provisional before. Bitemporal is overhead you pay only when past reports can legitimately change and someone must explain it. See bitemporal modeling.

Takeaway

• Event time is the axis for business numbers; ingestion time decides what to rescan; processing time is an accident of when you ran.
• Late data is structural, from offline clients, retries, out-of-order partitions, extraction cutoffs and upstream corrections, and no bound on lateness exists.
• A watermark or a batch lookback is a chosen completeness horizon: bigger catches more stragglers and delays every number.
• A lookback filters on ingestion time and writes by event time, and the merge is idempotent so rescans never double count.
• A late fact gets an inferred dimension row, never a drop; a late dimension version needs the facts re-pointed or an as-of join.
• Bitemporal modeling is for numbers that may be restated and must be explained; elsewhere state a final-after-T completeness rule.
• Restate by rebuilding one partition and swapping it atomically from immutable raw data.

Making the rescans cheap enough to run every hour, and the backfills that rebuild history, is the work of incremental processing. → Incremental Processing & Backfills

Further reading

• The Dataflow Model — Akidau et al., VLDB, 2015. The source of event time, windows, watermarks and triggers as a way to trade correctness, latency and cost.
• Flink: Generating Watermarks — Apache Flink docs. Bounded out-of-orderness strategies and idle sources, with code.
• Structured Streaming Programming Guide — Apache Spark docs. Event-time windows, watermarking and late-data handling in Spark.
• Bitemporal History — Martin Fowler, 2021. Valid time and transaction time, and why both matter once records are corrected.
• Slowly Changing Dimension Type 2 — Kimball Group. The standard treatment of versioned dimension rows.
• Questioning the Lambda Architecture — Jay Kreps, O'Reilly Radar, 2014. The original argument for replay over two code paths.