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 — every one of the eight keeps current state only, so the one-row-per-item-per-location-per-day shape is always derived, never extracted. Six of the eight pair that current-state table with a movement ledger you can integrate; NetSuite documents none, which is why it is the accumulate-daily-copies case below, and EWM's execution-layer 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 specified in the dashboard patterns library rather than restated here — fct_inventory_position is the periodic snapshot at item × stocking location × snapshot date grain, with dim_date, dim_item, and dim_location around it.

(a) Integrate the flows. The preferred path, and the one every ERP section below 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 on this page 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. The measures and additivity section of the inventory health pattern works the same rule through a real KPI row.

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.

SAP — where the balance lives

MARD holds current stock at material / plant / storage location. MCHB holds the same stock broken down by batch, which is the first trap: summing both double-counts. MSKA carries sales-order special stock, and MBEW carries the value side at material / valuation area. All of them are current state.

SAP — history, and the trap

SAP keeps no daily stock history, so a point-in-time balance is rebuilt from movements — MSEG and MKPF in ECC, MATDOC in S/4HANA — anchored on a snapshot you trust. The S/4 trap is worse than a modeling nuisance: stock quantities are no longer persisted in MARD and MCHB at all, and are calculated through NSDM proxy views over MATDOC. Replicate the physical tables and your quantities simply freeze, quietly, with no error anywhere. (Quantities are the subject here; period-end valuation history is a separate question, covered in the inventory guide's valuation section.)

What is on hand right now? →What was on hand at a point in time? →Extraction & CDC reality →

EWM — where the balance lives

In three tables that do not tie out row for row. Quantities live in /LIME/NQUAN, one row per stock GUID per tree node per unit of measure. Descriptive attributes live on /SCWM/QUAN, which exposes no product, stock-type, owner, or entitled party as directly declared columns — they sit inside include structures that neither public mirror expands, which is precisely why AQUA is the analytics entry point. The resolved identity — bin, handling unit, product, stock type, batch, owner, entitled party on one row — is on /SCWM/AQUA.

EWM — history, and the trap

Two traps, both fatal to a naive balance. The LIME rows hang on nodes of a location and handling-unit tree, so summing them without resolving GUID_PARENT counts nested handling units more than once. And AQUA is available quantity, not on-hand: stock already committed to open warehouse tasks is excluded, so it will never reconcile to the LIME quantities by construction. Treat EWM as the bin- and HU-level execution layer it is — a network-level inventory snapshot fact sources from the ERP side, not from here.

The LIME stock model →

JDE — where the balance lives

F41021, the item-location record: on-hand, committed, and inbound quantities by item, branch, location, and lot. F4102 sits beside it with the branch-level planning attributes you will want on the dimension rather than the fact.

JDE — history, and the trap

F41021 is current state only. The history is the cardex, F4111 — every inventory movement with quantity and cost and links back to its source documents — which makes JDE one of the cleanest rebuilds in the library. The trap is not the model, it is the encoding: Julian dates and implied decimals corrupt a running balance silently, off by a factor of ten or shifted by a day, with nothing failing. Convert both in silver, once, and never in the query.

Julian dates →Implied decimals →Inventory health: sourcing it from JD Edwards →

D365 — where the balance lives

InventSum, the aggregated on-hand by item and inventory dimension — physical, available, and posted balances. It only means something once resolved through InventDim by InventDimId, the hub holding one row per unique combination of site, warehouse, location, batch, and serial. The stock grain you report at is a property of that join, not of InventSum.

D365 — history, and the trap

InventTrans is the movement ledger — every physical and financial stock movement with its issue/receipt status, quantities, dates, and source document — so the rebuild path is available. Only aggregate on-hand export entities exist, so land InventSum and InventTrans as raw tables rather than fighting the entity layer. And every one of them is company-partitioned by dataareaid: leave it out of a join or a group-by and you have summed two legal entities into one balance.

InventDim: the dimension hub →Company partitioning: dataareaid →

M3 — where the balance lives

MITLOC holds the physical on-hand at the finest grain M3 tracks stock — item, warehouse, location, lot, and receipt. MITBAL is the warehouse-level roll-up of those rows, paired with the planning policy (safety stock, reorder point, lead time, main supplier) on the same record. Pick the grain deliberately: MITBAL is the cheap answer, MITLOC is the honest one.

M3 — history, and the trap

MITTRAis the stock transaction ledger — receipt, issue, transfer, adjustment, with the order reference that caused it — so the integration path is clean. Two encodings decide whether it comes out right: balances are stored in the item's basic unit, so a snapshot that mixes units is arithmetic on incompatible numbers, and every table is CONO-partitioned on the company / division / facility / warehouse ladder. Pin CONO before anything else.

The CONO / DIVI / FACI / WHLO ladder →Units of measure & quantities →

EBS — where the balance lives

MTL_ONHAND_QUANTITIES_DETAIL — and the single most expensive misreading in this reference is treating one of its rows as a balance. The rows are FIFO receipt slices, one per receipt, consumed in order as material issues. One item in one bin routinely holds many. On-hand at any grain is SUM(PRIMARY_TRANSACTION_QUANTITY) grouped by that grain, never a row lookup.

EBS — history, and the trap

MTL_MATERIAL_TRANSACTIONS is the material transaction ledger and the reconciliation backbone for every stock question, so the rebuild is well supported. The second trap is the org column: inventory is striped by ORGANIZATION_ID, not ORG_ID, and the two look interchangeable until a balance comes back for the wrong set of warehouses.

On-hand is a sum of slices →Two org columns, one of them wrong →

Fusion — where the balance lives

INV_ONHAND_QUANTITIES_DETAIL repeats the EBS model exactly — receipt-level FIFO slices, summed at your chosen grain. What Fusion adds is INV_ONHAND_QUANTITIES_SUMMARY, pre-aggregated at item + location and maintained automatically in step with the detail: the ready-made balance EBS never had. It is current state, not history — every write to the detail updates the matching summary row.

Fusion — history, and the trap

INV_MATERIAL_TXNSis the transaction ledger for the rebuild. The trap is the convenience: Fusion holds the same truth twice, once in maintained counters and once in insert-only event ledgers, and summing the wrong one — or both — double-counts. Decide which of the two your snapshot is sourced from, write it down in the fact's definition, and don't mix them in one measure.

Counters vs event ledgers →

NetSuite — where the balance lives

inventoryitemlocations — the per-item-per-location balance and policy record, carrying on hand, available, committed, back-ordered and in-transit quantities alongside the reorder point, safety stock, and lead times. This is the table inventory dashboards should read, not the rollup columns on the item record, which are global across every location.

NetSuite — history, and the trap

Current state, and this reference documents no stock movement ledger to integrate — which makes NetSuite the canonical accumulate-daily-copies case on this page. Your history starts the day the pipeline does, so start it. Two things to know before you do: on-hand value is a costing output that cannot be reconstructed by summing transaction amounts under any of the costing methods, so copy it rather than compute it; and deletes never reach a modification-date watermark, which is exactly what corrupts an accumulated copy — transactionline is the line-grain case where that bites hardest. Everything arrives through the one NetSuite2.com door.

Costing: value is not a transaction sum →One door out: the NetSuite2.com source →

The movement ledger for a past day is not immutable, and every ERP on this page 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.

Nothing new here — every cell restates a section above. Read the second and third columns together: the balance table tells you where to look, and the history column tells you whether looking there repeatedly is your only option.

One pattern is worth reading off the third column: not one of the eight keeps a daily stock history for you. The snapshot is something the lakehouse builds, in every case. The only question is whether you build it from the flows or from copies.

Five rules survive every ERP on this page, and they are the whole page in five lines.

Flows are ground truth. Stock is their integral. Store the movements, derive the levels, and you can answer questions nobody has asked yet.

Stock is read at period end, and never summed across time. LAST for a level, AVG of period ends for a rate denominator, SUM never.

Ratios recompute from summed components at every scope. Store numerators and denominators; never store a ratio, and never average one.

Snapshots restate; they don't append. A backdated correction rewrites every day from its posting date forward, by MERGE, inside a restatement window you have declared.

When you can only copy current state, your history starts today. So start today — and reconcile the copies against the flows wherever flows exist.