Inventory Analytics data model
Kimball warehouse for inventory: a daily periodic inventory snapshot at product/location/lot grain, a transaction-grain movement fact for receipts/issues/transfers, and a stockout fact quantifying lost sales over conformed product/location/supplier/uom/lot dimensions.
Inventory analytics covering daily stock positions, inventory movements, and stockout events across locations and lots — a Kimball star schema with conformed product (SCD2), location, supplier, unit-of-measure, and lot dimensions for stock-on-hand, turns, days-of-supply, and availability reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 128
- columns
- 22
- relationships
- 0
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Daily snapshot plus transaction movements (twin facts)
Stock on hand is semi-additive and needed daily for turns and days-of-supply, while movements are fully additive transactions; modeling both lets queries reconcile balances against flows.
Lot as a conformed dimension
Batch/lot tracking is required for expiry, recall, and FEFO control; modeling the lot as its own dimension supports both snapshot and movement facts without repeating lot attributes.
Dedicated stockout fact
Service-level and lost-sales reporting needs a record per stockout occurrence with duration and unmet demand, which would not be expressible from snapshot or movement grains alone.
Product modeled as SCD2
Reorder points, ABC class, and standard cost change over time and must align with the product version in effect at each snapshot and movement for accurate valuation history.
Tables in the Inventory Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_inventory_snapshot
Grain: one row per product per location per lot per day (periodic snapshot)
Daily inventory position snapshot — semi-additive stock levels.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| product_key | bigint | |
| location_key | bigint | |
| lot_key | bigint | |
| uom_key | bigint | |
| owner_key | bigint | |
| on_hand_quantity | integer | |
| available_quantity | integer | |
| allocated_quantity | integer | |
| quarantine_quantity | integer | |
| in_transit_quantity | integer | |
| inventory_value | decimal | |
| standard_cost | decimal | |
| days_of_supply | decimal | |
| inventory_turns | decimal | |
| is_below_reorder_point | boolean | |
| is_excess | boolean |
fct_inventory_movement
Grain: one row per inventory movement transaction
Transaction-grain movement fact — additive receipts, issues, transfers.
| Column | Type | Keys |
|---|---|---|
| inventory_movement_key | bigint | PK |
| date_key | bigint | |
| product_key | bigint | |
| location_key | bigint | |
| supplier_key | bigint | |
| lot_key | bigint | |
| uom_key | bigint | |
| movement_type_key | bigint | |
| adjustment_reason_key | bigint | |
| transaction_id | varchar | |
| reference_document | varchar | |
| quantity | integer | |
| unit_cost | decimal | |
| extended_cost | decimal | |
| running_balance | integer | |
| is_adjustment | boolean |
fct_stockout
Grain: one row per stockout occurrence
Transaction-grain stockout fact quantifying lost sales and service impact.
| Column | Type | Keys |
|---|---|---|
| stockout_key | bigint | PK |
| date_key | bigint | |
| product_key | bigint | |
| location_key | bigint | |
| recovery_date_key | bigint | |
| stockout_id | varchar | |
| duration_hours | decimal | |
| unmet_demand_quantity | integer | |
| lost_sales_amount | decimal | |
| backorder_quantity | integer | |
| root_cause | varchar | |
| is_recovered | boolean |
fct_cycle_count
Grain: one row per cycle-count line
Transaction-grain cycle-count fact for inventory record accuracy.
| Column | Type | Keys |
|---|---|---|
| cycle_count_key | bigint | PK |
| date_key | bigint | |
| product_key | bigint | |
| location_key | bigint | |
| lot_key | bigint | |
| adjustment_reason_key | bigint | |
| count_id | varchar | |
| system_quantity | integer | |
| counted_quantity | integer | |
| variance_quantity | integer | |
| abs_variance_quantity | integer | |
| variance_value | decimal | |
| count_accuracy_pct | decimal | |
| is_match | boolean | |
| is_recount_required | boolean |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| fiscal_period | varchar | |
| is_business_day | boolean | |
| is_period_end | boolean |
dim_product
Grain: one row per product per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| sku | varchar | |
| product_name | varchar | |
| product_category | varchar | |
| product_subcategory | varchar | |
| brand | varchar | |
| abc_class | varchar | |
| is_perishable | boolean | |
| shelf_life_days | integer | |
| standard_cost | decimal | |
| reorder_point | integer | |
| safety_stock | integer | |
| is_active | boolean |
dim_location
Grain: one row per stocking location
| Column | Type | Keys |
|---|---|---|
| location_key | bigint | PK |
| location_id | varchar | BK |
| location_name | varchar | |
| location_type | varchar | |
| warehouse_code | varchar | |
| zone | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| region | varchar |
dim_supplier
Grain: one row per supplier
| Column | Type | Keys |
|---|---|---|
| supplier_key | bigint | PK |
| supplier_id | varchar | BK |
| supplier_name | varchar | |
| supplier_tier | varchar | |
| country | varchar | |
| lead_time_days | integer | |
| min_order_quantity | integer | |
| is_approved | boolean |
dim_lot
Grain: one row per inventory lot / batch
| Column | Type | Keys |
|---|---|---|
| lot_key | bigint | PK |
| lot_id | varchar | BK |
| lot_number | varchar | |
| manufacture_date | date | |
| expiry_date | date | |
| country_of_origin | varchar | |
| quality_status | varchar |
dim_owner
Grain: one row per inventory owner / business unit
| Column | Type | Keys |
|---|---|---|
| owner_key | bigint | PK |
| owner_id | varchar | BK |
| owner_name | varchar | |
| ownership_type | varchar | |
| business_unit | varchar | |
| cost_center | varchar |
Lookup tables (3)
dim_uom
Grain: one row per unit of measure
| Column | Type | Keys |
|---|---|---|
| uom_key | bigint | PK |
| uom_code | varchar | BK |
| uom_name | varchar | |
| uom_type | varchar | |
| base_conversion_factor | decimal |
dim_movement_type
Grain: one row per movement type
| Column | Type | Keys |
|---|---|---|
| movement_type_key | bigint | PK |
| movement_code | varchar | BK |
| movement_name | varchar | |
| direction | varchar | |
| affects_valuation | boolean |
dim_adjustment_reason
Grain: one row per adjustment reason
| Column | Type | Keys |
|---|---|---|
| adjustment_reason_key | bigint | PK |
| reason_code | varchar | BK |
| reason_name | varchar | |
| reason_group | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_inventory_snapshot.date_key → dim_date (many-to-one)
- fct_inventory_snapshot.product_key → dim_product (many-to-one)
- fct_inventory_snapshot.location_key → dim_location (many-to-one)
- fct_inventory_snapshot.lot_key → dim_lot (many-to-one)
- fct_inventory_snapshot.uom_key → dim_uom (many-to-one)
- fct_inventory_snapshot.owner_key → dim_owner (many-to-one)
- fct_inventory_movement.date_key → dim_date (many-to-one)
- fct_inventory_movement.product_key → dim_product (many-to-one)
- fct_inventory_movement.location_key → dim_location (many-to-one)
- fct_inventory_movement.supplier_key → dim_supplier (many-to-one)
- fct_inventory_movement.lot_key → dim_lot (many-to-one)
- fct_inventory_movement.uom_key → dim_uom (many-to-one)
- fct_inventory_movement.movement_type_key → dim_movement_type (many-to-one)
- fct_inventory_movement.adjustment_reason_key → dim_adjustment_reason (many-to-one)
- fct_stockout.date_key → dim_date (many-to-one)
- fct_stockout.product_key → dim_product (many-to-one)
- fct_stockout.location_key → dim_location (many-to-one)
- fct_cycle_count.date_key → dim_date (many-to-one)
- fct_cycle_count.product_key → dim_product (many-to-one)
- fct_cycle_count.location_key → dim_location (many-to-one)
- fct_cycle_count.lot_key → dim_lot (many-to-one)
- fct_cycle_count.adjustment_reason_key → dim_adjustment_reason (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.