Debugging Late Arriving IoT Timestamps with Watermark Drift in dbt and SQL
Written by
Sage Stream
The problem I ran into
I was building a real-time analytics pipeline for IoT sensor readings—temperatures, device status, battery level—ingested continuously and then prepared for an AI model. Everything looked fine… until the model started “remembering” yesterday’s weather as if it were today’s.
When I dug in, I found a classic data observability failure mode that’s easy to miss at first:
- Events arrive out of order (a common thing for IoT).
- Downstream tables apply time windowing using a “watermark” (a notion of how late is acceptable).
- That watermark logic drifted after a deployment.
- Result: late-but-valid events were silently dropped or mis-bucketed, and the training data distribution shifted.
In this post, I’ll show the concrete way I diagnosed “watermark drift” using SQL + dbt-style models, and how I turned it into an observable metric.
What I mean by “watermark” (and why it can drift)
In streaming systems, a watermark is a timestamp that represents the system’s best guess of “the latest time we’ve effectively seen, accounting for lateness.”
If the watermark says “we’re at 12:00, so anything earlier than 11:45 is probably too late,” then an event with event_time = 11:40 arriving at 12:10 gets dropped.
Watermarks often depend on configuration like:
- allowed lateness (e.g., 15 minutes)
- current processing time
- sometimes job start time or time zone handling
If that config changes (or the job’s notion of time changes), the watermark can move too aggressively, creating a gap where late events disappear without errors.
The dataset I used (toy but realistic)
I used a table of raw IoT events with both:
event_time: when the sensor measuredingest_time: when the event hit my warehouse
-- Example schema -- event_id string -- device_id string -- event_time timestamp (sensor time) -- ingest_time timestamp (arrival time) -- temperature_c float
Here’s a small example to illustrate the situation:
WITH iot_events AS ( SELECT 'e1' AS event_id, 'd1' AS device_id, TIMESTAMP '2026-11-14 10:00:00' AS event_time, TIMESTAMP '2026-11-14 10:20:00' AS ingest_time, 21.0 AS temperature_c UNION ALL SELECT 'e2', 'd1', TIMESTAMP '2026-11-14 10:30:00', TIMESTAMP '2026-11-14 10:40:00', 22.5 UNION ALL SELECT 'e3', 'd1', TIMESTAMP '2026-11-14 10:10:00', -- late event: earlier measurement TIMESTAMP '2026-11-14 10:50:00', -- arrives later 20.8 ) SELECT * FROM iot_events;
Event e3 is late: its event_time is 10:10, but it arrives at 10:50.
The “watermark” logic I initially used
In my pipeline, I assigned each event to a time window based on event time, but I only allowed events newer than a computed watermark.
In SQL terms, I filtered with:
- compute watermark =
max_ingest_time_seen - allowed_lateness - keep events where
event_time >= watermark
A simplified version looked like this:
WITH bounds AS ( SELECT MAX(ingest_time) AS max_ingest_time_seen FROM iot_events ), watermark AS ( SELECT max_ingest_time_seen - INTERVAL '15 minutes' AS wm FROM bounds ) SELECT e.* FROM iot_events e CROSS JOIN watermark w WHERE e.event_time >= w.wm;
That seems harmless, but it’s sensitive to what you consider “max ingest time seen.”
How watermark drift showed up in observability
The drift happened after a deployment where the job started processing a new batch from a slightly different time boundary (a different backfill window). The “max ingest time seen” jumped forward, pushing the watermark forward too.
That made valid late events fall below the watermark threshold and get filtered out. No errors. Just missing data.
So instead of relying on the final filtered output, I added an observability check: how many events are dropped due to watermark gating, and what’s their lateness distribution?
Step-by-step: build a watermark observability model
I implemented this as a dbt-style model conceptually (even though the SQL below is portable). The idea is to produce a table that shows:
late_seconds = ingest_time - event_timewm_seconds_lag = max_ingest_time_seen - allowed_lateness - event_time- counts of events that fail the watermark condition
1) Create an intermediate “bounds” CTE
This identifies the anchor for the watermark.
WITH bounds AS ( SELECT MAX(ingest_time) AS max_ingest_time_seen, MIN(ingest_time) AS min_ingest_time_seen FROM iot_events ) SELECT * FROM bounds;
In a real warehouse, this “bounds” might be computed per device, per partition, or per stream shard. The key is consistency.
2) Compute the watermark timestamp from allowed lateness
WITH bounds AS ( SELECT MAX(ingest_time) AS max_ingest_time_seen FROM iot_events ), watermark AS ( SELECT max_ingest_time_seen - INTERVAL '15 minutes' AS wm FROM bounds ) SELECT * FROM watermark;
If this watermark shifts too far ahead, it will increase the number of dropped late events.
3) Score each event relative to the watermark
Here’s the core diagnostic query. It outputs whether each event would pass the gate, plus how late it is.
WITH bounds AS ( SELECT MAX(ingest_time) AS max_ingest_time_seen FROM iot_events ), watermark AS ( SELECT max_ingest_time_seen - INTERVAL '15 minutes' AS wm FROM bounds ), scored AS ( SELECT e.*, w.wm AS watermark_ts, EXTRACT(EPOCH FROM (e.ingest_time - e.event_time)) AS late_seconds, EXTRACT(EPOCH FROM (e.event_time - w.wm)) AS event_minus_watermark_seconds, CASE WHEN e.event_time >= w.wm THEN 1 ELSE 0 END AS passes_watermark FROM iot_events e CROSS JOIN watermark w ) SELECT event_id, device_id, event_time, ingest_time, late_seconds, event_minus_watermark_seconds, passes_watermark FROM scored ORDER BY ingest_time;
What this tells me:
late_secondsis how late the event arrived.event_minus_watermark_secondstells me if it’s on the safe side or below the watermark.passes_watermark = 0are precisely the events the pipeline silently throws away.
4) Aggregate into a single “watermark drift” metric
Now I compute counts and percentages.
WITH bounds AS ( SELECT MAX(ingest_time) AS max_ingest_time_seen FROM iot_events ), watermark AS ( SELECT max_ingest_time_seen - INTERVAL '15 minutes' AS wm FROM bounds ), scored AS ( SELECT e.*, w.wm AS watermark_ts, EXTRACT(EPOCH FROM (e.ingest_time - e.event_time)) AS late_seconds, CASE WHEN e.event_time >= w.wm THEN 1 ELSE 0 END AS passes_watermark FROM iot_events e CROSS JOIN watermark w ) SELECT MIN(watermark_ts) AS watermark_ts, COUNT(*) AS total_events, SUM(CASE WHEN passes_watermark = 1 THEN 1 ELSE 0 END) AS passed_events, SUM(CASE WHEN passes_watermark = 0 THEN 1 ELSE 0 END) AS dropped_due_to_watermark, 100.0 * SUM(CASE WHEN passes_watermark = 0 THEN 1 ELSE 0 END) / COUNT(*) AS pct_dropped FROM scored;
This becomes a crisp observability signal:
- if
pct_droppedspikes after a deployment, watermark gating is misbehaving.
What I changed in the pipeline to make it robust
The real fix wasn’t only “set allowed lateness higher.” It was: make the watermark computation deterministic and partition-aware, so one odd batch can’t push the watermark too far.
Two patterns I used:
- Compute max ingest time per stream partition (or per device group), not across the entire table.
- Use a stable “processing time” reference rather than “whatever max ingest_time exists right now.”
In SQL, that means introducing a parameter like processing_time (or using the maximum ingest_time within a bounded lookback window).
Example: compute watermark using only a bounded ingest window
WITH ingest_window AS ( -- Example: only consider the last 30 minutes of ingests for watermark computation SELECT MAX(ingest_time) AS max_ingest_time_seen FROM iot_events WHERE ingest_time >= (TIMESTAMP '2026-11-14 10:20:00' - INTERVAL '30 minutes') ), watermark AS ( SELECT max_ingest_time_seen - INTERVAL '15 minutes' AS wm FROM ingest_window ) SELECT e.event_id, e.event_time, e.ingest_time, w.wm AS watermark_ts, CASE WHEN e.event_time >= w.wm THEN 1 ELSE 0 END AS passes_watermark FROM iot_events e CROSS JOIN watermark w;
That isolates watermark computation from unrelated late spikes.
dbt-style model layout (how I wired it)
I ended up with two models:
stg_iot_events(raw → cleaned timestamps)fct_iot_events_watermark_observability(metrics + per-event pass/fail)fct_iot_events_filtered(actual filtered dataset for training/analytics)
Here’s the observational model in dbt-ish structure (still plain SQL):
-- models/fct_iot_events_watermark_observability.sql WITH bounds AS ( SELECT MAX(ingest_time) AS max_ingest_time_seen FROM {{ ref('stg_iot_events') }} WHERE ingest_time >= {{ dbt.dateadd('minute', -30, 'current_timestamp') }} ), watermark AS ( SELECT max_ingest_time_seen - INTERVAL '15 minutes' AS wm FROM bounds ), scored AS ( SELECT e.*, w.wm AS watermark_ts, EXTRACT(EPOCH FROM (e.ingest_time - e.event_time)) AS late_seconds, CASE WHEN e.event_time >= w.wm THEN 1 ELSE 0 END AS passes_watermark FROM {{ ref('stg_iot_events') }} e CROSS JOIN watermark w ) SELECT '{{ run_started_at }}' AS run_id, MIN(watermark_ts) AS watermark_ts, COUNT(*) AS total_events, SUM(CASE WHEN passes_watermark = 1 THEN 1 ELSE 0 END) AS passed_events, SUM(CASE WHEN passes_watermark = 0 THEN 1 ELSE 0 END) AS dropped_due_to_watermark, 100.0 * SUM(CASE WHEN passes_watermark = 0 THEN 1 ELSE 0 END) / COUNT(*) AS pct_dropped_due_to_watermark, APPROX_PERCENTILE(late_seconds, 0.5) AS late_seconds_p50, APPROX_PERCENTILE(late_seconds, 0.9) AS late_seconds_p90 FROM scored;
Why the percentile metrics mattered
When I only watched pct_dropped_due_to_watermark, it was too binary. Sometimes a deployment changed ingestion patterns slightly, and I needed to know whether lateness was growing overall.
Percentiles (like p50 and p90 of late_seconds) gave me a shape of lateness:
- If p90 jumps, lateness is getting worse.
- If p90 stays flat but drops spike, the watermark moved too aggressively.
The final takeaway I wrote into the playbook
After a weekend of chasing “why did the AI model drift,” I learned that watermark logic needs observability like any other production data quality control.
What I now monitor is specifically:
- the computed watermark timestamp itself
- the percentage of events dropped due to watermark gating
- lateness distribution (
ingest_time - event_time) percentiles
Those three together make “late arriving events are silently missing” detectable within minutes, not after training artifacts or dashboard anomalies show up.
In short: I turned a silent failure (watermark drift) into measurable, repeatable signals by scoring events against the watermark and tracking drop rates plus lateness percentiles.