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 MCHBin "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. (MBEWHkeeps 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).

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.

-- 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 SALK3when 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.
• MATDOCis 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 restriction (SAP Note 3255746, enforced June 2026) 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.