Supply inventory

Turn a short stream of inventory events into stock as of a date you choose, then reuse that figure for reorder flags and warehouse value. Bind a different date and the numbers update.

This intermediate tutorial assumes the mappings and RELATED() model from E-commerce . AS_OF_DATE is an INPUT; changing its BIND_VALUE reevaluates the same feature graph at another date. Run Data and Model before the inventory questions.

Data

Two warehouses, two products, nine inventory events (receipt, shipment, adjustment). Receipts add stock; shipments subtract; adjustments use the signed quantity as-is.

FeatureQL

/* SQL */
CREATE SCHEMA IF NOT EXISTS tutorial_supply;
--
DROP TABLE IF EXISTS tutorial_supply.inventory_events;
--
DROP TABLE IF EXISTS tutorial_supply.products;
--
DROP TABLE IF EXISTS tutorial_supply.warehouses;
--
CREATE TABLE tutorial_supply.warehouses (
  id BIGINT,
  name VARCHAR
);
--
INSERT INTO tutorial_supply.warehouses VALUES
  (1, 'Portland'),
  (2, 'Chicago');
--
CREATE TABLE tutorial_supply.products (
  id BIGINT,
  name VARCHAR,
  unit_cost BIGINT,
  reorder_point BIGINT
);
--
INSERT INTO tutorial_supply.products VALUES
  (1, 'Widget A', 10, 50),
  (2, 'Widget B', 25, 30);
--
CREATE TABLE tutorial_supply.inventory_events (
  id BIGINT,
  product_id BIGINT,
  warehouse_id BIGINT,
  event_type VARCHAR,
  quantity BIGINT,
  event_date DATE
);
--
INSERT INTO tutorial_supply.inventory_events VALUES
  (1, 1, 1, 'receipt', 100, DATE '2024-01-05'),
  (2, 1, 2, 'receipt', 80, DATE '2024-01-05'),
  (3, 2, 1, 'receipt', 60, DATE '2024-01-10'),
  (4, 1, 1, 'shipment', 40, DATE '2024-02-01'),
  (5, 1, 2, 'shipment', 30, DATE '2024-02-01'),
  (6, 2, 1, 'shipment', 20, DATE '2024-02-15'),
  (7, 1, 1, 'adjustment', -10, DATE '2024-03-01'),
  (8, 1, 1, 'shipment', 20, DATE '2024-03-10'),
  (9, 2, 1, 'receipt', 30, DATE '2024-03-25');
--
SELECT CAST(COUNT(*) AS INTEGER) AS cnt FROM tutorial_supply.inventory_events;

Result

Count BIGINT
9

Each product has a reorder_point and unit_cost. Edit the VALUES if you want different stock levels.

Model

Entities for products, warehouses, and events, plus an AS_OF_DATE input — the snapshot date is a parameter, not a hard-coded filter in every formula.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPLY AS
SELECT
    products := ENTITY(),
    warehouses := ENTITY(),
    inventory_events := ENTITY(),
    product_id := INPUT(BIGINT#products),
    warehouse_id := INPUT(BIGINT#warehouses),
    event_id := INPUT(BIGINT#inventory_events),
    as_of_date := INPUT(DATE)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SUPPLY.PRODUCTS	CREATED	Feature created as not exists
FM.SUPPLY.WAREHOUSES	CREATED	Feature created as not exists
FM.SUPPLY.INVENTORY_EVENTS	CREATED	Feature created as not exists
FM.SUPPLY.PRODUCT_ID	CREATED	Feature created as not exists
FM.SUPPLY.WAREHOUSE_ID	CREATED	Feature created as not exists
FM.SUPPLY.EVENT_ID	CREATED	Feature created as not exists
FM.SUPPLY.AS_OF_DATE	CREATED	Feature created as not exists

Map the three tables:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPLY AS
SELECT
    tables.products := EXTERNAL_COLUMNS(
        id BIGINT#products BIND TO product_id,
        name VARCHAR,
        unit_cost BIGINT,
        reorder_point BIGINT
        FROM TABLE(tutorial_supply.products)
    ),
    tables.warehouses := EXTERNAL_COLUMNS(
        id BIGINT#warehouses BIND TO warehouse_id,
        name VARCHAR
        FROM TABLE(tutorial_supply.warehouses)
    ),
    tables.inventory_events := EXTERNAL_COLUMNS(
        id BIGINT#inventory_events BIND TO event_id,
        product_id BIGINT#products,
        warehouse_id BIGINT#warehouses,
        event_type VARCHAR,
        quantity BIGINT,
        event_date DATE
        FROM TABLE(tutorial_supply.inventory_events)
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SUPPLY.TABLES.PRODUCTS	CREATED	Feature created as not exists
FM.SUPPLY.TABLES.WAREHOUSES	CREATED	Feature created as not exists
FM.SUPPLY.TABLES.INVENTORY_EVENTS	CREATED	Feature created as not exists

Stock contribution at as-of

Persist one feature: each event’s signed quantity if its date is on or before AS_OF_DATE, else zero.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPLY AS
SELECT
    inventory_line_contrib_at_as_of := IF(
        tables.inventory_events[event_date] <= as_of_date,
        CASE
            WHEN tables.inventory_events[event_type] = 'receipt' THEN tables.inventory_events[quantity]
            WHEN tables.inventory_events[event_type] = 'shipment' THEN -tables.inventory_events[quantity]
            ELSE tables.inventory_events[quantity]
        END,
        0
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SUPPLY.INVENTORY_LINE_CONTRIB_AT_AS_OF	CREATED	Feature created as not exists

Receipt → +qty, shipment → -qty, adjustment → the stored (already signed) quantity.

On-hand as of March 31

Sum those contributions by product and warehouse.

FeatureQL

WITH
    pid := tables.inventory_events[product_id],
    wid := tables.inventory_events[warehouse_id],
    on_hand := SUM(inventory_line_contrib_at_as_of) GROUP BY (pid, wid),
    product := RELATED(tables.products[name] VIA pid),
    warehouse := RELATED(tables.warehouses[name] VIA wid)
SELECT
    product,
    warehouse,
    on_hand
FROM FM.SUPPLY
FOR
    as_of_date := BIND_VALUE(DATE '2024-03-31'),
    event_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_supply.inventory_events ORDER BY id)
    )
WHERE on_hand <> 0
ORDER BY
    product,
    warehouse
;

Result

PRODUCT VARCHAR	WAREHOUSE VARCHAR	ON_HAND BIGINT
Widget A	Chicago	50
Widget A	Portland	30
Widget B	Portland	70

Widget A: Portland 30, Chicago 50. Widget B: Portland 70.

Same logic, earlier date

Only the bound date changes.

FeatureQL

WITH
    pid := tables.inventory_events[product_id],
    wid := tables.inventory_events[warehouse_id],
    on_hand := SUM(inventory_line_contrib_at_as_of) GROUP BY (pid, wid),
    product := RELATED(tables.products[name] VIA pid),
    warehouse := RELATED(tables.warehouses[name] VIA wid)
SELECT
    product,
    warehouse,
    on_hand
FROM FM.SUPPLY
FOR
    as_of_date := BIND_VALUE(DATE '2024-02-28'),
    event_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_supply.inventory_events ORDER BY id)
    )
WHERE on_hand <> 0
ORDER BY
    product,
    warehouse
;

Result

PRODUCT VARCHAR	WAREHOUSE VARCHAR	ON_HAND BIGINT
Widget A	Chicago	50
Widget A	Portland	60
Widget B	Portland	40

End of February → Portland A 60, Chicago A 50, Portland B 40 (the March shipment, adjustment, and receipt are excluded).

Below reorder point

Compare on-hand to each product’s reorder_point (RELATED()). At-or-below counts as triggered.

FeatureQL

WITH
    pid := tables.inventory_events[product_id],
    wid := tables.inventory_events[warehouse_id],
    on_hand := SUM(inventory_line_contrib_at_as_of) GROUP BY (pid, wid),
    reorder_pt := RELATED(tables.products[reorder_point] VIA pid),
    product := RELATED(tables.products[name] VIA pid),
    warehouse := RELATED(tables.warehouses[name] VIA wid)
SELECT
    product,
    warehouse,
    on_hand,
    reorder_pt
FROM FM.SUPPLY
FOR
    as_of_date := BIND_VALUE(DATE '2024-03-31'),
    event_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_supply.inventory_events ORDER BY id)
    )
WHERE on_hand <= reorder_pt
ORDER BY
    product,
    warehouse
;

Result

PRODUCT VARCHAR	WAREHOUSE VARCHAR	ON_HAND BIGINT	REORDER_PT BIGINT
Widget A	Chicago	50	50
Widget A	Portland	30	50

On March 31, Widget A is short in both warehouses (30 and 50 vs reorder 50). Widget B is fine (70 vs 30).

Inventory value by warehouse

On-hand × unit cost, rolled up by warehouse.

FeatureQL

WITH
    pid := tables.inventory_events[product_id],
    wid := tables.inventory_events[warehouse_id],
    on_hand := SUM(inventory_line_contrib_at_as_of) GROUP BY (pid, wid),
    uc := RELATED(tables.products[unit_cost] VIA pid),
    warehouse := RELATED(tables.warehouses[name] VIA wid),
    line_value := on_hand * uc
SELECT
    warehouse,
    inv_value := SUM(line_value) GROUP BY warehouse
FROM FM.SUPPLY
FOR
    as_of_date := BIND_VALUE(DATE '2024-03-31'),
    event_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_supply.inventory_events ORDER BY id)
    )
ORDER BY warehouse
;

Result

WAREHOUSE VARCHAR	INV_VALUE BIGINT
Chicago	500
Portland	2050

Portland 2050 (30×10 + 70×25), Chicago 500 (50×10).

What's next

Financial consolidation — rule-based revenue and Europe rollup
SaaS metrics — MRR and customer health
Analytics overview — concept map for this series

Last update at: 2026/08/04 11:25:38

On this page

Data Model Stock contribution at as-of On-hand as of March 31 Same logic, earlier date Below reorder point Inventory value by warehouse What's next