SCM POC — Data Dictionary & ER Diagram

Pipeline Overview

Six layers, each a Snowflake schema under database SCM_POC. Every layer below is documented with every table, every column, its data type, and its business meaning — verified live against INFORMATION_SCHEMA, not inferred from documentation that may be stale.

RAW tables

RAW_TRUSTED tables

STAGING tables

CURATED tables

AGGREGATE tables

SEMANTIC views

What each layer is for
RAW — one table per source extract, every column loaded as text, no transformation.
RAW_TRUSTED — same grain as RAW, but every value is type-cast (dates → DATE, measures → NUMBER/FLOAT) and blank/placeholder tokens are normalized to true NULL.
STAGING — one cleaned, business-rule-applied table per business concept (demand, delivery, purchasing, inventory, slow-moving, material master, planning parameters), with material/plant codes standardized to their canonical form.
CURATED — the conformed star schema: dimensions, bridges, and facts, with real primary/foreign keys, ready for direct business consumption.
AGGREGATE — pre-joined, pre-computed KPI tables built on top of CURATED, for reporting and fast query response.
SEMANTIC — the four Cortex Analyst semantic views (v2 generation) that the SCM_AGENT_WITH_RECS SCM Assistant Agent actually queries.

The one key that ties everything together
MATERIAL_12NC — Signify's 14-digit product code — is the conformed join key shared across every fact and dimension table from STAGING onward. PLANT_CODE (6-character SAP plant code) is the second universal key.

Scope of this document
Only the six schemas above. Legacy/deprecated schemas (SCM_SCHEMA, SCM_BRONZE, and the *_BACKUP_* clone schemas) are intentionally out of scope — they are not part of the live, current pipeline.

Note on RAW and RAW_TRUSTED depth: these two layers together carry roughly 2,750 columns, the great majority of which are unpopulated legacy SAP/BW fields (customer-extension ZZ* fields, unused industry-solution fields such as SAP Retail/Fashion/Healthcare add-ons, and internal SAP configuration codes with no reliable business meaning). To keep this document useful rather than exhaustive-for-its-own-sake, the RAW and RAW_TRUSTED sections document every table's purpose and every column that is actually populated and used downstream, and summarize the large unpopulated/cryptic tail compactly rather than listing each one. STAGING, CURATED, AGGREGATE, and SEMANTIC — the layers with real business logic — are documented in full, column by column.

ER Diagram

Two views: a table-level lineage flow across all six layers, and a detailed relational (star-schema) diagram of the CURATED layer, the layer with real primary/foreign key constraints.

1. Table-Level Flow Across the Six Layers

Every table in one layer is rebuilt from tables in the layer immediately to its left. No layer reads more than one hop upstream (e.g. AGGREGATE reads only CURATED, never STAGING directly). The two universal join keys — MATERIAL_12NC and PLANT_CODE — become standardized starting at STAGING and are used consistently through SEMANTIC.

RAW

Untyped text landing tables

DEMAND_QXP_AMS_CONS
DRM_AMS_CONS
PO_LT_DATA_US
…

→

RAW_TRUSTED

Typed 1:1 mirror of RAW

Same 12 tables,
typed + NULL-normalized

→

STAGING

Business-rule-applied, ID-standardized

STG_DEMAND_FORECAST
STG_DRM · STG_PO
STG_INVENTORY · …

→

CURATED

Conformed star schema

8 dims/bridges
15 facts/derived tables

→

AGGREGATE

Pre-joined KPI tables

Forecast, delivery,
vendor, risk scorecards

→

SEMANTIC

Cortex Analyst views (v2)

Purchasing · Delivery
Demand Planning · Inventory

2. CURATED Layer — Relational (Star Schema) Diagram

CURATED is the only layer with enforced primary/foreign-key relationships (declared as NOT ENFORCED constraints — Snowflake records the relationship for documentation and query-optimization purposes but does not reject violating rows at write time). Dimensions and bridges below; every fact/derived table's foreign keys are shown as chips, with the full relationship list in the table beneath for precise reference.

DimensionBridgeForeign key reference

DIM_MATERIAL

PK: MATERIAL_12NC

DIM_PLANT

PK: PLANT_CODE

DIM_SUPPLIER_SOURCE

PK: SUPPLIER_SK

DIM_CUSTOMER

PK: CUSTOMER_ID

DIM_BUSINESS_UNIT

PK: BU_ID (BG×BU×MAG)

DIM_BUSINESS_UNIT_BYCODE

Key: BU_CODE (view over DIM_BUSINESS_UNIT, unique per BU)

DIM_DELAY_REASON

PK: REASON_CODE

DIM_FISCAL_PERIOD

PK: FISCAL_PERIOD_CODE

BRIDGE_MATERIAL_PLANT

PK: (MATERIAL_12NC, PLANT_CODE)

BRIDGE_SUPPLIER_MATERIAL

PK: (SUPPLIER_SK, MATERIAL_12NC, PLANT_CODE)

BRIDGE_SUPPLIER_ENTERPRISE_MAPPING

Maps source-specific SUPPLIER_SK to an enterprise supplier ID

FACT_INVENTORY

PK: INVENTORY_SK

→ DIM_MATERIAL→ DIM_PLANT→ DIM_FISCAL_PERIOD

FACT_DEMAND_FORECAST

PK: DEMAND_SK

→ DIM_MATERIAL→ DIM_PLANT→ DIM_FISCAL_PERIOD

FACT_FORECAST_PERFORMANCE

PK: FCST_PERF_SK

→ DIM_MATERIAL→ DIM_FISCAL_PERIOD

FACT_PURCHASE_ORDER_LINE

PK: PO_SK

→ DIM_MATERIAL→ DIM_PLANT→ DIM_SUPPLIER_SOURCE

FACT_PO_SCHEDULE

PK: PO_SCHEDULE_SK

→ FACT_PURCHASE_ORDER_LINE→ DIM_MATERIAL→ DIM_PLANT→ DIM_SUPPLIER_SOURCE

FACT_PO_CONFIRMATION

PK: PO_CONFIRM_SK

→ FACT_PURCHASE_ORDER_LINE→ DIM_MATERIAL→ DIM_PLANT→ DIM_SUPPLIER_SOURCE

FACT_GOODS_MOVEMENT

PK: GOODS_MOVEMENT_SK

→ FACT_PURCHASE_ORDER_LINE→ DIM_MATERIAL→ DIM_PLANT

FACT_INBOUND_SUPPLY_EVENT

PK: INBOUND_EVENT_SK

→ FACT_PURCHASE_ORDER_LINE→ DIM_MATERIAL→ DIM_PLANT→ DIM_SUPPLIER_SOURCE

FACT_SALES_ORDER_LINE

PK: SO_SK

→ DIM_MATERIAL→ DIM_PLANT→ DIM_CUSTOMER→ DIM_BUSINESS_UNIT→ DIM_BUSINESS_UNIT_BYCODE

FACT_DELIVERY

PK: DELIVERY_SK

→ FACT_SALES_ORDER_LINE→ DIM_MATERIAL→ DIM_PLANT→ DIM_CUSTOMER→ DIM_BUSINESS_UNIT→ DIM_BUSINESS_UNIT_BYCODE

FACT_SLOW_MOVING_INVENTORY

PK: SMI_SK

→ DIM_MATERIAL→ DIM_PLANT→ DIM_FISCAL_PERIOD→ DIM_BUSINESS_UNIT→ DIM_BUSINESS_UNIT_BYCODE

SUPPLY_POSITION_BY_DATE

PK: (MATERIAL_12NC, PLANT_CODE, POSITION_DATE)

→ DIM_MATERIAL→ DIM_PLANT

SUPPLY_ORDER_IMPACT

PK: SO_SK

→ FACT_SALES_ORDER_LINE→ DIM_MATERIAL→ DIM_PLANT→ DIM_CUSTOMER→ DIM_BUSINESS_UNIT_BYCODE→ DIM_DELAY_REASON

Not shown above: DIM_FISCAL_PERIOD, DIM_CUSTOMER, and the bridges are dimension-side reference tables with no outbound foreign keys of their own. FACT_INVENTORY is not directly linked to FACT_DEMAND_FORECAST or FACT_PURCHASE_ORDER_LINE in CURATED — those cross-domain joins happen downstream in AGGREGATE (e.g. AGG_SUPPLY_HEALTH_MATERIAL_PLANT_MONTHLY), not here.

Full Foreign-Key Reference (CURATED)

Table	FK Column(s)	References	Notes
`BRIDGE_MATERIAL_PLANT`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`BRIDGE_MATERIAL_PLANT`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE
`BRIDGE_SUPPLIER_MATERIAL`	`SUPPLIER_SK`	`DIM_SUPPLIER_SOURCE`	SUPPLIER_SK
`BRIDGE_SUPPLIER_MATERIAL`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`BRIDGE_SUPPLIER_ENTERPRISE_MAPPING`	`SUPPLIER_SK`	`DIM_SUPPLIER_SOURCE`	SUPPLIER_SK
`FACT_INVENTORY`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`FACT_INVENTORY`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE
`FACT_INVENTORY`	`FISCAL_PERIOD_CODE`	`DIM_FISCAL_PERIOD`	FISCAL_PERIOD_CODE
`FACT_DEMAND_FORECAST`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC (may be a planning aggregate key — check IS_AGGREGATE_KEY)
`FACT_DEMAND_FORECAST`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE
`FACT_DEMAND_FORECAST`	`CALENDAR_MONTH_KEY / FISCAL_PERIOD_CODE`	`DIM_FISCAL_PERIOD`	CALENDAR_MONTH_KEY / FISCAL_PERIOD_CODE
`FACT_FORECAST_PERFORMANCE`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`FACT_FORECAST_PERFORMANCE`	`FISCAL_PERIOD_CODE`	`DIM_FISCAL_PERIOD`	FISCAL_PERIOD_CODE
`FACT_PURCHASE_ORDER_LINE`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`FACT_PURCHASE_ORDER_LINE`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE (receiving plant)
`FACT_PURCHASE_ORDER_LINE`	`SUPPLIER_SK`	`DIM_SUPPLIER_SOURCE`	SUPPLIER_SK (DC vendor — the actual PO counterparty)
`FACT_PO_SCHEDULE`	`PO_SK`	`FACT_PURCHASE_ORDER_LINE`	PO_SK
`FACT_PO_SCHEDULE`	`MATERIAL_12NC / PLANT_CODE / SUPPLIER_SK`	`DIM_MATERIAL / DIM_PLANT / DIM_SUPPLIER_SOURCE`	resolved via FACT_PURCHASE_ORDER_LINE (EKET carries no material column itself)
`FACT_PO_CONFIRMATION`	`PO_SK (implied)`	`FACT_PURCHASE_ORDER_LINE`	PO_SK
`FACT_PO_CONFIRMATION`	`MATERIAL_12NC / PLANT_CODE / SUPPLIER_SK`	`DIM_MATERIAL / DIM_PLANT / DIM_SUPPLIER_SOURCE`	resolved via FACT_PURCHASE_ORDER_LINE
`FACT_GOODS_MOVEMENT`	`PO_SK`	`FACT_PURCHASE_ORDER_LINE`	PO_SK
`FACT_GOODS_MOVEMENT`	`MATERIAL_12NC / PLANT_CODE`	`DIM_MATERIAL / DIM_PLANT`	resolved via FACT_PURCHASE_ORDER_LINE
`FACT_INBOUND_SUPPLY_EVENT`	`MATERIAL_12NC / PLANT_CODE / SUPPLIER_SK`	`DIM_MATERIAL / DIM_PLANT / DIM_SUPPLIER_SOURCE`	resolved via FACT_PURCHASE_ORDER_LINE
`FACT_SALES_ORDER_LINE`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`FACT_SALES_ORDER_LINE`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE (fulfilling plant)
`FACT_SALES_ORDER_LINE`	`CUSTOMER_ID`	`DIM_CUSTOMER`	CUSTOMER_ID (ship-to)
`FACT_SALES_ORDER_LINE`	`SOLD_TO_ID`	`DIM_CUSTOMER`	CUSTOMER_ID (sold-to / billing party)
`FACT_SALES_ORDER_LINE`	`BU_CODE / BG_KEY`	`DIM_BUSINESS_UNIT_BYCODE / DIM_BUSINESS_UNIT`	BU_CODE / BG_KEY
`FACT_DELIVERY`	`SO_SK`	`FACT_SALES_ORDER_LINE`	SO_SK
`FACT_DELIVERY`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`FACT_DELIVERY`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE (fulfilling plant)
`FACT_DELIVERY`	`CUSTOMER_ID`	`DIM_CUSTOMER`	CUSTOMER_ID (ship-to — physical recipient)
`FACT_DELIVERY`	`SOLD_TO_ID`	`DIM_CUSTOMER`	CUSTOMER_ID (sold-to — ordering/billing party)
`FACT_DELIVERY`	`BU_CODE / BG_KEY`	`DIM_BUSINESS_UNIT_BYCODE / DIM_BUSINESS_UNIT`	BU_CODE / BG_KEY
`FACT_SLOW_MOVING_INVENTORY`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`FACT_SLOW_MOVING_INVENTORY`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE
`FACT_SLOW_MOVING_INVENTORY`	`FISCAL_PERIOD_CODE`	`DIM_FISCAL_PERIOD`	FISCAL_PERIOD_CODE
`FACT_SLOW_MOVING_INVENTORY`	`BG_CODE / BU_CODE`	`DIM_BUSINESS_UNIT / DIM_BUSINESS_UNIT_BYCODE`	BG_KEY / BU_CODE
`SUPPLY_POSITION_BY_DATE`	`MATERIAL_12NC`	`DIM_MATERIAL`	MATERIAL_12NC
`SUPPLY_POSITION_BY_DATE`	`PLANT_CODE`	`DIM_PLANT`	PLANT_CODE
`SUPPLY_ORDER_IMPACT`	`SO_SK`	`FACT_SALES_ORDER_LINE`	SO_SK
`SUPPLY_ORDER_IMPACT`	`MATERIAL_12NC / PLANT_CODE`	`DIM_MATERIAL / DIM_PLANT`	MATERIAL_12NC / PLANT_CODE
`SUPPLY_ORDER_IMPACT`	`CUSTOMER_ID`	`DIM_CUSTOMER`	CUSTOMER_ID
`SUPPLY_ORDER_IMPACT`	`BU_CODE`	`DIM_BUSINESS_UNIT_BYCODE`	BU_CODE
`SUPPLY_ORDER_IMPACT`	`PRIMARY_DELAY_REASON`	`DIM_DELAY_REASON`	REASON_CODE (NULL for on-time deliveries)

RAWRaw Landing Tables

One table per source extract. Every column is loaded as text with no transformation — this layer exists purely to land the source data untouched before any cleansing happens.

DEMAND_QXP_AMS_CONS — 47 columns (all TEXT), 105,333 rows

Row count (live): 105,333
Total columns: 47 (all TEXT, no transformation applied)

The initial full-width landing tier for the monthly demand-planning (QxP) key-figure extract: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

SALES_AMS_CONS_VIPP — 46 columns (all TEXT), 243,827 rows

Row count (live): 243,827
Total columns: 46 (all TEXT, no transformation applied)

The initial full-width landing tier for the monthly sales-actuals key-figure extract: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

DRM_AMS_CONS — 233 columns (all TEXT), 756,429 rows

Row count (live): 756,429
Total columns: 233 (all TEXT, no transformation applied)

The initial full-width landing tier for the Delivery Reliability Measure (DRM) extract: every source column preserved as text, no transformation applied. The largest RAW table by both row and column count. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

EKBE_DATA_PO — 90 columns (all TEXT), 38,520 rows

Row count (live): 38,520
Total columns: 90 (all TEXT, no transformation applied)

The initial full-width landing tier for the SAP EKBE goods-movement history: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

EKES_DATA_PO — 35 columns (all TEXT), 20,725 rows

Row count (live): 20,725
Total columns: 35 (all TEXT, no transformation applied)

The initial full-width landing tier for the SAP EKES vendor order confirmations: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

EKET_DATA_PO — 78 columns (all TEXT), 12,073 rows

Row count (live): 12,073
Total columns: 78 (all TEXT, no transformation applied)

The initial full-width landing tier for the SAP EKET purchase-order schedule lines: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

FC_BIAS_FACC — 88 columns (all TEXT), 66,121 rows

Row count (live): 66,121
Total columns: 88 (all TEXT, no transformation applied)

The initial full-width landing tier for the forecast-bias snapshot extract: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

INVENTORY_AMS_CONS — 72 columns (all TEXT), 2,072,627 rows

Row count (live): 2,072,627
Total columns: 72 (all TEXT, no transformation applied)

The initial full-width landing tier for the batch-grain inventory snapshot: every source column preserved as text, no transformation applied. The largest RAW table by row count. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

MARC_AMS_CONSUMER — 277 columns (all TEXT), 56,060 rows

Row count (live): 56,060
Total columns: 277 (all TEXT, no transformation applied)

The initial full-width landing tier for the SAP MARC material-planning master: every source column preserved as text, no transformation applied. The widest RAW table by column count. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

PO_LT_DATA_US — 131 columns (all TEXT), 12,072 rows

Row count (live): 12,072
Total columns: 131 (all TEXT, no transformation applied)

The initial full-width landing tier for the purchase-order lead-time bridge extract: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

SLOWMO_AMS_CONS — 99 columns (all TEXT), 124,287 rows

Row count (live): 124,287
Total columns: 99 (all TEXT, no transformation applied)

The initial full-width landing tier for the slow-moving/dead-stock inventory extract: every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

T_MD_MATERIAL_CONSUMER — 180 columns (all TEXT), 536,076 rows

Row count (live): 536,076
Total columns: 180 (all TEXT, no transformation applied)

The initial full-width landing tier for the SAP BW material master (BIC_* enriched attributes): every source column preserved as text, no transformation applied. See the RAW_TRUSTED section for the typed, documented version of this table's meaningful columns.

RAW_TRUSTEDTyped & Cleaned Landing Tables

Same grain as RAW, one table per source extract, but every value is type-cast to its real data type and blank/placeholder tokens are normalized to true NULL. No business logic is applied yet.

DEMAND_QXP_AMS_CONS — 20 of 47 columns shown, 105,333 rows

Row count (live): 105,333
Total columns: 47

Monthly demand-planning (QxP) key-figure extract — one row per material/plant/period/key-figure/category slice. Unioned with SALES_AMS_CONS_VIPP into STAGING.STG_DEMAND_FORECAST as the pipeline's forecast-vs-actual base.

Column	Type	Business Meaning
`_12NC`	VARCHAR	Material 12NC, already canonical in this table — join key to DIM_MATERIAL.
`PLANT_NB`	VARCHAR	6-char canonical plant code — join key to DIM_PLANT.
`FISCAL_PERIOD`	DATE	Fiscal period, converted downstream to a YYYY-MM-01 calendar month key.
`KF`	VARCHAR	Key-figure code (e.g. ACT_CY, QxP EUR) — classified downstream into QUANTITY vs VALUE_EUR.
`CATEGORY`	VARCHAR	Distinguishes current-year vs prior-year sales slices — the ACT_CY/ACT_CY-1 labels live here, not in KF.
`VALUE`	NUMBER	The measure amount — routed to forecast_quantity or forecast_value_eur based on KF/CURRENCY.
`CURRENCY`	VARCHAR	Populated only for monetary key figures; blank implies a quantity measure.
`DISTRIBUTION_CHANNEL`	VARCHAR	2-char channel code; STAGING keeps only '01' (Consumer) per client scope.
`FIN_POST_IND`	VARCHAR	Finance posting indicator; STAGING keeps only '#' (default/no-allocation) per client scope.
`COMMERCIAL_SUB_TEAM`	VARCHAR	Genuinely-distinct source slice, included in STAGING's dedup partition key.
`PGORU`	VARCHAR	ORU org key.
`PGMRU`	VARCHAR	MRU org key.
`BG`	VARCHAR	Business group code, raw 'BU0118'-style prefix, cleaned to numeric bg_code downstream.
`BU`	VARCHAR	Business unit code, same cleanup as BG.
`CHANNEL_GROUP`	VARCHAR	Channel grouping attribute.
`APO_PLANN_ACC_NB`	VARCHAR	Planning account — join key to DRM's APO_PLANNING_ACCOUNT.
`MRP_CONTROLLER`	VARCHAR	Reporting-source MRP controller code.
`FIRST_IN_CHAIN_MANUFACTURER_KEY`	VARCHAR	Manufacturer key, passed through to STAGING.
`PRODUCT_LIFE_CYCLE_PHASE`	VARCHAR	Lifecycle phase attribute.
`LAST_UPDATE_DTTM`	TIMESTAMP	Source revision timestamp — keeps only the latest revision per business key.

The remaining ~27 columns are largely unused legacy SAP/BW fields (secondary hierarchy codes, alternate UOM/currency conversion fields, internal APO routing flags) not consumed by any downstream transformation. FIRST_IN_CHAIN_MANUFACTURER_NAME, ATO_ETO, SUPPLY_CLASS and SEGMENT are also passed through to STAGING but omitted above for brevity.

SALES_AMS_CONS_VIPP — 20 of 46 columns shown, 243,827 rows

Row count (live): 243,827
Total columns: 46

Monthly sales-actuals key-figure extract, structurally near-identical to DEMAND_QXP_AMS_CONS (46 vs 47 columns — one fewer legacy field). Fully unioned into the same STG_DEMAND_FORECAST target as the actuals half of the forecast-vs-actual base.

Column	Type	Business Meaning
`_12NC`	VARCHAR	Material 12NC, already canonical — join key to DIM_MATERIAL.
`PLANT_NB`	VARCHAR	6-char canonical plant code — join key to DIM_PLANT.
`FISCAL_PERIOD`	DATE	Fiscal period, converted downstream to a YYYY-MM-01 calendar month key.
`KF`	VARCHAR	Key-figure code — classified downstream into QUANTITY vs VALUE_EUR, same rules as DEMAND_QXP.
`CATEGORY`	VARCHAR	Distinguishes current-year vs prior-year sales slices.
`VALUE`	NUMBER	The measure amount — routed to forecast_quantity or forecast_value_eur.
`CURRENCY`	VARCHAR	Populated only for monetary key figures.
`DISTRIBUTION_CHANNEL`	VARCHAR	2-char channel code; STAGING keeps only '01' (Consumer).
`FIN_POST_IND`	VARCHAR	Finance posting indicator; STAGING keeps only '#'.
`COMMERCIAL_SUB_TEAM`	VARCHAR	Genuinely-distinct source slice, included in STAGING's dedup partition key.
`PGORU`	VARCHAR	ORU org key.
`PGMRU`	VARCHAR	MRU org key.
`BG`	VARCHAR	Business group code — carries the same raw-prefix conformance issue as DEMAND_QXP.BG.
`BU`	VARCHAR	Business unit code, same cleanup as BG.
`CHANNEL_GROUP`	VARCHAR	Channel grouping attribute.
`APO_PLANN_ACC_NB`	VARCHAR	Planning account.
`MRP_CONTROLLER`	VARCHAR	Reporting-source MRP controller code.
`PRODUCT_LIFE_CYCLE_PHASE`	VARCHAR	Lifecycle phase attribute.
`SUPPLY_CLASS`	VARCHAR	Supply classification attribute.
`LAST_UPDATE_DTTM`	TIMESTAMP	Source revision timestamp — keeps only the latest revision per business key (2,978 groups have multiple revisions live).

The remaining ~26 columns are unused legacy SAP/BW fields, the same categories as DEMAND_QXP_AMS_CONS's unused remainder (secondary hierarchy codes, alternate UOM/currency fields, internal routing flags).

DRM_AMS_CONS — 21 of 233 columns shown, 756,429 rows

Row count (live): 756,429
Total columns: 233

Delivery Reliability Measure (DRM) extract — one row per sales-order/delivery line, carrying on-time/complete-delivery scoring, root-cause miss flags, and dates across the supply→sales→outbound chain. The largest table by row count and the most heavily consumed by STAGING.STG_DRM.

Column	Type	Business Meaning
`MATERIAL`	VARCHAR	Material 12NC, already canonical — join key to DIM_MATERIAL.
`PLANT`	VARCHAR	6-char canonical plant code.
`DOC_NUMBER`	VARCHAR	Sales order number.
`S_ORD_ITEM`	VARCHAR	Sales order item.
`DELIV_NUMB`	VARCHAR	Delivery number.
`DELIV_ITEM`	VARCHAR	Delivery item.
`MRP_CONTRL`	VARCHAR	Reporting-source MRP controller code.
`SHIP_TO`	VARCHAR	Ship-to party ID.
`SOLD_TO`	VARCHAR	Sold-to party ID.
`EXTERNAL_SUPPLIER`	VARCHAR	Supplier ID (LIFNR or other format).
`TGT_AGREED_DP_DATE`	DATE	Agreed delivery-promise date — anchors on-time scoring and overdue detection.
`ACTUAL_DELIVERY_DATE`	DATE	Actual delivery date; NULL + an already-passed agreed date is scored as a miss, not excluded.
`ACTUAL_GOODS_ISSUE_DATE`	DATE	Actual goods-issue date.
`ACTUAL_QTY_DE_SIUM`	NUMBER	Actual delivered quantity.
`AGREED_DEL_PROMISE_ORDER_LINE_QTY_SIUM`	NUMBER	Agreed order-line quantity — paired with actual for the completeness check.
`DRM_TOTAL_LINES`	NUMBER	Eligibility counter feeding the DRM miss-rate denominator.
`DRM_SCORED_LINES`	NUMBER	Successful-outcome counter feeding the DRM miss-rate numerator.
`ITEM_CATEG`	VARCHAR	Item category; only '10ZE01' (standard delivery, ~96% of rows) enters DRM/OTIF scoring.
`ADP_DAYS_DEV`	VARCHAR	Delay in days vs the agreed date; literal '?' marks an unmeasurable line (excluded from scoring).
`REASON_FOR_REJECTION`	VARCHAR	Rejection/cancellation reason code — excludes the line from the DRM denominator when populated.
`COMPL_DEL`	VARCHAR	Delivery-completeness requirement indicator (a configuration flag, not proof of actual fulfillment).

Roughly 45 further MISSED_* granular miss-tier flag columns (supply/sales/outbound block reasons) and their matching *_TXT description fields are also consumed by STAGING, but are rolled up there into two derived fields (primary_delay_reason, delay_reason_group) rather than documented individually here. The remaining ~165 columns are unused legacy SAP BW fields (secondary routing/hierarchy codes, alternate date stamps, internal scoring diagnostics).

EKBE_DATA_PO — 11 of 90 columns shown, 38,520 rows

Row count (live): 38,520
Total columns: 90

Goods-movement history (SAP EKBE) — one row per PO/item/movement posting. Deduplicated and aggregated in STAGING to net received quantity and last goods-receipt date per PO line.

Column	Type	Business Meaning
`EBELN`	VARCHAR	PO number — join key.
`EBELP`	VARCHAR	PO item — join key.
`BELNR`	VARCHAR	Material document number, part of the dedup key.
`GJAHR`	VARCHAR	Material document fiscal year, part of the dedup key.
`BUZEI`	VARCHAR	Document line item, part of the dedup key.
`VGABE`	VARCHAR	Transaction/event type, part of the dedup key.
`BWART`	VARCHAR	Movement type — filtered to 101/102/107/108/161/162 (goods receipt, reversals, returns).
`SHKZG`	VARCHAR	Debit/credit indicator (S=receipt, H=return) — determines the sign of received_qty_net.
`MENGE`	NUMBER	Movement quantity — summed (signed by SHKZG) into received_qty_net.
`BUDAT`	DATE	Posting date — MAX() gives last_gr_date.
`GLCHANGETIME`	TIMESTAMP	Last-change timestamp — the dedup tiebreak (latest change wins).

The remaining 79 columns are unused legacy SAP MM document fields (accounting document line detail, tax codes, internal clearing references) not consumed by any downstream transformation.

EKES_DATA_PO — 7 of 35 columns shown, 20,725 rows

Row count (live): 20,725
Total columns: 35

Vendor order confirmations (SAP EKES) — vendor-confirmed delivery dates for scheduling-agreement POs. Deduplicated and aggregated in STAGING to earliest open-quantity confirmed date and total confirmed quantity per PO line.

Column	Type	Business Meaning
`EBELN`	VARCHAR	PO number — join key (matches the DC-PO number, not the EP-PO number, 100% live).
`EBELP`	VARCHAR	PO item — join key.
`ETENS`	VARCHAR	Confirmation sequence number, part of the dedup key.
`MENGE`	NUMBER	Confirmed quantity.
`DABMG`	NUMBER	Quantity already delivered against this confirmation — MENGE-DABMG>0 identifies still-open confirmations.
`EINDT`	DATE	Confirmed delivery date — MIN() over open confirmations gives earliest_confirmed_date.
`GLCHANGETIME`	TIMESTAMP	Last-change timestamp — the dedup tiebreak.

The remaining 28 columns are unused legacy SAP EKES fields (confirmation category codes, control indicators, alternate quantity units).

EKET_DATA_PO — 7 of 78 columns shown, 12,073 rows

Row count (live): 12,073
Total columns: 78

Purchase-order schedule lines (SAP EKET) — planned delivery schedule for standard POs. Deduplicated and aggregated in STAGING to earliest open-quantity schedule date and total scheduled quantity per PO line.

Column	Type	Business Meaning
`EBELN`	VARCHAR	PO number — join key.
`EBELP`	VARCHAR	PO item — join key.
`ETENR`	VARCHAR	Schedule line number, part of the dedup key.
`MENGE`	NUMBER	Scheduled quantity — summed into scheduled_qty.
`WEMNG`	NUMBER	Quantity already received against this schedule line — MENGE-WEMNG>0 identifies still-open lines.
`EINDT`	DATE	Scheduled delivery date — MIN() over open lines gives earliest_schedule_date.
`GLCHANGETIME`	TIMESTAMP	Last-change timestamp — the dedup tiebreak.

The remaining 71 columns are unused legacy SAP EKET fields (confirmation control keys, statistics-relevant flags, alternate date types).

FC_BIAS_FACC — 19 of 88 columns shown, 66,121 rows

Row count (live): 66,121
Total columns: 88

Forecast-bias snapshot — planned quantity/QxP and absolute-deviation figures captured at four snapshot horizons (N-3 through N) per material/planning-account/distribution-channel/fiscal-period. Only the Consumer channel ('1001') is in scope for STAGING.STG_FORECAST_BIAS.

Column	Type	Business Meaning
`MATERIAL`	VARCHAR	Material 12NC.
`APO_PLANN_ACCCODE`	VARCHAR	Planning account.
`DISTRIBUTION_CHANNEL`	VARCHAR	4-digit form ('1001', unlike DEMAND/SALES' 2-char '01'); STAGING filters to Consumer only.
`DISTR_CHANNEL_TXT`	VARCHAR	Channel description.
`APO_SCM_AREA_CODE_MX`	VARCHAR	Country-level SCM area code — cannot join to plant-grain tables directly.
`FISCPER`	VARCHAR	YYYYPPP fiscal period, resolved to calendar month via DIM_FISCAL_PERIOD.
`BG`	VARCHAR	Business group code.
`BU`	VARCHAR	Business unit code.
`MAG`	VARCHAR	MAG code.
`CAG`	VARCHAR	CAG code.
`CF_PLANNED_QTY_SNAPSHOT_N3`	NUMBER	Planned quantity captured 3 periods before the reporting period.
`CF_PLANNED_QTY_SNAPSHOT_N`	NUMBER	Planned quantity captured at the reporting period itself.
`ACTUAL_DP_DEL_QTY_CORRECTED`	NUMBER	Actual delivered quantity.
`ABS_DEV_N_3_CALCVALUE`	NUMBER	Absolute deviation at the N-3 snapshot horizon.
`ABS_DEV_N_0_CALCVALUE`	NUMBER	Absolute deviation at the N (current) snapshot horizon.
`CF_PLANNED_QXP_SNAPSHOT_N3`	NUMBER	Planned QxP (EUR) at the N-3 snapshot horizon.
`CF_PLANNED_QXP_SNAPSHOT_N`	NUMBER	Planned QxP (EUR) at the N snapshot horizon.
`CF_ABS_DEV_SNAPSHOT_N3`	NUMBER	CF absolute deviation at the N-3 snapshot horizon.
`CF_ABS_DEV_SNAPSHOT_N`	NUMBER	CF absolute deviation at the N snapshot horizon.

The N-2/N-1 snapshot variants of each of the four planned-qty/QxP/deviation families follow the identical pattern shown above for N-3/N (12 more columns, all consumed the same way). The remaining ~57 columns are unused legacy forecast-bias diagnostic fields.

INVENTORY_AMS_CONS — 13 of 72 columns shown, 2,072,627 rows

Row count (live): 2,072,627
Total columns: 72

Batch-grain inventory snapshot — one row per material/plant/storage-location/stock-type/batch/period. The largest table by row count. STAGING aggregates away the batch dimension into STG_INVENTORY at material+plant+storage_location+stock_type+period grain.

Column	Type	Business Meaning
`MATERIAL`	VARCHAR	Material 12NC.
`PLANT`	VARCHAR	Plant code.
`STORAGE_LOCATION`	VARCHAR	Storage location — kept, not collapsed, per capability-map requirement.
`STOCK_TYPE`	VARCHAR	SAP stock type code (A/Q/D/other) — decoded downstream to Unrestricted/Quality inspection/Blocked/Other.
`FISCALYEAR_PERIOD`	VARCHAR	YYYYPPP fiscal period, resolved to calendar month via DIM_FISCAL_PERIOD.
`TOTAL_STOCK_IM_QTY_BUOM`	NUMBER	On-hand quantity, base UOM — use this, not the _SU sibling (unit mismatch overstated supply by ~9.9M units live).
`GIT_GL_QTY_BUOM`	NUMBER	Goods-in-transit quantity, base UOM.
`TOTAL_STOCK_QUANTITY_IM_GIT_BUOM`	NUMBER	On-hand + GIT combined quantity, base UOM.
`TOTAL_STOCK_IM_VAL_RC`	NUMBER	On-hand value, reporting currency.
`GIT_VAL_RC`	NUMBER	GIT value, reporting currency.
`TOTAL_STOCK_VAL_IM_GIT_RC`	NUMBER	Combined on-hand + GIT value, reporting currency.
`MATL_TYPE`	VARCHAR	Material type — '10MANE' (finished goods) gates the is_available_stock scope flag.
`MRP_CONTROLLER`	VARCHAR	Reporting-source MRP controller code.

The remaining ~59 columns are largely the _SU (single-UOM) quantity siblings deliberately not used downstream (unit-mismatch risk, see the on-hand-quantity note above) plus unused legacy batch/valuation-class attributes.

MARC_AMS_CONSUMER — 20 of 277 columns shown, 56,060 rows

Row count (live): 56,060
Total columns: 277

SAP MARC material-planning master — one row per material/plant, MRP parameters, lot sizing, and procurement attributes. The widest table by column count. Normalized to canonical 12NC/6-char plant in STAGING.STG_MATERIAL_PLANNING.

Column	Type	Business Meaning
`MATNR`	VARCHAR	18-char SAP material number — stripped of leading zeros and prefixed '10' to canonical 12NC (applies to all forms, not just 18-char numeric).
`PLANT_NB`	VARCHAR	6-char plant code, preferred over WERKS when populated.
`WERKS`	VARCHAR	4-char plant code, fallback when PLANT_NB is blank (prefixed '10' downstream).
`DISMM`	VARCHAR	MRP type.
`DISPO`	VARCHAR	MRP controller code, prefixed '10' downstream.
`PLIFZ`	NUMBER	Planned delivery days.
`WEBAZ`	NUMBER	Goods-receipt processing days.
`EISBE`	NUMBER	Safety stock.
`MINBE`	NUMBER	Reorder point.
`MOQ`	NUMBER	Minimum order quantity — a genuine quantity (median 100, max 113,400), unlike SLOWMO.MOQ.
`BSTMI`	NUMBER	Minimum order quantity (lot-size constraint).
`BSTMA`	NUMBER	Maximum order quantity (lot-size constraint).
`BESKZ`	VARCHAR	Procurement type.
`MMSTA`	VARCHAR	Material status.
`EKGRP`	VARCHAR	Purchasing group.
`WZEIT`	NUMBER	Total replenishment lead-time days.
`DISLS`	VARCHAR	Lot-sizing procedure code — the true procedure domain (16 categorical codes: WB, EX, X2, X4...).
`LOSGR`	NUMBER	Costing lot size — a numeric value, distinct from and easily confused with DISLS.
`APOKZ`	VARCHAR	APO-relevant flag.
`MAABC`	VARCHAR	ABC classification.

The remaining ~257 columns are largely sparse/unused legacy SAP MRP fields (alternate planning strategy groups, forecast profile codes, secondary UOM conversions, plant-specific storage parameters) not consumed by any downstream transformation.

PO_LT_DATA_US — 21 of 131 columns shown, 12,072 rows

Row count (live): 12,072
Total columns: 131

Purchase-order lead-time bridge — one row per DC-PO/item, resolving the 18-char DCPO material to canonical 12NC and carrying vendor, value, and lead-time detail. Feeds both STG_PO (deduped PO fact) and STG_PO_EP_ALLOCATION (EP-PO allocation detail).

Column	Type	Business Meaning
`DC_PO_NUMBER`	VARCHAR	DC purchase order number — primary key grain.
`DC_PO_ITEM`	VARCHAR	DC purchase order item — primary key grain.
`DC_PO_PLANT`	VARCHAR	4-char plant code, prefixed '10' downstream to 6-char canonical.
`DCPO_MATERIAL`	VARCHAR	18-char zero-padded material — stripped/prefixed to canonical 12NC downstream (100% join rate).
`DCPO_VENDOR`	VARCHAR	DC-side supplier ID.
`DCPO_NAME`	VARCHAR	DC-side supplier name.
`DCPO_Q`	NUMBER	Ordered quantity.
`DCPO_BUOM`	VARCHAR	Order unit of measure — needed since 339 of 8,116 POs mix ST/SET units.
`DCPO_PO_TOTAL_VALUE_EUR`	NUMBER	EUR-converted PO value.
`DCPO_PO_TOTAL_VALUE`	NUMBER	Document-currency PO value — kept separate from the EUR figure, not mixed.
`CURRENCY`	VARCHAR	Document currency (always 'USD' on all 12,072 rows live).
`EINDT_DATE`	DATE	Planned delivery date.
`REQUESTED_PDT`	NUMBER	Requested PDT-leg lead time in days (a duration, not a date).
`ACTUAL_PDT`	NUMBER	Actual PDT-leg lead time in days.
`ACTUAL_TOTAL_LT`	NUMBER	End-to-end actual lead time — basis of the real supply_delay_days metric.
`REQUESTED_LT`	NUMBER	End-to-end requested lead-time baseline, paired with ACTUAL_TOTAL_LT.
`DC_PO_GR_POSTING_DATE`	DATE	Goods-receipt posting date.
`DC_PO_RELEASE_DATE`	DATE	PO release date.
`GLOBAL_CHECK`	VARCHAR	Data-quality flag — carried as an attribute, never used as a filter.
`EP_PO_NUMBER`	VARCHAR	Upstream EP-PO allocation number (multiple EP-POs can fund one DC-PO line).
`BG`	VARCHAR	Business group code.

The remaining ~110 columns are largely unused legacy SAP/EP-PO fields (secondary vendor-role attributes, alternate lead-time diagnostics, internal routing/approval codes) not consumed by STAGING.

SLOWMO_AMS_CONS — 18 of 99 columns shown, 124,287 rows

Row count (live): 124,287
Total columns: 99

Slow-moving/dead-stock inventory extract — one row per material/plant/period/sales-org slice, carrying aged-stock quantity/value buckets. Aggregated in STAGING (SUM measures, MAX attributes) to material+plant+period grain, since the source is not unique at that grain (52.5% of combinations carry more than one row).

Column	Type	Business Meaning
`MATERIAL_PLANT`	VARCHAR	Composite key; material 12NC is extracted from this field.
`PLANT`	VARCHAR	6-char canonical plant code.
`PLANT_NAME`	VARCHAR	Plant name — the authoritative source for DIM_PLANT's display name.
`PERIOD`	VARCHAR	YYYYMM calendar period.
`OH_QNT`	NUMBER	On-hand quantity.
`OH_VALUE`	NUMBER	On-hand value, stored in kEUR (thousands of EUR) — multiplied by 1000 downstream.
`GIT_QNT`	NUMBER	Goods-in-transit quantity.
`GIT_VALUE`	NUMBER	Goods-in-transit value, also in kEUR.
`SLOW_MO_QNT`	NUMBER	Slow-moving quantity.
`SLOW_MO_VALUE`	NUMBER	Slow-moving value, in kEUR.
`CUR_QTY`	NUMBER	Current-month quantity.
`PREV_QTY`	NUMBER	Prior-month quantity.
`BG_MD`	VARCHAR	Business group code (source header is literally 'BG (md)').
`MAG_MD`	VARCHAR	True 3-char MAG code — MAG itself is the human-readable description, not the code.
`PRODUCT_LIFE_CYCLE_PHASE`	VARCHAR	Lifecycle phase — combined with on-hand qty > 0 to define dead stock ('Not-active'/'Phase out'/'Phase-out Initiated').
`DEMAND`	VARCHAR	'demand'/'no demand' flag — drives the demand_category classification (NULL rows, no stock position, are excluded).
`MOQ`	NUMBER	A lot-size/rounding indicator (values 0-6) here, NOT a true order quantity — do not confuse with MARC_AMS_CONSUMER.MOQ.
`MRP_CONTROLLER`	VARCHAR	Reporting-source MRP controller code.

The 0-6mo/7-12mo/12mo+ aging-bucket quantity and value columns, QTY_DIFF/VALUE_DIFF, SMOS_MONTH_COUNT, ORU_KEY/MRU_KEY, BRAND, MANUFACTURER(_PLANT) and SAFETY_STOCK are also consumed downstream, following the same pattern as the columns shown above. The remaining ~65 columns are unused legacy SLOWMO fields (alternate sales-org breakdowns, internal snapshot version markers).

T_MD_MATERIAL_CONSUMER — 21 of 180 columns shown, 536,076 rows

Row count (live): 536,076
Total columns: 180

SAP BW material master (BIC_* enriched attributes) — one row per material, canonical 12NC already native. Source for DIM_MATERIAL's descriptive/classification attributes in STAGING.STG_MATERIAL_MASTER.

Column	Type	Business Meaning
`MATERIAL`	VARCHAR	Canonical 12NC, no transform needed.
`BASE_UOM`	VARCHAR	Base unit of measure.
`EANUPC`	VARCHAR	EAN/UPC barcode.
`BIC_CBRAND`	VARCHAR	Brand.
`BIC_CSUCCESOR`	VARCHAR	Successor material 12NC.
`BIC_CLED`	VARCHAR	5-value LED classification code (10/15/00/20/80) — a category code, not a boolean.
`BIC_CPRODCLAS`	VARCHAR	Product classification.
`BIC_CPRODTYPE`	VARCHAR	Product type — value '10' gates the is_in_scope_consumer_fg flag.
`BIC_CPRODFAM`	VARCHAR	Product family.
`BIC_C10NC`	VARCHAR	10-digit material number variant.
`BIC_PMAG`	VARCHAR	MAG code.
`BIC_PBU`	VARCHAR	BU code.
`BIC_CTECHNO`	VARCHAR	Technology attribute.
`BIC_CVOLTAGE`	VARCHAR	Voltage attribute.
`GROSS_WT`	NUMBER	Raw gross weight — must be unit-normalized via UNIT_OF_WT before aggregating (92x overstatement if summed raw, live).
`NET_WEIGHT`	NUMBER	Raw net weight — same unit-normalization requirement as GROSS_WT.
`UNIT_OF_WT`	VARCHAR	Weight unit code (KG/G/LB/MG); '#' is the SAP blank marker, distinct from NULL.
`VOLUME`	NUMBER	Raw volume — must be unit-normalized via VOLUMEUNIT before aggregating (11 possible source units).
`VOLUMEUNIT`	VARCHAR	Volume unit code (M3/CDM/CCM/HL/GLL/CFT/IN3/OZA/PT/QT).
`MATL_TYPE`	VARCHAR	Material type — '10MANE' gates is_in_scope_consumer_fg alongside BIC_CPRODTYPE='10'.
`DEL_FLAG`	VARCHAR	BW-deletion flag — drives DIM_MATERIAL.is_active rather than a hard exclusion filter.

The remaining ~159 columns are largely unused legacy BW attributes (secondary classification hierarchies, alternate packaging/dimension fields, historical revision markers). BIC_CCBRAND, BIC_CPRDSGM, BIC_CPRODGRP1/2/3, BIC_CPRODFAC, BIC_CALTMATNR, BIC_CSOFF, BIC_CXMPLSTAA(/DA), BIC_PGMRU, BIC_CCTRY_ORG, BIC_CPACKTYPE, PROD_HIER, MATL_GROUP, IND_SECTOR, CREATEDON and MANUFACTOR are also consumed downstream but omitted above for brevity.

STAGINGBusiness-Rule-Applied Staging Tables

Nine tables, each a cleaned, business-rule-applied, standardized version of one (or two closely related) RAW_TRUSTED source(s). Material and plant codes are standardized to their canonical form here.

STG_DEMAND_FORECAST — 27 columns, 246,026 rows

Grain: One row per material + plant + calendar month + key figure + category + organizational dimension combination (demand and sales rows unioned into one measure table)
Row count (live): 246,026
Built from (RAW_TRUSTED): DEMAND_QXP_AMS_CONS, SALES_AMS_CONS_VIPP

Combines statistical demand forecast (DEMAND_QXP) and actual sales (SALES_AMS_CONS_VIPP) into a single measure table keyed by material, plant, period and key figure, with physical quantity and monetary value strictly separated into their own columns so they can never be summed together by mistake.

Key business rules applied:

Only rows whose material code is fully numeric are kept, in both the demand and sales branches — this excludes every placeholder/dummy material family regardless of its specific prefix, rather than pattern-matching a fixed list of known placeholder codes.
Only rows with FIN_POST_IND = '#' (the SAP default/no-allocation marker) are kept. Other posting codes carry revenue values with no associated quantities — rebates, credits, reclassifications and offset entries — and would inflate reported revenue with postings that are not real sales.
Both branches are scoped to Distribution Channel '01' (Consumer) — the demand/sales pipeline is Consumer-only end to end.
Each business record is deduplicated to the latest source revision (by source_update_ts) per full business key, so superseded revisions are not summed as if they were additive rows.
Key figures are explicitly classified as QUANTITY or VALUE_EUR before storage; a raw value column is never passed through to downstream supply-impact logic without this classification.

Column	Type	Business Meaning / Formula
`material_12nc`	TEXT	Canonical 12NC material code; already conformed in both source files, no transform needed.
`plant_code`	TEXT	6-character canonical plant code; already conformed in both source files.
`calendar_month_key`	TEXT	Fiscal period recast as a YYYY-MM-01 calendar string for joining to other monthly tables.
`key_figure`	TEXT	Raw key-figure code (KF) identifying which measure the row's VALUE holds (e.g. quantity, QxP EUR, etc.).
`category`	TEXT	Source category classification; current-year vs prior-year actuals (ACT_CY / ACT_CY-1) are distinguished here, not in key_figure.
`commercial_sub_team`	TEXT	Commercial sub-team slice; a genuinely distinct dimension member, needed so legitimately repeating material/plant/period/key-figure rows aren't collapsed as duplicates.
`fin_post_ind`	TEXT	Finance posting indicator. Only the '#' (default/no-allocation) value is retained by the table's row filter.
`measure_type`	TEXT	Derived classification of key_figure into 'QUANTITY', 'VALUE_EUR', or 'UNKNOWN', used to route the row into forecast_quantity or forecast_value_eur.
`forecast_quantity`	NUMBER(18,4)	Physical unit measure; populated only when key_figure represents units (quantity-type key figures).
`forecast_value_eur`	NUMBER(18,4)	Monetary measure in EUR; populated only when key_figure represents value (QxP / NNS / GAS / currency-bearing key figures).
`currency`	TEXT	Source currency code for value-type rows.
`oru_key`	TEXT	Operating Reporting Unit key.
`mru_key`	TEXT	Market Reporting Unit key.
`bg_code`	TEXT	Business Group code, normalized to numeric-only form (strips prefixes such as 'BG') to match the canonical code form used elsewhere (e.g. STG_DRM).
`bu_code`	TEXT	Business Unit code, normalized to numeric-only form for the same reason as bg_code.
`channel_group`	TEXT	Channel group classification.
`planning_account`	TEXT	APO planning account; the join key to STG_DRM.planning_account.
`manufacturer_key`	TEXT	First-in-chain manufacturer key.
`manufacturer_name`	TEXT	First-in-chain manufacturer name.
`ato_eto_flag`	TEXT	Assemble-to-order / Engineer-to-order indicator.
`lifecycle_phase`	TEXT	Product lifecycle phase.
`supply_class`	TEXT	Supply classification.
`segment`	TEXT	Business segment.
`mrp_controller`	TEXT	MRP controller code; already canonical in source.
`source_file`	TEXT	Literal tag identifying which source table the row came from: 'DEMAND_QXP' or 'SALES_VIPP'.
`source_update_ts`	TIMESTAMP	Source system's last-update timestamp for the record; used to keep only the latest revision per business key.
`_LOAD_TS`	TIMESTAMP_LTZ	Timestamp this row was standardized (source has no native load timestamp).

STG_DRM — 55 columns, 756,429 rows

Grain: One row per sales order line / delivery item
Row count (live): 756,429
Built from (RAW_TRUSTED): DRM_AMS_CONS

Delivery Reliability Metric (DRM) fact at the sales-order-line grain: order, shipment, customer and root-cause detail together with a derived on-time/complete delivery outcome and a prioritized miss-reason classification.

Key business rules applied:

Only rows with a populated material code are kept.
DRM/OTIF scoring is restricted to the standard-delivery item category ('10ZE01', ~96% of records); returns and free-of-charge lines are excluded, along with lines that have no eligible total line count.
Lines whose delay is flagged unmeasurable, and lines that were cancelled/rejected (a populated rejection reason), are excluded from the miss-rate denominator entirely (scored as NULL, not 0) rather than counted as either a hit or a miss.
A line with no actual delivery date is only treated as 'unknown, not yet due' while its agreed date is still in the future. Once the agreed date has passed with no delivery, it counts as a miss rather than staying excluded as unknown.
Channel 1004 (internal sales) is deliberately kept in this table — unlike the '01'-only scope applied to demand/sales — because internal sales are relevant to the DRM metric.
The true miss reason is derived from a priority-ordered set of indicator flags (Supply, then Sales, then Outbound, then Other/Misc) because the source's own root-cause text fields are empty; within each tier, specific flags are checked before that tier's catch-all flag so a specific cause is never masked by 'other'.
The delivery-requirement flag reflects a configured requirement, not actual fulfillment; actual completion is a separately derived flag based on delivered quantity versus agreed quantity.
AG code/name are preserved as text (not numeric) so leading zeros in the code are retained.

Column	Type	Business Meaning / Formula
`sales_order_number`	TEXT	Sales order document number.
`sales_order_item`	TEXT	Sales order line item number.
`delivery_number`	TEXT	Delivery document number.
`delivery_item`	TEXT	Delivery line item number.
`material_12nc`	TEXT	Canonical 12NC material code; already conformed in source.
`plant_code`	TEXT	6-character canonical plant code; already conformed in source.
`mrp_controller`	TEXT	MRP controller code; already canonical in source.
`planning_account`	TEXT	APO planning account; fully overlaps STG_DEMAND_FORECAST.planning_account as a join key.
`ship_to_id / ship_to_name / ship_to_country`	TEXT	Ship-to party identity and location.
`sold_to_id / sold_to_name`	TEXT	Sold-to party identity.
`er_root_cause / er_root_cause_desc`	TEXT	Exception-report root cause code/text (sparsely populated in source).
`sor_root_cause / sor_root_cause_desc`	TEXT	Service-order-reliability root cause code/text (sparsely populated in source).
`bg_key / bg_name`	TEXT	Business Group key and name.
`bu_code / bu_name`	TEXT	Business Unit code and name.
`banner`	TEXT	Customer banner.
`ag_code / ag_name`	TEXT	AG code and label, preserved as text so leading zeros in the code are not lost.
`mag_code / mag_name`	TEXT	MAG code and label.
`cag_code`	TEXT	CAG code.
`supplier_id_raw / supplier_name`	TEXT	External supplier identifier (raw source format) and name.
`target_agreed_dp_date`	DATE	Target/agreed delivery-promise date.
`so_creation_date`	DATE	Sales order item creation date.
`best_confirmed_delivery_date`	DATE	Best confirmed delivery date.
`actual_delivery_date`	DATE	Actual delivery date; NULL if not yet delivered.
`actual_goods_issue_date`	DATE	Actual goods issue date.
`appointment_date`	DATE	Appointment date at time of measurement.
`max_appointment_date`	DATE	Latest appointment date recorded.
`actual_qty`	NUMBER(18,4)	Actual delivered quantity (source unit of measure).
`agreed_qty`	NUMBER(18,4)	Agreed delivery-promise order-line quantity.
`lines_drm / line_adp`	TEXT	Supporting line-level counters carried from source.
`drm_pct`	NUMBER(8,4)	DRM percentage as reported by source; equals 100 * drm_scored_lines / drm_total_lines.
`drm_total_lines / drm_scored_lines`	NUMBER	Eligible-line count and successfully-scored-line count; needed to tell an eligible failure (total=1, scored=0) apart from an ineligible line (total=0).
`sor_total_lines / sor_scored_lines`	NUMBER	Same eligibility/outcome pairing for the SOR metric.
`er_total_lines / er_scored_lines`	NUMBER	Same eligibility/outcome pairing for the ER metric.
`missed_lines_er`	NUMBER	Count of ER-missed lines.
`adp_delay_days`	NUMBER	Agreed-delivery-promise delay in days (parsed numerically; non-numeric source values yield NULL).
`is_adp_unmeasurable`	BOOLEAN	TRUE when the source delay value is the literal unmeasurable marker ('?'), so the line can be excluded from delay-based scoring rather than treated as a real 0-day or missing value.
`item_categ`	TEXT	Item category code; delivery scoring is restricted to the standard-delivery category.
`missed_no_stock, missed_supply_reason_for_rej, missed_supply_delivery_block, missed_del_group, missed_supply_others`	NUMBER/BOOLEAN	Supply-tier miss-reason indicator flags.
`missed_credit_block, missed_sales_delivery_block, missed_sales_reason_for_rej, missed_incomplete_order, missed_inconsistent_data, missed_sales_ex_credit_misc`	NUMBER/BOOLEAN	Sales-tier miss-reason indicator flags.
`missed_transport, missed_warehouse, missed_appointment_calls, missed_wrong_day, missed_outbound_ex_warehouse_transport`	NUMBER/BOOLEAN	Outbound-tier miss-reason indicator flags.
`missed_misc`	NUMBER/BOOLEAN	Catch-all miss-reason indicator.
`primary_delay_reason`	TEXT	Derived single miss reason, chosen by priority across all MISSED_* flags (Supply tier first, then Sales, then Outbound, then Other/Misc); each tier's specific flags are tested before that tier's catch-all.
`delay_reason_group`	TEXT	Derived top-level grouping of primary_delay_reason into Supply / Sales / Outbound / Other.
`is_drm_miss`	BOOLEAN (tri-state via NULL)	Derived DRM outcome. NULL = excluded from scoring (unmeasurable, cancelled/rejected, non-standard item category, or not yet due); TRUE = miss (including overdue lines with no delivery date yet); FALSE = on-time/complete.
`complete_delivery_required_flag`	TEXT	Whether complete delivery was a configured requirement for this line — a requirement/configuration indicator, not proof that delivery actually happened complete.
`is_delivery_complete`	BOOLEAN	Derived actual-completion outcome: TRUE when delivered quantity meets or exceeds agreed quantity (and agreed quantity is positive).
`appointment_flag`	TEXT	Appointment-related status flag.
`carrier`	TEXT	Carrier name (sparsely populated).
`shipment_tms_id`	TEXT	Transport-management-system shipment identifier.
`last_carrier_status`	TEXT	Last known carrier status from the TMS.
`rejection_reason_code / rejection_reason_desc`	TEXT	Rejection reason code and description, when the line was rejected.
`delivery_block_code / delivery_block_desc`	TEXT	Delivery block code and description, when the line is blocked.
`overall_delivery_status`	TEXT	Derived overall status: 'C' (completed) when an actual delivery date exists, else NULL — the source extract has no direct status column.
`rejection_status`	TEXT	Derived rejection status: 'REJECTED' when a rejection reason code is present, else NULL.
`_LOAD_TS`	TIMESTAMP_LTZ	Timestamp this row was standardized.

STG_PO — 30 columns, 12,063 rows

Grain: One row per DC purchase order + item (deduplicated to a single row per PO/item)
Row count (live): 12,063
Built from (RAW_TRUSTED): PO_LT_DATA_US (base/lead-time and vendor data), EKBE_DATA_PO (goods receipts), EKET_DATA_PO (schedule lines), EKES_DATA_PO (vendor confirmations)

Unified purchase-order fact at DC-PO + item grain: lead-time, vendor and value data from the PO lead-time extract, left-joined to aggregated goods-receipt, delivery-schedule and vendor-confirmation activity.

Key business rules applied:

Deduplicated to one row per DC-PO + item: the base source itself contains exact full-row duplicates for a handful of PO/item pairs, and every join below is against this deduplicated base so a missed dedup here would have re-doubled every downstream aggregate.
Goods-receipt, schedule-line and vendor-confirmation source data are each deduplicated at their own natural grain (by latest change timestamp) before being aggregated to PO+item, preventing duplicate source rows from inflating received/scheduled/confirmed quantities.
Received quantity nets goods-receipt movements using the debit/credit indicator (receipt vs. return/reversal) rather than a fixed list of movement-type codes, so purchase returns are correctly netted rather than silently excluded.
Earliest schedule and confirmation dates only consider lines that still have open (undelivered) quantity, so a fully-consumed historical schedule line can never win over the genuinely active expected date.
DC-PO and EP-PO vendor identities are exposed as two separate fields rather than merged, since a PO can be re-sourced to a different fulfilling vendor and collapsing the two would hide which counterparty actually shipped.
The EUR-denominated value and the document/local-currency value are published as two explicit value/currency pairs rather than mismatched, since the document currency and the EUR-converted amount are not the same figure.
Delay and lead-time figures are end-to-end (PO release/creation to goods-receipt posting), not just the vendor-confirmation leg; the confirmation-leg-only figure is retained separately as a diagnostic and must not drive on-time/lateness classification.
The data-quality flag is carried as an attribute for filtering, never applied as a row-exclusion filter.
EP-PO allocation detail is intentionally not carried at this grain, since some DC-PO/item combinations legitimately split across more than one EP-PO — that detail lives in STG_PO_EP_ALLOCATION instead.

Column	Type	Business Meaning / Formula
`po_number`	TEXT	DC purchase order number.
`po_item`	TEXT	DC purchase order item/line number.
`material_12nc`	TEXT	Canonical 12NC, derived from the 18-character zero-padded DC-PO material code.
`plant_code`	TEXT	6-character canonical plant code, derived from the 4-character DC-PO plant code.
`dc_supplier_id / dc_supplier_name / dc_supplier_country`	TEXT	Identity of the vendor actually fulfilling the DC purchase order.
`ep_supplier_id / ep_supplier_name / ep_supplier_country`	TEXT	Identity of the original/upstream (EP) vendor, kept separate from the DC vendor rather than merged, since a PO can be re-sourced to a different fulfilling vendor.
`vendor_category`	TEXT	Vendor category classification.
`po_type`	TEXT	Purchase order type.
`bg_code / bu_code`	TEXT	Business Group / Business Unit codes.
`mru_key / oru_key`	TEXT	Market/Operating Reporting Unit keys.
`ordered_qty`	NUMBER(18,4)	Quantity ordered on the PO line.
`ordered_uom`	TEXT	Unit of measure for ordered_qty; carried through as-is (no conversion) so mixed-unit POs can be identified downstream.
`received_qty`	NUMBER(18,4)	Net received quantity, aggregated from goods-receipt movements with returns/reversals netted out.
`open_qty`	NUMBER(18,4)	Derived: ordered_qty minus received_qty.
`po_value_eur`	NUMBER(18,4)	PO total value already converted to EUR by the source.
`reporting_currency`	TEXT	Literal 'EUR', paired explicitly with po_value_eur.
`po_value_local`	NUMBER(18,4)	PO total value in the document's own currency.
`currency`	TEXT	Document currency code paired with po_value_local (observed as 'USD' on essentially all rows).
`po_release_date`	DATE	PO release date.
`planned_delivery_date`	DATE	Originally planned delivery date.
`confirmed_delivery_date`	DATE	Best available expected delivery date: earliest open vendor confirmation, else earliest open schedule date, else the planned delivery date.
`requested_pdt_days`	NUMBER	Requested vendor lead time in days (a day count, not a date).
`actual_gr_date`	DATE	Actual goods-receipt posting date (from goods-receipt activity, falling back to the source's own GR date).
`supply_delay_days`	NUMBER(8,2)	End-to-end delay: actual total lead time minus requested lead time, PO release/creation to GR posting. Positive = late.
`requested_lt_days`	NUMBER(8,2)	End-to-end requested lead-time baseline.
`pdt_delay_days_diagnostic`	NUMBER(8,2)	The vendor-confirmation-leg-only delay figure, retained as a diagnostic; not used for on-time or lateness classification.
`actual_total_lt_days`	NUMBER	Actual end-to-end lead time achieved, release/creation to GR posting.
`marc_planned_lt_days`	NUMBER	Planned lead time from the material master.
`dq_global_check_failed`	NUMBER(1,0)	Data-quality flag marking records with potential master-data or process issues that could distort lead-time analysis. Attribute only, never used to exclude rows.
`schedule_qty`	NUMBER(18,4)	Quantity from vendor confirmations, falling back to scheduled quantity when no confirmation exists.

STG_PO_EP_ALLOCATION — 8 columns, 12,072 rows

Grain: One row per DC-PO + item + EP-PO + item allocation (full allocation detail, narrower dedup than STG_PO)
Row count (live): 12,072
Built from (RAW_TRUSTED): PO_LT_DATA_US

Preserves the full DC-PO-to-EP-PO allocation detail that STG_PO's DC-PO/item grain cannot represent whenever a single DC-PO/item is fulfilled by more than one EP-PO.

Key business rules applied:

Deduplicated only on the full natural key (DC-PO, DC-PO item, EP-PO, EP-PO item) — deliberately narrower than STG_PO's dedup, so a DC-PO/item that legitimately splits across multiple EP-POs keeps every allocation row rather than being collapsed to one.
Must not be joined back to STG_PO at DC-PO/item grain — doing so would re-fan-out the already-correct received/open quantity computed there.
Reconciles fully against STG_PO: the sum of ep_po_qty for a given DC-PO/item equals that item's dc_po_qty on every row.

Column	Type	Business Meaning / Formula
`dc_po_number / dc_po_item`	TEXT	DC purchase order number and item.
`ep_po_number / ep_po_item`	TEXT	EP (upstream) purchase order number and item allocated against this DC-PO/item.
`ep_po_qty`	NUMBER(18,4)	Quantity allocated to this EP-PO.
`ep_po_creation_date`	DATE	EP-PO creation date.
`ep_supplier_id / ep_supplier_name / ep_supplier_country`	TEXT	EP vendor identity for this allocation.
`ep_inco_term`	TEXT	Incoterm for the EP-PO.
`ep_order_acknowledgement`	TEXT	EP-PO order acknowledgement status.
`dc_po_qty`	NUMBER(18,4)	Total ordered quantity on the parent DC-PO/item, repeated on every allocation row for reconciliation.

STG_INVENTORY — 18 columns, 1,655,310 rows

Grain: Material + plant + storage location + stock type + fiscal period
Row count (live): 1,655,310
Built from (RAW_TRUSTED): INVENTORY_AMS_CONS, joined to CURATED.DIM_FISCAL_PERIOD for calendar mapping

Inventory position aggregated from batch-level source detail up to material/plant/storage-location/stock-type/period grain. Storage location and stock type are deliberately preserved (not collapsed) so blocked and quality-inspection stock stay separately visible.

Key business rules applied:

Aggregation collapses only the batch dimension; material, plant, storage location, stock type and fiscal period are all preserved as separate grain members so questions about blocked or quality-inspection stock by location remain answerable.
On-hand, goods-in-transit and combined quantities all use the base-unit-of-measure source columns consistently — the single-unit-of-measure and base-unit-of-measure columns differ per material (e.g. 1 SET = 6 pieces), and mixing them would overstate total supply.
Stock type is decoded into a business label using only the three documented codes (Unrestricted / Quality inspection / Blocked) plus blank; any other observed code is bucketed as 'Other' rather than guessed at, since no authoritative decode exists for it.
'Available stock' is exposed as a flag rather than enforced as a filter: on-hand quantity greater than zero, material type = finished goods, and stock type = Unrestricted. Consumers filter on the flag for availability KPIs while the full stock picture, including blocked and quality-inspection stock, stays queryable.
The value columns carry the source's reporting currency, which is not guaranteed to be EUR.

Column	Type	Business Meaning / Formula
`material_12nc`	TEXT	Canonical 12NC material code; already conformed in source.
`plant_code`	TEXT	6-character canonical plant code; already conformed in source.
`storage_location`	TEXT	Storage location code.
`stock_type`	TEXT	Raw stock type code.
`fiscal_period_code`	TEXT	Source fiscal year/period code (YYYYPPP).
`calendar_month_key`	DATE	Calendar-month key resolved via DIM_FISCAL_PERIOD.
`fiscal_year`	NUMBER	Fiscal year, resolved via DIM_FISCAL_PERIOD.
`fiscal_period_num`	NUMBER	Fiscal period number within the year, resolved via DIM_FISCAL_PERIOD.
`on_hand_qty`	NUMBER(18,4)	On-hand stock quantity, summed across batches in base unit of measure.
`git_qty`	NUMBER(18,4)	Goods-in-transit quantity, summed across batches in base unit of measure.
`total_qty_incl_git`	NUMBER(18,4)	On-hand plus goods-in-transit quantity, summed in base unit of measure.
`on_hand_value_eur`	NUMBER(18,4)	On-hand value in the source reporting currency (not necessarily EUR despite the column name).
`git_value_eur`	NUMBER(18,4)	Goods-in-transit value in the source reporting currency.
`total_value_eur`	NUMBER(18,4)	Combined on-hand plus GIT value in the source reporting currency.
`stock_type_desc`	TEXT	Derived business label for stock_type: 'Unrestricted' (A), 'Quality inspection' (Q), 'Blocked' (D), 'Unspecified' (blank), or 'Other' (any undocumented code).
`is_unrestricted`	BOOLEAN	TRUE when stock_type = 'A' (Unrestricted).
`is_available_stock`	BOOLEAN	Derived availability flag: on-hand quantity > 0, material type is finished-goods, and stock type is Unrestricted.
`mrp_controller`	TEXT	MRP controller code; already canonical in source.

STG_FORECAST_BIAS — 15 columns, 62,509 rows

Grain: Material + planning account + fiscal period + distribution channel
Row count (live): 62,509
Built from (RAW_TRUSTED): FC_BIAS_FACC, joined to CURATED.DIM_FISCAL_PERIOD for calendar mapping

Forecast bias / accuracy snapshot comparing planned quantities across multiple forecast horizons (three periods back through the current period) against actual delivered quantity, at material/planning-account/period/channel grain.

Key business rules applied:

The true grain includes distribution channel, not just material + planning account + period — omitting it causes rows for different channels (e.g. Consumer vs. Professional) sharing the same material/account/period to collide into one another.
Scoped to Distribution Channel '1001' (Consumer) only. This is the only table in the extract whose source data spans beyond Consumer; the non-Consumer rows are excluded so the table stays comparable with the other Consumer-only tables it joins to.
This source represents the distribution channel with a 4-digit code ('1001') where demand/sales tables use a 2-digit code ('01') for the same Consumer population — the two are equivalent encodings of the same scope, not different scopes.

Column	Type	Business Meaning / Formula
`material_12nc`	TEXT	Canonical 12NC material code.
`planning_account`	TEXT	APO planning account code.
`distribution_channel`	TEXT	Distribution channel code (4-digit form in this source, e.g. '1001').
`distribution_channel_desc`	TEXT	Distribution channel description.
`scm_area_code`	TEXT	Country-level SCM area code; cannot be joined directly to plant-grain tables.
`fiscal_period_code`	TEXT	Source fiscal year/period code (YYYYPPP).
`calendar_month_key`	DATE	Calendar-month key resolved via DIM_FISCAL_PERIOD.
`fiscal_year`	NUMBER	Fiscal year, resolved via DIM_FISCAL_PERIOD.
`fiscal_period_num`	NUMBER	Fiscal period number within the year, resolved via DIM_FISCAL_PERIOD.
`bg / bu / mag / cag`	TEXT	Business Group, Business Unit, MAG and CAG classification codes.
`planned_qty_n3 / planned_qty_n2 / planned_qty_n1 / planned_qty_n`	NUMBER(18,4)	Snapshot of planned quantity taken 3, 2, 1 periods before, and at, the current period.
`actual_delivered_qty`	NUMBER(18,4)	Actual delivered quantity, corrected.
`abs_dev_n3 / abs_dev_n2 / abs_dev_n1 / abs_dev_n0`	NUMBER(18,4)	Absolute deviation between planned and actual at each snapshot horizon.
`planned_qxp_n3 / planned_qxp_n2 / planned_qxp_n1 / planned_qxp_n`	NUMBER(18,4)	Snapshot of planned QxP (value) figure at each forecast horizon.
`cf_abs_dev_n3 / cf_abs_dev_n2 / cf_abs_dev_n1 / cf_abs_dev_n0`	NUMBER(18,4)	Calculated absolute deviation for the QxP figure at each horizon.

STG_MATERIAL_PLANNING — 24 columns, 56,060 rows

Grain: One row per material + plant (MRP view)
Row count (live): 56,060
Built from (RAW_TRUSTED): MARC_AMS_CONSUMER

Material Requirements Planning (MRP) parameters at material/plant grain: lot sizing, safety stock, reorder point, procurement type and lead times.

Key business rules applied:

The material-code transform is applied to every source material code format present — 18-character zero-padded numeric codes and non-padded alphanumeric codes alike — rather than gating on an 18-character length, which would leave a meaningful share of records unable to join to the material dimension.
Plant code prefers the source's pre-formed 6-character plant code when populated, falling back to deriving it from the 4-character plant code only when needed.
The lot-size quantity (a number) and the lot-sizing procedure (a categorical code) are two distinct source columns and are kept as two distinct output columns rather than conflated.
Only rows with a populated material code, and a resolvable plant code from either source field, are kept.

Column	Type	Business Meaning / Formula
`material_12nc`	TEXT	Canonical 12NC, derived from the source material code (handles both zero-padded numeric and non-padded alphanumeric source formats).
`plant_code`	TEXT	6-character canonical plant code: uses the pre-formed plant code when present, otherwise derives it from the 4-character plant code.
`mrp_type`	TEXT	MRP type.
`mrp_controller`	TEXT	MRP controller code, with the standard prefix applied.
`planned_delivery_days`	NUMBER	Planned delivery time in days.
`gr_processing_days`	NUMBER	Goods-receipt processing time in days.
`safety_stock`	NUMBER(18,4)	Safety stock quantity.
`reorder_point`	NUMBER(18,4)	Reorder point quantity.
`moq`	NUMBER(18,4)	Minimum order quantity (a genuine quantity figure from this source).
`min_order_qty`	NUMBER(18,4)	Minimum lot size.
`max_order_qty`	NUMBER(18,4)	Maximum lot size.
`procurement_type`	TEXT	Procurement type (make/buy indicator).
`special_procurement_key`	TEXT	Special procurement key.
`material_status`	TEXT	Material status code.
`material_status_valid_from`	DATE	Date the material status became effective.
`purchasing_group`	TEXT	Purchasing group responsible for the material.
`total_replenishment_lt_days`	NUMBER	Total replenishment lead time in days.
`lot_sizing_procedure_code`	TEXT	The true categorical lot-sizing procedure code (distinct from the numeric lot-size quantity below).
`costing_lot_size_qty`	NUMBER(18,4)	The numeric costing lot-size quantity (distinct from the procedure code above).
`apo_relevant_flag`	TEXT	APO relevance indicator.
`profit_center`	TEXT	Profit center code.
`abc_classification`	TEXT	ABC classification code.
`apo_type`	TEXT	APO material type.
`_LOAD_TS`	TIMESTAMP_LTZ	Timestamp this row was standardized.

STG_SLOW_MOVING — 28 columns, 72,110 rows

Grain: Material + plant + calendar month, aggregated across sales organizations
Row count (live): 72,110
Built from (RAW_TRUSTED): SLOWMO_AMS_CONS

Slow-moving and dead-stock inventory position per material/plant/month, aggregated up from a source grain that fans out across sales organizations, with all monetary values converted from thousands of EUR to EUR.

Key business rules applied:

All monetary columns are converted from the source unit (thousands of EUR) to EUR by multiplying by 1000; quantity columns need no conversion.
Every row with a populated stock-position indicator is kept and classified as 'demand' (active article still carrying slow-moving stock) or 'no demand' (zero-demand article); only rows representing no stock position that month are excluded.
Material + plant + period is not naturally unique in the source — it fans out across sales organizations — so the table is explicitly aggregated to that grain: additive measures (quantities, values) are summed, and descriptive attributes take a single representative value across the fanned-out rows.
The lot-size-indicator column is a rounding/lot-size indicator, not an order quantity, and is deliberately given a distinct name so it cannot be confused with the genuine minimum-order-quantity figure carried elsewhere in material planning data.
Dead stock is a lifecycle condition, not a demand-signal condition: on-hand quantity greater than zero for a product whose lifecycle phase is inactive/phased-out. An active product simply having a quiet month is not dead stock.

Column	Type	Business Meaning / Formula
`material_12nc`	TEXT	Canonical 12NC material code, extracted from the source's composite material/plant key field.
`plant_code`	TEXT	6-character canonical plant code; already conformed in source.
`plant_name_raw`	TEXT	Plant name, taking the non-blank value across the fanned-out source rows.
`calendar_month_yyyymm`	TEXT	Source period in YYYYMM form.
`calendar_month_key`	TEXT	Period recast as a YYYY-MM-01 calendar string for joining to other monthly tables.
`on_hand_qty / on_hand_value`	NUMBER(18,4)	On-hand quantity and value (value converted to EUR), summed across fanned-out source rows.
`git_qty / git_value`	NUMBER(18,4)	Goods-in-transit quantity and value (value converted to EUR), summed.
`qty_0_6m / qty_7_12m / qty_gt_12m`	NUMBER(18,4)	On-hand quantity aged 0-6 months, 7-12 months, and over 12 months, summed.
`value_7_12m / value_gt_12m`	NUMBER(18,4)	On-hand value (EUR) aged 7-12 months and over 12 months, summed.
`slow_mo_qty / slow_mo_value`	NUMBER(18,4)	Slow-moving quantity and value (EUR), summed.
`current_qty / prior_month_qty`	NUMBER(18,4)	Current and prior-month quantity, summed.
`current_value / prior_month_value`	NUMBER(18,4)	Current and prior-month value (EUR), summed.
`qty_mom_delta / value_mom_delta`	NUMBER(18,4)	Month-over-month change in quantity and value (EUR), summed.
`smos_month_count`	NUMBER	Slow-moving/obsolete-stock month counter, taking a representative value across fanned-out rows.
`bg_code / bu_code`	TEXT	Business Group / Business Unit codes, taking a representative value across fanned-out rows.
`mag_code / mag_name`	TEXT	MAG code (from the true coded field, not the description field) and its readable name.
`lifecycle_phase`	TEXT	Product lifecycle phase, used to determine dead-stock status.
`oru_key / mru_key`	TEXT	Operating/Market Reporting Unit keys.
`brand`	TEXT	Brand.
`source_manufacturer_id`	TEXT	Manufacturer identifier as recorded in this source.
`manufacturer_plant`	TEXT	Manufacturer plant.
`safety_stock_raw`	TEXT	Safety-stock value as recorded in this source.
`slowmo_lot_size_indicator`	TEXT	A lot-size/rounding indicator from this source, deliberately renamed and kept separate from the genuine minimum-order-quantity figure in material planning data — the two share a similar source name but hold incompatible meanings.
`mrp_controller`	TEXT	MRP controller code; already canonical in source.
`demand_flag`	TEXT	Representative demand-signal value across fanned-out rows: 'demand' (active article still carrying slow-moving stock) or 'no demand' (zero-demand article).
`demand_category`	TEXT	Derived label for demand_flag: 'Active' or 'No demand this period'.
`is_dead_stock`	BOOLEAN	Derived flag: TRUE when on-hand quantity is greater than zero and the product's lifecycle phase indicates it has been phased out.
`_LOAD_TS`	TIMESTAMP_LTZ	Timestamp this row was standardized.

STG_MATERIAL_MASTER — 41 columns, 536,076 rows

Grain: One row per material
Row count (live): 536,076
Built from (RAW_TRUSTED): T_MD_MATERIAL_CONSUMER

Material master attributes at the material level: brand and commercial hierarchy, product classification, physical dimensions, and lifecycle/status fields.

Key business rules applied:

Weight is normalized to kilograms from whatever unit the source row records (kg/g/lb/mg); an unmapped or blank unit resolves to NULL rather than being assumed to be kilograms, since a visibly missing value is safer than a silently wrong unit assumption.
Volume is normalized to cubic meters from any of 11 source units, including imperial units, for the same reason.
Volume values that normalize to over 100 cubic meters for a single article are flagged as implausible rather than dropped — the row stays in the data, but the flag lets it be excluded from any volume rollup until the source value is corrected.
The SAP blank-value marker is treated explicitly as blank wherever it appears in classification logic (unit codes, product type), since a plain NULL check alone would not catch it.
In-scope Consumer finished-goods materials are identified with a flag (material type = finished goods and product type = 10), not enforced as a filter — hard-filtering this dimension would orphan any fact row that references an out-of-scope material, since it is a conformed dimension referenced from multiple fact tables.
BW-deleted materials are retained as rows rather than excluded, so every fact table that references a material always finds a matching row; deletion status is exposed as an attribute instead of being used to drop records.
Only rows with a populated material code are kept.

Column	Type	Business Meaning / Formula
`material_12nc`	TEXT	Canonical 12NC material code; already conformed in source.
`base_uom`	TEXT	Base unit of measure.
`ean_upc`	TEXT	EAN/UPC barcode.
`brand`	TEXT	Brand code.
`commercial_brand`	TEXT	Commercial brand code.
`successor_12nc`	TEXT	Successor material's 12NC, when this material has been superseded.
`commercial_release_date`	DATE	Commercial release date.
`deletion_date`	DATE	Deletion date.
`led_indicator_code`	TEXT	LED classification code (a 5-value categorical code, not a true/false indicator).
`innovation_flag`	TEXT	Innovation classification.
`innovation_date`	DATE	Innovation date.
`product_class / product_segment / product_type`	TEXT	Product classification hierarchy fields.
`product_group_1 / product_group_2 / product_group_3`	TEXT	Product grouping hierarchy fields.
`product_family`	TEXT	Product family.
`product_facility`	TEXT	Product facility.
`alternate_material_nr`	TEXT	Alternate material number.
`material_10nc`	TEXT	10-digit material code variant.
`sales_off_flag`	TEXT	Sales-off status flag.
`cross_plant_status / cross_plant_status_date`	TEXT / DATE	Cross-plant material status and its effective date.
`mag_code`	TEXT	MAG code.
`bu_code`	TEXT	Business Unit code.
`mru_code`	TEXT	Market Reporting Unit code.
`technology`	TEXT	Technology classification.
`voltage`	TEXT	Voltage classification.
`country_of_origin`	TEXT	Country of origin.
`packaging_type`	TEXT	Packaging type.
`gross_wt / net_weight / volume`	TEXT (raw)	Raw source weight/volume values in their original mixed units — kept for lineage but must not be aggregated directly; use the _kg / _m3 columns instead.
`unit_of_wt`	TEXT	Unit of measure for gross_wt/net_weight.
`volume_unit`	TEXT	Unit of measure for volume.
`gross_weight_kg / net_weight_kg`	NUMBER(18,6)	Weight normalized to kilograms from whatever unit the row used; NULL when the unit is unmapped or blank, rather than assuming kilograms.
`volume_m3`	NUMBER(18,9)	Volume normalized to cubic meters from any of the 11 source units (including imperial); NULL when the unit is unmapped or blank.
`is_volume_implausible`	BOOLEAN	Flag (not a filter): TRUE when the normalized volume exceeds 100 cubic meters for a single article — implausible for a consumer lighting product and likely a source decimal error. Rows stay in the data but should be excluded from volume rollups.
`product_hierarchy`	TEXT	Product hierarchy code.
`material_group`	TEXT	Material group.
`ind_sector`	TEXT	Industry sector.
`material_type`	TEXT	Material type code.
`is_in_scope_consumer_fg`	BOOLEAN	Flag (not a filter): TRUE when material type is finished-goods (10MANE) and product type is 10 — the client-confirmed Consumer finished-goods scope. Consumers apply this flag themselves; the dimension itself stays complete.
`created_on`	DATE	Record creation date.
`manufacturer_id`	TEXT	Manufacturer identifier.
`deleted_in_bw`	TEXT	BW deletion-flag status; deleted materials are retained as rows here, with this field marking their status rather than being dropped.
`_LOAD_TS`	TIMESTAMP_LTZ	Timestamp this row was standardized.

CURATEDConformed Star Schema

Dimensions, bridges, and facts with real primary/foreign keys. This is the layer every AGGREGATE table and SEMANTIC view is built on.

Dimensions & Bridges (11 tables)

DIM_FISCAL_PERIOD — 18 columns

Grain: One row per calendar month, 2021–2030 (120 rows)
Primary key: FISCAL_PERIOD_CODE
Source: Generated calendar (not sourced from a transactional table)

The period calendar used across the entire pipeline. FISCAL_PERIOD_CODE (YYYYPPP) is a calendar encoding, not a true SAP fiscal calendar — PPP is simply the calendar month number (001=January … 012=December), confirmed by cross-correlating INVENTORY (keyed YYYYPPP) against SLOWMO (keyed YYYYMM) for the same material+plant: the 001=January mapping matched 86% of periods vs. 18% for a fiscal August-start calendar.

Column	Type	Business Meaning
`FISCAL_PERIOD_CODE`	TEXT	Primary key. YYYYPPP string, PPP = calendar month. Matches the period fields used in the inventory and forecast-bias sources.
`FISCAL_YEAR`	NUMBER	Calendar year component (named fiscal_year for downstream compatibility; equals the calendar year).
`PERIOD_NUM`	NUMBER	Calendar month number (1–12).
`CALENDAR_START_DATE`	DATE	First day of the calendar month.
`CALENDAR_MONTH_KEY`	TEXT	ISO string YYYY-MM-01. Primary join key to the demand and slow-moving facts.
`CALENDAR_MONTH_YYYYMM`	TEXT	YYYYMM compact string. Join key to the slow-moving fact's native period format.
`MONTH_NAME`	TEXT	Abbreviated month name (Jan … Dec).
`CALENDAR_MONTH_NUM`	NUMBER	Calendar month as integer (alias of PERIOD_NUM).
`CALENDAR_YEAR`	NUMBER	Calendar year as integer (alias of FISCAL_YEAR).
`CALENDAR_QUARTER`	NUMBER	Calendar quarter (1–4).
`FISCAL_HALF`	TEXT	H1 (Jan–Jun) or H2 (Jul–Dec).
`FISCAL_QUARTER`	TEXT	Calendar quarter as a display string (Q1–Q4).
`IS_YEAR_END_PERIOD`	BOOLEAN	TRUE when period_num = 12 (December).
`IS_ADJUSTMENT_PERIOD`	BOOLEAN	TRUE for a 13th-period SAP adjustment period. Always FALSE — no period-013 codes exist in this dataset.
`CALENDAR_END_DATE`	DATE	Last day of the calendar month.
`MAPPING_STATUS`	TEXT	DATA_VALIDATED — confirmed by cross-correlating inventory and slow-moving periods for the same material+plant (86% match rate).
`MAPPING_SOURCE`	TEXT	Method used to validate the mapping (empirical cross-source correlation).
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

DIM_MATERIAL — 41 columns

Grain: One row per unique 14-digit material code (536,076 rows)
Primary key: MATERIAL_12NC
Source: Material master, enriched with lifecycle/supply-class attributes from the demand-planning source

The conformed product master. MATERIAL_12NC is the universal join key used across every CURATED fact and dimension table. Lifecycle phase and supply class are resolved from plant-level demand-planning data (not the material master itself) because they're more current there — where different plants report different values for the same material, the modal value is kept and a conflict flag/count is exposed alongside it.

Column	Type	Business Meaning
`MATERIAL_12NC`	TEXT	Primary key. 14-digit Signify product code.
`BRAND`	TEXT	Top-level Signify brand (e.g. Philips).
`COMMERCIAL_BRAND`	TEXT	Sub-brand or commercial label within the top-level brand.
`PRODUCT_CLASS`	TEXT	Commercial product classification. 100% NULL in this extract — never populated by source.
`PRODUCT_SEGMENT`	TEXT	Mid-level product segment within product class.
`PRODUCT_TYPE`	TEXT	Product technology type (e.g. LED Lamp, Gear).
`IND_SECTOR`	TEXT	SAP industry sector code.
`PRODUCT_FAMILY`	TEXT	Commercial product family (internal family codes). Hue/WiZ/LED Lamps & Luminaires are business units, not families — resolve those via the business-unit dimension, not this column.
`PRODUCT_GROUP_1`	TEXT	SAP product group level 1.
`PRODUCT_GROUP_2`	TEXT	SAP product group level 2. Near-entirely NULL (99.4%).
`TECHNOLOGY`	TEXT	Light-source technology (LED, HID, Fluorescent).
`VOLTAGE`	TEXT	Nominal operating voltage class. 100% NULL — never populated.
`COUNTRY_OF_ORIGIN`	TEXT	ISO 2-letter manufacturing country. 100% NULL — never populated.
`PACKAGING_TYPE`	TEXT	Primary packaging format. 100% NULL — never populated.
`EAN_UPC`	TEXT	EAN-13/UPC-A barcode.
`BASE_UOM`	TEXT	SAP base unit of measure. Every quantity column across CURATED facts uses this unit.
`WEIGHT_UOM`	TEXT	Unit of measure for the two weight columns (always KG in this dataset).
`GROSS_WEIGHT_KG`	FLOAT	Gross weight normalized to kilograms (source carries mixed KG/G/LB/MG units — this is the converted figure; unmapped units resolve to NULL rather than an assumed KG).
`NET_WEIGHT_KG`	FLOAT	Net weight normalized to kilograms, same conversion rule.
`COMMERCIAL_RELEASE_DATE`	DATE	First commercial release date.
`DELETION_DATE`	DATE	SAP deletion/discontinuation date. Populated = marked for deletion.
`LED_INDICATOR_CODE`	TEXT	Signify LED indicator attribute.
`INNOVATION_FLAG`	TEXT	New-launch/innovation flag. 100% NULL — never populated.
`SALES_OFF_FLAG`	TEXT	TRUE when flagged end-of-life/no-new-orders.
`CROSS_PLANT_STATUS`	TEXT	SAP cross-plant material status code.
`SUCCESSOR_12NC`	TEXT	Replacement product code when this material is being phased out.
`LIFECYCLE_PHASE`	TEXT	Product lifecycle phase (Introduction/Growth/Maturity/Decline/Phase-Out). Resolved from plant-level data — see the conflict-count column.
`SUPPLY_CLASS`	TEXT	Replenishment strategy code (e.g. MTS=Make-to-Stock, MTO=Make-to-Order). Same plant-level resolution caveat as lifecycle phase.
`ATO_ETO_FLAG`	TEXT	Assemble-to-Order / Engineer-to-Order indicator.
`MANUFACTURER_NAME`	TEXT	Primary manufacturer name.
`SUPPLY_CLASS_HAS_PLANT_CONFLICT`	BOOLEAN	TRUE when different plants report different supply_class values for this material — the published value is the modal (most common) one and may not apply everywhere.
`SUPPLY_CLASS_DISTINCT_COUNT`	NUMBER	Number of distinct supply_class values observed across plants for this material.
`LIFECYCLE_PHASE_HAS_PLANT_CONFLICT`	BOOLEAN	Same conflict flag for lifecycle_phase.
`LIFECYCLE_PHASE_DISTINCT_COUNT`	NUMBER	Number of distinct lifecycle_phase values across plants.
`PRODUCT_HIERARCHY`	TEXT	SAP product hierarchy code (PRDHA).
`MATERIAL_GROUP`	TEXT	SAP material group (MATKL).
`MATERIAL_TYPE`	TEXT	SAP material type (e.g. FERT=Finished Good).
`MANUFACTURER_ID`	TEXT	Signify internal manufacturer ID. 100% NULL — never populated.
`IS_ACTIVE`	BOOLEAN	TRUE when the material has no deletion date and is not flagged sales-off.
`IS_AGGREGATE_KEY`	BOOLEAN	TRUE when material_12nc is a non-canonical planning aggregate key (e.g. a planning-hierarchy placeholder) rather than a real 14-digit product code. These have no inventory records — filter them out before joining to inventory.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

DIM_PLANT — 9 columns

Grain: One row per distinct plant code across all source tables (37 rows)
Primary key: PLANT_CODE
Source: Union of plant codes appearing in the material-planning, inventory, delivery, slow-moving, and purchasing sources

Plant / distribution-center master. Country is derived from the plant-code prefix (e.g. 10US01 → United States) since no source carries a clean authoritative country name field.

Column	Type	Business Meaning
`PLANT_CODE`	TEXT	Primary key. SAP plant code, the plant-level join key across every CURATED fact table.
`PLANT_NAME`	TEXT	Descriptive plant/DC name.
`COUNTRY`	TEXT	Country name, derived from the plant-code prefix.
`COUNTRY_DERIVED`	TEXT	Identical to COUNTRY — both derived the same way.
`COUNTRY_ISO`	TEXT	2-letter ISO country code, extracted from the plant-code prefix.
`PLANT_TYPE`	TEXT	Plant role classification (DC/Manufacturing/Warehouse). 100% NULL — never populated.
`IS_US_PLANT`	BOOLEAN	TRUE when the plant code carries the 'US' prefix. The canonical way to scope any query to the US market.
`REGION`	TEXT	Geographic region. Always 'AMS' (Americas) in this POC.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

DIM_SUPPLIER_SOURCE — 9 columns

Grain: One row per distinct supplier per ID namespace (186 rows)
Primary key: SUPPLIER_SK
Source: Union of purchasing, delivery-reliability, and demand-planning supplier/manufacturer references

Suppliers are tracked in three separate, non-overlapping ID namespaces that must never be cross-joined without a business-provided mapping: PO_LT (the purchasing-org vendor, from purchase orders), DRM (the delivery-reliability external supplier), and DEMAND_MANUFACTURER (the manufacturer referenced in demand-planning data). The same raw numeric ID can mean a different real-world supplier in each namespace — always filter by SUPPLIER_ID_SPACE before joining.

Column	Type	Business Meaning
`SUPPLIER_SK`	TEXT	Primary key. Deterministic hash of (supplier_id_space, source_supplier_id).
`SUPPLIER_ID_SPACE`	TEXT	Namespace: PO_LT / DRM / DEMAND_MANUFACTURER. Always pair with source_supplier_id — the same raw ID can collide across namespaces.
`SOURCE_SUPPLIER_ID`	TEXT	Raw supplier identifier within its namespace. Not unique without supplier_id_space.
`SUPPLIER_NAME`	TEXT	Supplier/manufacturer display name.
`SUPPLIER_COUNTRY`	TEXT	Supplier country, when available from source.
`VENDOR_CATEGORY`	TEXT	Business category of the supplier (e.g. Internal, Third-Party).
`SOURCE_SYSTEM`	TEXT	Authoritative source system for this record.
`MAPPING_STATUS`	TEXT	Status of the enterprise-wide supplier mapping. Currently 'ASSUMED' pending a business-provided enterprise supplier master.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

BRIDGE_SUPPLIER_ENTERPRISE_MAPPING — 8 columns

Grain: One row per DC↔manufacturer supplier link (35 rows)
Primary key: SUPPLIER_SK
Source: Direct DC-supplier-to-manufacturer links recorded in the purchasing source itself

Maps a purchasing-org (DC) vendor to its upstream manufacturer (EP) enterprise identity, where multiple DC vendor records can resolve to the same manufacturer. This is a direct link recorded in the source purchase-order extract, not a fuzzy/probabilistic match, so confidence is 1.0 for every row — still labeled 'ASSUMED' pending a business-provided enterprise supplier master, matching the convention used by the supplier dimension itself.

Column	Type	Business Meaning
`SUPPLIER_SK`	TEXT	FK → DIM_SUPPLIER_SOURCE. The source-system-specific supplier being mapped.
`ENTERPRISE_SUPPLIER_ID`	TEXT	Canonical enterprise-wide supplier identifier.
`MAPPING_STATUS`	TEXT	Status of this mapping (ASSUMED / BUSINESS_CONFIRMED / UNMATCHED).
`MAPPING_CONFIDENCE`	FLOAT	Confidence score 0–1. 1.0 here since it's a direct link recorded in source, not inferred.
`MAPPING_SOURCE`	TEXT	How the mapping was established.
`VALID_FROM`	DATE	Effective-from date. 100% NULL — never populated.
`VALID_TO`	DATE	Effective-through date. 100% NULL — never populated.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

DIM_CUSTOMER — 5 columns

Grain: One row per unique customer ID, ship-to or sold-to (2,144 rows)
Primary key: CUSTOMER_ID
Source: Ship-to and sold-to party references from the delivery-reliability source

A single customer_id may act as both a ship-to (delivery recipient) and sold-to (billing/ordering) party — this table holds one row per unique ID regardless of role. Country is only available for ship-to rows (the source does not carry sold-to country).

Column	Type	Business Meaning
`CUSTOMER_ID`	TEXT	Primary key. SAP ship-to or sold-to party number.
`CUSTOMER_NAME`	TEXT	Customer display name.
`COUNTRY`	TEXT	Customer country (ship-to geography only; NULL for sold-to-only IDs).
`COUNTRY_HAS_CONFLICT`	BOOLEAN	TRUE when this customer_id appears with more than one non-null country across source rows — when TRUE, the country value is unreliable; use the delivery fact's row-level ship-to country instead.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

DIM_BUSINESS_UNIT — 8 columns

Grain: One row per Business Group × Business Unit × MAG combination (64 rows)
Primary key: BU_ID
Source: Union of the business-hierarchy attributes from the delivery-reliability and demand-planning sources

The full-grain business hierarchy table. Do not use this table directly for BU-level grouping — one BU_CODE spans multiple MAG entries here, so grouping by bu_code on this table double-counts. Use DIM_BUSINESS_UNIT_BYCODE (below) for a clean one-row-per-BU lookup instead.

Column	Type	Business Meaning
`BU_ID`	TEXT	Primary key. Composite: bg_key\|bu_code\|mag_code.
`BG_KEY`	TEXT	Business Group code (e.g. 9003). The highest level in the Signify commercial hierarchy.
`BG_NAME`	TEXT	Business Group display name (e.g. Consumer). May be NULL — use DIM_BUSINESS_UNIT_BYCODE.bg_name which resolves cross-BU.
`BU_CODE`	TEXT	Business Unit code (e.g. 9540 = Hue Connected). Not unique in this table.
`BU_NAME`	TEXT	Business Unit display name. May be NULL here — see DIM_BUSINESS_UNIT_BYCODE for the resolved fallback.
`MAG_CODE`	TEXT	Market and Application Group code — the sub-level below BU. Available on the slow-moving and forecast-performance facts but NOT on the demand-forecast fact.
`MAG_NAME`	TEXT	MAG display name.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

DIM_BUSINESS_UNIT_BYCODE — 4 columns

Grain: One row per unique Business Unit code (4 rows: 0118, 2596, 9540, 9668)
Primary key: BU_CODE
Source: CURATED-to-CURATED view collapsed from DIM_BUSINESS_UNIT — not sourced from STAGING

The clean, one-row-per-BU lookup used by every semantic view for BU-level rollups without fan-out. BU name is resolved across all rows sharing that BU; BG name is resolved across all BUs sharing that BG, so every code reliably gets a readable label (falling back to the code itself only if truly no name exists anywhere in source). All four BUs in this POC sit under one Business Group — BG_KEY='BS9003' (Consumer): 0118=LED Lamps & Luminaires, 2596=Modular, 9540=Hue Connected, 9668=WiZ Connected.

Column	Type	Business Meaning
`BU_CODE`	TEXT	Primary key. Business Unit code.
`BU_NAME`	TEXT	Business Unit display name, resolved cross-source.
`BG_KEY`	TEXT	Business Group code.
`BG_NAME`	TEXT	Business Group display name, resolved cross-source.

DIM_DELAY_REASON — 5 columns

Grain: One row per distinct delay/miss reason code (14 rows)
Primary key: REASON_CODE
Source: Union of the DRM miss-bucket categories and SAP rejection reason codes

Delivery-miss reason reference. Combines three sources: the DRM system's coded miss-bucket categories (the primary, most-populated source), SAP rejection reason codes, and SOR (schedule-of-record) root-cause codes — the latter carry no description text in source, so reason_description is deliberately left NULL rather than invented for those rows.

Column	Type	Business Meaning
`REASON_CODE`	TEXT	Primary key. Delay/miss reason code.
`REASON_DESCRIPTION`	TEXT	Human-readable description. NULL for SOR-sourced codes (no description exists in source).
`REASON_GROUP`	TEXT	Higher-level category (e.g. Supply, Commercial, Logistics, 'Sales: rejection').
`REASON_SOURCE`	TEXT	Which system assigned the code: DRM_MISS_BUCKET, REASON_FOR_REJECTION, or SOR_ROOT_CAUSE.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

BRIDGE_MATERIAL_PLANT — 26 columns

Grain: One row per material-plant combination (56,060 rows)
Primary key: MATERIAL_12NC, PLANT_CODE
Source: Material-planning (MRP) master data

Replenishment and MRP parameters per material-plant pair: safety stock, reorder point, planned lead time, procurement type, and ABC classification.

Column	Type	Business Meaning
`MATERIAL_12NC`	TEXT	Part of composite PK. FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	Part of composite PK. FK → DIM_PLANT.
`MRP_TYPE`	TEXT	SAP MRP type controlling how replenishment is triggered (e.g. PD=MRP-driven, VB=reorder-point).
`MRP_CONTROLLER`	TEXT	The planner/planning group responsible for this material at this plant.
`PLANNED_DELIVERY_DAYS`	NUMBER	Planned delivery time (PLIFZ) in calendar days — PO creation to goods receipt.
`GR_PROCESSING_DAYS`	NUMBER	Goods-receipt processing time in calendar days after physical receipt.
`TOTAL_INBOUND_LT_DAYS`	NUMBER	planned_delivery_days + gr_processing_days. NULL only when both components are NULL.
`SAFETY_STOCK`	NUMBER	Safety stock quantity. Sparse — populated on only ~1.1% of rows (55,418 of 56,060 are zero/NULL); any safety-stock analysis covers a sliver of the portfolio.
`REORDER_POINT`	NUMBER	Reorder point quantity. Also sparse.
`MOQ`	NUMBER	Minimum order quantity from vendor/MRP planning.
`MIN_ORDER_QTY`	NUMBER	Alternative minimum-order-quantity field; may overlap with MOQ depending on configuration.
`MAX_ORDER_QTY`	NUMBER	Maximum order quantity per replenishment cycle.
`PROCUREMENT_TYPE`	TEXT	E=external procurement, F=in-house production, X=both.
`SPECIAL_PROCUREMENT_KEY`	NUMBER	Refines procurement type (e.g. subcontracting, consignment, stock transfer).
`MATERIAL_STATUS`	TEXT	Plant-level material status controlling which transactions are allowed.
`MATERIAL_STATUS_VALID_FROM`	DATE	Effective date of the plant-level status.
`PURCHASING_GROUP`	TEXT	The buyer/buying team responsible for procuring this material at this plant.
`TOTAL_REPLENISHMENT_LT_DAYS`	NUMBER	Total replenishment lead time from SAP (may differ from total_inbound_lt_days when SAP stores an overridden total directly).
`LOT_SIZE_PROCEDURE`	TEXT	MRP lot-sizing procedure code (e.g. EX=exact quantity, FX=fixed lot, MB=monthly).
`COSTING_LOT_SIZE_QTY`	NUMBER	Costing lot size, used for unit-cost calculations, not MRP order sizing.
`APO_RELEVANT`	BOOLEAN	TRUE = active in an SAP APO/IBP integration model.
`APO_RELEVANT_FLAG_RAW`	NUMBER	Raw SAP code before mapping to TRUE/FALSE, preserved for lineage.
`PROFIT_CENTER`	TEXT	SAP profit center for this material+plant.
`ABC_CLASSIFICATION`	TEXT	A=high-value/high-volume, B=medium, C=low. 40.1% NULL — not run/maintained for all material-plants.
`APO_TYPE`	TEXT	APO/IBP planning type or model category.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

BRIDGE_SUPPLIER_MATERIAL — 7 columns

Grain: One row per manufacturer-material-plant combination (4,855 rows)
Primary key: SUPPLIER_SK, MATERIAL_12NC, PLANT_CODE
Source: Manufacturer key per material from the demand-planning source

Which manufacturer supplies which material, at which plant. Plant is required in the grain — 57 materials have genuinely different manufacturers per plant, and omitting plant caused 315 phantom relationships when this was first built material-only.

Column	Type	Business Meaning
`SUPPLIER_SK`	TEXT	Part of composite PK. FK → DIM_SUPPLIER_SOURCE (DEMAND_MANUFACTURER namespace).
`SOURCE_SUPPLIER_ID`	TEXT	Raw manufacturer key from the demand-planning source.
`MATERIAL_12NC`	TEXT	Part of composite PK. FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	Part of composite PK. Required in the grain — 57 materials have different manufacturers per plant.
`SUPPLIER_NAME`	TEXT	Manufacturer display name.
`IS_FIRST_IN_CHAIN`	BOOLEAN	Always TRUE in this table — indicates the primary (first-tier) manufacturer. Reserved for future multi-tier relationships.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

Facts — Inventory, Demand & Purchasing (6 tables)

FACT_INVENTORY — 20 columns

Grain: Material × plant × storage location × stock type × fiscal period, repeating monthly snapshot (1,655,310 rows)
Primary key: INVENTORY_SK
Source: STG_INVENTORY

On-hand and goods-in-transit stock position. REPEATING MONTHLY SNAPSHOT — always filter to a specific fiscal period, or a query silently sums every month ever loaded. GIT rows carry stock_type IS NULL by definition. Value columns are worth a specific caveat: 38% of rows carry an individually-negative value even though quantity is positive — investigated live (2026-08-20) and confirmed to be inter-stock-type value reclassification pairs (e.g. one material showing -€386M on one stock type exactly offset by +€386M on another), the same accounting pattern already accepted for signed goods-movement quantities. Only trust the value summed across all stock types for a material+plant+period; a single stock-type slice can carry this reclass noise.

Column	Type	Business Meaning
`INVENTORY_SK`	TEXT	Surrogate PK: hash of material+plant+storage_location+stock_type+fiscal_period.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	FK → DIM_PLANT.
`STORAGE_LOCATION`	TEXT	SAP storage location within the plant. NULL when the snapshot has no location split.
`STOCK_TYPE`	TEXT	A=Unrestricted, Q=Quality Inspection, D=Blocked/Damaged, plus other SAP categories. GIT rows carry NULL. Always filter stock_type='A' for available-stock analysis.
`STOCK_TYPE_DESC`	TEXT	Human-readable stock-type description.
`IS_UNRESTRICTED`	BOOLEAN	TRUE when stock_type='A'. Quick filter for available-stock queries.
`MRP_CONTROLLER`	TEXT	MRP controller carried through from the inventory source.
`FISCAL_PERIOD_CODE`	TEXT	FK → DIM_FISCAL_PERIOD.
`CALENDAR_MONTH_KEY`	TEXT	ISO date string derived from fiscal_period_code.
`FISCAL_YEAR`	NUMBER	Calendar year of the snapshot.
`FISCAL_PERIOD_NUM`	NUMBER	Calendar month number (1–12).
`ON_HAND_QTY`	NUMBER	On-hand quantity at this storage_location+stock_type grain. SUM across stock types = total physical stock.
`GIT_QTY`	NUMBER	Goods-in-transit quantity.
`TOTAL_QTY_INCL_GIT`	NUMBER	on_hand_qty + git_qty.
`ON_HAND_VALUE_EUR`	NUMBER	On-hand value. See the reclassification caveat above — trust only when summed across all stock types.
`GIT_VALUE_EUR`	NUMBER	Goods-in-transit value.
`TOTAL_VALUE_EUR`	NUMBER	Total value including GIT. Same caveat as on_hand_value_eur.
`ON_HAND_PCT_OF_TOTAL`	NUMBER	on_hand_qty / total_qty_incl_git. 93.1% NULL — deterministically NULL whenever total stock is 0 (most storage/stock-type combos in the repeating snapshot carry no stock at all).
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_DEMAND_FORECAST — 29 columns

Grain: Material × plant × month × key figure × source file × channel × planning account × ORU × MRU (246,026 rows)
Primary key: DEMAND_SK
Source: STG_DEMAND_FORECAST

Holds both the forward demand forecast (source_file=DEMAND_QXP) and actual sales (source_file=SALES_VIPP) in one long/pivoted table — never sum across both without filtering source_file. Similarly, forecast_quantity carries both plan and actuals split by category (plan vs. ACT_CY/ACT_CY-1) — never sum without filtering category, or plan and actuals get silently added together. material_12nc mixes real 14-digit product codes with non-canonical planning-aggregate keys that have no inventory records (95.99% of total demand volume comes from just 73 aggregate keys) — check is_aggregate_key before joining to inventory.

Column	Type	Business Meaning
`DEMAND_SK`	TEXT	Surrogate PK, unique per full planning grain.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL. May be a real 14-digit code or a planning aggregate key.
`IS_AGGREGATE_KEY`	BOOLEAN	TRUE when material_12nc is a non-canonical planning aggregate key. Filter FALSE for shortage/inventory analysis.
`PLANT_CODE`	TEXT	FK → DIM_PLANT.
`CALENDAR_MONTH_KEY`	TEXT	FK → DIM_FISCAL_PERIOD.
`FISCAL_PERIOD_CODE`	TEXT	YYYYPPP, resolved from calendar_month_key.
`FISCAL_YEAR`	NUMBER	Calendar year of the planning period.
`KEY_FIGURE`	TEXT	APO/IBP key figure name (e.g. Qty, Value, DP_FCST).
`CATEGORY`	TEXT	ACT_CY = current-year actuals, ACT_CY-1 = prior-year actuals, NULL = pure forecast rows.
`MEASURE_TYPE`	TEXT	QUANTITY or VALUE_EUR. Never sum the two together.
`FORECAST_QUANTITY`	NUMBER	Planned OR actual quantity — split by category. Negative values (0.17% of rows) are expected and correct: sales returns/credit reversals, never on plan rows.
`FORECAST_VALUE_EUR`	NUMBER	Planned or actual value in EUR.
`CURRENCY`	TEXT	Always EUR in this dataset.
`ORU_KEY`	NUMBER	Operating Reporting Unit key.
`MRU_KEY`	TEXT	Market Reporting Unit key, pairs with ORU_KEY.
`BG_CODE`	TEXT	Business Group code carried from the forecast source.
`BU_CODE`	TEXT	Business Unit code. Join to the BU dimension for names.
`CHANNEL_GROUP`	TEXT	Sales channel group. Part of the grain.
`PLANNING_ACCOUNT`	TEXT	Customer/account planning key. Joins to the delivery and sales-order facts.
`SOURCE_SUPPLIER_ID`	TEXT	APO manufacturer key. Joins to the supplier dimension (DEMAND_MANUFACTURER namespace).
`ATO_ETO_FLAG`	TEXT	Configure-to-order flag.
`LIFECYCLE_PHASE`	TEXT	Lifecycle phase at the time of the forecast snapshot.
`SUPPLY_CLASS`	TEXT	Supply classification at snapshot time.
`SEGMENT`	TEXT	Market segment attribute.
`MRP_CONTROLLER`	TEXT	MRP controller from the demand source.
`FIN_POST_IND`	TEXT	Finance posting indicator.
`COMMERCIAL_SUB_TEAM`	TEXT	Commercial sub-team.
`SOURCE_FILE`	TEXT	DEMAND_QXP = forecast export, SALES_VIPP = actual sales. Always filter on this.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_FORECAST_PERFORMANCE — 33 columns

Grain: Material × planning account × fiscal period × distribution channel (62,509 rows)
Primary key: FCST_PERF_SK
Source: STG_FORECAST_BIAS

Forecast bias/accuracy tracking with successive forecast snapshots at N, N-1, N-2, N-3 lags compared to actuals. Distribution channel is part of the grain — without it, 1,527 (material, account, period) combinations collided across different channels.

Column	Type	Business Meaning
`FCST_PERF_SK`	TEXT	Surrogate PK.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL.
`PLANNING_ACCOUNT`	TEXT	Customer/account planning key. Part of the grain.
`DISTRIBUTION_CHANNEL`	TEXT	SAP distribution channel code. Part of the grain.
`DISTRIBUTION_CHANNEL_DESC`	TEXT	Channel description.
`SCM_AREA_CODE`	TEXT	Country-level SCM area code. This whole fact is already 100% US (single value).
`FISCAL_PERIOD_CODE`	TEXT	FK → DIM_FISCAL_PERIOD.
`CALENDAR_MONTH_KEY`	TEXT	ISO date string.
`FISCAL_YEAR`	NUMBER	Calendar year.
`FISCAL_PERIOD_NUM`	NUMBER	Calendar month number.
`BG`	TEXT	Business Group code, sourced from FC_BIAS as-is.
`BU`	TEXT	Business Unit code, sourced from FC_BIAS as-is.
`MAG`	TEXT	Market and Application Group code — available here, unlike the demand-forecast fact.
`PLANNED_QTY_N3`	NUMBER	Forecast quantity locked 3 months before the reporting period.
`PLANNED_QTY_N2`	NUMBER	Forecast quantity locked 2 months before.
`PLANNED_QTY_N1`	NUMBER	Forecast quantity locked 1 month before.
`PLANNED_QTY_N`	NUMBER	Forecast quantity for the current reporting period.
`ACTUAL_DELIVERED_QTY`	NUMBER	Actual delivered quantity. Denominator for accuracy/bias below.
`ABS_DEV_N3`	NUMBER	Absolute deviation between planned_qty_n3 and actual (unsigned).
`ABS_DEV_N2`	NUMBER	Absolute deviation at N-2 lag.
`ABS_DEV_N1`	NUMBER	Absolute deviation at N-1 lag.
`ABS_DEV_N0`	NUMBER	Absolute deviation for the current period, \|planned_qty_n - actual\|.
`PLANNED_QXP_N3`	NUMBER	Monetary planned value (Qty×Price) at N-3 lag.
`PLANNED_QXP_N2`	NUMBER	Monetary planned value at N-2 lag.
`PLANNED_QXP_N1`	NUMBER	Monetary planned value at N-1 lag.
`PLANNED_QXP_N`	NUMBER	Monetary planned value at current period.
`CF_ABS_DEV_N3`	NUMBER	Customer-facing absolute deviation at N-3 lag.
`CF_ABS_DEV_N2`	NUMBER	Customer-facing absolute deviation at N-2 lag.
`CF_ABS_DEV_N1`	NUMBER	Customer-facing absolute deviation at N-1 lag.
`CF_ABS_DEV_N0`	NUMBER	Customer-facing absolute deviation, current period.
`FORECAST_ACCURACY_N`	NUMBER	1 - ABS(abs_dev_n0)/actual_delivered_qty. NULL when actual=0. Denominator changed from planned_qty_n specifically to fix 65.5% of rows going NULL when plan was zero.
`FORECAST_BIAS_PCT`	NUMBER	(planned_qty_n - actual)/actual. Positive = over-forecast, negative = under-forecast. Computed from signed difference, not the unsigned deviation field.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_PURCHASE_ORDER_LINE — 40 columns

Grain: One row per PO number + item (12,063 rows)
Primary key: PO_SK
Source: STG_PO

Purchase-order line with ordered/received/open quantity, value, actual-vs-planned lead time, and vendor. Two distinct vendor identities are exposed side by side: DC (distribution-center) supplier — the actual PO counterparty — and EP (enterprise/manufacturer) supplier — the upstream manufacturer. Positive supply_delay_days = late.

Column	Type	Business Meaning
`PO_SK`	TEXT	Surrogate PK, referenced by the schedule, confirmation, and goods-movement facts.
`PO_NUMBER`	TEXT	SAP purchase order number.
`PO_ITEM`	TEXT	SAP PO line item number.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	Receiving plant. FK → DIM_PLANT.
`SUPPLIER_SK`	TEXT	FK → DIM_SUPPLIER_SOURCE, keyed on the DC vendor.
`DC_SUPPLIER_ID`	TEXT	SAP vendor number of the DC supplier, the formal PO counterparty.
`DC_SUPPLIER_NAME`	TEXT	DC supplier name. Resolves to the literal text 'Unknown Vendor' (not NULL) for the 16 lines with no source supplier ID.
`DC_SUPPLIER_COUNTRY`	TEXT	DC supplier country.
`EP_SUPPLIER_ID`	TEXT	SAP vendor number of the EP (manufacturer) supplier upstream of the DC. NULL = domestic replenishment; populated = import with a linked external order — this NULL carries real meaning, don't fill it.
`EP_SUPPLIER_NAME`	TEXT	EP supplier name.
`EP_SUPPLIER_COUNTRY`	TEXT	EP supplier country.
`VENDOR_CATEGORY`	TEXT	Business category of the DC supplier.
`PO_TYPE`	TEXT	SAP PO document type (e.g. NB=standard PO, LP=scheduling agreement).
`BG_CODE`	TEXT	Business Group code from the PO source.
`BU_CODE`	TEXT	Business Unit code from the PO source.
`MRU_KEY`	TEXT	Matches mru_key in the demand-forecast fact.
`ORU_KEY`	NUMBER	Matches oru_key in the demand-forecast fact.
`ORDERED_QTY`	NUMBER	Original ordered quantity.
`ORDERED_UOM`	TEXT	Unit of measure for ordered_qty.
`RECEIVED_QTY`	NUMBER	Quantity received to date.
`OPEN_QTY`	NUMBER	GREATEST(ordered_qty - COALESCE(received_qty,0), 0).
`OVER_DELIVERED_QTY`	NUMBER	GREATEST(received_qty - ordered_qty, 0) — the over-delivery signal that flooring open_qty at 0 would otherwise destroy.
`IS_OVER_DELIVERED`	BOOLEAN	TRUE when received_qty > ordered_qty.
`PO_VALUE_EUR`	NUMBER	PO line value in EUR.
`REPORTING_CURRENCY`	TEXT	Always 'EUR' — pairs with po_value_eur so it's never mistaken for the document currency.
`PO_VALUE_LOCAL`	NUMBER	PO line value in its original document currency.
`CURRENCY`	TEXT	Original document currency of po_value_local.
`PO_RELEASE_DATE`	DATE	PO release/creation date.
`PLANNED_DELIVERY_DATE`	DATE	Originally planned delivery date.
`CONFIRMED_DELIVERY_DATE`	DATE	Vendor-confirmed expected delivery date.
`REQUESTED_PDT_DAYS`	NUMBER	Requested lead time in days, agreed at PO creation.
`ACTUAL_GR_DATE`	DATE	Actual goods-receipt date.
`SUPPLY_DELAY_DAYS`	NUMBER	actual GR minus requested date. Positive = late, negative = early — this is the basis for on-time/vendor performance metrics.
`ACTUAL_TOTAL_LT_DAYS`	NUMBER	Actual elapsed days, PO release to goods receipt.
`MARC_PLANNED_LT_DAYS`	NUMBER	Planned lead time from the material-plant master data.
`IS_DELAYED`	BOOLEAN	TRUE when supply_delay_days > 0.
`IS_SIGNIFICANTLY_DELAYED`	BOOLEAN	TRUE when supply_delay_days > 14 days.
`IS_FULLY_RECEIVED`	BOOLEAN	TRUE when received_qty ≥ ordered_qty.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_PO_SCHEDULE — 14 columns

Grain: One row per PO/item/schedule line — SAP EKET (12,063 rows)
Primary key: PO_SCHEDULE_SK
Source: STG_PO

SAP delivery-schedule (call-off) lines for standard purchase orders, with scheduled delivery date and quantity.

Column	Type	Business Meaning
`PO_SCHEDULE_SK`	TEXT	Surrogate PK: hash of po_number\|po_item\|schedule_line.
`PO_NUMBER`	TEXT	FK → FACT_PURCHASE_ORDER_LINE.
`PO_ITEM`	TEXT	Combine with po_number for the natural EKET key.
`SCHEDULE_LINE`	TEXT	SAP schedule line number (ETENR) — distinguishes multiple delivery dates within one PO item.
`PO_SK`	TEXT	FK → FACT_PURCHASE_ORDER_LINE.
`MATERIAL_12NC`	TEXT	Resolved from the PO-line fact. FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	Resolved from the PO-line fact. FK → DIM_PLANT.
`SUPPLIER_SK`	TEXT	FK → DIM_SUPPLIER_SOURCE, resolved from the PO-line fact.
`SOURCE_SUPPLIER_ID`	TEXT	DC supplier ID, resolved from the PO-line fact.
`SCHEDULED_DELIVERY_DATE`	DATE	Expected delivery date for this schedule line.
`SCHEDULED_QTY`	NUMBER	Quantity scheduled on this line.
`RECEIVED_QTY_TO_SCHEDULE`	NUMBER	Quantity already received against this specific schedule line.
`OPEN_SCHEDULE_QTY`	NUMBER	GREATEST(scheduled_qty - received_qty_to_schedule, 0).
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_PO_CONFIRMATION — 15 columns

Grain: One row per PO/item/confirmation sequence/type — SAP EKES (20,687 rows)
Primary key: PO_CONFIRM_SK
Source: STG_PO

Vendor confirmations (order acknowledgements, shipping notifications) against a PO, tracked separately from actual receipts to measure vendor responsiveness.

Column	Type	Business Meaning
`PO_CONFIRM_SK`	TEXT	Surrogate PK. Includes confirmation_type in the grain — AB and LA types share the same (PO, item, seq) and would otherwise merge incorrectly.
`PO_NUMBER`	TEXT	FK → FACT_PURCHASE_ORDER_LINE.
`PO_ITEM`	TEXT	Combine with po_number to join to the PO-line fact.
`CONFIRMATION_SEQ`	TEXT	SAP confirmation sequence — a vendor may submit multiple confirmations per PO+item.
`CONFIRMATION_TYPE`	TEXT	AB = order acknowledgement (vendor accepts), LA = shipping notification (vendor dispatched). Part of the PK.
`CONFIRMATION_CREATION_INDICATOR`	TEXT	SAP status/creation indicator, not the AB/LA type. Kept for lineage only.
`MATERIAL_12NC`	TEXT	Resolved from the PO-line fact.
`PLANT_CODE`	TEXT	Resolved from the PO-line fact.
`SUPPLIER_SK`	TEXT	FK → DIM_SUPPLIER_SOURCE (DC supplier).
`SOURCE_SUPPLIER_ID`	TEXT	DC supplier ID, resolved from the PO-line fact.
`CONFIRMED_DELIVERY_DATE`	DATE	Vendor-confirmed expected delivery date.
`CONFIRMED_QTY`	NUMBER	Total quantity vendor confirmed. Gross before any reduction.
`REDUCED_QTY`	NUMBER	Quantity already consumed/reduced against this confirmation.
`OPEN_CONFIRMED_QTY`	NUMBER	GREATEST(confirmed_qty - COALESCE(reduced_qty,0), 0).
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_GOODS_MOVEMENT — 18 columns

Grain: One row per PO/item/accounting-doc/fiscal-year/doc-line/transaction-type/movement-type — SAP EKBE (14,777 rows)
Primary key: GOODS_MOVEMENT_SK
Source: STG_PO history extract

Goods-receipt history against purchase orders (movement types 101=GR, 102=reversal, 107/108=blocked-stock in/out, 161/162=return/reversal). Quantities and values are signed by the SAP debit/credit indicator, not by a hardcoded movement-type list — this correctly makes returns/reversals reduce the running total.

Column	Type	Business Meaning
`GOODS_MOVEMENT_SK`	TEXT	Surrogate PK. Full SAP composite key required — PO+item+doc alone is not unique across fiscal years/doc lines.
`PO_NUMBER`	TEXT	FK → FACT_PURCHASE_ORDER_LINE.
`PO_ITEM`	TEXT	Combine with po_number to join to the PO-line fact.
`PO_SK`	TEXT	FK → FACT_PURCHASE_ORDER_LINE.
`MATERIAL_12NC`	TEXT	Resolved from the PO-line fact.
`PLANT_CODE`	TEXT	Resolved from the PO-line fact.
`POSTING_DATE`	DATE	Accounting posting date — the date the movement was financially recorded.
`MOVEMENT_TYPE`	NUMBER	101=GR against PO, 102=GR reversal, 107=GR to blocked stock, 108=GR from blocked stock, 161=return to supplier, 162=return reversal.
`FISCAL_YEAR_SAP`	TEXT	SAP fiscal year of the accounting document. Part of the PK — the doc number is only unique within a fiscal year.
`DOCUMENT_LINE`	TEXT	SAP accounting document line item. Part of the PK.
`TRANSACTION_TYPE`	NUMBER	SAP transaction/event type, refining the movement type. Part of the PK.
`GR_QTY_NET`	NUMBER	Net goods-receipt quantity, signed (S=positive receipt, H=negative return/reversal). Sign comes from the debit/credit field, not the movement-type code.
`GR_QTY_RAW`	NUMBER	Raw unsigned quantity before sign application.
`GR_VALUE_LC`	NUMBER	Goods-movement value in local currency, signed by debit/credit.
`CURRENCY`	TEXT	Local/document currency of the posting.
`BATCH`	TEXT	SAP batch number, when batch management is active.
`DEBIT_CREDIT`	TEXT	S=debit (stock increases), H=credit (stock decreases). Drives the signed quantity/value columns above.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_INBOUND_SUPPLY_EVENT — 12 columns

Grain: One row per open inbound supply signal per PO/item (150 rows)
Primary key: INBOUND_EVENT_SK
Source: Derived from FACT_PO_SCHEDULE and FACT_PO_CONFIRMATION

Unifies two different signal types into one inbound-supply timeline: EKET delivery-schedule lines (standard POs with no open vendor confirmation) and EKES vendor confirmations (which override the schedule line for the same PO+item when they carry open quantity). Feeds the supply-position projection below. Past-due dates are rolled forward to the data-anchor month so overdue open supply isn't dropped from the position calculation, and eligible quantity is capped pro-rata to the PO line's own open_qty to prevent stale/over-consumed confirmations from overstating future supply.

Column	Type	Business Meaning
`INBOUND_EVENT_SK`	TEXT	Surrogate PK.
`PO_NUMBER`	TEXT	FK → FACT_PURCHASE_ORDER_LINE.
`PO_ITEM`	TEXT	Combine with po_number to join to the PO-line fact.
`CONFIRMATION_SEQ`	TEXT	EKES confirmation sequence. NULL for EKET-sourced signals.
`SIGNAL_SOURCE`	TEXT	EKET = delivery schedule line, EKES = vendor confirmation (overrides EKET when it carries open quantity).
`CONFIRMATION_TYPE`	TEXT	AB/LA, propagated from the confirmation fact. NULL for EKET signals.
`EXPECTED_DELIVERY_DATE`	DATE	Expected arrival date. Past-due dates are rolled forward to the anchor month.
`ELIGIBLE_QTY`	NUMBER	Open inbound quantity eligible to cover demand. Capped pro-rata to the PO line's own open_qty.
`MATERIAL_12NC`	TEXT	Resolved from the PO-line fact.
`PLANT_CODE`	TEXT	Resolved from the PO-line fact.
`SUPPLIER_SK`	TEXT	FK → DIM_SUPPLIER_SOURCE (DC supplier).
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

Facts — Delivery, Slow-Moving & Supply-Risk (5 tables)

FACT_DELIVERY — 67 columns

Grain: One row per sales-order-line/delivery-line (756,429 rows)
Primary key: DELIVERY_SK
Source: STG_DRM

The DRM (Delivery Reliability Management) scoring fact. drm_pct=100 for a reliable line, 0 for a missed line; is_drm_miss=1 when missed. Carries a bank of 17 MISSED_* reason-bucket flags — a specific reason always wins over that tier's generic 'other' bucket when both apply. DRM scoring excludes rejected/cancelled lines and lines with an unmeasurable delay figure. rejection_reason_code being NULL is a valid, normal state (no rejection occurred), not a data gap.

Column	Type	Business Meaning
`DELIVERY_SK`	TEXT	Surrogate PK.
`SO_SK`	TEXT	FK → FACT_SALES_ORDER_LINE.
`SALES_ORDER_NUMBER`	TEXT	Sales order document number.
`SALES_ORDER_ITEM`	TEXT	Sales order line item.
`DELIVERY_NUMBER`	TEXT	Delivery document number. 3.16% NULL — undelivered lines have no delivery document yet.
`DELIVERY_ITEM`	TEXT	Delivery line item.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	Fulfilling plant. FK → DIM_PLANT.
`CUSTOMER_ID`	TEXT	Ship-to customer. FK → DIM_CUSTOMER.
`SHIP_TO_COUNTRY`	TEXT	Ship-to country on this delivery line.
`SOLD_TO_ID`	TEXT	Sold-to (billing) party.
`BU_CODE`	TEXT	Business Unit code. FK → DIM_BUSINESS_UNIT_BYCODE.
`BG_KEY`	TEXT	Business Group code from DRM.
`AG_CODE`	TEXT	Article group code.
`AG_NAME`	TEXT	Article group name.
`CAG_CODE`	NUMBER	Customer Account Group code.
`BANNER`	TEXT	Retail banner/key account.
`PLANNING_ACCOUNT`	TEXT	Joins to the demand-forecast fact for demand-to-order reconciliation.
`MRP_CONTROLLER`	TEXT	MRP controller from the DRM source.
`SO_CREATION_DATE`	DATE	Sales order creation date.
`CUSTOMER_REQUESTED_DATE`	DATE	Customer requested/agreed delivery-promise date.
`BEST_CONFIRMED_DELIVERY_DATE`	DATE	Best confirmed delivery date.
`ACTUAL_DELIVERY_DATE`	DATE	Actual delivery date. 3.22% NULL — not yet delivered.
`ACTUAL_GOODS_ISSUE_DATE`	DATE	Actual goods-issue date. 3.24% NULL — not yet issued.
`APPOINTMENT_DATE`	DATE	Appointment date. 95.3% NULL — only appointment-based deliveries carry this.
`MAX_APPOINTMENT_DATE`	DATE	Latest scheduled appointment date.
`DELIVERY_AGREED_QTY`	NUMBER	Agreed (promised) delivery quantity.
`DELIVERY_ACTUAL_QTY`	NUMBER	Actual delivered quantity.
`PRIMARY_DELAY_REASON`	TEXT	Primary miss reason, derived from the MISSED_* buckets. NULL for on-time lines (95.7% NULL, by design).
`DELAY_REASON_GROUP`	TEXT	Reason group (Supply/Sales/Outbound/Other).
`ER_REASON_CODE`	TEXT	Exception-report root cause. 100% NULL — empty in source, structural gap.
`SOR_REASON_CODE`	TEXT	Schedule-of-record root cause. 99.98% NULL — empty in source.
`DELAY_DAYS_ADP`	NUMBER	Agreed-delivery-promise delay in days on a miss. Positive = late.
`IS_ADP_UNMEASURABLE`	BOOLEAN	TRUE when the delay figure can't be measured (missing dates). Exclude when averaging.
`ITEM_CATEG`	TEXT	Sales order item category.
`DRM_PCT`	NUMBER	DRM reliability percentage for the line. 100=reliable, 0=missed.
`DRM_TOTAL_LINES`	NUMBER	Lines eligible for DRM scoring (always 1 per row).
`DRM_SCORED_LINES`	NUMBER	1 if scored as reliable, 0 if missed — the additive numerator for DRM%.
`SOR_TOTAL_LINES`	NUMBER	Same eligibility pattern for SOR scoring.
`SOR_SCORED_LINES`	NUMBER	SOR success numerator.
`ER_TOTAL_LINES`	NUMBER	Same eligibility pattern for ER scoring.
`ER_SCORED_LINES`	NUMBER	ER success numerator.
`IS_DRM_MISS`	NUMBER	1 when the line was missed. NULL = excluded from scoring (unmeasurable/rejected) — this NULL is the eligibility contract, not a gap.
`DRM_FLAG`	NUMBER	1 when the line is DRM-reliable.
`DRM_EXCLUSION_REASON`	TEXT	Populated only for lines excluded from scoring.
`DRM_SEGMENT_NOTE`	TEXT	Annotation for ADP-unmeasurable/rejected lines that are now scored rather than excluded (2026-08-17 pipeline change).
`SOR_FLAG`	NUMBER	1 when SOR data exists for this line.
`ER_FLAG`	NUMBER	1 when ER data exists for this line.
`MISSED_NO_STOCK, MISSED_SUPPLY_REASON_FOR_REJ, MISSED_SUPPLY_DELIVERY_BLOCK, MISSED_DEL_GROUP, MISSED_SUPPLY_OTHERS`	NUMBER	Supply-tier miss-reason indicators (5 columns).
`MISSED_CREDIT_BLOCK, MISSED_SALES_DELIVERY_BLOCK, MISSED_SALES_REASON_FOR_REJ, MISSED_INCOMPLETE_ORDER, MISSED_INCONSISTENT_DATA, MISSED_SALES_EX_CREDIT_MISC`	NUMBER	Sales-tier miss-reason indicators (6 columns).
`MISSED_TRANSPORT, MISSED_WAREHOUSE, MISSED_APPOINTMENT_CALLS, MISSED_WRONG_DAY, MISSED_OUTBOUND_EX_WAREHOUSE_TRANSPORT`	NUMBER	Outbound-tier miss-reason indicators (5 columns).
`MISSED_MISC`	NUMBER	Catch-all miss indicator.
`MISSED_UNATTRIBUTED`	NUMBER	1 when the line is a DRM miss and none of the 17 coded flags is set.
`COMPLETE_DELIVERY_REQUIRED_FLAG`	TEXT	Whether the order was configured to require complete delivery (a requirement setting, not proof of fulfillment).
`IS_DELIVERY_COMPLETE`	BOOLEAN	Actual-outcome flag: TRUE when delivered qty met/exceeded agreed qty. Use this, not the flag above, for 'was it actually fulfilled complete'.
`APPOINTMENT_FLAG`	TEXT	Appointment flag.
`CARRIER`	TEXT	Carrier. 100% NULL — structural capability gap, never populated in this extract.
`SHIPMENT_TMS_ID`	TEXT	Transport-management shipment ID. 100% NULL — same structural gap.
`LAST_CARRIER_STATUS`	TEXT	Last carrier status. 100% NULL — same structural gap.
`REJECTION_REASON_CODE`	TEXT	SAP rejection code. NULL when not rejected (a normal state, not a gap).
`REJECTION_REASON_LABEL`	TEXT	Display-ready rejection reason.
`REJECTION_REASON_DESC`	TEXT	Rejection reason description.
`DELIVERY_BLOCK_CODE`	TEXT	Delivery block code. NULL = not blocked (a normal state).
`DELIVERY_BLOCK_DESC`	TEXT	Delivery block description.
`OVERALL_DELIVERY_STATUS`	TEXT	Derived status: 'C' (completed) when an actual delivery date exists.
`REJECTION_STATUS`	TEXT	Derived status: 'REJECTED' when a rejection reason is present.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_SALES_ORDER_LINE — 36 columns

Grain: One row per sales order line (756,429 rows)
Primary key: SO_SK
Source: STG_DRM

Order-status, open-quantity, OTIF, and fill-rate view of the same underlying DRM data as FACT_DELIVERY, at sales-order-line grain. open_qty respects rejection/cancellation/block status — a rejected line's open quantity is 0, not the unfulfilled remainder.

Column	Type	Business Meaning
`SO_SK`	TEXT	Surrogate PK.
`SALES_ORDER_NUMBER`	TEXT	Sales order document number.
`SALES_ORDER_ITEM`	TEXT	Sales order line item.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	Fulfilling plant. FK → DIM_PLANT.
`CUSTOMER_ID`	TEXT	Ship-to customer.
`SOLD_TO_ID`	TEXT	Sold-to party.
`BU_CODE`	TEXT	Business Unit code.
`BG_KEY`	TEXT	Business Group code.
`PLANNING_ACCOUNT`	TEXT	Customer/account planning key.
`AG_CODE, AG_NAME`	TEXT	Article group code and name.
`CAG_CODE`	NUMBER	Customer Account Group code.
`BANNER`	TEXT	Retail banner/key account.
`SO_CREATION_DATE`	DATE	Sales order creation date.
`CUSTOMER_REQUESTED_DATE`	DATE	Customer requested delivery date.
`BEST_CONFIRMED_DELIVERY_DATE`	DATE	Best confirmed delivery date.
`ACTUAL_DELIVERY_DATE`	DATE	Actual delivery date. NULL = not yet delivered.
`ACTUAL_GOODS_ISSUE_DATE`	DATE	Actual goods-issue date.
`APPOINTMENT_DATE, MAX_APPOINTMENT_DATE`	DATE	Appointment window dates, conditional (only appointment-based deliveries).
`ORDERED_QTY`	NUMBER	Ordered quantity on the SO line.
`DELIVERED_QTY`	NUMBER	Delivered quantity.
`OPEN_QTY`	NUMBER	Open (undelivered) quantity; 0 for rejected/complete/cancelled lines.
`ORDER_STATUS`	TEXT	OPEN / PARTIAL / COMPLETE / REJECTED / BLOCKED.
`DELIVERY_BLOCK_CODE, DELIVERY_BLOCK_DESC`	TEXT	Delivery block on the SO line.
`REJECTION_REASON_DESC`	TEXT	Rejection reason on the SO line.
`DELIVERY_DELAY_DAYS`	NUMBER	Actual delivery minus requested date. Positive = late. This is SO-line lateness — for miss-lateness use FACT_DELIVERY.delay_days_adp.
`IS_ON_TIME`	BOOLEAN	TRUE when delivered on/before requested date. NULL when not yet delivered — never defaulted to FALSE, which would wrongly assert 'late'.
`PRIMARY_DELAY_REASON, DELAY_REASON_GROUP`	TEXT	Dominant miss reason/group on the SO line.
`IS_DRM_MISS`	NUMBER	1 when this SO line was a DRM miss.
`PRIMARY_ER_REASON_CODE`	TEXT	100% NULL — structural gap, ER data empty in source.
`PRIMARY_SOR_REASON_CODE`	TEXT	99.98% NULL — SOR data empty in source.
`COMPLETE_DELIVERY_REQUIRED_FLAG`	TEXT	Configuration flag, not an outcome measure.
`IS_DELIVERY_COMPLETE`	BOOLEAN	Actual-outcome fulfillment flag.
`APPOINTMENT_FLAG`	TEXT	Appointment flag.
`PRIMARY_CARRIER, LAST_CARRIER_STATUS`	TEXT	100% NULL — structural carrier-data gap.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

FACT_SLOW_MOVING_INVENTORY — 32 columns

Grain: Material × plant × calendar month, repeating monthly snapshot (72,110 rows)
Primary key: SMI_SK
Source: STG_SLOW_MOVING

Aging-bucket inventory view (0-6mo / 7-12mo / >12mo) for slow-moving and dead-stock analysis. Holds both Active and No-demand rows since a 2026-08-18 pipeline fix. Client definition: healthy = 0-6 months, slow-moving = 6+ months (7-12M + >12M combined). GIT_QTY here uses a NULL-when-absent convention, unlike FACT_INVENTORY's 0-when-absent — a known, documented inconsistency, not yet resolved.

Column	Type	Business Meaning
`SMI_SK`	TEXT	Surrogate PK.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	FK → DIM_PLANT.
`CALENDAR_MONTH_YYYYMM`	TEXT	Source period format.
`CALENDAR_MONTH_KEY`	TEXT	ISO date string. Part of the PK.
`FISCAL_PERIOD_CODE`	TEXT	Resolved via DIM_FISCAL_PERIOD.
`FISCAL_YEAR`	NUMBER	Calendar year of the snapshot.
`ON_HAND_QTY`	NUMBER	Total on-hand at month-end. Includes all aging buckets.
`ON_HAND_VALUE`	NUMBER	Total on-hand stock value at month-end.
`GIT_QTY, GIT_VALUE`	NUMBER	Goods-in-transit quantity/value. 71.3% NULL — different convention from FACT_INVENTORY's 0-default, a known open item.
`QTY_0_6M`	NUMBER	Stock aged 0-6 months — the healthy-stock definition.
`QTY_7_12M`	NUMBER	Stock aged 7-12 months.
`QTY_GT_12M`	NUMBER	Stock aged >12 months — the primary dead-stock indicator.
`VALUE_7_12M, VALUE_GT_12M`	NUMBER	Value of the 7-12M and >12M aging buckets.
`SLOW_MO_QTY, SLOW_MO_VALUE`	NUMBER	QTY_7_12M + QTY_GT_12M and its value — the 'slow-moving = 6+ months' client definition.
`HEALTHY_QTY`	NUMBER	Alias of QTY_0_6M — the 'healthy' definition.
`CURRENT_QTY, PRIOR_MONTH_QTY`	NUMBER	Current vs. prior-month quantity. NULL on a material's first appearance (no prior month exists) — structural, not a defect.
`QTY_MOM_DELTA`	NUMBER	Month-over-month change. Negative = stock fell (correct by design).
`CURRENT_VALUE, PRIOR_MONTH_VALUE, VALUE_MOM_DELTA`	NUMBER	Same current/prior/delta pattern for value.
`SMOS_MONTH_COUNT`	NUMBER	Consecutive months flagged slow-moving. 96.67% NULL — only measured for a subset; currently a NULL here defaults is_chronic_slow_mover to FALSE, an open question (does NULL mean zero months or not-measured?).
`BG_CODE, BU_CODE`	NUMBER/TEXT	Business group/unit code from the slow-moving source.
`MAG_CODE, MAG_NAME`	TEXT	Market and Application Group code/name.
`LIFECYCLE_PHASE`	TEXT	Product lifecycle phase at snapshot time.
`ORU_KEY, MRU_KEY`	NUMBER/TEXT	Operating/Market Reporting Unit keys.
`SOURCE_MANUFACTURER_ID`	NUMBER	Manufacturer ID from the slow-moving source.
`MRP_CONTROLLER`	TEXT	MRP controller from the slow-moving source.
`DEMAND_FLAG`	TEXT	Raw source demand signal: 'no demand' or 'demand'.
`DEMAND_CATEGORY`	TEXT	'No demand this period' or 'Active' — the reporting label derived from demand_flag.
`HAS_SLOW_STOCK_GT_12M`	BOOLEAN	TRUE when qty_gt_12m > 0.
`IS_CHRONIC_SLOW_MOVER`	BOOLEAN	TRUE when smos_month_count ≥ 3. Use only for chronic/persistent questions, not the default slow-moving reading.
`SLOW_MO_VALUE_PCT`	NUMBER	Slow-moving value as % of total on-hand value, pre-computed at row grain.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

SUPPLY_POSITION_BY_DATE — 12 columns

Grain: One row per material-plant-date event (supply arrival, demand due-date, or the anchor snapshot date) (24,205 rows)
Primary key: MATERIAL_12NC, PLANT_CODE, POSITION_DATE
Source: Derived from FACT_INVENTORY, FACT_INBOUND_SUPPLY_EVENT, and FACT_SALES_ORDER_LINE

The projected supply-vs-demand balance timeline: for each material+plant, a row exists for the inventory-snapshot anchor date, each inbound-event date, and each open-demand due date. Past-due dates are rolled forward to the anchor month so overdue supply/demand isn't dropped.

Column	Type	Business Meaning
`MATERIAL_12NC`	TEXT	Part of composite PK. FK → DIM_MATERIAL.
`PLANT_CODE`	TEXT	Part of composite PK. FK → DIM_PLANT.
`POSITION_DATE`	DATE	Part of composite PK. The date this row's supply-or-demand event occurs.
`ON_HAND_QTY`	NUMBER	On-hand (unrestricted) stock at the latest snapshot, carried forward at the anchor row — the opening stock for the position calculation.
`INVENTORY_RECORD_AVAILABLE`	BOOLEAN	TRUE when an actual inventory snapshot exists. FALSE means on_hand_qty is assumed 0 (no snapshot exists) — distinguishes genuine zero-stock from missing data.
`ELIGIBLE_INBOUND_QTY`	NUMBER	Inbound supply quantity arriving on this position_date.
`OPEN_DEMAND_QTY`	NUMBER	Total open order demand due on this position_date.
`CUMULATIVE_INBOUND_QTY`	NUMBER	Running total of eligible inbound supply up to and including this date.
`CUMULATIVE_DEMAND_QTY`	NUMBER	Running total of open demand due up to and including this date.
`CUMULATIVE_SUPPLY_AVAILABLE`	NUMBER	on_hand_qty + cumulative_inbound_qty. The key metric used to check whether demand due by a given date can be covered.
`PROJECTED_BALANCE_QTY`	NUMBER	cumulative_supply_available − cumulative_demand_qty. Negative = projected shortage — the table's entire purpose.
`_LOAD_TS`	TIMESTAMP_LTZ	Pipeline load timestamp.

SUPPLY_ORDER_IMPACT — 32 columns

Grain: One row per open sales-order line, anchored to a snapshot month (4,934 rows)
Primary key: SO_SK
Source: Derived from FACT_SALES_ORDER_LINE joined to inventory, inbound supply, and demand-forecast

Joins each open sales-order line to its available inventory, inbound POs, and 3-month forecast to identify at-risk orders. Not currently exposed via any of the four SEMANTIC v2 views — the delivery-outcome fields below being ~99.6% NULL likely reflects that this table holds mostly-open (undelivered) lines, but that hasn't been formally confirmed with the business. Many columns are explicitly flagged non-additive (repeated per order line, describing the shared material+plant position) — ORDER_QTY_AT_RISK is the one column safe to SUM across order lines.

Column	Type	Business Meaning
`DATA_ANCHOR_MONTH`	TEXT	The snapshot month this row's calculations are anchored to.
`SO_SK`	TEXT	FK → FACT_SALES_ORDER_LINE.
`SALES_ORDER_NUMBER, SALES_ORDER_ITEM`	TEXT	Sales order identifiers.
`MATERIAL_12NC`	TEXT	FK → DIM_MATERIAL.
`BRAND, PRODUCT_FAMILY, PRODUCT_CLASS, LIFECYCLE_PHASE, SUCCESSOR_12NC`	TEXT/NUMBER	Denormalized material attributes.
`PLANT_CODE, PLANT_NAME, PLANT_COUNTRY`	TEXT	Denormalized plant attributes.
`CUSTOMER_ID, SOLD_TO_ID, CUSTOMER_NAME, CUSTOMER_COUNTRY`	TEXT	Denormalized customer attributes.
`BU_CODE, BU_NAME, BANNER, AG_CODE, AG_NAME, CAG_CODE`	TEXT/NUMBER	Denormalized commercial hierarchy attributes.
`SO_CREATION_DATE, CUSTOMER_REQUESTED_DATE, BEST_CONFIRMED_DELIVERY_DATE, ACTUAL_DELIVERY_DATE`	DATE	Order date milestones.
`ORDERED_QTY, DELIVERED_QTY, OPEN_QTY`	NUMBER	Order quantity position.
`ORDER_STATUS, DELIVERY_BLOCK_CODE`	TEXT	Order status and block code.
`DELIVERY_DELAY_DAYS, IS_ON_TIME, IS_DRM_MISS`	NUMBER/BOOLEAN	Delivery-outcome fields — ~99.6% NULL (open-line population).
`PRIMARY_DELAY_REASON, DELAY_REASON_GROUP`	TEXT	Delay reason, when known.
`PRIMARY_ER_REASON_CODE, PRIMARY_SOR_REASON_CODE, PRIMARY_CARRIER, LAST_CARRIER_STATUS`	TEXT	100% NULL — same structural gaps as the delivery fact.
`AVAILABLE_INVENTORY_QTY`	NUMBER	⚠ Non-additive. Unrestricted on-hand for this material+plant, repeated per order line.
`GOODS_IN_TRANSIT_QTY, TOTAL_STOCK_QTY`	NUMBER	⚠ Non-additive. GIT and total stock for this material+plant.
`INVENTORY_AS_OF_PERIOD`	TEXT	Fiscal period of the inventory snapshot used.
`INVENTORY_RECORD_AVAILABLE`	BOOLEAN	TRUE when a real inventory record exists.
`TOTAL_INBOUND_QTY, ON_TIME_INBOUND_QTY, DELAYED_INBOUND_QTY`	NUMBER	⚠ Non-additive. Open PO quantity for this material+plant, split by expected timing.
`NEAREST_INBOUND_DATE`	DATE	Earliest confirmed delivery date across open POs for this material+plant.
`MAX_SUPPLY_DELAY_DAYS, OPEN_PO_COUNT`	NUMBER	⚠ Non-additive. Worst-case delay and open PO count for this material+plant.
`SUPPLY_AVAILABLE_BY_DATE`	NUMBER	On-hand + inbound arriving by the customer's required date, via an ASOF join to SUPPLY_POSITION_BY_DATE.
`FORECAST_QTY_3M, FORECAST_MONTHS_COVERED`	NUMBER	⚠ Non-additive. 3-month forward forecast; months_covered flags when the '3-month' window is really narrower.
`YTD_ACTUAL_SALES_QTY`	NUMBER	⚠ Non-additive. Year-to-date actual sales for this material+plant. 76 rows carry a negative value — held pending a business decision on risk-scoring treatment.
`TOTAL_LINES_SCORED, DRM_ON_TIME_LINES, DRM_PCT_COMPUTED, AVG_DRM_PCT, HISTORICAL_AVG_DELAY_DAYS`	NUMBER	⚠ Non-additive. Historical DRM performance for this material+plant over a 6-month window.
`MOST_COMMON_DELAY_REASON, MOST_COMMON_DELAY_GROUP, MOST_COMMON_ER_REASON`	TEXT	⚠ Non-additive. Modal delay reason across historical DRM lines.
`PLANNED_DELIVERY_DAYS, SAFETY_STOCK, MOQ, MRP_TYPE, PROCUREMENT_TYPE`	NUMBER/TEXT	Denormalized planning parameters from the material-plant bridge.
`CUMULATIVE_OPEN_QTY`	NUMBER	FIFO cumulative open demand up to this order, ordered by requested date then creation date.
`CUMULATIVE_SHORTAGE_QTY`	NUMBER	GREATEST(cumulative_open_qty - supply_available_by_date, 0). Do not sum across orders.
`ORDER_QTY_AT_RISK`	NUMBER	Marginal shortage attributed to this specific order. Safe to sum — correctly totals to the exact material+plant shortage.
`SUPPLY_DELAY_FLAG, INVENTORY_SHORTAGE_FLAG, ORDER_AT_RISK_FLAG`	BOOLEAN	Risk flags — supply delayed / on-hand insufficient / total supply insufficient by required date.
`IS_SLOW_MOVER, SMOS_MONTH_COUNT, SLOW_STOCK_GT_12M_QTY`	BOOLEAN/NUMBER	Slow-moving context, joined from the latest slow-moving snapshot for this material+plant.

AGGREGATEPre-Computed KPI Tables

Thirteen tables, pre-joined and pre-computed from CURATED for reporting, dashboards, and fast natural-language query response.

AGG_FORECAST_PERFORMANCE_MATERIAL_MONTHLY — 21 columns, 13,390 rows

Grain: One row per material per fiscal month. Collapses planning-account and distribution-channel detail so most questions can be answered at material level directly.
Row count (live): 13,390
Built from (CURATED): FACT_FORECAST_PERFORMANCE

Material-level rollup of forecast planning quantities, actuals, bias, and MAD (Mean Absolute Deviation) at each forecast horizon.

Column	Type	Business Meaning / Formula
`MATERIAL_12NC`	TEXT	Material identifier (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month for the period.
`FISCAL_YEAR`	NUMBER	Fiscal year of the period.
`BU_CODE`	TEXT	Business unit, picked via MAX since a material rarely spans more than one BU.
`PLANNED_QTY_N3_SUM`	NUMBER	Total forecast quantity planned at the N-3 horizon.
`PLANNED_QTY_N2_SUM`	NUMBER	Total forecast quantity planned at the N-2 horizon.
`PLANNED_QTY_N1_SUM`	NUMBER	Total forecast quantity planned at the N-1 horizon.
`PLANNED_QTY_N0_SUM`	NUMBER	Total forecast quantity at the N-0 (most current/last-look) horizon.
`ACTUAL_DELIVERED_QTY_SUM`	NUMBER	Total actual delivered quantity.
`BIAS_RATIO_N3`	NUMBER	(planned N-3 − actual) / actual. Signed, expressed as a 0–1 fraction. NULL when actuals are 0.
`BIAS_RATIO_N2`	NUMBER	Same formula at N-2.
`BIAS_RATIO_N1`	NUMBER	Same formula at N-1.
`BIAS_RATIO_N0`	NUMBER	Same formula at N-0.
`MAD_N3`	NUMBER	Mean Absolute Deviation at N-3 — average of per-line absolute forecast error.
`MAD_N2`	NUMBER	Mean Absolute Deviation at N-2.
`MAD_N1`	NUMBER	Mean Absolute Deviation at N-1.
`MAD_N0`	NUMBER	Mean Absolute Deviation at N-0.
`VOLUME_GAP_QTY_N3`	NUMBER	Absolute volume gap at N-3 = planned − actual (units, not a ratio).
`ROW_COUNT`	NUMBER	Number of underlying line-level rows collapsed into this material/month row.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_FORECAST_PERFORMANCE_BU_MONTHLY — 12 columns, 36 rows

Grain: One row per business unit per fiscal month. Sourced independently from the material-level table — MAPE must average each line's own ratio, not divide summed totals, or large- and small-volume materials mixing within a BU/period understates error.
Row count (live): 36
Built from (CURATED): FACT_FORECAST_PERFORMANCE

BU-level forecast error (MAPE) at each horizon.

Column	Type	Business Meaning / Formula
`BU_CODE`	TEXT	Business unit code (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`FISCAL_YEAR`	NUMBER	Fiscal year.
`MATERIAL_COUNT`	NUMBER	Distinct materials contributing to the BU/period.
`MAPE_N3`	NUMBER	Mean Absolute Percentage Error at N-3 = average, over rows with actual>0, of \|deviation\|/actual. Zero-actual rows excluded from both numerator and denominator.
`MAPE_N2`	NUMBER	Same MAPE formula at N-2.
`MAPE_N1`	NUMBER	Same MAPE formula at N-1.
`MAPE_N0`	NUMBER	Same MAPE formula at N-0.
`MAPE_SCORED_ROWS`	NUMBER	Rows with actual > 0 that contributed to the MAPE calculation — a coverage indicator.
`TOTAL_ROWS`	NUMBER	Total rows for the BU/period, including zero-actual rows excluded from MAPE.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_SLOW_MOVING_PRODUCT_FAMILY_MONTHLY — 16 columns, 753 rows

Grain: One row per product family × plant × fiscal month.
Row count (live): 753
Built from (CURATED): FACT_SLOW_MOVING_INVENTORY, DIM_MATERIAL

Product-family-level slow-moving and dead-stock rollup.

Column	Type	Business Meaning / Formula
`PRODUCT_FAMILY`	TEXT	Product family from the material dimension; unmatched materials roll up to a literal 'Unknown' bucket rather than being dropped.
`PLANT_CODE`	TEXT	Plant code (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`MATERIAL_COUNT`	NUMBER	Distinct materials contributing.
`ON_HAND_QTY_SUM`	NUMBER	Total on-hand quantity.
`ON_HAND_VALUE_SUM`	NUMBER	Total on-hand value.
`QTY_7_12M_SUM`	NUMBER	Total quantity aged 7–12 months.
`QTY_GT_12M_SUM`	NUMBER	Total quantity aged >12 months.
`VALUE_7_12M_SUM`	NUMBER	Value aged 7–12 months.
`VALUE_GT_12M_SUM`	NUMBER	Value aged >12 months.
`SLOW_MO_QTY_SUM`	NUMBER	Total quantity classified slow-moving.
`SLOW_MO_VALUE_SUM`	NUMBER	Total value classified slow-moving.
`SLOW_MO_VALUE_RATIO`	NUMBER	Slow-moving value ÷ total on-hand value. NULL when on-hand value is 0.
`CHRONIC_SLOW_MOVER_COUNT`	NUMBER	Count of material/plant rows flagged chronically slow-moving.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_DELIVERY_PERFORMANCE_PLANT_MONTHLY — 14 columns, 190 rows

Grain: One row per plant × fiscal month, bucketed by the month of the customer's requested delivery date. Includes a synthetic anchor-month row for plants with currently-open overdue lines but zero requests this month.
Row count (live): 190
Built from (CURATED): FACT_DELIVERY, DIM_FISCAL_PERIOD, FACT_DEMAND_FORECAST (anchor-month reference only)

Plant-level delivery reliability (DRM) and OTIF scorecard.

Column	Type	Business Meaning / Formula
`PLANT_CODE`	TEXT	Plant code (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period, keyed to requested-date month (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`TOTAL_ELIGIBLE_LINES`	NUMBER	Delivery lines with a valid on-time (DRM) flag.
`ON_TIME_LINES`	NUMBER	Lines delivered on time per the DRM flag.
`DRM_PCT`	NUMBER	100 × on-time lines ÷ total eligible lines.
`OTIF_ELIGIBLE_LINES`	NUMBER	Lines eligible for OTIF scoring (requires a goods-issue date, agreed qty>0, non-null actual qty).
`OTIF_LINES`	NUMBER	Lines both on time and in full.
`OTIF_PCT`	NUMBER	100 × OTIF lines ÷ OTIF-eligible lines.
`MISSED_NO_STOCK_COUNT`	NUMBER	Missed deliveries attributed to no-stock-available.
`MISSED_CREDIT_BLOCK_COUNT`	NUMBER	Missed deliveries attributed to a credit block.
`OPEN_OVERDUE_COUNT`	NUMBER	Currently-open lines whose requested date is already past, attached only to the anchor-month row (NULL elsewhere by design — a 'right now' snapshot, not a monthly time series).
`AVG_DAYS_OVERDUE`	NUMBER	Average days late for lines that shipped after the requested date, for that plant/month.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_DELIVERY_PERFORMANCE_CUSTOMER_MONTHLY — 7 columns, 12,011 rows

Grain: One row per customer (ship-to) × fiscal month. Powers top-N-customers-by-misses / Pareto analysis.
Row count (live): 12,011
Built from (CURATED): FACT_DELIVERY, DIM_FISCAL_PERIOD

Customer-level delivery-miss rate.

Column	Type	Business Meaning / Formula
`CUSTOMER_ID`	TEXT	Customer (ship-to) identifier (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`TOTAL_ELIGIBLE_LINES`	NUMBER	Lines with a valid DRM flag for this customer/month.
`MISSED_LINES`	NUMBER	Lines that missed the on-time commitment.
`MISS_RATE_PCT`	NUMBER	100 × missed lines ÷ total eligible lines.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_DELIVERY_PERFORMANCE_PRODUCT_FAMILY_MONTHLY — 8 columns, 62 rows

Grain: One row per product family × fiscal month.
Row count (live): 62
Built from (CURATED): FACT_DELIVERY, DIM_MATERIAL, DIM_FISCAL_PERIOD

Product-family-level delivery reliability with the dominant miss reason.

Column	Type	Business Meaning / Formula
`PRODUCT_FAMILY`	TEXT	Product family; unmatched materials roll up to 'Unknown'.
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`TOTAL_ELIGIBLE_LINES`	NUMBER	Lines with a valid DRM flag for the family/month.
`ON_TIME_LINES`	NUMBER	On-time lines.
`DRM_PCT`	NUMBER	100 × on-time lines ÷ total eligible lines.
`DOMINANT_MISS_REASON`	TEXT	The single delay-reason category with the most missed lines for that family/month (ties broken deterministically).
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_VENDOR_PO_PERFORMANCE_MONTHLY — 14 columns, 111 rows

Grain: One row per supplier × fiscal month, bucketed by actual goods-receipt month (falls back to planned delivery date for not-yet-received lines).
Row count (live): 111
Built from (CURATED): FACT_PURCHASE_ORDER_LINE, DIM_FISCAL_PERIOD

Vendor purchase-order performance scorecard.

Column	Type	Business Meaning / Formula
`DC_SUPPLIER_ID`	TEXT	Supplier identifier (grain key).
`DC_SUPPLIER_NAME`	TEXT	Supplier name.
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`PO_LINE_COUNT`	NUMBER	Total PO lines for the supplier/month (received or still open).
`RECEIVED_LINE_COUNT`	NUMBER	Lines actually received — the denominator basis below, so not-yet-received lines never silently count as on-time.
`ON_TIME_LINE_COUNT`	NUMBER	Received lines not flagged delayed.
`PO_ON_TIME_PCT`	NUMBER	100 × on-time received lines ÷ received lines.
`OTIF_LINE_COUNT`	NUMBER	Received lines both on time and in full.
`VENDOR_OTIF_PCT`	NUMBER	100 × OTIF lines ÷ received lines.
`AVG_DAYS_LATE`	NUMBER	Average days late, computed only over lines with a positive supply delay.
`LATE_PO_COUNT`	NUMBER	Lines flagged significantly delayed.
`LEAD_TIME_VARIABILITY`	FLOAT	STDDEV of (actual − planned lead time) across the supplier's lines that month. NULL when fewer than 2 lines exist.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_MATERIAL_PLANT_PLANNING_PARAMS — 17 columns, 56,060 rows

Grain: One row per material × plant — a current snapshot, not a monthly time series. A parameter-health table reflecting the latest known state.
Row count (live): 56,060
Built from (CURATED): BRIDGE_MATERIAL_PLANT, FACT_DEMAND_FORECAST, FACT_INVENTORY, FACT_PURCHASE_ORDER_LINE

MRP-parameter calibration health, comparing configured safety stock/reorder point against a recalculated 'should-be' figure.

Column	Type	Business Meaning / Formula
`MATERIAL_12NC`	TEXT	Material identifier (grain key).
`PLANT_CODE`	TEXT	Plant code (grain key).
`MRP_TYPE`	TEXT	MRP type/strategy code.
`MRP_CONTROLLER`	TEXT	Responsible planner/controller.
`SAFETY_STOCK`	NUMBER	Configured safety-stock quantity.
`REORDER_POINT`	NUMBER	Configured reorder-point quantity.
`MAX_ORDER_QTY`	NUMBER	Configured maximum order/stock quantity.
`TOTAL_INBOUND_LT_DAYS`	NUMBER	Total inbound lead time in days.
`AVG_DAILY_DEMAND`	NUMBER	Sum of demand quantity over the trailing 3 known fiscal months ÷ 90 days, anchored to the latest known period.
`CALCULATED_ROP`	NUMBER	'Should-be' reorder point = avg daily demand × total inbound lead-time days.
`IS_ROP_CALIBRATED`	BOOLEAN	TRUE if reorder_point ≥ calculated_rop. NULL when avg daily demand can't be computed.
`HAS_DEMAND_ACTIVITY_12MO`	BOOLEAN	Any demand-forecast activity in the trailing 12 months.
`HAS_INVENTORY_ACTIVITY_12MO`	BOOLEAN	Any on-hand or GIT inventory in the trailing 12 months.
`HAS_PO_ACTIVITY_12MO`	BOOLEAN	Any purchase-order activity in the trailing 12 months.
`IS_ACTIVE`	BOOLEAN	TRUE if any of the three activity flags above is TRUE.
`MRP_EXCEPTION_FLAG`	BOOLEAN	Proxy exception flag (not a genuine SAP MRP exception code — none exists in source): TRUE if current unrestricted stock is below safety stock OR above max order quantity.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_INVENTORY_TURNOVER_MATERIAL_MONTHLY — 9 columns, 364,002 rows

Grain: One row per material × plant × fiscal month.
Row count (live): 364,002
Built from (CURATED): FACT_DEMAND_FORECAST (actual-sales rows only), FACT_INVENTORY, BRIDGE_MATERIAL_PLANT

Inventory turnover ratio, sales value against inventory value.

Column	Type	Business Meaning / Formula
`MATERIAL_12NC`	TEXT	Material identifier (grain key).
`PLANT_CODE`	TEXT	Plant code (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`ACTUAL_SALES_VALUE_EUR`	NUMBER	Actual sales value in EUR (from actual-sales category rows).
`ON_HAND_VALUE_EUR`	NUMBER	Total on-hand inventory value in EUR.
`TURNOVER_RATIO_MONTHLY`	NUMBER	Actual sales value ÷ on-hand value. NULL when on-hand value is below 1 EUR (avoids extreme ratios from near-zero residual stock).
`MRP_TYPE`	TEXT	MRP type, joined for reference/filtering.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_SUPPLY_HEALTH_MATERIAL_PLANT_MONTHLY — 23 columns, 364,388 rows

Grain: One row per material × plant × fiscal month. The cross-domain workhorse table — a material/plant/period is included as long as it has at least one of an inventory, demand, or inbound-PO signal.
Row count (live): 364,388
Built from (CURATED): FACT_INVENTORY, FACT_DEMAND_FORECAST, FACT_INBOUND_SUPPLY_EVENT, DIM_FISCAL_PERIOD, BRIDGE_MATERIAL_PLANT

Combines inventory, demand, planning parameters, and inbound-PO signal into a single row so stockout/safety-stock/days-of-supply questions don't require multi-way joins.

Column	Type	Business Meaning / Formula
`MATERIAL_12NC`	TEXT	Material identifier (grain key).
`PLANT_CODE`	TEXT	Plant code (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`UNRESTRICTED_QTY`	NUMBER	On-hand quantity that is unrestricted (freely usable).
`RESTRICTED_QTY`	NUMBER	On-hand quantity that is restricted (e.g. quality-hold).
`RESTRICTED_RATIO`	NUMBER	Restricted qty ÷ (unrestricted + restricted qty).
`GIT_QTY`	NUMBER	Goods-in-transit quantity.
`TOTAL_VALUE_EUR`	NUMBER	Total inventory value across all stock statuses.
`DEMAND_QTY_MONTHLY`	NUMBER	Total forecast demand quantity for the material/plant/month.
`DOS_DAYS`	NUMBER	Days of Supply = unrestricted qty ÷ (monthly demand ÷ 30). NULL when there's no demand that month.
`SAFETY_STOCK`	NUMBER	Configured safety-stock quantity.
`REORDER_POINT`	NUMBER	Configured reorder-point quantity.
`MAX_ORDER_QTY`	NUMBER	Configured maximum order/stock quantity.
`IS_BELOW_SAFETY_STOCK`	BOOLEAN	TRUE if unrestricted qty is below safety stock. NULL when safety stock isn't configured.
`IS_ABOVE_MAX_STOCK`	BOOLEAN	TRUE if unrestricted qty exceeds the maximum order quantity. NULL when max order qty isn't configured.
`EXCESS_QTY_OVER_MAX`	NUMBER	Stock held above the configured maximum, floored at 0.
`IS_STOCKOUT_RISK`	BOOLEAN	TRUE when unrestricted qty is 0 AND there is positive demand that month.
`OPEN_PO_QTY_IN_MONTH`	NUMBER	Total quantity expected from open inbound POs due that month.
`HAS_COVERING_PO`	BOOLEAN	TRUE if any open inbound PO quantity is due that month.
`MRP_TYPE`	TEXT	MRP type, joined for reference.
`MRP_CONTROLLER`	TEXT	Responsible planner/controller.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_FORECAST_TO_DELIVERY_MATERIAL_MONTHLY — 10 columns, 13,390 rows

Grain: One row per material × fiscal month (no plant split — delivery lines are summed across plants to match the material-level grain of the forecast-performance table).
Row count (live): 13,390
Built from (CURATED): FACT_DELIVERY, DIM_FISCAL_PERIOD, plus AGG_FORECAST_PERFORMANCE_MATERIAL_MONTHLY for the bias figure

Links forecast bias to downstream delivery/stockout impact.

Column	Type	Business Meaning / Formula
`MATERIAL_12NC`	TEXT	Material identifier (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`BIAS_RATIO_N3`	NUMBER	Forecast bias at the N-3 horizon, carried from the material forecast-performance table.
`IS_HIGH_BIAS`	BOOLEAN	TRUE when the absolute N-3 bias ratio exceeds 25% — the business-agreed threshold.
`TOTAL_DELIVERY_LINES`	NUMBER	Total delivery lines for the material/month, summed across plants.
`NO_STOCK_MISS_LINES`	NUMBER	Delivery lines missed specifically due to no stock available.
`NO_STOCK_MISS_RATE`	NUMBER	No-stock miss lines ÷ total delivery lines.
`HAS_NO_STOCK_MISS`	BOOLEAN	TRUE if the material/month had at least one no-stock-caused miss.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_CROSS_DOMAIN_RISK_MATERIAL_PLANT_MONTHLY — 12 columns, 364,388 rows

Grain: One row per material × plant × fiscal month. Combines risk flags from other AGGREGATE tables to identify materials/plants at risk across multiple domains simultaneously.
Row count (live): 364,388
Built from (CURATED): FACT_DELIVERY, FACT_PURCHASE_ORDER_LINE, DIM_FISCAL_PERIOD, plus AGG_SUPPLY_HEALTH_MATERIAL_PLANT_MONTHLY and AGG_FORECAST_TO_DELIVERY_MATERIAL_MONTHLY

Composite cross-domain risk score. Equal-weight (25 points/domain) scoring is a starting-point convention, not a validated business weighting. This table's row scope follows the supply-health table's coverage, so a material/plant/period with only a late-PO or delivery-miss signal and no inventory/demand/inbound-PO row will not appear here — a known, small, documented coverage limitation.

Column	Type	Business Meaning / Formula
`MATERIAL_12NC`	TEXT	Material identifier (grain key).
`PLANT_CODE`	TEXT	Plant code (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`IS_STOCKOUT_RISK`	BOOLEAN	Carried from the supply-health table (zero stock with positive demand).
`HAS_NO_STOCK_MISS`	BOOLEAN	TRUE if the material/plant/month had at least one no-stock-caused delivery miss.
`HAS_LATE_PO`	BOOLEAN	TRUE if the material/plant/month had at least one significantly delayed purchase order.
`IS_HIGH_BIAS`	BOOLEAN	High-bias flag (>25% at N-3), broadcast from the material-level forecast-to-delivery table (material+period grain, no plant split there).
`RISK_DOMAIN_COUNT`	NUMBER	Count of risk domains triggered (0–4): stockout risk, no-stock miss, late PO, high bias.
`SUPPLY_CHAIN_RISK_SCORE`	NUMBER	risk_domain_count × 25 (equal-weighted, 0–100 scale).
`IS_MULTI_DOMAIN_RISK`	BOOLEAN	TRUE when 2 or more risk domains trigger simultaneously.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

AGG_PLANT_SCORECARD_MONTHLY — 9 columns, 497 rows

Grain: One row per plant × fiscal month. An executive rollup for plant ranking and trending.
Row count (live): 497
Built from (CURATED): FACT_PURCHASE_ORDER_LINE, DIM_FISCAL_PERIOD, plus AGG_CROSS_DOMAIN_RISK_MATERIAL_PLANT_MONTHLY and AGG_DELIVERY_PERFORMANCE_PLANT_MONTHLY

Executive plant scorecard combining risk, delivery, and purchasing performance.

Column	Type	Business Meaning / Formula
`PLANT_CODE`	TEXT	Plant code (grain key).
`FISCAL_PERIOD_CODE`	TEXT	Fiscal period (grain key).
`CALENDAR_MONTH_KEY`	TEXT	Calendar month.
`MATERIAL_PLANT_COUNT`	NUMBER	Material/plant combinations scored for the plant/month.
`AVG_RISK_SCORE`	NUMBER	Average composite supply-chain risk score across all materials at the plant.
`MULTI_DOMAIN_RISK_COUNT`	NUMBER	Material/plant combinations flagged multi-domain risk.
`DRM_PCT`	NUMBER	Delivery reliability % for the plant/month, carried from the delivery-performance table.
`PLANT_PO_ON_TIME_PCT`	NUMBER	100 × on-time received PO lines ÷ received PO lines.
`_LOAD_TS`	TIMESTAMP_LTZ	Row build timestamp.

SEMANTICCortex Analyst Semantic Views (v2)

The four semantic views wired into SCM_AGENT_WITH_RECS, the live SCM Assistant Agent. Each view exposes logical tables, facts, dimensions, metrics, and a set of business-verified natural-language queries.

SV_GOLDEN_PURCHASING_V2 — 29 exposed fields, 9 verified queries

Purchase-order lines with actual-vs-planned lead time and vendor performance, EKET schedule lines, EKES vendor confirmations, EKBE goods movements, plus planning-parameter completeness and material-master hygiene. Overdue logic is anchored to a fixed snapshot date, not the current date, so results don't silently drift day to day.

Wrapped in the live SCM_AGENT_WITH_RECS Cortex Agent — the SCM Assistant Agent this pipeline actually serves. A separate, earlier generation of these views (without the _V2 suffix) also exists live, wired to different agents (CURATED_SCM_AGENT / CURATED_SCM_AGENT_WITH_SKILLS) — out of scope for this document by design.

Base CURATED tables: FACT_PURCHASE_ORDER_LINE, FACT_PO_SCHEDULE, FACT_PO_CONFIRMATION, FACT_GOODS_MOVEMENT, DIM_MATERIAL, DIM_PLANT, DIM_SUPPLIER_SOURCE, BRIDGE_MATERIAL_PLANT

Facts (measures) (10)

Name	Meaning
`open_qty, po_value_eur, ordered_qty, received_qty`	Core PO-line quantities/value.
`supply_delay_days`	Requested-vs-actual delay. Positive = late.
`actual_total_lt_days, marc_planned_lt_days`	Actual vs. MARC-master planned lead time.
`lt_variance_days`	Planned-vs-actual gap = actual − MARC planned. Use for 'lead time gap vs plan' questions, not supply_delay_days.
`late_flag, on_time_flag, severe_lt_breach_flag, lt_reliable_flag`	Delivery-timing flags. Severe breach = actual > 2× planned lead time.
`open_overdue_flag`	1 when open and confirmed date is before the snapshot anchor.
`scheduled_qty, open_schedule_qty, sched_overdue_flag, sched_partial_gr_flag`	Schedule-line (EKET) quantities and status.
`confirmed_qty, reduced_qty, open_confirmed_qty`	Vendor confirmation (EKES) quantities.
`gr_qty_net, gr_value_lc, gr_flag`	Signed goods-receipt quantity/value.
`safety_stock, reorder_point, planned_delivery_days, total_inbound_lt_days`	Material-plant planning parameters.

Dimensions (8)

Name	Meaning
`po_number, po_item, material_12nc, po_type`	PO line identifiers and type.
`dc_supplier_id/name/country, ep_supplier_id/name/country`	Two vendor identities — DC (direct counterparty) and EP (upstream manufacturer).
`vendor_category, bg_code, bu_code`	Vendor category and business hierarchy.
`po_release_date, planned_delivery_date, confirmed_delivery_date, actual_gr_date`	PO date milestones.
`plant_code/name, country_iso, region, plant_type, is_us_plant`	Plant attributes.
`brand, product_family, product_class, material_group, material_type, lifecycle_phase`	Material attributes.
`is_active, sales_off_flag, innovation_flag, commercial_release_date, deletion_date, successor_12nc`	Material hygiene attributes.
`mrp_controller, mrp_type, procurement_type, purchasing_group, abc_classification`	Planning parameters.

Metrics (aggregated) (11)

Name	Meaning
`avg_lt_variance_days`	AVG(supply_delay_days) — requested-vs-actual, NOT actual-minus-planned.
`avg_planned_vs_actual_lt_days`	AVG(actual − MARC planned). Use this for 'gap between actual and planned lead time'.
`avg_actual_lt_days, avg_planned_lt_days`	Simple averages of actual/planned lead time.
`vendor_on_time_pct`	% of PO lines delivered on time or early.
`severe_lt_breach_count, lt_reliability_pct`	Count exceeding 2× planned LT; % within ±20% of planned LT.
`vendor_fill_rate_pct`	Received qty as % of ordered qty, at PO-line grain.
`sched_fill_rate_pct`	Same fill-rate concept at schedule-line (EKET) grain — different denominator.
`confirmed_received_rate`	Vendor confirmation reliability: received vs. confirmed quantity.
`open_overdue_po_lines, total_po_value_eur, distinct_vendors, distinct_po_materials`	Volume/coverage metrics.
`total_safety_stock, material_plants_with_safety_stock, mrp_param_missing_count`	Planning-parameter completeness.
`distinct_material_count, inactive_material_count, sales_off_material_count`	Material-master hygiene metrics.

Business-verified natural-language queries (9)

Which 5 vendors have the largest gap between actual and planned lead time?

Rank vendors by on-time delivery percentage.

Which materials have an actual lead time more than double their planned lead time?

Which purchase orders are open and overdue as of the snapshot?

What is the inbound fill rate at schedule-line grain?

What is vendor confirmation reliability — received versus confirmed quantity per vendor?

What percentage of PO lines have no vendor acknowledgement (unconfirmed PO rate)?

How complete are planning parameters (safety stock / reorder point) by MRP controller?

What is the material master hygiene — active, inactive and new-product counts?

SV_GOLDEN_DELIVERY_FULFILMENT_V2 — 34 exposed fields, 8 verified queries

Delivery-grain DRM reliability with the 17 MISSED_* reason buckets, sales-order-line open-order/OTIF/fill-rate/cycle-time measures, and an order-at-risk supply overlay (from SUPPLY_ORDER_IMPACT). Conformed to material, plant, customer (ship-to and sold-to, tracked separately), business unit, and delay-reason dimensions. Overdue logic anchored to the same fixed snapshot date as the purchasing view.

Base CURATED tables: FACT_DELIVERY, FACT_SALES_ORDER_LINE, SUPPLY_ORDER_IMPACT, DIM_MATERIAL, DIM_PLANT, DIM_CUSTOMER, DIM_BUSINESS_UNIT_BYCODE, DIM_DELAY_REASON

Facts (measures) (10)

Name	Meaning
`delivery_agreed_qty, delivery_actual_qty`	Delivery-line quantities.
`drm_pct_line, drm_reliable_flag, drm_miss_flag`	Per-line DRM score and outcome flags.
`delay_days_adp, is_adp_unmeasurable`	Delay in days on a miss — the correct measure for 'how late once missed'. NOT the same as sales-order-line delivery_delay_days.
`on_time_gi_flag, scored_line_flag`	Goods-issue timing and scoring-eligibility flags.
`17 missed_* flags`	One flag per coded miss-reason bucket (no stock, credit block, transport, warehouse, wrong day, etc.), plus missed_unattributed for uncoded misses.
`so_ordered_qty, so_delivered_qty, so_open_qty`	Sales-order-line quantity position.
`delivery_delay_days`	SO-line lateness (requested vs. actual). Different from delay_days_adp.
`order_cycle_time_days`	Actual delivery date minus SO creation date.
`so_on_time_flag, so_in_full_flag, so_otif_flag, so_open_flag, so_open_overdue_flag, so_drm_miss_flag`	SO-line status flags.
`soi_open_qty, soi_order_qty_at_risk, soi_cumulative_shortage_qty, soi_available_inventory_qty, soi_open_po_count`	Order-at-risk supply overlay from SUPPLY_ORDER_IMPACT.

Dimensions (13)

Name	Meaning
`sales_order_number/item, delivery_number, material_12nc, plant_key`	Delivery-line identifiers.
`customer_key (ship-to), sold_to_id`	Two distinct customer roles, resolved via separate CUSTOMER / SOLD_TO_CUSTOMER logical tables.
`bu_code, banner`	Business unit and key-account attribution.
`primary_delay_reason, delay_reason_group, miss_reason`	miss_reason is the turn-key label: unattributed misses resolve to 'Uncategorized', sorted last in any breakdown.
`rejection_reason_code/label/desc`	label is display-ready — 'Unclassified' when rejected with no code, NULL when not rejected.
`drm_segment_note`	Annotates ADP-unmeasurable/rejected lines now scored rather than excluded (2026-08-17 pipeline change).
`delivery_block_code/desc, overall_delivery_status`	Block and status fields.
`customer_requested_date, actual_delivery_date, actual_goods_issue_date, carrier, ag_name`	Dates and shipment attributes.
`order_status, so_delivery_block_code/desc, so_rejection_reason_desc, so_creation_date, so_requested_date`	SO-line status and date fields.
`soi_anchor_month, soi_order_at_risk_flag, soi_inventory_shortage_flag, soi_supply_delay_flag`	Supply-risk dimensions, already snapshot-anchored (no current-date drift).
`material/plant/customer attributes`	brand, product_family, product_class, lifecycle_phase, plant geography, customer name/country.
`bu_name, bg_name`	Resolved business-unit names (e.g. Hue Connected) — use these, not raw codes, for outbound BU breakdowns.
`reason_code/description/group/source`	Delay-reason dimension lookup.

Metrics (aggregated) (11)

Name	Meaning
`drm_reliability_pct`	SUM(scored)/SUM(total) × 100 — additive at any grain. The delivery-grain (on-time) reliability / OTIF figure.
`delivery_grain_otif_pct`	On-time AND in-full at delivery grain — different denominator from the SO-line OTIF below.
`missed_lines, missed_units`	Count of misses and the commercially-weighted agreed quantity on missed lines.
`avg_delay_days_adp, median_delay_days_adp`	Average/median lateness on a miss — pair both, the distribution is right-skewed.
`18 _miss_rate / _loss_rate metrics`	Share-of-misses vs. per-scored-opportunity versions for each of the 17 miss-reason buckets.
`distinct_customers, distinct_materials, distinct_orders`	Coverage metrics.
`so_line_count, open_order_lines, open_overdue_order_lines, total_open_qty`	SO-line volume metrics.
`otif_pct, on_time_pct, in_full_pct, fill_rate_pct`	SO-line-grain fulfilment KPIs — different denominators from the delivery-grain versions above.
`avg_days_overdue, median_days_overdue`	SO-line lateness over late lines only.
`avg_order_cycle_time_days, median_order_cycle_time_days`	Order-to-delivery cycle time.
`order_lines_at_risk, total_order_qty_at_risk, total_cumulative_shortage_qty`	Supply-risk rollups.

Business-verified natural-language queries (8)

How has DRM percentage trended over the last 6 months?

Which plants or DCs are dragging down network DRM the most?

What are the top reasons for delivery shortfalls?

Which customers have the most units on missed lines?

How many open overdue orders do I have and for how much quantity?

What is the average order cycle time by plant?

Which materials have the most open order quantity at risk from supply?

Which business units have the worst OTIF percentage?

SV_SCM_DEMAND_PLANNING_V2 — 40 exposed fields, 8 verified queries

APO demand forecast and sales actuals, forecast accuracy/bias (WMAPE at lags N0–N3, volume-weighted bias, demand coefficient of variation), slow-moving/dead-stock, on-hand/GIT inventory for days-of-supply, and the projected supply position. FACT_FORECAST_PERFORMANCE is already 100% US — no US filter is needed or possible on that table.

Base CURATED tables: FACT_DEMAND_FORECAST, FACT_FORECAST_PERFORMANCE, FACT_SLOW_MOVING_INVENTORY, FACT_INVENTORY, SUPPLY_POSITION_BY_DATE, DIM_MATERIAL, DIM_PLANT, DIM_FISCAL_PERIOD, DIM_BUSINESS_UNIT_BYCODE

Facts (measures) (11)

Name	Meaning
`forecast_qty, forecast_value_eur`	Planned/actual quantity and value — NULL for the non-matching measure type on each row.
`planned_qty_n/n1/n2/n3`	Consensus forecast at each snapshot lag.
`actual_delivered_qty`	Actual delivered quantity — the denominator for accuracy/bias metrics below.
`abs_dev_n0/n1/n2/n3`	Absolute forecast deviation at each lag.
`forecast_accuracy_n`	Per-row accuracy. Unbounded below — prefer the WMAPE-based overall metric for reporting.
`forecast_bias_pct`	Per-row signed bias ratio. Extreme where actual is tiny — use the volume-weighted metric, not a simple average of this column.
`planned_qxp_n/n1/n2/n3`	Monetary consensus forecast at each lag.
`slow_mo_qty/value, qty_gt_12m, value_gt_12m, qty_7_12m, value_7_12m, qty_0_6m`	Slow-moving aging-bucket quantities/values.
`smi_on_hand_qty/value, smi_git_qty, slow_mo_value_pct, smos_month_count`	Slow-moving on-hand context and duration counter.
`inv_on_hand_qty/git_qty/total_qty, inv_on_hand_value/git_value/total_value, inv_on_hand_pct`	Inventory position facts, at storage-location+stock-type grain.
`projected_balance_qty, open_demand_qty, eligible_inbound_qty, cumulative_supply_available, cumulative_demand_qty`	Supply-position projection facts.

Dimensions (14)

Name	Meaning
`key_figure, measure_type`	APO key figure code and QUANTITY/VALUE_EUR classifier.
`channel_group, demand_planning_account, segment`	Sales channel, planning account, market segment.
`source_file`	DEMAND_QXP (forecast) vs. SALES_VIPP (actuals) — always filter, key_figure alone doesn't distinguish.
`demand_bg_code, oru_key, mru_key, currency`	Business group and planning hierarchy keys.
`fc_planning_account, scm_area_code, fc_bg/bu/mag`	Forecast-performance-side hierarchy and area code (SCM area is country-level, not plant-level).
`is_high_bias`	TRUE when \|bias ratio\| > 0.20.
`fp_material_12nc, fp_month`	Fact-local material/month keys for the forecast-performance table.
`demand_flag, demand_category, is_chronic_slow_mover, has_slow_stock_gt_12m`	Slow-moving classification dimensions.
`smi_bg_code, smi_mag_code, sm_material_12nc, sm_plant_code`	Fact-local slow-moving keys.
`storage_location, stock_type, is_unrestricted, inv_month`	Inventory-snapshot dimensions. is_unrestricted is the correct availability filter.
`sp_material_12nc, sp_plant_code, position_date`	Fact-local supply-position keys.
`material/plant attributes`	brand, product_family (NOT the same as BU — Hue/WiZ are business units), lifecycle_phase, supply_class, is_active, successor_12nc, plant geography.
`calendar_month_key, fiscal_year/quarter/half, month_name`	Time dimension, from the fiscal-period lookup.
`bu_code/name, bg_key/name`	Business hierarchy — MAG is a sub-level below BU, only available on forecast_performance.mag / slow_moving.mag_code, not on the demand-forecast fact itself.

Metrics (aggregated) (15)

Name	Meaning
`total_forecast_qty`	Sums BOTH forecast and actuals — do not use without filtering source_file.
`forecast_plan_qty, actual_sales_qty`	Forecast-only and actuals-only quantity, correctly separated by source_file.
`demand_plan_vs_actual_bias_pct`	Plan-vs-actual bias rebuilt from source, aligned to the same period window.
`total_forecast_value_eur, distinct_forecast_materials`	Value total and material coverage.
`total_consensus_n/n1/n2/n3, total_actual_delivered`	Total consensus forecast at each lag vs. actual.
`total_deviation_n, plan_vs_actual_gap_n3, signed_plan_vs_actual_gap_n3`	Absolute and signed deviation totals.
`overall_forecast_accuracy`	1 − WMAPE, computed at the overall (SUM/SUM) level — not an average of per-row accuracy.
`weighted_forecast_bias_pct`	The one canonical bias metric — volume-weighted, not a row-average.
`wmape, wmape_n1/n2/n3`	The canonical forecast-error metric at each lag, SUM/SUM over the full population (zero-actual rows are legitimate errors, not excluded).
`demand_cv`	Coefficient of variation — STDDEV/AVG of actuals, the basis for XYZ segmentation.
`high_bias_material_count, total_qxp_n`	High-bias material count and total monetary forecast.
`total_slow_mover_qty/value, total_dead_stock_qty/value, total_aged_qty/value_7_12m, total_fresh_stock_qty`	Slow-moving rollups — all repeating snapshots, filter the latest period.
`dead_stock_pct, distinct_slow_moving_materials`	Dead-stock share of on-hand and material coverage.
`total_on_hand_qty, unrestricted_on_hand_qty, total_git_qty, total_effective_stock, total_on_hand/git/inventory_value`	Inventory rollups.
`total_projected_balance, projected_shortage_qty, material_plants_in_shortage`	Supply-position rollups.

Business-verified natural-language queries (8)

What is the weighted MAPE across all materials?

What is the volume-weighted forecast bias by business unit?

How does weighted forecast error change from the 3-month to the current horizon?

Which materials have the most variable demand (highest coefficient of variation)?

Which materials have the highest dead stock (no movement over 12 months) in the latest period?

What is the days of supply (stock cover) by material and plant?

Where is projected supply short of demand (plan versus available supply gap)?

Which materials are over- or under-forecast by more than 20 percent?

SV_SCM_INVENTORY_V2 — 30 exposed fields, 7 verified queries

On-hand/GIT stock, aging/dead-stock buckets, replenishment parameters, available-stock share, and below-safety-stock exposure. Slow-moving and inventory are both repeating monthly snapshots — filter to the latest period. Both EUR figures are already actual EUR (no ×1,000 scaling needed).

Base CURATED tables: FACT_INVENTORY, FACT_SLOW_MOVING_INVENTORY, BRIDGE_MATERIAL_PLANT, DIM_MATERIAL, DIM_PLANT, DIM_FISCAL_PERIOD, DIM_BUSINESS_UNIT_BYCODE

Facts (measures) (8)

Name	Meaning
`on_hand_qty, git_qty, total_qty_incl_git`	Inventory position quantities, at storage-location+stock-type grain.
`on_hand_value_eur, git_value_eur, total_value_eur`	Inventory position values.
`on_hand_pct_of_total`	On-hand as % of total including GIT.
`slow_mo_qty/value, qty_gt_12m, value_gt_12m, qty_7_12m, value_7_12m, qty_0_6m`	Slow-moving aging-bucket facts.
`smi_on_hand_qty/value, smi_git_qty, slow_mo_value_pct, smos_month_count`	Slow-moving on-hand context and duration counter.
`safety_stock, reorder_point`	Replenishment parameters. Both sparse — ~1.1% coverage.
`planned_delivery_days, gr_processing_days, total_inbound_lt_days, total_replenishment_lt_days`	Lead-time parameters.
`moq, min_order_qty, max_order_qty`	Order-quantity constraints.

Dimensions (9)

Name	Meaning
`storage_location, stock_type, stock_type_desc, is_unrestricted`	Inventory-snapshot dimensions. is_unrestricted is the correct availability filter — there's no literal 'unrestricted' value on stock_type itself.
`inv_plant_code, inv_month`	Fact-local inventory keys.
`demand_flag, demand_category, is_chronic_slow_mover, has_slow_stock_gt_12m`	Slow-moving classification.
`smi_bg_code, smi_mag_code, sm_material_12nc, sm_plant_code, sm_month`	Fact-local slow-moving keys.
`mp_material_12nc, mp_plant_code, abc_classification`	Fact-local replenishment-parameter keys and ABC class.
`material attributes`	brand, product_family, product_class (removed — 100% NULL, see finding below), product_segment/type, lifecycle_phase, supply_class, is_active.
`plant attributes`	plant_code/name, country, country_iso, region, plant_type (removed — 100% NULL), is_us_plant.
`calendar_month_key, fiscal_year/quarter/half, month_name`	Time dimension.
`bu_code/name, bg_key/name`	Business hierarchy.

Metrics (aggregated) (13)

Name	Meaning
`total_on_hand_qty`	Total on-hand across ALL stock types — for available-only, use the metric below.
`unrestricted_on_hand_qty, unrestricted_on_hand_value_eur`	Available-stock-only quantity and value — the correct basis for DOS and stockout-risk questions.
`available_stock_share_pct`	Unrestricted on-hand as % of total on-hand — ~96% network-wide in the latest period.
`total_git_qty, total_effective_stock`	Goods-in-transit and effective (on-hand + GIT) stock.
`total_on_hand_value_eur, total_git_value_eur, total_inventory_value_eur`	Value rollups, all actual EUR.
`distinct_materials`	Material coverage carrying stock.
`total_safety_stock, material_plants_with_safety_stock`	Safety-stock totals and coverage count — surface the ~1.1% coverage whenever this is used.
`avg_planned_lead_time, avg_replenishment_lt`	Average lead-time metrics.
`total_slow_mover_qty/value_eur, total_dead_stock_qty/value, total_aged_qty/value_7_12m, total_fresh_stock_qty`	Slow-moving rollups — repeating snapshots, filter the latest period; mixed base UOM, include base_uom as a dimension.
`total_smi_on_hand_qty/value`	Slow-moving on-hand denominators.
`dead_stock_pct, slow_mover_pct, aging_coverage_ratio`	Dead-stock and slow-moving share metrics — network SLOB ≈28%.
`healthy_stock_qty, slow_moving_stock_qty, slow_moving_qty_pct`	Client-definition healthy (0-6mo) vs. slow-moving (6+ mo) rollups.
`distinct_slow_moving_materials`	Material coverage of the slow-moving population.

Business-verified natural-language queries (7)

Which plant has the highest on-hand inventory in the latest period?

What share of on-hand stock is available (unrestricted) by plant?

Which plant has the highest dead stock value in the latest period?

Which US plants have the most materials currently below their safety stock?

How much slow-moving stock is there right now by plant?

Which materials have the longest planned lead time?

Which storage locations hold the most blocked or QI stock?