Someone pulls yesterday's revenue from the warehouse at 9am. Solid number, goes in the standup. At 2pm someone else pulls the same figure for the same day and it's higher by a few percent. Nobody edited anything. No job failed overnight. The number just isn't the same number twice, and the two people who ran it are now quietly doubting each other's queries.
Today's total in your warehouse almost never means what it sounds like it means. It means "today's total, as of the last time the pipeline ran, and as far as the source system had told us by then." Those two qualifiers are where the disagreement lives. They're invisible in a dashboard that just says "Revenue: $48,210."
Two clocks, not one system
A source system and a warehouse don't share a clock. The source records event time: when the order was actually placed, the payment actually captured, the ticket actually closed. The warehouse records load time: when a pipeline happened to run and pick that record up.
Most days those two line up closely enough that nobody notices the gap. The gap becomes visible the moment someone compares a number from the source system's own dashboard against the same day's figure in the warehouse, because the source system usually updates itself continuously and the warehouse updates itself on a schedule. Ask both "what happened today" at 9am and you'll get two different, both-correct answers, because the warehouse's 9am snapshot genuinely didn't have the last hour of orders yet.
Late-arriving data isn't a bug
The deeper cause isn't scheduling, it's that records don't always arrive in the order they happened. A payment can authorize at 11:58pm and settle two hours later. A webhook can fire, fail, and retry the next morning. A field team can log a job in the app once they're back near signal, hours after the job actually finished. Every one of these lands in the warehouse dated to when it happened, not when it arrived, which means a day that looked finished can quietly grow rows for days afterward.
This has a name in data warehousing: a late-arriving fact, and it's old enough to have a standard playbook rather than a workaround. Kimball Group's writeup on the pattern treats it as a normal thing a warehouse has to handle, not an error condition. Streaming systems formalise the same idea as a watermark: an explicit, tracked estimate of "we believe all data up to this point has arrived," with anything after it defined as late rather than wrong. Google Cloud's Dataflow documentation is a clear description of how that tracking works in practice. The concept transfers even if you're running nightly batch jobs rather than a streaming pipeline: the question "as of when is this number final" needs an answer either way.
Where the mismatch actually comes from
Three specific things, and it's rarely more than one of them at a time once you know to look.
Timezone cutoff. "Today" in the source system's business timezone and "today" in UTC, which is what most pipelines default to, can disagree by several hours at the edges of the day. An order at 11:40pm local time can land in the warehouse dated to the next UTC day. Nobody wrote a bug; two systems just used different definitions of when the day ends.
Snapshot timing. If the extract for "today" runs before today is actually over, which is common when a morning report needs to be ready before the business day starts, it is definitionally incomplete. It isn't wrong. It's a partial answer being read as a final one.
Backfill and retry lag. Payment processors, ad platforms and third-party APIs commonly revise or backfill data for up to 24 to 72 hours after the fact. A number that was accurate at the time it was pulled can become stale purely because the source changed its mind afterward.
How to stop arguing about today's total
None of this requires solving the timing problem. It requires making it visible instead of invisible.
- Pick one canonical timezone and stamp it explicitly. UTC is usually the right choice for storage, with the business's local timezone applied only at the point a human reads the number. Write the boundary rule down once so nobody re-derives it under deadline.
- Put an "as of" timestamp on every total, not just a date. "Revenue for Aug 9, as of Aug 10 09:00 UTC" tells the reader this number can still move. A bare date implies it's finished.
- Decide how long a day stays open before you call it final, and say so. A common approach: treat the current day and the prior day as provisional, and only report a day as settled once it's 48 hours old. Slower than instant, but it stops the argument.
- Reconcile against the source with a tolerance window, not an exact match. Comparing today's warehouse total to today's source total will almost always show a gap. Comparing yesterday's totals, once both sides have had a full day to settle, is the check that's actually meaningful.
The pipeline habit this connects to is the same one behind monitoring a pipeline properly. The fix isn't a smarter query. It's deciding in advance what "done" means for a given number, then building the check around that definition instead of around whatever the query happens to return right now.
One thing you can do tomorrow
Pick the one number your leadership team quotes most and ask when it's actually considered final: end of day, 24 hours later, 48 hours later, never formally decided. If the honest answer is the last one, that's the whole problem in one sentence, and it's usually a half-day of work to fix once someone writes the rule down.
That's the layer we work in when we design and run data pipelines for clients who've already had the meeting where two people read the same dashboard and got two different numbers.
Sources: Kimball Group, Late Arriving Fact, Google Cloud, Dataflow streaming pipelines.
