Supply inventory

This tutorial reconstructs inventory from a stream of receipts, shipments, and adjustments, then reuses the snapshot for replenishment and valuation.

Unlike a transaction total, stock is meaningful as of a particular moment. You will bind two dates to the same feature graph and watch later events enter or leave the result.

This tutorial assumes the mappings and RELATED() model from E-commerce . Run Data and Model before the inventory questions.

Meet the inventory events

The dataset contains two warehouses, two products, and nine inventory events:

a receipt adds its quantity;
a shipment subtracts its quantity;
an adjustment already carries a positive or negative sign.

FeatureQL

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 also has a reorder point and a unit cost. Those values do not change the stock calculation; they give us new questions to ask once stock is known.

Build a date-aware feature model

Declare products, warehouses, and inventory events as entities. Add AS_OF_DATE as an input so the snapshot date is supplied at query time rather than hard-coded into every formula.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPLY AS
PERSIST
    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 product, warehouse, and event columns:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPLY AS
PERSIST
    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

The event rows now carry typed product and warehouse keys. That lets later aggregations group the same signed contributions along either relationship.

Define what each event contributes

Persist the smallest reusable building block: an event's contribution to stock at the bound date.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPLY AS
PERSIST
    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

The feature does two jobs:

It excludes events after AS_OF_DATE.
It converts the remaining event type into a signed quantity.

A receipt contributes +qty, a shipment contributes -qty, and an adjustment contributes its stored signed value.

Reconstruct stock on March 31

Bind AS_OF_DATE to March 31 and sum the contributions by product and warehouse:

FeatureQL

DEFINE
    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)
RETURN
    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

The snapshot is:

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

For Portland's Widget A, the event arithmetic is 100 - 40 - 10 - 20 = 30.

These are balances at month-end. Adding them to a February balance would double-count stock, which is why inventory is called a semi-additive measure. Metric additivity explains how to forbid a time-axis rollup while keeping warehouse and product rollups available.

Move the snapshot back one month

Run the same query with AS_OF_DATE bound to February 28:

FeatureQL

DEFINE
    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)
RETURN
    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

The earlier snapshot is Portland A 60, Chicago A 50, and Portland B 40. The March shipment, adjustment, and receipt have not happened yet, so they contribute zero.

The formula did not change. Only the bound date changed.

Turn the snapshot into a reorder decision

Bring each product's reorder point into the March snapshot and compare it with on-hand quantity. This example treats at or below the threshold as needing reorder.

FeatureQL

DEFINE
    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)
RETURN
    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

Widget A triggers in both warehouses: Portland has 30 and Chicago has 50, against a reorder point of 50. Widget B has 70 against a threshold of 30, so it does not trigger.

Value the inventory by warehouse

Reuse on-hand again, this time multiplying each product balance by its unit cost before rolling the result up by warehouse:

FeatureQL

DEFINE
    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
RETURN
    warehouse,
    inv_value := SUM(line_value) PER ROW 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 is worth 2050 (30 × 10 + 70 × 25). Chicago is worth 500 (50 × 10).

One event-sourced snapshot now supports both an operational flag and a financial measure.

What's next

You have rebuilt state from events, parameterized it by date, and reused the result without duplicating the event logic.

Continue with Temporal & experiments to separate when a fact applied from when it became known, or try Financial consolidation for rule-driven rollups.

Last update at: 2026/10/13 08:32:21

On this page

Meet the inventory events Build a date-aware feature model Define what each event contributes Reconstruct stock on March 31 Move the snapshot back one month Turn the snapshot into a reorder decision Value the inventory by warehouse What's next