At 9:07 a.m. on a Friday, our revenue dashboard dropped by 11%. No deploys. No red alerts. Just panic in the business channel and three teams asking if payments were broken.

Nothing was broken. The real issue was timing. A partner queue retried overnight, events arrived late, and our “daily” metrics were unknowingly mixing event-time truth with processing-time convenience. By noon, the numbers swung back, but trust had already taken a hit.

If this sounds familiar, this guide is for you. I will walk through a practical late-arriving events reconciliation workflow using event-time watermarks, dbt incremental models, and a safe PostgreSQL MERGE upsert path for backfills, so your dashboards become boring again (in the best way).

The real problem is not late data, it is silent re-interpretation

Late events are normal in distributed systems. Mobile clients go offline, queues retry, connectors pause, and upstream teams rerun jobs. The danger starts when your pipeline quietly changes yesterday’s result without surfacing why.

A stable system needs three explicit decisions:

• How long you wait for out-of-order events (watermark policy).
• How you update historical rows without duplicates (incremental + merge key).
• How you communicate correction windows to stakeholders (reconciliation cadence).

A practical architecture that teams can actually run

1. Capture event timestamps at ingest and preserve them end-to-end.
2. Apply bounded lateness in streaming jobs (watermark).
3. Land raw immutable data in a bronze table.
4. Build silver models incrementally in dbt using unique keys.
5. Run a rolling reconciliation window (for example, last 3 days) with MERGE.

This gives you a fast path for today and a controlled correction path for yesterday.

Step 1: Set an explicit event-time watermark

Apache Flink’s watermark strategy lets you tell the system how much out-of-orderness you expect. This is your first reliability contract.

WatermarkStrategy<OrderEvent> wm = WatermarkStrategy
    .<OrderEvent>forBoundedOutOfOrderness(Duration.ofMinutes(10))
    .withTimestampAssigner((event, ts) -> event.eventTimeEpochMillis())
    .withIdleness(Duration.ofMinutes(1));

DataStream<OrderEvent> orders = env
    .fromSource(kafkaSource, wm, "orders-source")
    .keyBy(OrderEvent::orderId)
    .process(new OrderAggregationFn());

Tradeoff: a larger lateness bound improves completeness but increases result latency and state retention. A smaller bound is faster but risks dropping valid late records.

Step 2: Model incrementally in dbt with a correction window

dbt incremental models are ideal here because you can process new rows quickly while still reprocessing a bounded historical window when needed.

{{
  config(
    materialized='incremental',
    unique_key='order_event_id',
    incremental_strategy='merge'
  )
}}

with src as (
  select
    order_event_id,
    order_id,
    event_time,
    amount,
    status,
    updated_at
  from {{ ref('stg_orders_raw') }}

  {% if is_incremental() %}
    where event_time >= (
      select coalesce(max(event_time), '1970-01-01'::timestamp)
      from {{ this }}
    ) - interval '3 day'
  {% endif %}
)

select * from src;

The 3-day lookback is the key. It intentionally re-opens a small historical slice so late events can be reconciled without full table rebuilds.

Step 3: Reconcile with a deterministic MERGE job

For warehouse truth tables, run a scheduled MERGE over the same correction horizon.

MERGE INTO mart.daily_order_metrics AS t
USING (
  SELECT
    date_trunc('day', event_time) AS metric_day,
    count(*) AS order_count,
    sum(amount) AS gross_revenue
  FROM silver.orders_events
  WHERE event_time >= now() - interval '3 day'
  GROUP BY 1
) AS s
ON t.metric_day = s.metric_day
WHEN MATCHED THEN
  UPDATE SET
    order_count = s.order_count,
    gross_revenue = s.gross_revenue,
    reconciled_at = now()
WHEN NOT MATCHED THEN
  INSERT (metric_day, order_count, gross_revenue, reconciled_at)
  VALUES (s.metric_day, s.order_count, s.gross_revenue, now());

This keeps corrections explicit, reviewable, and idempotent.

How to choose the correction window without guessing

Start with observed delay percentiles, not opinions. If 99% of events arrive within 18 hours and 99.9% within 52 hours, a 3-day window is a defensible default. Then measure and tune monthly.

• If corrections keep appearing after 3 days, extend to 5 days.
• If reprocessing cost is high and corrections are tiny, shrink and add a weekly deep reconcile job.
• If finance requires immutable closes, split into “provisional” and “finalized” reporting layers.

Operational guardrails that prevent repeat incidents

Most teams stop at SQL correctness, then get surprised again two weeks later. Add lightweight operational guardrails so reconciliation is a habit, not a heroic response.

• Delay SLO: track P95/P99 ingestion lag per source daily.
• Reconciliation budget: set a hard runtime budget for correction jobs, then tune window size to fit it.
• Drift alerts: alert when corrected values exceed a threshold (for example, more than 1.5% movement on closed days).
• Auditability: persist reconciled_at, pipeline run id, and changed row counts for each correction cycle.

These controls create an explicit contract between data engineering and business consumers. The message becomes: numbers can change inside a known window, for known reasons, under known limits. That clarity usually does more for trust than another dashboard redesign.

Troubleshooting: what usually breaks first

1) Duplicate rows after incremental runs

Cause: weak or nullable unique keys in incremental models.

Fix: enforce non-null grain keys (or generate a surrogate key) and add dbt tests for uniqueness + not null.

2) Watermark is too aggressive, valid late events vanish

Cause: lateness bound chosen from ideal logs, not real network behavior.

Fix: inspect ingestion lag distribution by source and set per-source bounds where possible.

3) MERGE job gets slower every week

Cause: the merge scans too much target data.

Fix: bound source rows to correction window, index or cluster join keys, and keep merge statements narrow.

4) Stakeholders still distrust metrics

Cause: technical correctness without visibility.

Fix: publish two columns in BI: is_provisional and reconciled_at. Trust improves when change is expected and explained.

FAQ

Do I always need streaming watermarks if I already run batch dbt models?

No. But if upstream is stream-first, watermarks reduce ambiguity early and make downstream correction windows more predictable.

Why not just run full refresh every night?

For small datasets, full refresh is fine. At scale, it increases cost and recovery time. Incremental + bounded reconciliation gives most of the correctness at a fraction of runtime.

Should I use append-only tables and avoid MERGE entirely?

Append-only is excellent for lineage, but serving layers still need corrected aggregates. A common pattern is append-only raw + merged analytics marts.

Related reads on 7tech

• From Spreadsheet Chaos to a Laptop Lakehouse: DuckDB + Iceberg + dbt
• The Metric Drift You Don’t See Coming: SQL and Data Engineering Patterns
• The Checkout Freeze Migration: Zero-Downtime PostgreSQL Schema Changes
• When “It Works on My Machine” Hit Production: Python Dependency Management with uv

Actionable takeaways for this week

• Pick and document one watermark policy per critical source, with a clear lateness bound.
• Add a 48-72 hour correction window to your dbt incremental models for high-impact facts.
• Schedule one deterministic MERGE reconcile job and log row-level deltas.
• Expose provisional vs reconciled status in dashboards so metric changes are expected, not alarming.
• Review late-arrival percentiles monthly and tune window size based on observed lag, not anecdotes.

Sources reviewed

• dbt Docs: Configure incremental models
• Apache Flink Docs: Generating Watermarks
• PostgreSQL Docs: MERGE

Late data is not a failure mode. Invisible correction is. If you define timing contracts, merge rules, and communication rules up front, your numbers stay both fast and trustworthy.