Your pipeline ran clean last night. This morning, yesterday’s production total is different from the number on the report you sent at 8 a.m. Nobody edited anything, and no job failed.
The usual cause is data that showed up late. A remote site lost its radio link for six hours, the device kept recording, and the missing readings arrived after your load had already moved on.
Why field data arrives late
Flow computers, RTUs and PLCs at remote sites do not stop recording when the network drops. Most can buffer readings locally, and a polling system or historian collects the backlog when the link returns. That behavior is good. It means a comms outage costs you latency instead of data.
The catch is that two different clocks now describe every row. Event time is when the device measured the value. Load time is when your pipeline first saw it. On a healthy link they sit seconds apart. After an outage they can sit hours or days apart.
Other sources do the same thing. A field ticket entered on Friday for Wednesday’s work, a lab result posted a week after sampling, a corrected meter reading from the measurement team. Each has an event date in the past and a load date today.
How an incremental load loses rows
Most incremental loads track a high-water mark. The job remembers the largest timestamp it loaded and asks the source for everything newer.
Say the last run loaded rows up to 02:00. The site’s link drops at 02:10 and returns at 08:00. The device buffered readings stamped 02:10 through 08:00. The next scheduled run asks for rows newer than 02:00, and whether it finds them depends on what the source does with them.
If the source filters on event time and the backlog has not been collected yet, the run sees nothing for that site and advances the mark to the newest row it saw from other sites, say 07:55. When the backlog lands at 08:05 with timestamps between 02:10 and 07:50, every one of those rows is older than the mark. The next run never asks for them.
No error is raised. The load succeeded, the row counts looked plausible, and a six-hour hole sits in one site’s data. You find it when someone compares a daily total against another system.
Track both timestamps
Fixing this starts with keeping both times in your raw layer. Add an ingested_at column that your pipeline sets on first sight, and leave the device’s event_ts untouched.
With both columns you can answer questions that were impossible before. How late does each site run, typically and at worst? Which rows arrived after the report that used them was published? Did yesterday’s total change, and by how much?
The second point matters for trust. If a number can change after you publish it, you want to know that it changed and why, instead of discovering it from an angry email.
Re-read a lookback window
The simplest repair for the high-water-mark problem is a lookback window. Each run re-reads everything from the mark minus a fixed overlap, say 48 hours, instead of from the mark itself.
Re-reading means you will see rows you already loaded. That is fine as long as your load is idempotent, so the same row arriving twice leaves one row behind.
Key the target table on the natural identity of a reading, usually the tag or point and the event timestamp. Then write with an upsert. In PostgreSQL that looks like this:
insert into raw.readings (tag_id, event_ts, value, quality, ingested_at)
select tag_id, event_ts, value, quality, now()
from staging.readings
on conflict (tag_id, event_ts)
do update set value = excluded.value,
quality = excluded.quality
where raw.readings.value is distinct from excluded.value
or raw.readings.quality is distinct from excluded.quality;
The where clause skips rows that did not change, so a re-read of two days of data does not rewrite two days of data. Note that ingested_at keeps its first-seen value, which is what you want for the lateness analysis above.
Pick the window from your own data
How long should the lookback be? Do not guess. Once you store ingested_at, measure the gap between event time and ingest time per site and look at the tail, not the average.
A window of 48 hours covers an outage of a day plus slack. It does not cover a site whose radio was dead for a week. For those, a window long enough to catch everything is expensive, so run a second, slower job that sweeps back 30 or 90 days on a weekly schedule. Sweeping is cheap when the upsert skips unchanged rows.
You can also invert the problem. If the source can tell you which rows are new to it, such as a sequence number or an insert-order cursor, use that instead of timestamps. Whether it can depends on the system, so check before you rely on it.
Decide what late means for reports
A pipeline fix does not settle the business question. When late data arrives, does yesterday’s report change?
You have three honest options. Restate the report and show that it was restated. Freeze the number at publication and carry late rows into the day they arrived, with a flag. Or hold publication until a cutoff, say 10 a.m., and accept that the report comes out later.
Pick one on purpose and write it down. Production accounting, operations and finance often need different answers, and the worst outcome is each team quietly using a different one.
A short checklist
- Store event time and ingest time as separate columns.
- Key target tables on tag and event time so reloads are safe.
- Re-read a lookback window every run, sized from measured lateness.
- Sweep a longer window on a slower schedule.
- Say in writing whether late rows restate history.
- Alert when a site has sent nothing for longer than its normal interval, because silence looks the same as a clean run.
None of this is exotic. It is the difference between a pipeline that succeeded and a pipeline whose output you can defend when someone asks why the number moved.