Handling Late-Arriving Data

Manage ingestion pipelines to reconcile events that arrive out of chronological order.

In this lesson, you will learn how to maintain data integrity in time-series systems when events arrive out of their chronological sequence.

The Challenge of Time

When dealing with time-series data—such as IoT sensor readings or financial logs—we usually assume events arrive in the order they occurred. However, network latency, device connectivity issues, or system outages often result in "late-arriving data." If a sensor logs a temperature reading at 10:00 AM, but the data packet reaches your database at 10:15 AM (after a 10:05 AM packet has already been processed), your time-series analysis could be skewed.

Reconciliation Strategies

To handle this, we don't just append data to the end of a table. Instead, we use a two-pronged approach:

Buffer and Sort: Use a "watermark" strategy. You define a delay threshold (e.g., 60 seconds) during which the system buffers incoming events, sorts them by their original timestamp, and then commits them to the permanent storage. This ensures that the data is ingested chronologically.
Upsert/Merge Logic: For data that arrives after the watermark, you must perform an UPSERT (Update or Insert) operation. Rather than creating duplicate entries, you use the event's unique identifier and timestamp to update existing rows or fill missing gaps in your time-series buckets.

Practical Example

If you are using SQL to store time-series metrics, you can use the ON CONFLICT clause (common in PostgreSQL) to handle late arrivals:

INSERT INTO sensor_data (sensor_id, ts, reading)
VALUES ('temp_01', '2023-10-27 10:00:00', 22.5)
ON CONFLICT (sensor_id, ts) 
DO UPDATE SET reading = EXCLUDED.reading;

In this snippet, if a record with the same timestamp arrives late, the database won't create a duplicate row; it simply updates the existing record with the new value. This keeps your time-series window accurate despite the chaotic arrival of network packets. By combining defined watermarks with robust UPSERT logic, you ensure that your analytical queries reflect the reality of when the event happened, not when it reached the server.

Key Takeaway: Use ingestion watermarks to buffer and sort incoming streams, and employ UPSERT operations to reconcile late-arriving data without duplicating records.