Late Data: Quantify the Silent Loss, Then Heal It

This lesson is not about the fix

You already know the fix. sql-l4-idempotent-merge told you a strict high-water mark drops late rows and that a lookback catches them, and the previous lesson restated it. What no lesson has made you do is the part an on-call engineer actually gets paged for: finding the damage and putting a number on it. An unquantified fix is a guess, and a guess is not something you write in an incident report.

Two clocks, and only one of them is yours

Every event row carries two timestamps and they are not the same thing.

• Event time is when the thing happened. A payment at 02:00 has an event time of 02:00 forever.
• Processing time is when your loader could first see the row. That is a property of the network, the queue, the mobile client that was offline, and the source system's commit order.

Event time versus processing time is the single most transferable idea in streaming, and it bites batch first. Here is the whole failure in one line: a row events at 22:15 on the 12th and arrives at 07:40 on the 14th, by which point the 06:00 load has already advanced the watermark past it. The next run filters on event_ts > watermark, the row is below the boundary, and it is skipped. Forever. No error, no retry, no alert. The run is green.

Table

Late is not the same as lost. 9007 was three hours late and still made it. 9005, 9009 and 9012 all crossed the watermark while in flight, and nothing raised an error.

event_id	event_ts (happened)	ingested_at (arrived)	vs watermark 06:00 on the 14th	in fct_events?
9004	12th 13:50	12th 13:52	arrived long before	yes
9005	12th 22:15	14th 07:40	arrived AFTER, event time below	no, silently dropped
9007	13th 11:05	13th 14:05	3h late, still arrived before	yes
9009	13th 23:45	14th 09:15	arrived AFTER, event time below	no, silently dropped
9012	14th 05:20	14th 11:50	arrived AFTER, event time below	no, silently dropped
9013	14th 09:35	14th 09:38	event time above, not yet due	no, correctly

Read that last column carefully. Row 9013 is also missing from the fact, and that is correct: its event time is above the watermark, so the next run will pick it up. Only rows whose event time is at or below the watermark and that never landed are losses. Your audit has to make that distinction or it will report a number that is not real.

The audit is an anti-join

The query is small and you should be able to write it from memory:

SELECT e.event_id, e.event_ts, e.ingested_at
FROM source_events e
WHERE e.event_ts <= (SELECT last_value FROM etl_watermarks WHERE pipeline = 'events_incremental')
  AND NOT EXISTS (SELECT 1 FROM fct_events f WHERE f.event_id = e.event_id)

Below the boundary, absent from the target. That count, and the revenue attached to it, is the number that goes in the write-up. Add the arrival delay per row and you also get the input for the next decision: how wide the healing window has to be.

Then heal it with a rolling window

The production fix is a bounded rolling reprocessing window: every run rebuilds the last few days (three is a common default) from source instead of only the slice above the watermark. Late arrivals then self-heal on the next run, because the day they belong to is rebuilt anyway.

That only works because the write is idempotent. Reprocessing the same three days every single run would triple your data with an append; with partition replacement (delete the day, reinsert the day) it converges to the same rows no matter how many times it runs. Module 8.2 is the prerequisite for this lesson, and this is where you cash it in.

Two things a strong answer adds. First, the window is bounded: three days, not the whole table, because cost is a design constraint and an unbounded recompute is how a nightly job becomes a six hour job. Second, the downstream aggregate has to be rebuilt in the same script, because a repaired fact under a stale total is still a wrong dashboard.

Common mistake: counting every row missing from the target as a loss. Rows whose event time is above the watermark are not lost, they are simply not due yet, and including them inflates the incident number and destroys your credibility in the review.

Interview nuance: "how do you handle late-arriving data" is a stock question, and almost every candidate jumps straight to "I would add a lookback window." The stronger answer is two steps: first quantify what was already lost with an anti-join below the watermark, then size the window from the observed arrival delays rather than picking three days because it sounds right. Naming the measurement before the remedy is the difference between an engineer and someone reciting a pattern.

On a real platform this differs. Streaming engines make this tradeoff explicit rather than accidental, but not to the same degree. Flink states it fully with a watermark plus an allowed lateness, and events past the allowance can be routed to a side output instead of vanishing. Spark Structured Streaming gives you only the watermark delay threshold in withWatermark, and anything later than that threshold is dropped with no error and no side channel, which is much closer to the batch failure you just audited. Batch has the same tradeoff with none of the instrumentation, which is why you have to build the audit yourself.