Lookback window

You meet it when#

Data arrives late — a phone was offline, a vendor batched their export, a partner's job failed and reran. If you only ever process "yesterday", those rows land in a window you already closed and are never picked up.

The pattern#

Idempotency makes this nearly free: you are already replacing a window, so make the window wider.

delete from daily_revenue where order_date >= current_date - 7;
insert into daily_revenue
select order_date, sum(revenue_usd)
from orders
where order_date >= current_date - 7
group by order_date;

Choosing N by measuring#

Do not guess. Look at the distribution of arrival lag in your raw data and pick the value that covers, say, 99% of it:

select
    datediff('day', ordered_at, _loaded_at) as lag_days,
    count(*)                                as rows,
    round(100.0 * sum(count(*)) over (order by datediff('day', ordered_at, _loaded_at))
          / sum(count(*)) over (), 2)       as cumulative_pct
from raw_orders
group by 1
order by 1;

Read off the row where cumulative percentage crosses 99. That is your N.

The half that is not technical#

Event-time attribution means yesterday's number changes for a few days. Stakeholders read that as a bug unless you tell them first.

One sentence, in the report footer and said out loud once:

Figures are provisional for seven days and firm after that.

That sentence ends the problem entirely. The alarm was never about the number moving — it was about being surprised.