A Data Pipeline Has Four Clocks
4 min read

A Data Pipeline Has Four Clocks


Suppose an order happened on Monday. The file containing it arrived Wednesday. The pipeline picked it up Thursday. A reference-data correction on Friday changed which sales territory owned the order.

What did Thursday’s report say about Monday?

That question is much harder than “what are Monday’s sales?” Both questions sound historical. Only one asks us to reconstruct what the system knew at the time.

I find that many data models name created_at and updated_at, then call time handled. Those columns are useful, but they often collapse several clocks into one. The trouble stays invisible until someone asks why last month’s numbers changed or wants the exact report that was visible during a close.

Four dates for one record

Use this entirely synthetic order:

ClockExampleWhat it tells us
Event timeMonday, March 2When the order happened
Arrival timeWednesday, March 4When the source reached our boundary
Processing timeThursday, March 5When our pipeline accepted and published it
Knowledge timeFriday, March 6When we learned that its original classification was wrong

Four timelines for one order, showing an event, arrival, publication, and later knowledge correction.

These timestamps answer different questions; sorting by one does not reconstruct the other three.

The first three are easy to confuse because they often occur minutes apart in a healthy system. But even a short outage, a late partner file, or a replay makes them diverge. Knowledge time is subtler. It marks when an interpretation became available to the analytical system, and a correction may say it was effective long before we learned about it.

That introduces a fifth date in some models: valid time, the period during which the corrected relationship applies in the business world. It is distinct from the four operational clocks above. I keep the distinction explicit instead of forcing every timestamp into one taxonomy.

Why a normal date filter cannot replay yesterday’s report

Imagine the order was initially attributed to North. On Friday, a corrected mapping says it belonged to West effective from the start of March.

Run this query today:

SELECT territory, SUM(amount)
FROM orders o
JOIN current_territory_map m ON o.account_id = m.account_id
WHERE o.event_date = DATE '2026-03-02'
GROUP BY territory;

It answers “How do we classify Monday’s orders using today’s mapping?” It cannot answer what Thursday’s report showed, because current_territory_map has forgotten its earlier answer.

A more complete model can retain two histories: the business validity of a mapping and when the platform knew that version. In simplified terms:

-- Conceptual columns on a versioned mapping
account_id, territory,
valid_from, valid_to, -- business-effective period
known_from, known_to -- period when this version was believed

Replaying Thursday means selecting not only orders whose event falls in scope, but mapping versions that were known by Thursday. Restating Monday using Friday’s correction selects a different knowledge boundary. Neither is intrinsically wrong; they answer different questions.

This is related to reference data rewriting historical interpretation, but it adds another requirement: a reproducible report needs an explicit as-known-at boundary, not just an effective date.

Watermarks have clocks too

A source modified_at watermark may be enough to detect changed business rows. It is not a reliable record of when a file arrived, whether a run committed, or what reference version a report used. Likewise, an ingestion timestamp is good lineage and a poor substitute for business event time.

I like to keep two separate questions in the pipeline contract:

  1. What changed since the last successful input boundary? This chooses ingestion and incremental behavior.
  2. What version of the answer should consumers see? This chooses publication and reporting semantics.

One pipeline can use an arrival-time watermark while exposing an event-time date dimension. That is perfectly reasonable. Trouble starts when the same date column is quietly expected to do both jobs.

You don’t always need a bitemporal database

The complete model sounds expensive because it can be. Versioning every reference table, retaining publish snapshots, and offering point-in-time joins have real storage and query costs. Most dashboards don’t need to recreate every answer ever displayed.

If reproducibility matters only at month-end, an immutable close snapshot plus the source and mapping versions may be simpler. If corrections must routinely restate long history, effective-dated reference tables and targeted rebuilds may fit. If audit users need “what did we know on Tuesday?”, knowledge-time history becomes hard to avoid.

My preferred starting point is a question, not a schema: Which past answer must we be able to reconstruct, and what would count as the same answer? If the requirement is only current best knowledge, one clock may suffice for a given operation. If the requirement is historical belief, an ordinary event-date filter will never get us there.

More on Data engineering Reference Data Can Rewrite History →