Model inventory levels

To report inventory stock on hand for any historical day, combine a detailed inventory transaction fact table with a daily periodic snapshot fact table. The transaction table records movements such as receipts, sales, and scrap adjustments, while the periodic snapshot captures the closing balance of stock on hand per product per warehouse.

Inventory transactions vs periodic snapshots

Inventory management requires two complementary tables to satisfy auditing and performance needs:

fact_inventory_transactions (grain: one row per stock movement event)
transaction_id
product_key
warehouse_key
date_key
time_key
movement_type (po_receipt, customer_sale, transfer_in, transfer_out, scrap_adjustment)
quantity_moved (positive for additions, negative for deductions)
unit_cost_amount

fact_inventory_daily_snapshot (grain: one row per product per warehouse per day)
product_key
warehouse_key
date_key
quantity_on_hand
total_inventory_cost
safety_stock_quantity
out_of_stock_flag

Why both tables are necessary:

• Reconstructing closing stock solely from transaction history requires running a cumulative sum across millions of historical transactions for every dashboard view, which is computationally expensive.
• The daily periodic snapshot pre-calculates the exact closing quantity on hand for each day, making historical stock lookups instant.
• Physical stock counting audits produce variance adjustments in fact_inventory_transactions that flow naturally into subsequent snapshot closing balances.

Semi-additive quantities on hand

Quantity on hand is a semi-additive measure. You can meaningfully sum inventory across products or warehouses on a specific date to compute total company inventory. However, you cannot sum quantity on hand across dates: summing 100 units on Monday, 100 units on Tuesday, and 100 units on Wednesday does not equal 300 units of stock. Reporting queries must apply the last recorded balance or an average over time rather than a standard sum. Inventory valuation calculations also track cost basis (such as FIFO or weighted average unit cost) alongside raw item counts.

Snapshot storage optimizations

If an enterprise carries 50,000 SKUs across 20 warehouses, a dense daily snapshot generates one million rows every day, totaling 365 million rows a year. If ninety percent of SKUs experience zero stock changes on a given day, storing redundant dense rows wastes storage. Two proven optimization strategies:

• Sparse snapshot with forward filling: Only write a snapshot row when a product has movement or non-zero stock, using a date spine and SQL window function (LAST_VALUE(quantity_on_hand IGNORE NULLS)) to forward-fill missing dates at query time.
• Dense monthly snapshots with daily transaction deltas: Store dense closing balances only on the final day of each month, adding daily transaction deltas to calculate intra-month balances on demand.