SAP stock on hand in the lakehouse: MARD, MATDOC, and the snapshot trap

Table set. MARD is the workhorse: one row per material / plant / storage location, with the stock split across status buckets — LABST (valuated unrestricted-use), INSME (quality inspection), SPEME (blocked), plus UMLME (in transfer), EINME (restricted batch), and RETME (blocked returns). Batch detail lives in MCHB at material / plant / storage location / batch. You do NOT add them together: batch quantities in MCHB are the same stock already counted in MARD, broken down one level finer. Summing both double-counts every batch-managed material.

What is NOT in MARD. Sales-order-assigned stock (special stock E) lives in MSKA, not in the anonymous MARD buckets. In-transit stock is not in MARD at all — it sits at plant level (MARC) or in dedicated transit tables. If your on-hand number must tie to what finance calls inventory, missing special and in-transit stock is where the tie-out fails.

Grain. Material / plant / storage location (add batch only when the business question needs batch — expiry, recalls, genealogy). Aggregating MARD up to plant level is safe; joining MCHB in “for completeness” is how double-counts happen.

-- Current stock on hand by plant and storage location, split by stock status
-- ECC: MARD is persisted and current. S/4HANA: see the extraction section —
-- the physical MARD table no longer carries updated quantities; land MATDOC
-- and build balances (next section), or extract through the application layer.
SELECT
  m.werks                                   AS plant,
  m.lgort                                   AS storage_location,
  m.matnr                                   AS material,
  m.labst                                   AS unrestricted_qty,
  m.umlme                                   AS in_transfer_qty,
  m.insme                                   AS in_quality_inspection_qty,
  m.einme                                   AS restricted_batch_qty,
  m.speme                                   AS blocked_qty,
  m.retme                                   AS blocked_returns_qty,
  m.labst + m.umlme + m.insme
        + m.einme + m.speme + m.retme       AS on_hand_qty
FROM bronze_sap.mard AS m
WHERE m.mandt = '100'
  AND (m.labst <> 0 OR m.umlme <> 0 OR m.insme <> 0
    OR m.einme <> 0 OR m.speme <> 0 OR m.retme <> 0)
  -- AND m.lvorm = ''  -- exclude deletion-flagged records (config-specific)
ORDER BY plant, storage_location, material;

Table pages: MARD, MCHB, MSKA, MARC →

The table you want does not exist. MARD and MCHB are current-state — SAP overwrites them in place, and no history table keeps daily balances. (MBEWH keeps period-end values only, and only for valuation.) Historical stock is rebuilt from movements: anchor on a known balance, then accumulate signed material-document quantities backward or forward. This is not a workaround — it is how SAP’s own BW inventory model works (non-cumulative key figures: a reference point plus movements).

This is not an SAP quirk. No ERP in this library persists a daily inventory balance — all eight keep current state only, and most pair it with a movement ledger to integrate (NetSuite documents none, so it accumulates daily copies instead). The daily balance is always built downstream, never extracted. Inventory Snapshot Patterns covers that build for all eight references side by side.

The table set. ECC: MSEG (movement lines) + MKPF (header, which owns the posting date). S/4HANA: MATDOC alone — it is denormalized, so the posting date is on the line. In ECC, newer releases also carry BUDAT_MKPF denormalized onto MSEG; if your extract has it populated you can skip the MKPF join entirely.

The trap. Movement-based reconstruction assumes complete movement history. Archived material documents (common on systems more than a few years old) silently amputate the early history, and every balance computed before the archive horizon is wrong. Anchor on a physical snapshot you trust and only reconstruct within the retained window.

Grain. Movements are at material-document-line grain; the deliverable is a daily balance at material / plant / storage location / day. The grain conversion (transaction → periodic snapshot) happens exactly once, in gold. Putting the conversion in gold rather than silver is a layer-contract choice, not a law — the snapshot’s grain and calendar are business decisions, and the Medallion Layer Contracts guide defends why gold owns those.

-- Running stock balance at each movement date (runs on ECC MSEG or S/4HANA
-- MATDOC — on S/4 swap the FROM to bronze_sap.matdoc and budat_mkpf → budat).
-- SHKZG: S = debit (increase), H = credit (decrease).
-- Assumes movement history is complete for the window you reconstruct.
WITH movements AS (
  SELECT
    m.matnr,
    m.werks,
    m.lgort,
    to_date(m.budat_mkpf, 'yyyyMMdd')                        AS posting_date,
    sum(CASE m.shkzg WHEN 'S' THEN m.menge
                     WHEN 'H' THEN -m.menge END)             AS net_qty
  FROM bronze_sap.mseg AS m
  WHERE m.mandt = '100'
    AND m.budat_mkpf <> '00000000'
    AND m.budat_mkpf <> ''
    AND m.sobkz = ''   -- anonymous stock only; drop to include special stock (E/K/O)
    -- older ECC without BUDAT_MKPF populated: join MKPF on (mandt, mblnr,
    -- mjahr) and use mkpf.budat here instead
  GROUP BY m.matnr, m.werks, m.lgort, to_date(m.budat_mkpf, 'yyyyMMdd')
)
SELECT
  matnr                                     AS material,
  werks                                     AS plant,
  lgort                                     AS storage_location,
  posting_date,
  sum(net_qty) OVER (
    PARTITION BY matnr, werks, lgort
    ORDER BY posting_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                                         AS balance_qty
FROM movements
ORDER BY material, plant, storage_location, posting_date;

Table pages: MSEG, MKPF, MATDOC →

Table set. Movement analysis (receipts, issues, scrap, transfers) reads the same movement tables as the point-in-time rebuild — MSEG/MKPF on ECC, MATDOC on S/4HANA. The movement type (BWART) says why stock moved; the debit/credit indicator (SHKZG) says which direction. Direction from SHKZG is reliable on every movement type; leaning on memorized movement-type numbers is not — decode BWART against the movement-type configuration table T156 instead of hardcoding meanings. Goods-receipt and goods-issue families like 101/102 and 601/602 come in do/undo pairs — net the reversal partner or every corrected posting counts twice.

Grain. One row per material-document line. For “receipts by vendor by month” style questions, aggregate to the calendar grain — but keep the line-grain silver table, because every new question needs a different cut of the same lines.

-- Monthly movement summary by movement type, signed
-- Decode BWART via T156 rather than hardcoding type meanings.
-- On S/4 swap the FROM to bronze_sap.matdoc and budat_mkpf → budat.
SELECT
  date_trunc('month', to_date(m.budat_mkpf, 'yyyyMMdd'))     AS posting_month,
  m.werks                                                    AS plant,
  m.bwart                                                    AS movement_type,
  count(*)                                                   AS movement_lines,
  sum(CASE m.shkzg WHEN 'S' THEN m.menge
                   WHEN 'H' THEN -m.menge END)               AS net_qty
FROM bronze_sap.mseg AS m
WHERE m.mandt = '100'
  AND m.budat_mkpf <> '00000000'
  AND m.budat_mkpf <> ''
GROUP BY date_trunc('month', to_date(m.budat_mkpf, 'yyyyMMdd')), m.werks, m.bwart
ORDER BY posting_month, plant, movement_type;

Table pages: MSEG, MATDOC →

Table set. MBEW: one row per material / valuation area, carrying the moving-average price (VERPR), standard price (STPRS), total valuated quantity (LBKUM), and total value (SALK3). Two rules keep valuation honest: prefer SALK3 when you need current total value — SAP already computed it, and recomputing quantity × price re-derives it with your rounding instead of SAP’s; and never value historical quantities with current prices — MBEW is current-only, price history needs MBEWH (period-end snapshots) or a slowly-changing price dimension you maintain yourself.

The trap. LBKUM/SALK3 cover valuated stock. The reconciliation gap to your MARD-based on-hand number is usually special stock and non-valuated stock — if the two numbers must agree, define which one is the metric before someone asks why they differ.

Grain. Material / valuation area (plant, in the common configuration). This is a dimension-shaped table with measures on it — in the landing pattern it feeds a price/valuation dimension, not a fact.

Table page: MBEW →

• The S/4HANA stock-table trap — the one that breaks pipelines silently. In S/4HANA, the aggregated stock quantities are no longer persisted in MARD, MCHB, and their special-stock peers; quantities are calculated on the fly from MATDOC through NSDM proxy views, and the physical tables keep only master-data attributes (SAP Note 2206980, primary text). Replicate the physical MARD table from S/4 and the quantity columns are stale or empty — the pipeline runs green while the numbers freeze. On S/4: land MATDOC and derive balances, or extract through the application layer (ODP/CDS), which reads through the proxy views.
• On ECC all of these are ordinary transparent tables; direct replication works, including the persisted MARD/MCHB quantities.
• MATDOC is high-volume (one row per movement line, forever) and its primary key is a split GUID (KEY1–KEY6 in this reference), not MBLNR/MJAHR — merge/dedupe on the GUID, not the document number.
• Movement tables are effectively append-plus-reversal rather than update-in-place, which makes them friendlier to incremental loads than the delivery tables — but reversals mean late-arriving negatives: a day you already aggregated can change. Recompute a trailing window, not just “yesterday.”
• The ODP-RFC supported-use policy (SAP Note 3255746), its June 2026 technical enforcement (SAP Security Note 3748819), and SAP BDC Connect’s curated-data-products model are covered in the deliveries guide’s extraction section — the same constraints apply here.

• Bronze: MATDOC (or MSEG+MKPF) raw, all clients, CDC flags kept. MARD/MBEW raw as reference snapshots.
• Silver: one client. A conformed inventory_movement table at material-document-line grain: posting date resolved (header join or BUDAT_MKPF, decided once), quantities signed via SHKZG, zero-dates nulled, movement type decoded.
• Gold: two facts with different grains — fct_inventory_movement (transaction grain, serves movement analysis) and fct_inventory_balance_daily (periodic snapshot at material / plant / storage location / day, built from the movements — the Kimball periodic-snapshot pattern). The movements only produce a balance on days something moved, so the gap-free daily series comes from cross-joining a date spine and forward-filling the running balance, here in gold. Valuation joins in from a price dimension built off MBEW/MBEWH, not by embedding prices in the movement fact. These two serve on-hand, days-of-supply, turns, and slow-mover analysis without touching SAP-shaped tables again.