Streaming buffer is your hidden constraint

The boundary usually shows up through a failed MERGE

The streaming buffer rarely gets treated as a design concern up front. It usually arrives later, through a failure. A MERGE starts rejecting the newest rows. An UPDATE behaves as if recent data is briefly out of reach. A cleanup step lands in an awkward half-working state where the warehouse seems close to supporting the pattern but not quite. That looks like a product quirk if the pipeline was designed around row-level mutation. It isn’t. It’s the platform telling you the ingestion shape and the mutation shape are no longer aligned.

Once fresh rows are arriving continuously, the clean assumption is append first, reconcile later. BigQuery is perfectly capable of sitting inside near-real-time systems, but it does not want to behave like a transaction engine with a nicer SQL dialect. If the pipeline keeps asking for row-by-row certainty against the newest streamed data, the problem is not an odd limitation. The problem is that the workload shape never adapted to the boundary the warehouse already made explicit.

Streaming changes what “latest state” can honestly mean

Once a table is fed through streaming, the safest starting point is an append-only landing zone that tells the truth about arrival. That is why this topic sits naturally next to event-table partitioning. Raw data should land cheaply, predictably, and in a shape that is easy to revisit. Stronger claims about current status, latest state, or clean upserts belong later, after reconciliation has had a chance to do real work.

That distinction matters because a lot of streaming designs quietly import OLTP expectations into the warehouse. Stream the row. Mutate it immediately. Assume the freshest state is the settled state. That is the part that breaks. BigQuery can support fresh analytical pipelines. It just expects those pipelines to admit they are analytical. Arrival is one thing. Curated truth is another.

Append first, reconcile on purpose, publish state later

The durable pattern here is not complicated. Land raw events. Revisit a window. Collapse duplicates and late arrivals against business identity. Then publish the curated surface. It is less satisfying than the fantasy of immediate perfect state, but it survives contact with real ingestion behavior.

Once the model is shaped that way, a lot of related choices get clearer. Now the pipeline has to care about stale rows, about explicit change detection, and about whether the curated table is revisiting enough history to catch late changes instead of only reprocessing whatever happened to land in the last few minutes.

-- Raw append-only landing
insert into raw.orders_events (
  event_at,
  order_id,
  status,
  payload,
)
values (current_timestamp(), 'O123', 'paid', '{"source":"app"}');

-- Periodic reconciliation into curated latest-state table
merge mart.orders_latest t
using (
  select
    order_id,
    any_value(status) as status,
    max(event_at) as updated_at,

  from
    raw.orders_events

  where
    event_date >= date_sub(current_date(), interval 3 day)

  group by
    order_id
) s
on
  t.order_id = s.order_id

when matched then update set
  status = s.status,
  updated_at = s.updated_at

when not matched then insert
  (order_id, status, updated_at)
  values (s.order_id, s.status, s.updated_at);

That shape is less about elegance than about admitting what the system can actually guarantee. Raw arrival stays cheap. Curated state gets published by a process that can revisit enough history to correct itself. The warehouse stops being asked to pretend that streaming and instant mutation are the same thing.

The reconciliation window is part of the model

A revisit window is not just an operational knob. It is part of what the curated table means.

If the reconciliation step only looks back a few minutes when late data, changed children, or retried events can arrive much later, the table is quietly overstating its own certainty. The size of that window should be driven by actual arrival patterns and actual business tolerance for wrongness, not by whatever feels cheapest in the moment. A curated model that does not revisit enough history is not merely under-optimized. It is semantically weak.

That also affects the serving layer. Fresh raw ingestion does not automatically mean every dashboard now deserves live reads against half-settled state. In many cases the more honest move is to publish a calmer reporting surface higher up the precompute ladder and let the reconciliation process settle before the read path starts making strong promises.

Sometimes the right answer is to stop streaming

There is a point where the design pressure becomes clear. If the dominant requirement is immediate row mutation, transactional certainty, or application-grade upsert behavior against the freshest records, then the pipeline has crossed a boundary. At that point the question is not how to work around the streaming buffer more cleverly. The question is whether BigQuery is still the right place for that interaction pattern.

That is also where ingestion shape and storage shape start to connect. Append-heavy raw zones behave very differently from mutation-heavy datasets, which is part of the dataset classification logic in physical versus logical storage. There does not need to be one universal answer across the warehouse. But the answer for each layer does need to match what that layer is actually doing.

The rule

Treat the streaming buffer as a boundary, not as an irritating exception. If data streams in, land it append-only, reconcile it through a real revisit window, and publish curated state afterward. Let raw tables describe arrival. Let curated tables describe settled meaning.

And if the system keeps demanding transactional mutation semantics against the newest rows, stop trying to bend the warehouse into that shape. The ingestion pattern has probably crossed into a different kind of system.