How to Handle Late-Arriving Data in ETL Pipelines

In distributed data systems, events rarely arrive in the order they occur. A mobile checkout completed at 14:02 UTC might linger on an offline client device, route through fluctuating cellular networks, and finally hit your message broker at 18:45 UTC. If your pipelines assume that processing time mirrors event time, this late-arriving data will silently corrupt reporting metrics, distort machine learning features, and break referential integrity between dimensional tables.

Effectively managing event-time skew requires deliberate design. Whether operating near real-time streaming architectures or micro-batch Lakehouse engines, data engineering teams must adopt resilient patterns to handle late arriving records without degrading query latency or ballooning compute costs.

Why Late-Arriving Data Breaks Modern Data Pipelines

Late-arriving data occurs whenever a significant delta exists between event time (when an action took place in the physical world) and processing time (when the pipeline ingests and computes the event). This disparity introduces critical failure modes across modern etl & elt workflows:

• Early-Arriving Facts and Orphaned Records: In dimensional modeling, a transaction event (fact) may land before the associated customer registration record (dimension) finishes syncing from an external CRM. If your pipeline enforces strict foreign key constraints or relies on inner joins, the fact record is dropped or orphaned.
• Corrupted Windowed Aggregations: In real-time analytics, aggregations like 5-minute rolling averages or hourly revenue counters close when the system clock advances. When out-of-order data lands hours after a window has closed, standard streaming pipelines either drop the records or fail to update previously emitted aggregates.
• SCD Type 2 Overwrite Failures: Slowly Changing Dimension (SCD) Type 2 tables maintain historic attribute states via validity ranges (start_date, end_date, is_current). An update arriving out of chronological order can disrupt the chronological chain, causing an obsolete attribute to be tagged as currently active.
• Machine Learning Feature Store Skew: Point-in-time joins for ML feature generation require accurate historical state reconstruction. If late data is backfilled improperly into current snapshots, training datasets suffer from target leakage or temporal misalignment.

Event Occurs (T0) ──> Network/Device Delay ──> Broker Ingest (T0 + 4 hrs) ──> Target Delta Table
                           │
                           ▼
              Standard Closed Window (Dropped or Miscalculated)

Architectural Patterns to Handle Late-Arriving Data

To handle late arriving events reliably across both streaming and batch architectures, data teams leverage four primary design patterns depending on latency SLAs and storage capabilities.

1. Watermarking with Allowed Lateness (Streaming)

In stream processing engines like Apache Spark Structured Streaming, Apache Flink, and cloud-native services on AWS and Azure, watermarking tracks the maximum event time observed minus an acceptable latency threshold.

A watermark acts as a moving threshold:

$$\text{Watermark} = \max(\text{Event Time}) - \text{Allowed Latency}$$

For example, setting a 2-hour watermark tells the engine to keep state memory open for micro-batches covering the past 120 minutes. Any event arriving with a timestamp older than the watermark is discarded or redirected to a dead-letter queue (DLQ) for asynchronous reconciliation. This approach caps the state store size in memory while accommodating transient network delays.

2. Inferred Dimensions (The "Early Fact" Pattern)

When fact records precede their dimension records, do not drop the fact. Instead, apply the inferred dimension pattern:

1. Insert the fact record using a generated surrogate key for the unknown dimension.
2. Insert a stub/placeholder record into the dimension table containing the surrogate key, marking attributes as Pending or Unknown.
3. When the true dimension record eventually arrives via your etl & elt processes, execute an idempotent UPDATE that populates the stub with actual profile data without needing to reprocess the historical fact table.

3. Native Lakehouse Upserts via Idempotent MERGE

Modern lakehouses powered by Databricks Delta Lake and Snowflake eliminate the rigid file-rewriting bottlenecks of older Hadoop architectures. By leveraging ACID transaction logs, pipelines can merge out-of-order batches directly into target tables:

MERGE INTO silver.fact_orders AS target
USING bronze.staged_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED AND source.event_timestamp > target.event_timestamp THEN
  UPDATE SET 
    target.status = source.status,
    target.updated_at = source.event_timestamp,
    target.amount = source.amount
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, status, event_timestamp)
  VALUES (source.order_id, source.customer_id, source.amount, source.status, source.event_timestamp);

Using conditional matches (source.event_timestamp > target.event_timestamp) ensures that an older, delayed payload will not overwrite a newer state that has already been processed.

4. Dual-Path Reconciliation (Lambda Lakehouse Pattern)

For business metrics that require immediate updates (e.g., intraday fraud alerts) alongside exact financial reconciliation, implement a dual-path pipeline:

• Speed Layer (Real-Time): Ingests streaming events, tolerates an aggressive 15-minute watermark, and outputs provisional metrics to operational dashboards.
• Batch Reconciliation Layer (T+1): Runs nightly over partitioned object storage (e.g., Amazon S3 or Azure ADLS Gen2), reading all delayed events collected throughout the day, executing an idempotent re-aggregation, and overwriting provisional daily partitions.

Comparison of Strategies for Late-Arriving Data

Practical Implementation: Handling Out-of-Order Updates in Delta Lake

Consider an operational scenario on Databricks where sensor readings or transaction events arrive with arbitrary delays. A standard append creates duplicate or out-of-sequence rows.

Here is an architectural pattern using PySpark Structured Streaming to persist late arriving data cleanly into a Delta table:

from pyspark.sql import functions as F

# Stream ingestion with event-time watermarking
raw_stream = (
    spark.readStream
    .format("cloudFiles")  # Databricks Auto Loader
    .option("cloudFiles.format", "json")
    .schema(telemetry_schema)
    .load("s3://telemetry-landing-zone/")
)

# Apply a 3-hour watermark on the actual hardware event timestamp
watermarked_stream = (
    raw_stream
    .withWatermark("device_timestamp", "3 hours")
    .dropDuplicates(["device_id", "device_timestamp"])
)

# Micro-batch execution to handle upserts into Delta Lake
def merge_to_silver(microBatchDF, batchId):
    microBatchDF.createOrReplaceTempView("updates")
    
    microBatchDF._jdf.sparkSession().sql("""
        MERGE INTO silver.sensor_readings AS target
        USING updates AS src
        ON target.device_id = src.device_id 
           AND target.reading_date = src.reading_date
        WHEN MATCHED AND src.device_timestamp > target.device_timestamp THEN
            UPDATE SET *
        WHEN NOT MATCHED THEN
            INSERT *
    """)

watermarked_stream.writeStream \
    .foreachBatch(merge_to_silver) \
    .outputMode("update") \
    .option("checkpointLocation", "s3://telemetry-checkpoints/sensor_silver/") \
    .start()

In this implementation, data arriving within three hours of its occurrence updates state cleanly. If an edge node comes back online after 24 hours, its records are captured by an isolated backfill job rather than corrupting streaming checkpoints.

Production Best Practices for Engineering Resilience

1. Partition by Ingestion Date, Query by Event Date: In high-volume environments, partition your raw/bronze storage by ingestion_date (processing time) to avoid writing tiny files across thousands of historical partitions. Expose downstream silver and gold layers partitioned or clustered by event_date using Liquid Clustering (Delta) or Automatic Clustering (Snowflake).
2. Instrument Skew Observability: Set alerts on the delta between max(event_timestamp) and current_timestamp(). Sudden spikes in event delay frequently indicate upstream API latency, upstream worker backpressure, or mobile application synchronization failures.
3. Establish Clear SLAs for Cutoff: Define clear organizational boundaries for allowed lateness. Financial ledgers often enforce a strict "month-end close" after which late events cannot alter closed periods and must post as adjustments in the current accounting period instead.

Handling edge cases like high-skew datasets and asynchronous dependencies requires experienced engineering. At Izan Technologies, we design and deploy scalable data platforms that solve complex ingestion challenges while ensuring client ownership of their systems.

How Izan Technologies Helps

Izan Technologies is an elite data and AI consultancy that builds production-grade data engineering, machine learning, and streaming architectures across AWS, Azure, Databricks, and Snowflake.

Whether you need to overhaul brittle legacy ETL jobs, design modern lakehouse pipelines that seamlessly handle late arriving records, or build high-throughput data platforms, Izan Technologies delivers custom solutions tailored to your infrastructure. Crucially, our clients retain 100% ownership of all intellectual property, source code, and data pipelines developed throughout our engagements.

Ready to optimize your data infrastructure? Try our Smart AI DataStudio or talk to an expert to modernize your data platforms today.