Yesterday's totals change once the daily export replaces the intraday table

A model built at 08:00 and the same model rebuilt at 14:00 disagreeing about yesterday is usually correct behaviour, badly framed. The intraday table is a provisional stream: rows land as they arrive, some arrive hours after the event they describe, and when the settled daily table for that date is written it replaces rather than extends what you already read. Anything that queried the provisional object and published the answer as final has published an estimate without labelling it.

That does not mean every movement is benign. Five things produce the same symptom, and only one of them is the export behaving as designed. This page is about the raw event export and the warehouse tables built from it. Restatement inside an ad platform's own reporting is a different mechanism with a different fix, and it belongs to the reporting cluster.

Audit the partitions before you audit the model

Almost every investigation here starts in the wrong place — in the transformation code — because that is where the number is computed. The evidence is upstream, and it is 4 columns wide.

1. List every partition for the last 21 days with its row count, its minimum and maximum event timestamp in UTC, and a flag for whether the settled daily table for that date exists at all.
2. Compare each row count against the trailing 4 partitions for the same weekday, not against yesterday. Traffic has a weekly shape, so a Sunday judged against a Saturday will look broken every week.
3. Read the maximum event timestamp. A settled partition should reach the end of its reporting day; one that stops at 19:40 is missing the evening, whatever its row count says.
4. Flag any date where the settled table is absent but the intraday one is not. That is the pair that produces the 08:00-versus-14:00 disagreement, and it is visible before anyone opens a dashboard.
5. Record the audit as a table your pipeline writes on every run. Done once by hand it answers today's question; run on a schedule it becomes the freshness contract everything downstream can check.

Five reasons a number you already published moves

The first three are freshness problems and share a family of fixes. The last two are correctness problems that will not resolve themselves no matter how long you wait, which is why separating them early is worth the audit.

The boundary that puts an event in the wrong day

Export partitions are usually named for a date derived from one clock, while the reporting day your business talks about is derived from another — the property's configured timezone, the finance calendar, or the local time where the campaign ran. When those differ, the totals are not wrong so much as answering a question nobody asked. The tell is a day whose row count looks healthy and whose hourly shape is displaced: the morning peak lands in the previous partition and the last hours of trading fall into the next.

• Define the reporting date once, as a derived column in the staging layer, and never let a query recompute it from a raw timestamp inline.
• Store event timestamps in UTC and derive local dates from them, rather than storing local times and trying to recover the offset later.
• Handle the offset shifts. Two days a year are 23 or 25 hours long in many markets, and a job that assumes 24 will silently under- or over-count on exactly those dates.
• Keep the export's own partition date as a separate column. You need both — one to find the data, one to report it — and collapsing them is how this bug becomes permanent. The three-layer model that keeps derived definitions out of the raw layer is set out in turning raw event rows into a model people can query.

When the counts are identical and the metric still moved

This is the cause that survives every freshness fix, because nothing about the volume changed. Someone added an event parameter, renamed one, or started sending a field with a different type, and a derived metric that reads that field now resolves differently for rows written after the change. Nested parameter structures make it worse: a query reading a value by key returns nothing rather than failing when the key is absent, so the metric drops without an error anywhere.

Diff the field list between two partitions either side of the movement. It is a cheap query and it either produces a suspect immediately or rules the whole category out. The same class of silent absence explains conversions that arrive with no click identifier attached — the mechanism traced in the click identifier missing by the time the form submits, where an empty field and a never-populated field are indistinguishable downstream.

A number that changes because more data arrived is a pipeline working. A number that changes because a field started arriving under a different name is a pipeline that has stopped telling you the truth.

Wait, rebuild on arrival, or freeze and restate

There are only three defensible policies, and the mistake is running all three by accident in different models.

1. Wait. Downstream models refuse to run for a date until its partition passes the completeness rule. Right for finance-facing and client-facing numbers, where being late is cheaper than being wrong. The cost is that a delayed export delays the whole chain, so the alert on absence matters more than the alert on lateness.
2. Rebuild on arrival. Models run early on provisional data and re-run over a trailing window when the settled partition lands. Right for operational dashboards that need same-day signal. Every rebuild re-reads the same rows, so the query cost multiplies exactly the way fan-out multiplies a tagging bill — see the tagging server bill climbing faster than traffic.
3. Freeze and restate. Snapshot the figure at a stated cut-off, publish it as final, and record any later movement as an explicit restatement with a version and a reason. Right for anything an external party has already acted on, because silently changing a number somebody quoted is worse than restating it.

The four checks worth wiring in permanently

• Partition existence, as a gate rather than a warning. A model that runs against a missing date should fail loudly instead of producing a small number.
• Row-count deviation against the trailing same-weekday median, with the tolerance you chose written into the check rather than held in somebody's head.
• Maximum event timestamp per partition, which catches the truncated day that a row-count check on its own will pass.
• Schema drift between consecutive partitions, so a new or renamed field is announced by the pipeline rather than discovered in a client meeting.

None of this is sophisticated, and all of it is the kind of work that gets deferred until the first restatement. Building the freshness contract at the same time as the model — rather than adding it after a bad month — is part of what we mean by measurement being in the definition of done on MVP and product builds. The rest of this silo sits under tracking, consent and event pipelines, part of our marketing and advertising practice.

• event export
• warehouse
• data freshness
• diagnostics