Operations at a grocery-delivery marketplace at Instacart scale asks two questions the warehouse cannot answer - what share of catalogue items were unavailable at 18:00 yesterday and how many store-item pairs have been unavailable for more than three days. The warehouse has an order-lines fact and an inventory-change event fact at 1800 stores and 60000 items per store. Design the model.

What the two questions actually require

Both existing tables are transaction-grained: one row per thing that happened. A transaction fact answers how much moved; it cannot answer how much was there, because state at a moment is not a row in it - it is a reduction over every row before that moment. Asking "unavailable at 18:00" of an event table means replaying every change for every store-item pair up to 18:00, which is why the dashboard times out rather than being wrong.

The two questions also differ from each other. The first asks for state at an instant. The second asks for duration in a state. Those want different physical shapes.

The design

An interval table as the computed truth, and a periodic snapshot derived from it.

1. Availability interval table, one row per store-item-state run: store_key, item_key, state, valid_from, valid_to, built incrementally from the change events by closing the previous row when a new change arrives. This is where the state logic lives, once.
2. Periodic snapshot fact, one row per store-item per day with a declared measurement time - 18:00 local - generated from the interval table with a single valid_from <= t < valid_to predicate. Grain declared in the table's name and its documentation, because the reader's first question will be "one row per what".
3. The first question is then a filter and a count on the snapshot, joinable to the store and item dimensions like any other fact. The second is one predicate on the interval table: state = 'unavailable' and valid_to - valid_from > 3 days.

Kimball's three fact grains - transaction, periodic snapshot, accumulating snapshot - exist for exactly this split, and the snapshot is the one most teams skip and then emulate badly in BI.

The hard part

Sizing. 1800 stores times 60000 items is 108 million pairs, so a daily full snapshot is 108 million rows a day and about 39 billion rows a year - more than the fact tables it is meant to serve. Three mitigations, in order of how much they buy:

• Snapshot only pairs the store actually carries (typically a fraction of the catalogue), which usually removes most of the rows.
• Snapshot exceptions only - the unavailable pairs - and treat absence as available. This turns 108 million rows into the size of the problem, often under a million, and it is correct as long as the interval table remains the source for anything needing both states.
• Coarsen the grain to the question: daily at 18:00, not hourly, until someone proves they need hourly.

What it costs

Two derived tables to build and backfill, and a rule nobody can break: the interval table is the only place state is computed. If a BI developer re-derives availability with a window function in a dashboard, the numbers diverge and the snapshot's authority is gone.

When this is the wrong answer

If the event table is small - a few million rows - and the question is asked twice a month, answer it with a window function over events and build nothing. The snapshot earns its keep when state questions are frequent or the event history is large enough that replaying it is the cost. The threshold to watch is not row count but how often someone asks "as at".