Retail & E-commerce Analytics data model
Kimball dimensional warehouse for omnichannel retail: transaction-grain sales, order-header funnel, periodic inventory snapshots, returns, and web events — sharing conformed customer, product, store, date, and promotion dimensions (SCD2 where history matters).
End-to-end retail and e-commerce analytics covering sales, orders, inventory, returns, and clickstream — built as a Kimball star schema with conformed dimensions so every business process is measured by the same customer, product, store, and date. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 15
- tables
- 163
- columns
- 28
- relationships
- 11
- 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
Transaction-grain fct_sales
Storing one row per order line is the lowest useful grain and lets every measure roll up to product, customer, store, or day without loss.
SCD2 on customer, product, employee
Loyalty tier, address, price, and role change over time; type-2 history preserves the value as-of each transaction.
Separate periodic snapshot for inventory
Inventory levels are semi-additive and best modeled as a daily snapshot rather than transactions.
Tables in the Retail & E-commerce 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 (5)
fct_sales
Grain: one row per order line item
Transaction-grain sales fact — the heart of the bus matrix.
| Column | Type | Keys |
|---|---|---|
| sales_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| product_key | bigint | |
| store_key | bigint | |
| channel_key | bigint | |
| promotion_key | bigint | |
| employee_key | bigint | |
| payment_method_key | bigint | |
| shipping_method_key | bigint | |
| order_number | varchar | |
| order_line_number | smallint | |
| quantity | integer | |
| unit_price | decimal | |
| discount_amount | decimal | |
| tax_amount | decimal | |
| gross_amount | decimal | |
| net_amount | decimal | |
| cost_amount | decimal | |
| margin_amount | decimal |
fct_orders
Grain: one row per order header
Order-header fact for funnel + fulfillment analysis.
| Column | Type | Keys |
|---|---|---|
| order_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| store_key | bigint | |
| channel_key | bigint | |
| payment_method_key | bigint | |
| shipping_method_key | bigint | |
| order_number | varchar | |
| order_status | varchar | |
| item_count | integer | |
| subtotal | decimal | |
| discount_total | decimal | |
| shipping_cost | decimal | |
| tax_total | decimal | |
| order_total | decimal |
fct_inventory_snapshot
Grain: one row per product per store per day (periodic snapshot)
Periodic snapshot fact — semi-additive (snapshot quantities).
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| product_key | bigint | |
| store_key | bigint | |
| quantity_on_hand | integer | |
| quantity_on_order | integer | |
| quantity_committed | integer | |
| reorder_point | integer | |
| units_sold | integer | |
| stockout_flag | boolean | |
| inventory_value | decimal |
fct_returns
Grain: one row per returned line item
| Column | Type | Keys |
|---|---|---|
| return_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| product_key | bigint | |
| store_key | bigint | |
| order_number | varchar | |
| reason_code | varchar | |
| quantity_returned | integer | |
| refund_amount | decimal | |
| restock_flag | boolean |
fct_web_events
Grain: one row per web/app interaction event
Event-grain clickstream fact.
| Column | Type | Keys |
|---|---|---|
| event_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| product_key | bigint | |
| channel_key | bigint | |
| session_id | varchar | |
| event_type | varchar | |
| event_timestamp | timestamp | |
| page_url | varchar | |
| device_type | varchar | |
| referrer | varchar |
Dimension tables (10)
dim_date
Grain: one row per calendar day
Conformed date dimension shared by every fact.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| day_name | varchar | |
| day_of_month | smallint | |
| day_of_year | smallint | |
| week_of_year | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| fiscal_period | varchar | |
| is_weekend | boolean | |
| is_holiday | boolean | |
| holiday_name | varchar |
dim_customer
Grain: one row per customer per SCD2 version
Type-2 SCD: tracks loyalty tier and address history.
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| date_of_birth | date | PII |
| gender | varchar | |
| loyalty_tier | varchar | |
| loyalty_points | integer | |
| address_line1 | varchar | PII |
| city | varchar | |
| state_province | varchar | |
| postal_code | varchar | PII |
| country | varchar | |
| customer_segment | varchar | |
| acquisition_channel | varchar | |
| signup_date | date | |
| is_active | 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 | |
| brand | varchar | |
| category | varchar | |
| subcategory | varchar | |
| department | varchar | |
| supplier_key | bigint | |
| unit_cost | decimal | |
| list_price | decimal | |
| color | varchar | |
| size | varchar | |
| weight_grams | integer | |
| is_active | boolean |
dim_store
Grain: one row per store / channel location
| Column | Type | Keys |
|---|---|---|
| store_key | bigint | PK |
| store_id | varchar | BK |
| store_name | varchar | |
| store_type | varchar | |
| address_line1 | varchar | |
| city | varchar | |
| state_province | varchar | |
| region | varchar | |
| country | varchar | |
| manager_name | varchar | PII |
| open_date | date | |
| square_footage | integer |
dim_supplier
Grain: one row per supplier
| Column | Type | Keys |
|---|---|---|
| supplier_key | bigint | PK |
| supplier_id | varchar | BK |
| supplier_name | varchar | |
| contact_email | varchar | PII |
| country | varchar | |
| lead_time_days | smallint | |
| rating | decimal |
dim_employee
Grain: one row per employee per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| employee_key | bigint | PK |
| employee_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| role | varchar | |
| department | varchar | |
| store_key | bigint | |
| hire_date | date | |
| is_active | boolean |
dim_promotion
Grain: one row per promotion
| Column | Type | Keys |
|---|---|---|
| promotion_key | bigint | PK |
| promotion_id | varchar | BK |
| promotion_name | varchar | |
| promotion_type | varchar | |
| discount_pct | decimal | |
| start_date | date | |
| end_date | date | |
| channel | varchar |
dim_payment_method
Grain: one row per payment method
| Column | Type | Keys |
|---|---|---|
| payment_method_key | bigint | PK |
| method_code | varchar | BK |
| method_name | varchar | |
| provider | varchar |
dim_shipping_method
Grain: one row per shipping method
| Column | Type | Keys |
|---|---|---|
| shipping_method_key | bigint | PK |
| method_code | varchar | BK |
| carrier | varchar | |
| service_level | varchar | |
| estimated_days | smallint |
dim_channel
Grain: one row per sales channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_sales.date_key → dim_date (many-to-one)
- fct_sales.customer_key → dim_customer (many-to-one)
- fct_sales.product_key → dim_product (many-to-one)
- fct_sales.store_key → dim_store (many-to-one)
- fct_sales.channel_key → dim_channel (many-to-one)
- fct_sales.promotion_key → dim_promotion (many-to-one)
- fct_sales.employee_key → dim_employee (many-to-one)
- fct_sales.payment_method_key → dim_payment_method (many-to-one)
- fct_sales.shipping_method_key → dim_shipping_method (many-to-one)
- fct_orders.date_key → dim_date (many-to-one)
- fct_orders.customer_key → dim_customer (many-to-one)
- fct_orders.store_key → dim_store (many-to-one)
- fct_orders.channel_key → dim_channel (many-to-one)
- fct_orders.payment_method_key → dim_payment_method (many-to-one)
- fct_orders.shipping_method_key → dim_shipping_method (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.store_key → dim_store (many-to-one)
- fct_returns.date_key → dim_date (many-to-one)
- fct_returns.customer_key → dim_customer (many-to-one)
- fct_returns.product_key → dim_product (many-to-one)
- fct_returns.store_key → dim_store (many-to-one)
- fct_web_events.date_key → dim_date (many-to-one)
- fct_web_events.customer_key → dim_customer (many-to-one)
- fct_web_events.product_key → dim_product (many-to-one)
- fct_web_events.channel_key → dim_channel (many-to-one)
- dim_product.supplier_key → dim_supplier (many-to-one)
- dim_employee.store_key → dim_store (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.