Supply Chain Analytics data model
Kimball warehouse for supply chain: an accumulating order-fulfillment snapshot tracking order->pick->ship->deliver milestones, a daily inventory periodic snapshot, and a transaction-grain shipment fact over conformed product/warehouse/supplier/customer/carrier dimensions.
Supply chain analytics covering the order-to-deliver fulfillment lifecycle, daily inventory positions across distribution centers, and outbound shipments — a Kimball star schema with conformed product (SCD2), warehouse, supplier, customer, and carrier dimensions for perfect-order, fill-rate, and on-time-in-full 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
- 144
- columns
- 21
- relationships
- 5
- 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
Accumulating snapshot for order fulfillment
An order line passes well-defined milestones (order, pick, ship, deliver) with measurable lags; an accumulating snapshot lets a single row be progressively updated to support cycle-time and OTIF analysis.
Daily inventory snapshot separate from movements
On-hand stock is semi-additive and must be reported daily per product/warehouse for days-of-supply and stockout-risk independent of transaction volume.
Product modeled as SCD2
Product attributes such as category, packaging, and hazmat class change over time and must be tied to the version in effect when each order or shipment occurred.
Carrier as its own conformed dimension
Carrier performance (on-time %, mode, service level) is analyzed across both fulfillment and shipment processes, so it is conformed rather than degenerate.
Tables in the Supply Chain 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_order_fulfillment
Grain: one row per order line (accumulating snapshot order->pick->ship->deliver)
Accumulating snapshot of the order-to-deliver fulfillment lifecycle.
| Column | Type | Keys |
|---|---|---|
| order_fulfillment_key | bigint | PK |
| order_date_key | bigint | |
| product_key | bigint | |
| warehouse_key | bigint | |
| customer_key | bigint | |
| carrier_key | bigint | |
| order_status_key | bigint | |
| pick_date_key | bigint | |
| ship_date_key | bigint | |
| deliver_date_key | bigint | |
| order_number | varchar | |
| order_line_number | varchar | |
| ordered_quantity | integer | |
| shipped_quantity | integer | |
| delivered_quantity | integer | |
| line_amount | decimal | |
| order_to_pick_hours | decimal | |
| pick_to_ship_hours | decimal | |
| ship_to_deliver_hours | decimal | |
| is_on_time | boolean | |
| is_in_full | boolean |
fct_inventory_snapshot
Grain: one row per product per warehouse per day (periodic snapshot)
Daily inventory position snapshot — semi-additive stock levels.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| product_key | bigint | |
| warehouse_key | bigint | |
| on_hand_quantity | integer | |
| available_quantity | integer | |
| allocated_quantity | integer | |
| in_transit_quantity | integer | |
| safety_stock_quantity | integer | |
| reorder_point | integer | |
| inventory_value | decimal | |
| days_of_supply | decimal | |
| is_below_safety_stock | boolean |
fct_shipment
Grain: one row per shipment line
Transaction-grain shipment fact for transportation analytics.
| Column | Type | Keys |
|---|---|---|
| shipment_key | bigint | PK |
| ship_date_key | bigint | |
| product_key | bigint | |
| warehouse_key | bigint | |
| customer_key | bigint | |
| carrier_key | bigint | |
| ship_mode_key | bigint | |
| delivery_date_key | bigint | |
| shipment_number | varchar | |
| tracking_number | varchar | |
| shipped_quantity | integer | |
| shipment_weight_kg | decimal | |
| freight_cost | decimal | |
| declared_value | decimal | |
| transit_days | smallint | |
| is_damaged | boolean |
fct_purchase_order
Grain: one row per purchase order line (accumulating snapshot)
Accumulating snapshot of inbound procurement from order to receipt.
| Column | Type | Keys |
|---|---|---|
| purchase_order_key | bigint | PK |
| order_date_key | bigint | |
| product_key | bigint | |
| supplier_key | bigint | |
| warehouse_key | bigint | |
| uom_key | bigint | |
| ship_mode_key | bigint | |
| expected_receipt_date_key | bigint | |
| actual_receipt_date_key | bigint | |
| po_number | varchar | |
| po_line_number | varchar | |
| ordered_quantity | integer | |
| received_quantity | integer | |
| unit_price | decimal | |
| line_amount | decimal | |
| promised_lead_time_days | integer | |
| actual_lead_time_days | integer | |
| is_on_time_receipt | boolean | |
| is_complete_receipt | 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_holiday | 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 | |
| unit_of_measure | varchar | |
| unit_weight_kg | decimal | |
| unit_volume_m3 | decimal | |
| hazmat_class | varchar | |
| is_perishable | boolean | |
| shelf_life_days | integer | |
| is_active | boolean |
dim_warehouse
Grain: one row per warehouse / DC
| Column | Type | Keys |
|---|---|---|
| warehouse_key | bigint | PK |
| warehouse_id | varchar | BK |
| warehouse_name | varchar | |
| warehouse_type | varchar | |
| address_line1 | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| postal_code | varchar | |
| region | varchar | |
| total_capacity_units | bigint |
dim_supplier
Grain: one row per supplier per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| supplier_key | bigint | PK |
| supplier_id | varchar | BK |
| supplier_name | varchar | |
| supplier_tier | varchar | |
| country | varchar | |
| preferred_carrier | varchar | |
| contact_email | varchar | PII |
| contact_phone | varchar | PII |
| lead_time_days | integer | |
| quality_rating | decimal | |
| is_approved | boolean |
dim_customer
Grain: one row per ship-to customer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| customer_name | varchar | PII |
| customer_segment | varchar | |
| ship_to_address | varchar | PII |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| postal_code | varchar | PII |
| sales_region | varchar |
dim_carrier
Grain: one row per freight carrier
| Column | Type | Keys |
|---|---|---|
| carrier_key | bigint | PK |
| carrier_id | varchar | BK |
| carrier_name | varchar | |
| carrier_mode | varchar | |
| scac_code | varchar | |
| service_level | varchar | |
| on_time_pct | decimal |
Lookup tables (3)
dim_order_status
Grain: one row per fulfillment status
| Column | Type | Keys |
|---|---|---|
| order_status_key | bigint | PK |
| status_code | varchar | BK |
| status_name | varchar | |
| status_group | varchar |
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_ship_mode
Grain: one row per shipping mode
| Column | Type | Keys |
|---|---|---|
| ship_mode_key | bigint | PK |
| ship_mode_code | varchar | BK |
| ship_mode_name | varchar | |
| service_class | varchar | |
| is_expedited | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_order_fulfillment.order_date_key → dim_date (many-to-one)
- fct_order_fulfillment.product_key → dim_product (many-to-one)
- fct_order_fulfillment.warehouse_key → dim_warehouse (many-to-one)
- fct_order_fulfillment.customer_key → dim_customer (many-to-one)
- fct_order_fulfillment.carrier_key → dim_carrier (many-to-one)
- fct_order_fulfillment.order_status_key → dim_order_status (many-to-one)
- fct_inventory_snapshot.date_key → dim_date (many-to-one)
- fct_inventory_snapshot.product_key → dim_product (many-to-one)
- fct_inventory_snapshot.warehouse_key → dim_warehouse (many-to-one)
- fct_shipment.ship_date_key → dim_date (many-to-one)
- fct_shipment.product_key → dim_product (many-to-one)
- fct_shipment.warehouse_key → dim_warehouse (many-to-one)
- fct_shipment.customer_key → dim_customer (many-to-one)
- fct_shipment.carrier_key → dim_carrier (many-to-one)
- fct_shipment.ship_mode_key → dim_ship_mode (many-to-one)
- fct_purchase_order.order_date_key → dim_date (many-to-one)
- fct_purchase_order.product_key → dim_product (many-to-one)
- fct_purchase_order.supplier_key → dim_supplier (many-to-one)
- fct_purchase_order.warehouse_key → dim_warehouse (many-to-one)
- fct_purchase_order.uom_key → dim_uom (many-to-one)
- fct_purchase_order.ship_mode_key → dim_ship_mode (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.