Inventory Snapshot Patterns

Inventory produces two facts, and they are two views of the same truth rather than two truths. The transaction fact is the movement ledger: one row per receipt, issue, transfer, and adjustment, at the grain the ERP posted it. The periodic snapshot fact is the level: one row per item per stocking location per snapshot date, holding the balance as it stood at the close of that date.

The relationship between them is an identity, not a convention. Closing balance equals opening balance plus receipts minus issues plus or minus adjustments — which is to say the flows are ground truth and the stock is their integral. Everything else on this page follows from that one sentence.

It has a direction, and the direction matters. Given the flows and one trusted anchor balance you can always rebuild the snapshot, for any grain and any calendar, retroactively. Given only snapshots you can never recover the flows: two days that differ by ten units could be one issue of ten, or a receipt of forty and an issue of fifty, and no amount of querying will tell you which. Store the flows.

No ERP in this library persists the daily balance for you: the most any of them keeps is a current-state balance, a sparse period-grain history, or a quantity you reconstruct by undoing transactions, so the one-row-per-item-per-location-per-day shape is always derived, never extracted. Most pair that current-state table with a movement ledger you can integrate; NetSuite documents none — the accumulate-daily-copies case — and a warehouse execution system’s task ledger is out of scope here. Change capture on the stock table does not change any of that: CDC records that a balance moved; it does not create the daily row. The daily balance is the first of four things the metrics in this library need that the source either never keeps or overwrites, set out together in the source history guide.

The snapshot fact itself is fct_inventory_position — the periodic snapshot at item × stocking location × snapshot date grain, with dim_date, dim_item, and dim_location around it.

Shorthand on this page: CDC is change data capture.

(a) Integrate the flows. The preferred path, and the one this page is written against. Land the movement ledger in bronze as the source publishes it. Conform it in silver into one movement ledger with a signed quantity, a posting date, and resolved item and location keys — unit conversions, sign conventions, and encoding fixes all belong here, once. Then in gold, integrate forward from a physical balance you trust with a window function, and the snapshot exists for every date in the window, including dates that came and went before anyone thought to build a pipeline.

-- Path (a): rebuild the snapshot by integrating the flows. A date spine
-- crossed with the item-location grain, left-joined to the conformed silver
-- movement ledger, with one trusted anchor balance folded into the first day
-- of the window. The anchor is the physical balance at the CLOSE of the day
-- before <window_start>, so it never double-counts that day's movements, and
-- it must already be at item x location grain — pre-aggregate it if your
-- source is finer (lot, receipt slice, batch), or the MAX() below silently
-- picks one of its rows and understates the opening position.
-- The grain is the UNION of both sides on purpose: an item-location that
-- carries an opening balance and never moves again is exactly the slow-moving
-- stock this fact exists to surface, and it has no row in the ledger.
-- Placeholders only — the real column names are in that ERP's own guide.
WITH grain AS (
  SELECT DISTINCT <item_column>, <location_column>
  FROM <catalog>.<silver_schema>.<movement_ledger>
  WHERE <posting_date_column> BETWEEN DATE '<window_start>' AND DATE '<window_end>'
  UNION
  SELECT DISTINCT <item_column>, <location_column>
  FROM <catalog>.<gold_schema>.<anchor_balance>
),
spine AS (
  SELECT
    d.<date_column> AS snapshot_date,
    g.<item_column>,
    g.<location_column>
  FROM <catalog>.<gold_schema>.<date_dim> AS d
  CROSS JOIN grain AS g
  WHERE d.<date_column> BETWEEN DATE '<window_start>' AND DATE '<window_end>'
),
daily_delta AS (
  SELECT
    s.snapshot_date,
    s.<item_column>,
    s.<location_column>,
    COALESCE(SUM(m.<signed_quantity_column>), 0)
      + COALESCE(MAX(a.<anchor_quantity_column>), 0) AS qty_delta
  FROM spine AS s
  LEFT JOIN <catalog>.<silver_schema>.<movement_ledger> AS m
    ON  m.<item_column> = s.<item_column>
    AND m.<location_column> = s.<location_column>
    AND m.<posting_date_column> = s.snapshot_date
  LEFT JOIN <catalog>.<gold_schema>.<anchor_balance> AS a
    ON  a.<item_column> = s.<item_column>
    AND a.<location_column> = s.<location_column>
    AND s.snapshot_date = DATE '<window_start>'
  GROUP BY s.snapshot_date, s.<item_column>, s.<location_column>
)
SELECT
  snapshot_date,
  <item_column>,
  <location_column>,
  SUM(qty_delta) OVER (
    PARTITION BY <item_column>, <location_column>
    ORDER BY snapshot_date
    ROWS UNBOUNDED PRECEDING
  ) AS on_hand_qty
FROM daily_delta

(b) Accumulate copies. Every ERP in this library exposes a current-state balance table. Copy it on a schedule, stamp each copy with its snapshot date, append, and you have a snapshot fact by tomorrow morning with no reconstruction logic at all. It is the easiest thing on this page to build and it is sometimes the only thing available — but understand what you are accepting. Your history starts the day the pipeline does. It can never be backfilled, because the balance you missed no longer exists anywhere. And it can never be restated, because when a correction lands in the ERP for a date three weeks back, your copy of that date is a photograph of a number that has since changed.

The standing rule for path (b). Reconcile the copies against the flows anyway. Wherever a movement ledger exists, run the integration over a trailing window and compare it to what you copied. It costs one scheduled query and it is the only thing that will tell you a balance has drifted — from a delete you never saw, a company partition you forgot to pin, or a unit you never converted — before someone in finance tells you at close.

A stored balance is a level, and a level has to be read at a moment. The moment is the close of the period: the stored value for a day is the balance at the end of that day, the value for a month is the balance at the end of the month, and the aggregation from daily rows up to a monthly figure is LAST, not SUM and not AVG.

Averages are not forbidden — they are just a query-time derivation, never a storage semantic. Store period ends; average them in the query when a metric calls for it. And when you do, say how many points went into it, because “average inventory” over two month-end balances and over sixty daily balances are different numbers with the same name, and the difference shows up in every ratio built on top of them.

That matters most where the average balance is a denominator — inventory turns and days of supply both publish that switch explicitly, and it is the switch that most often explains why two teams quote different turns off the same warehouse.

On-hand is semi-additive. It adds up perfectly across items, across locations, across product hierarchies and regions — and not at all across snapshot dates. Twelve month-end balances summed together produce a number with no physical meaning whatsoever, and the reason it survives so long in a dashboard is that it looks plausible: it trends, it filters, it drills. It is just roughly twelve times the average balance.

The wrong query is the default one: SUM(on_hand_qty) grouped by month. The right query depends on the question. For a level — what was on hand at the end of March — take the LAST snapshot in the period. For a rate denominator — average inventory for the quarter — average the period-end balances, and say how many. Two different queries, neither of them SUM.

-- Reading a period-end level out of a daily snapshot. The stock at the close
-- of a period is the LAST row in that period per item and location — never the
-- sum of the days, never their average. QUALIFY filters after the window
-- function has run, so no subquery is needed.
-- The period itself comes from the date dimension, not the fact: the fiscal
-- calendar is a dimension attribute, and putting it on the fact would freeze
-- one calendar into the grain.
SELECT
  d.<period_column>,
  f.<item_column>,
  f.<location_column>,
  f.on_hand_qty AS period_end_qty
FROM <catalog>.<gold_schema>.<snapshot_fact> AS f
JOIN <catalog>.<gold_schema>.<date_dim> AS d
  ON d.<date_column> = f.snapshot_date
WHERE f.snapshot_date BETWEEN DATE '<window_start>' AND DATE '<window_end>'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY f.<item_column>, f.<location_column>, d.<period_column>
  ORDER BY f.snapshot_date DESC
) = 1;

-- The alternative, where the date dimension already carries the flag: a plain
-- join and no window function, with the fiscal calendar's definition of
-- "period end" owned in one place instead of restated in every query.
SELECT
  f.<item_column>,
  f.<location_column>,
  d.<period_column>,
  f.on_hand_qty
FROM <catalog>.<gold_schema>.<snapshot_fact> AS f
JOIN <catalog>.<gold_schema>.<date_dim> AS d
  ON d.<date_column> = f.snapshot_date
WHERE d.is_period_end

The same discipline covers every ratio built on the fact. Store the components — quantity, value, days, cost of goods sold — and recompute the ratio from the summed numerator and the summed denominator at whatever scope is on screen. Never store a ratio and never average one: the average of per-item turns is not the turns of the portfolio, and the gap widens exactly where the mix is interesting. Additivity is a property of each measure, and a ratio is not additive on any axis — which is why the components are what the fact carries.

The snapshot cadence is the fact grain. That makes it a design-time decision with a migration behind it, not a tuning knob you revisit when someone asks a new question — and it is worth agreeing out loud in the workshop, before anyone builds.

Take daily wherever volume allows. Every other calendar is a filter over daily: a weekly figure is the daily row on the week’s closing day, a fiscal month-end is the daily row flagged in the date dimension, and both come out of the same fact with no rebuild. Go weekly-only and you have locked in one definition of a week forever, and you can never answer a daily question — not a stockout date, not a build-up that started on a Tuesday, not a count adjustment that reversed within the week. The rows you didn’t store are gone.

The usual objection is cost, and it is usually overstated. A daily snapshot partitioned or clustered on snapshot date is cheap to store and cheap to prune in Delta, because every query that reads it filters on that column first. Where it genuinely doesn’t fit — a catalog wide enough that the daily cross-product is millions of rows a day, most of them zero — the honest fallback is a hybrid: daily for a rolling window that covers operational questions, and period-end rows kept forever for the trend. State the window in the fact’s definition so nobody discovers it by getting an empty chart.

A periodic snapshot is not merely periodic; it is dense. That is Kimball’s requirement, and he states it as a property of the fact itself. The snapshot is uniformly dense in its foreign keys: a row exists for every period at the fact’s grain, here item × stocking location × snapshot date, even when nothing moved that day. The days nothing moved are rows the fact owes, not rows it may skip. A zero-movement day is not missing data; it is the observation that the balance held.

Replay alone will never produce it. Movement rows exist only where something happened, so a replay that keeps only the posted movement rows reproduces the transaction ledger’s own shape: active item-locations get rows, and the intersections where nothing ever moved get none. The dense shape exists only if it is generated explicitly, and no verified source hands it over: where a history exists at all it is a current-state balance, a sparse period-grain series, or a quantity you reconstruct by undoing transactions. The dense daily balance is always built, never extracted.

Generating it is less exotic than it sounds, because the rebuild SQL earlier on this page already does it. The spine is the date dimension crossed with the grain, and the grain is “the UNION of both sides on purpose”: the balance side contributes item-locations the movement ledger never mentions, which is exactly the slow-moving stock this fact exists to surface. The spine is also where the zero-activity rows come from: every item-location pairs with every date, whether or not anything moved.COALESCE(SUM(...), 0) does lighter work: it turns an empty movement sum into a zero delta so the running balance holds through the quiet days. A dense fact buys one dividend a sparse one cannot: a day that never loaded can be stored as a row with no measured balance, and a failed load becomes visible instead of silently flat. That holds only if the missed day is allowed to be empty: carrying the prior balance forward at storage draws the same flat line for a failed load as for a quiet day.

The cost is workable before you commit. The row count is the distinct item × location pairs in the window times the days in it, and the zero-activity share is one minus the share of pair-days with a movement. The grain CTE in the rebuild SQL is the sizing query: run it over the window, multiply by the days, and compare the result with the movement ledger’s row count. If the number is comfortable, take daily; if it is not, the fallback is the hybrid the grain decision above already describes, not a narrower daily fact. A cheap lever remains: cluster or partition the fact on the snapshot date.

Where on-hand lives for your ERP (and, where the reference documents one, the movement ledger) is that reference’s own inventory documentation: SAP’s inventory guide, and the inventory modules for Dynamics 365, Oracle EBS, Oracle Fusion, JD Edwards, Infor M3, and NetSuite.

The movement ledger for a past day is not immutable, and every ERP in this library will prove it to you eventually. Postings arrive backdated, because posting date and entry date are different columns for a reason. Physical counts land as adjustments dated to the count, not to the day the variance was keyed. Reversals undo a movement that your snapshot already integrated. None of these are exceptions; they are ordinary month-end.

So the snapshot fact is not append-only. When a correction lands, recompute every affected day from the earliest corrected date forward — the balance on that date is wrong, and so is every date after it, because the running total carries the error along — and MERGE the result by item, location, and snapshot date. An INSERT here gives you two rows for one day and a fact that silently double-counts at the grain it was defined at.

Declare the restatement window out loud, in the fact’s own definition: how far back a correction will be honored, and what happens beyond it. A dashboard whose numbers move for last quarter without explanation loses trust faster than one that was slightly wrong; a documented restatement window is the difference between a correction and a surprise.

One failure mode deserves naming separately, because it produces no correction event at all. A feed that can’t see deletes leaves movements in bronze that no longer exist in the source, and the integrated balance drifts high forever with nothing to trigger a recompute. Which feeds are exposed to that, and what to do about each, is the whole subject of deletes & change tracking. It is the single most common cause of a snapshot that reconciles on day one and not on day ninety.