I need an ETL setup that can handle late-arriving data

Here’s a practical ETL pattern that handles late-arriving data well.

Recommended approach: incremental ETL with a reprocessing window

Instead of only loading “new since last run,” reprocess a recent time window on every run.

Core idea

1. Extract data changed since the last successful run.
2. Also re-pull a lookback window of recent history, such as the last 3–30 days.
3. Upsert into the target tables using stable business keys.
4. Track watermarks and run status so the pipeline is restartable.

This catches records that arrive late or get updated after the fact.

---

Typical architecture

1) Raw/staging layer

Land source data as-is in a raw zone:

• append-only
• include ingestion timestamp
• include source event timestamp if available
• keep a source batch ID or file name

Example fields:

• source_id
• event_time
• ingested_at
• batch_id
• payload columns

2) Processing layer

Deduplicate and normalize records in staging:

• choose the latest version of a record by updated_at or ingested_at
• handle nulls, type casting, schema drift
• dedupe by business key + version timestamp

3) Curated/warehouse layer

Load with:

• MERGE / UPSERT
• slowly changing dimensions if needed
• fact tables keyed by business identifiers and event dates

---

Handling late-arriving facts

If an event belongs to an earlier date but arrives now:

• insert it into the fact table using its true event_date
• recompute any aggregates or rollups affected by the backfill window

Example:

• a transaction from Jan 3 arrives on Jan 10
• load it into Jan 3 partition
• refresh Jan 3 summaries, not just Jan 10

---

Good strategies by data type

For append-only events

Use:

• event timestamp
• dedupe key
• lookback window
• partition overwrite or merge

For mutable source records

Use:

• source updated_at
• hash comparison for change detection
• merge into dimension tables

For daily aggregates

Use:

• recompute the last N days every run
• optionally maintain a correction job for older late data

---

Watermark pattern

Store the last processed watermark:

• last_successful_event_time
• last_successful_ingestion_time

But don’t rely on it alone. Combine it with a lookback window.

Example:

• watermark = 2026-10-01 00:00:00
• lookback = 7 days
• next extract range = 2026-09-24 to now

---

Idempotency requirements

Your ETL should be safe to rerun:

• use deterministic primary keys
• upsert instead of blind insert
• stage data before publishing
• avoid duplicate loads by batch ID or hash

---

Late data design options

Option A: Sliding window reprocessing

Best for most cases.

• simple
• robust
• good for daily/hourly pipelines

Option B: Event-time partition overwrite

Best for partitioned warehouses.

• overwrite affected partitions
• efficient for large volumes

Option C: CDC-based pipeline

Best when source systems support change data capture.

• captures inserts/updates/deletes
• still may need a small lookback for safety

---

Example workflow

1. Pull source data from the last 7 days.
2. Land in raw storage.
3. Deduplicate by business_key + latest updated_at.
4. Merge into warehouse tables.
5. Rebuild aggregates for the same 7-day window.
6. Update watermark and log success.

---

Example SQL merge pattern

MERGE INTO target_table t
USING staging_table s
ON t.business_key = s.business_key
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET
    col1 = s.col1,
    col2 = s.col2,
    updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (business_key, col1, col2, updated_at)
  VALUES (s.business_key, s.col1, s.col2, s.updated_at);

---

Operational safeguards

• alert on unusually high late-arrival volume
• keep audit tables for counts by batch
• log rejected/invalid records
• test backfill behavior regularly
• monitor partition refresh times

---

Rule of thumb for lookback window

Choose based on source behavior:

• reliable source: 1–3 days
• moderate delay: 7–14 days
• highly delayed sources: 30+ days or CDC + periodic backfills

---

If you want, I can also give you:

1. a reference architecture diagram,
2. a dbt/SQL implementation, or
3. a Python + Airflow example for late-arriving data handling.