Skip to content

Better Late than never: A Guide to Handling Late-Arriving Data

Late-aware Pipeline

You check your sales dashboard at 9:00 AM. It says $10,000 in revenue for yesterday. You check it again at noon, and suddenly, yesterday’s revenue is $10,500. What happened?

You’ve just been hit by late-arriving data.

This is one of the most common and frustrating problems in data engineering. It’s not a bug; it’s an unavoidable reality of distributed systems. But if you don’t handle it, it undermines trust in your data.

What is Late-Arriving Data?

Late-arriving data are records that arrive after you expected them to, often after you’ve already run your daily aggregations or published a report.

The key concept here is Event Time vs. Processing Time.

  • Event Time: When the event actually happened in the real world (for example,a user clicked “buy” at 8:59 PM).
  • Processing Time: When your pipeline saw the event (for example, the server processed the log at 9:05 AM the next day).

A record is “late” if its Event Time is in a period you thought was “closed.”

Common causes are everywhere:

  • A mobile device that was offline and just synced its activity.
  • A third-party vendor sending a data export 12 hours late.
  • Upstream system retries, network partitions, or CDC lag.
  • Long-running database transactions that only commit hours later.

Why Late Data is Such a Headache

If you only append data as it arrives (based on processing time), you’re headed for trouble. Late data introduces a host of challenges that can break your analytics and drive your stakeholders crazy.

  • Inaccurate Metrics & Reports: Your daily and weekly aggregates are simply wrong until the late data is merged. This forces you to constantly issue corrections.
  • Drifting Windowed Aggregations: If you calculate “revenue per hour,” a late event from 2:00 PM will change that hour’s total after you’ve already reported it.
  • Duplicates & Out-of-Order Events: Retries and replays can easily create double-counts unless you have a robust deduplication strategy.
  • Complex Reprocessing: How do you fix history? Re-running an entire pipeline for one late event is slow and expensive. You need a way to make surgical corrections.
  • Ambiguous SLAs: When is a dataset truly “final”? How long do you wait for late data? This ambiguity erodes trust.
  • Increased Costs: In streaming, keeping windows open longer to wait for late data increases memory and state storage costs.
  • Audit Nightmares: You must be able to explain to an auditor or executive why a number changed three days after the fact.

Taming the Timeline: How to Handle Late Data

You can’t stop data from being late, but you can design a system that expects it. Here are the core patterns, from tool-agnostic concepts to concrete architectures.

1. Design for Event Time

This is the golden rule. Every single record in your pipeline should have two timestamps:

  • event_time: When it happened.
  • ingest_time (or processing_time): When you received it.

Always build your business aggregations on event_time. Use ingest_time only for monitoring your pipeline’s operational health (for example, “Is data arriving on time?”).

2. Use Watermarks and Triggers (Streaming)

In stream processing, a watermark is a moving timestamp that represents your pipeline’s notion of “completeness.” It’s a heuristic that says, “I am 99% confident I have seen all events up to this point in event time.”

For example, a watermark of T-7 days means the system assumes data older than 7 days is unlikely to arrive. Any data that does arrive older than the watermark is considered “late.”

Triggers then define when to emit results for a window (for example, an hourly aggregate). You can emit an early, “preliminary” result, and then a final, “corrected” result once the watermark passes the window’s end.

3. Embrace Idempotency and Upserts

Your pipeline must be idempotent, meaning running the same data through it twice doesn’t create duplicates.

  1. Deduplication: Ensure every event has a stable, unique event_id (or a composite key like user_id + transaction_id). Use this key to deduplicate records.
  2. Upserts (MERGE): Don’t just APPEND data to your final tables. This creates conflicts. Use an UPSERT (or MERGE) operation. This logic says: “If this record (by its key) already exists, UPDATE it. If not, INSERT it.”

This is the fundamental pattern for correcting history and is a core feature of modern data lakehouse formats like Apache Iceberg, Delta Lake, and Apache Hudi, as well as data warehouses (BigQuery, Snowflake).

4. Plan for Repair and Backfills

What about data that is extremely late, older than your watermark or policy allows? Don’t just drop it. Route these events to a separate “backfill” path or a dead-letter queue.

You can then trigger a targeted backfill job that recomputes only the specific partitions (for example, the specific days or hours) affected by this “ancient” data, merging the corrections into your final table.

5. Define Your “Done” (Policies & Contracts)

Be explicit.

  • Lateness Policy: Define your allowed_lateness (for example, “We will accept and correct data for up to 7 days”).
  • Data Contracts: Define expectations with your data producers. What are the keys? Is event_time guaranteed? What’s their SLA for sending data?
  • Finalization Rules: Publish a schedule for your consumers. For example: “The daily report is preliminary for 24 hours. It is revised daily for 7 days. It is considered final after 7 days.”

Concrete Recipes and Architectures

The Modern Data Architecture: Immutable Log + ACID Lake

The most robust pattern is to separate ingestion from correction:

  1. Immutable Raw Log: Land all incoming data (late or not) into an immutable, append-only log. This is your source of truth (for example, Kafka, Kinesis, or just raw files on S3/GCS).
  2. Curated ACID Tables: Use streaming and/or batch jobs to read from the raw log, clean, deduplicate, and MERGE the data into curated tables (for example, Delta Lake or Iceberg tables). Your users only query these curated tables.

Example 1: Spark Streaming with Watermarks

Here’s how you’d handle data up to 7 days late in Spark Structured Streaming.

Python

# 1. Read from Kafka and define event_time
events = spark.readStream.format("kafka").load() \
    .select("value") \
    .select(from_json("value", schema).alias("e")).select("e.*")
# 2. Set a 7-day watermark and drop duplicates
clean_events = events \
    .withWatermark("event_time", "7 days") \
    .dropDuplicates(["event_id"]) # Requires a unique key
# 3. Aggregate on event-time windows (e.g., 1 day)
daily_revenue = clean_events \
    .groupBy(window("event_time", "1 day"), "region") \
    .agg(sum("amount").alias("revenue"))
# 4. Write to a temporary table in "update" mode.
# This stream will continuously output corrected aggregates for the last 7 days.
query = daily_revenue.writeStream \
    .outputMode("update") \
    .option("checkpointLocation", "...") \
    .toTable("analytics.daily_revenue_tmp")

Example 2: Correcting History with Delta Lake MERGE

The Spark stream above writes corrected aggregates to a temp table. A separate, periodic batch job can then MERGE these corrections into the final, gold-standard report table.

SQL

-- This job runs every 15 minutes, merging the latest updates.
MERGE INTO analytics.daily_revenue AS target
USING analytics.daily_revenue_tmp AS source
ON  target.region = source.region 
    AND target.date = source.window.start -- Match on day and region
-- If a record for that day/region already exists...
WHEN MATCHED THEN 
  UPDATE SET 
    revenue = source.revenue, 
    _last_updated = current_timestamp()
-- If it's a new day/region combo...
WHEN NOT MATCHED THEN 
  INSERT (date, region, revenue, _last_updated)
  VALUES (source.window.start, source.region, source.revenue, current_timestamp());

This MERGE pattern is the key. It works just as well in BigQuery, Snowflake, Redshift, and other modern warehouses.

Monitoring and SLAs: Trust but Verify

You can’t just set this up and walk away. You must monitor your lateness.

  • Metrics to Track:
    • Event Lag: The delta between event_time and ingest_time. (Alert if this spikes!)
    • Percent Late: What percentage of your data arrives past your watermark?
    • Backfill Queue Length: How much “ancient” data is waiting for repair?
  • Data Quality & Scheduling:
    • Run data quality tests to reconcile counts and check for nulls.
    • Be explicit about your schedule: for example, “D-day preliminary at T+1h; revised at T+24h; final at T+7d.”

A Quick Decision Guide

Not sure where to start? Here’s a simple guide:

  • If data is seconds/minutes late: A short streaming watermark (for example, 1 hour) is all you need.
  • If data is hours/days late: This is the most common case. Use a long watermark (for example, 3-7 days) combined with a periodic MERGE job into an ACID lake or warehouse.
  • If data is weeks/months late: Don’t keep your windows open this long. Route this data to a backfill/repair process and recompute the affected partitions from your raw, immutable log.

Late-arriving data isn’t a bug; it’s a feature of the real world. Stop fighting it. Plan for it, design for it, and build pipelines that are resilient, accurate, and trustworthy.

Tags: