skip to content

Why is a GA4 daily export table, events_YYYYMMDD, unsafe to load once and never revisit?

level: seniorimportance: should knowfreq 40%

answer

  1. yesterday's table is not frozen
  2. one table is provisional and disappears
  3. events can arrive after their day
  4. a high-water mark misses the rewrite
  5. reload a trailing window, replace the date

basics

~20 s

GA4 can rewrite a daily export table after it first appears, because late-arriving events are folded in and the day's data is finalized in place. A pipeline that loads each table once and marks the date done silently keeps the first, incomplete version.

solid answer

~50 s

The GA4 export writes one table per day, `events_YYYYMMDD`, plus a provisional `events_intraday_YYYYMMDD` when streaming export is on. Two things break "load once, mark the date done". First, the intraday table is **provisional**: it can contain duplicate rows, some fields are not final, and it is dropped once the daily table for that date is written — so anything you loaded from it must be replaced, not appended to. Second, the daily table itself can be **rewritten** after its first appearance as late-arriving events are folded in, so the row count you saw at 06:00 is not necessarily the final one. The working pattern is to reprocess a trailing window of recent days rather than trusting a high-water mark, replace each date's partition wholesale instead of appending, and dedupe on a stable event key before anything downstream counts rows.

code

text · 8 lines
text
analytics_123456789/
  events_20240112            <- finalized, but can still be rewritten
  events_20240113            <- finalized
  events_20240114            <- finalized
  events_intraday_20240115   <- today, provisional; dropped when events_20240115 lands

# a high-water-mark loader that stops at the newest date
# never re-reads 20240112 after late events are folded in

go deeper

for a junior

Know that GA4 writes one table per day plus a separate provisional table for today, and that today's numbers are expected to move until the day's table is finalized.

for a middle

Explain the lifecycle: the intraday table accumulates, the daily table is written and the intraday one is removed, and late-arriving events can change a past date. Describe replacing a date's partition rather than appending.

for a senior

Show the operational habits: a trailing reprocess window sized from measured behaviour, dedupe on a stable event key, row-count and last-modified reconciliation that alerts when a loaded date changes, and provisional numbers kept out of history.

for a principal

Own the freshness-versus-correctness contract with the business: what is promised for today, when a day is declared final, and what happens to a published number when a restatement arrives after the fact.

## Two tables, two very different contracts A GA4 property linked to BigQuery writes into `analytics_<property_id>`. Depending on the export options selected, you get one or both of: - **Daily export** — once per day, GA4 writes `events_YYYYMMDD` for the previous day in the property's reporting time zone. This is the finalized, enriched table most models should be built on. - **Streaming export** — events flow continuously into `events_intraday_YYYYMMDD` for the current day. This table is explicitly provisional: it is not guaranteed to contain every event, some fields are not populated the way the daily table populates them, and duplicate rows can occur. When the daily export for that date completes, GA4 writes the daily table and the corresponding intraday table for that date is removed. Treating those two as interchangeable sources is the first failure. A dashboard that unions today's intraday table with historical daily tables is fine as long as everyone understands today's numbers will move; a *persisted model* that appends intraday rows and never reconciles them against the daily table will carry duplicates and provisional values forever. ## Why the daily table is not immutable either The more subtle failure is assuming that once `events_20240115` exists, it is final. Events can reach GA4 after the day they occurred — a mobile client that was offline, a server-side send with a backdated timestamp, a slow batch of hits — and GA4 folds them into the day they belong to. The result is that the table for a past date can be rewritten with more rows than it first had. A pipeline with a high-water mark that says "the latest date I have ingested is 2024-01-15, so I start at 2024-01-16" will never see the additional rows, and the loss is invisible: no error, no gap in the calendar, just a day that is quietly a couple of percent short forever. The generic name for this is late-arriving data, and the generic remedy — do not trust a monotonic cursor when the source can rewrite history — applies to plenty of sources. What is GA4-specific is that the rewrite is **whole-table replacement of a date**, which makes the fix pleasantly simple: reload the date. ## The load pattern that survives both Four rules cover it. 1. **Reprocess a trailing window.** On each run, reload the last N days rather than only the newest date. Choose N from observed behaviour on your own property — compare row counts for a date on consecutive days for a few weeks and see when they stop moving — rather than from a number someone quoted. 2. **Replace, never append.** Load each source date into its own target partition and overwrite that partition wholesale. Appending is what turns a rewrite into a duplicate. 3. **Dedupe on a stable key.** Even within one table, and especially for intraday rows, the same event can appear twice. The customary key combines `user_pseudo_id`, `event_name`, `event_timestamp` and `event_bundle_sequence_id`; validate on your own data that the combination is unique before relying on it. 4. **Separate provisional from final.** If you need same-day freshness, expose intraday-derived numbers under a clearly labelled "today, provisional" surface, and never let them persist into the tables that history is computed from. ## Detecting that the assumption broke Two cheap checks catch this class of bug. Record, per source date, the row count and the table's last-modified time at load; if a table's last-modified time moves after you marked the date complete, you have a rewrite to pick up. And run a reconciliation that recomputes a simple metric (events per day) for the trailing window on every run and alerts when a previously-loaded date's value changes by more than a small tolerance — which both catches late arrivals and tells you empirically how long your trailing window should be. ## Time zone, one more trap The table name's date is in the property's reporting time zone, while `event_timestamp` is microseconds since the Unix epoch in UTC. So `events_20240115` is not "the UTC day of 15 January" unless the property is on UTC, and grouping by a UTC-derived date across tables will smear events across two table dates. Pick one convention — the table date or a timestamp-derived date in a declared zone — write it down, and use it everywhere.

  • How would you decide how many trailing days to reprocess?
    Measure it. Record each source date's row count and the table's last-modified time on every run, then look at how long a date keeps changing on your own property. Set the window comfortably beyond the observed tail, and keep the check running so a change in client behaviour — a new offline-capable mobile app, say — shows up as a widening tail instead of silent loss.
  • What is the risk of building persisted models directly on events_intraday tables?
    They are provisional by design: rows can be duplicated, some fields are not final, events can be missing, and the table for a date is dropped once the daily table lands. Anything you persist from it must be replaced by the daily table's version later. Use it only for clearly-labelled same-day freshness, never as the source of history.
  • Why can grouping the export by a UTC date disagree with the table names?
    The table suffix is a date in the property's reporting time zone, while event_timestamp is UTC microseconds. Unless the property runs on UTC, a single daily table spans parts of two UTC days and a UTC-derived date pulls events across table boundaries. Choose one convention, state it in the model, and apply it consistently.

saying these in an interview costs you the question

  • Assumes a daily export table never changes after it appears
  • Appends the export instead of replacing each date
  • Persists intraday rows as if they were final
  • Uses a max-date high-water mark as the only cursor
  • Treats the table-name date as a UTC date

context

resolving links…