SAP delivery data in the lakehouse: LIKP, LIPS, and what breaks

Table set. LIKP alone answers header-level on-time performance. You do not need LIPS unless you measure at line level, and you do not need VBFA — the dates that matter are already on the delivery header: WADAT (planned goods movement date), WADAT_IST (actual goods movement date), LFDAT (delivery date, the date the goods should arrive at the customer). Comparing WADAT_IST to WADATmeasures shipping-execution performance; comparing against the order's requested date (VBAK.VDATU, via the item-level order reference in how orders became deliveries) measures promise-keeping — different metric, different join, don't mix them.

The trap. SAP DATS columns are never NULL — an unfilled date is the string 00000000. A delivery that has not been goods-issued has WADAT_IST = '00000000', and a naive to_date() either fails or produces garbage epoch dates that quietly land in your fact table. Filter or NULL them at silver, once, for every DATS column you carry.

Grain. One row per delivery header. On-time-shipment is a header-level fact; computing it from LIPS then de-duplicating back up is extra work for the same answer.

-- On-time ship rate by month and shipping point
-- Grain: one row per delivery header (LIKP). On-time = actual goods issue
-- on or before the planned goods movement date.
-- Assumes DATS columns landed as 'yyyyMMdd' strings (typical raw extract);
-- drop the to_date() calls if your pipeline already casts them.
WITH shipped AS (
  SELECT
    vbeln,
    vstel                                  AS shipping_point,
    kunnr                                  AS ship_to,
    to_date(wadat,     'yyyyMMdd')         AS planned_gi_date,
    to_date(wadat_ist, 'yyyyMMdd')         AS actual_gi_date
  FROM bronze_sap.likp
  WHERE mandt = '100'                      -- always pin the client
    AND wadat_ist <> '00000000'            -- goods issue actually posted
    AND wadat     <> '00000000'  -- guard the planned date too
    -- AND lfart IN ('LF')                 -- restrict to your outbound delivery types (config-specific)
)
SELECT
  date_trunc('month', actual_gi_date)         AS ship_month,
  shipping_point,
  count(*)                                    AS deliveries_shipped,
  count_if(actual_gi_date <= planned_gi_date) AS shipped_on_time,
  round(100.0 * count_if(actual_gi_date <= planned_gi_date) / count(*), 1)
                                              AS otd_pct
FROM shipped
GROUP BY date_trunc('month', actual_gi_date), shipping_point
ORDER BY ship_month, shipping_point;

Table page: LIKP→

Table set — and the ECC/S/4 fork. This is the question where the system version changes the tables. In ECC, delivery statuses live in VBUK (header) and VBUP (item) — separate status tables keyed by VBELN, not in this reference's catalog but required there. In S/4HANA, VBUK and VBUP are gone for new documents: the status fields moved onto LIKP and LIPS themselves (delivered via the LIKP_STATUS include — WBSTK goods movement status, GBSTK overall status, FKSTK billing status, KOSTK picking status, and peers). If your extract pipeline was built on ECC and you migrate, the status join disappears — plan for it rather than discovering it.

The trap. "Open" has more than one meaning: not yet picked (KOSTK), not yet goods-issued (WBSTK), not yet billed (FKSTK). Pick the status that matches the business question; WBSTKis the shipping-backlog one. Status values run A (not yet processed) → B (partially) → C (completely).

Grain. Header for backlog counts; add LIPSonly when you need line detail — and don't sum LIPS.LFIMGacross materials, the quantities are in sales units and mixed units don't add.

-- Open delivery backlog with aging (S/4HANA)
-- Open = goods movement not complete on the header (WBSTK <> 'C').
-- ECC variant: these status fields live on VBUK, not LIKP — join
--   VBUK ON (mandt, vbeln) and read vbuk.wbstk instead.
SELECT
  l.vstel                                       AS shipping_point,
  CASE
    WHEN to_date(l.wadat, 'yyyyMMdd') >= current_date() THEN '0 — not yet due'
    WHEN datediff(current_date(), to_date(l.wadat, 'yyyyMMdd')) <= 7
                                                        THEN '1 — up to 7 days late'
    WHEN datediff(current_date(), to_date(l.wadat, 'yyyyMMdd')) <= 30
                                                        THEN '2 — 8 to 30 days late'
    ELSE                                                     '3 — over 30 days late'
  END                                           AS backlog_age,
  count(DISTINCT l.vbeln)                       AS open_deliveries,
  count(*)                                      AS open_delivery_lines
FROM bronze_sap.likp AS l
JOIN bronze_sap.lips AS i
  ON  i.mandt = l.mandt
  AND i.vbeln = l.vbeln
WHERE l.mandt = '100'
  AND l.wadat <> '00000000'
  AND l.wbstk <> 'C'
GROUP BY l.vstel, backlog_age
ORDER BY shipping_point, backlog_age;

Table pages: LIKP, LIPS→

Table set. LIPS carries its own order reference: VGBEL (preceding document) and VGPOS (preceding item) point at VBAK/VBAP. For order-to-delivery analysis you do not need VBFA— the document-flow table earns its place only when you traverse longer chains (order → delivery → invoice, returns) or need flows that LIPS doesn't carry. VBFA survives in S/4HANA but its key gained RUUID and the old SD index tables (VAKPA, VLKPA and peers) are gone.

The trap. Order-to-delivery is many-to-many at line level: one order line ships across multiple deliveries (splits), and one delivery can combine lines from several orders when shipping criteria match (order combination — standard SAP behavior, header-level order fields are therefore meaningless on combined deliveries). Joining VBAP to LIPS without aggregating first duplicates every split order line and inflates ordered quantity by the split count. Aggregate LIPS to the order-line grain before comparing.

Grain. The comparison grain is the sales order line (VBAP). Delivered quantity is an aggregate over LIPS, never a join-through column.

-- Order-line fill: ordered vs delivered, one row per open-or-shipped order line
-- LIPS hits the same order line once per delivery split — aggregate FIRST.
WITH delivered AS (
  SELECT
    mandt,
    vgbel                        AS order_vbeln,
    vgpos                        AS order_posnr,
    sum(lfimg)                   AS delivered_qty_su,   -- sales units, same unit as the order line
    count(DISTINCT vbeln)        AS delivery_count
  FROM bronze_sap.lips
  WHERE mandt = '100'
  GROUP BY mandt, vgbel, vgpos
)
SELECT
  o.vbeln                                        AS sales_order,
  o.posnr                                        AS order_item,
  o.matnr                                        AS material,
  o.kwmeng                                       AS ordered_qty_su,
  coalesce(d.delivered_qty_su, 0)                AS delivered_qty_su,
  coalesce(d.delivery_count, 0)                  AS deliveries,
  round(100.0 * coalesce(d.delivered_qty_su, 0) / o.kwmeng, 1)
                                                 AS line_fill_pct
FROM bronze_sap.vbap AS o
LEFT JOIN delivered AS d
  ON  d.mandt       = o.mandt
  AND d.order_vbeln = o.vbeln
  AND d.order_posnr = o.posnr
WHERE o.mandt  = '100'
  AND o.abgru  = ''              -- exclude rejected order lines
  AND o.kwmeng > 0
ORDER BY sales_order, order_item;

Table pages: LIPS, VBAK, VBAP →

Table set.Posting goods issue writes a material document. In ECC that's MKPF (header) and MSEG (items); MSEG rows carry the delivery reference in VBELN_IM / VBELP_IM. In S/4HANA the material document is one denormalized table, MATDOC — MKPF and MSEG still exist as names but are CDS compatibility views redirecting reads to MATDOC (writes no longer land there). A query against MSEG keeps working on S/4; the persisted rows live in MATDOC. Note the ECC caveat: VBELN_IM/VBELP_IM arrived via an enhancement note (1668550) — on older ECC systems without it those columns are empty and the delivery↔material-document path is VBFA.

The trap. Goods issues get reversed. Movement type 601 posts the issue for a delivery, 602 reverses it. Counting 601s without netting the 602s overstates shipments — net the two movement types against each other.

Grain. One row per material-document line. Net to the delivery-item grain before joining back to LIPS, or reversed shipments double your rows.

-- Net goods-issue quantity per delivery item (works on ECC and S/4HANA;
-- on S/4 the MSEG name is a compatibility view over MATDOC)
SELECT
  m.vbeln_im                       AS delivery,
  m.vbelp_im                       AS delivery_item,
  m.matnr                          AS material,
  m.meins                          AS base_unit,
  sum(CASE m.bwart WHEN '601' THEN m.menge
                   WHEN '602' THEN -m.menge END)
                                   AS net_issued_qty
FROM bronze_sap.mseg AS m
WHERE m.mandt     = '100'
  AND m.bwart    IN ('601', '602') -- delivery goods issue and its reversal
  AND m.vbeln_im <> ''             -- only movements referencing a delivery
GROUP BY m.vbeln_im, m.vbelp_im, m.matnr, m.meins
HAVING sum(CASE m.bwart WHEN '601' THEN m.menge
                        WHEN '602' THEN -m.menge END) <> 0
ORDER BY delivery, delivery_item;

Table pages: MKPF, MSEG, MATDOC→

• LIKP and LIPS are transparent tables in both ECC and S/4HANA — plain row-level replication works; no pool/cluster handling.
• Deliveries are not immutable: quantities, dates, and statuses change until (and sometimes after) goods issue, and a delivery can be deleted outright before goods issue, leaving no row behind. Full-snapshot loads hide this; a delta feed must carry updates and deletes, not just inserts.
• The extraction landscape shifted recently, and the mirror sites haven't caught up. SAP Business Data Cloud Connect for Databricks went GA in October 2025 (Databricks announcement: databricks.com/blog/announcing-general-availability-sap-business-data-cloud-connect-databricks): zero-copy, bi-directional Delta Sharing — but of curated SAP "data products," not raw tables. As of July 2026 there is no published data product at raw LIKP/LIPS grain; if your model needs delivery-line detail, you are still landing the tables yourself.
• If that landing runs on a third-party tool speaking ODP over RFC: SAP Note 3255746 starts technically blocking unauthorized ODP-RFC calls in June 2026, with a temporary opt-out through December 2026. ODP over OData and direct table/CDC paths are unaffected.
• SAP ships released CDS views over these tables — I_DeliveryDocument (LIKP) and I_DeliveryDocumentItem (LIPS/LIKP) — as stable, supported read interfaces on S/4HANA.

• Bronze: tables as extracted, all clients, DATS/TIMS as strings, CDC operation flags kept. No interpretation.
• Silver: one client. delivery_header (grain: VBELN) and delivery_item (grain: VBELN + POSNR). Zero-dates become real NULLs here, exactly once. If the source was ECC, the VBUK/VBUP status join happens here so gold never knows it existed.
• Gold: fct_delivery_line at delivery-item grain, with planned-GI / actual-GI / requested-delivery as role-playing date keys, ship-to and sold-to as separate customer keys (LIKP carries both — KUNNR is ship-to, KUNAG is sold-to; conflating them breaks any customer-level OTD cut). Header-only measures (total weight, package count) stay on a header fact or degenerate view — allocating them down to lines invents data. This fact serves OTD%, line fill, and backlog aging without further joins to SAP-shaped tables.