Prompt
How do I model historical data in a warehouse for trend analysis?
Latest observation
To model historical data in a warehouse for trend analysis, the main goal is to preserve time-variant history so you can answer questions like:
- How did sales change over time?
- What was the state of a customer/product at a given point?
- How did metrics compare month over month or year over year?
Here’s the usual approach.
1) Use a dimensional model
For trend analysis, a star schema is typically best:
- Fact tables store measurable events or snapshots
- Dimension tables store descriptive context
Example:
fact_saleswith measures likerevenue,units_solddim_date,dim_product,dim_customer,dim_store
This makes time-based aggregation easy and fast.
2) Choose the right fact table type
Historical modeling depends on the type of analysis you need.
A. Transaction fact
Use when each row represents an event. Example: every sale, click, or shipment.
Good for:
- Daily/weekly/monthly trends
- Event-level analysis
- Drill-down from summary to detail
Typical columns:
date_keyproduct_keycustomer_key- measures:
amount,quantity
B. Periodic snapshot fact
Use when you want to capture the state at regular intervals, like daily balances or monthly inventory.
Good for:
- Balance trends
- Inventory over time
- Headcount over time
Typical columns:
snapshot_date_keyproduct_key- measures:
inventory_on_hand,account_balance
C. Accumulating snapshot fact
Use for process lifecycle tracking, such as order fulfillment stages.
Good for:
- Cycle time analysis
- Pipeline aging
- Process bottlenecks
3) Make dimensions historical too
If dimension attributes change over time, use Slowly Changing Dimensions (SCDs).
SCD Type 1
Overwrite old values.
- Use when history does not matter
- Example: correcting a typo
SCD Type 2
Create a new row for each change.
- Use when you need full history
- Example: a customer changes region, and you want to know what region they belonged to at the time of a sale
Typical fields:
effective_start_dateeffective_end_dateis_current
SCD Type 3
Store limited previous values in extra columns.
- Use for simple “before/after” comparisons
For trend analysis, Type 2 is the most common choice.
4) Use a proper date dimension
A dim_date table is essential for trend queries.
Include:
- day, week, month, quarter, year
- fiscal periods
- holiday flags
- weekday/weekend indicators
This makes rollups and comparisons much easier.
5) Keep grain consistent
Define the grain of each table clearly.
Examples:
- One row per sale line item
- One row per customer per day
- One row per product per month
Do not mix multiple grains in one fact table. That causes confusion and incorrect aggregations.
6) Store timestamps and loading metadata
Even if you summarize data, keep:
- source event timestamp
- load timestamp
- batch/job ID
- source system
This helps with:
- auditing
- late-arriving data
- reprocessing
- debugging
7) Handle late-arriving and corrected data
Historical data often changes after initial load.
You may need to:
- update facts when transactions are corrected
- version dimensions with SCD Type 2
- allow backfills for late data
- maintain audit trails
8) Consider summary tables or materialized views
For frequent trend queries, create:
- daily aggregates
- monthly aggregates
- KPI summary tables
This improves performance while keeping the detailed fact table for drill-down.
9) Example structure
A simple sales warehouse might look like:
dim_date(date_key, date, month, quarter, year, …)dim_product(product_key, product_id, product_name, category, effective_start_date, effective_end_date, is_current)dim_customer(customer_key, customer_id, segment, region, effective_start_date, effective_end_date, is_current)fact_sales(sales_key, date_key, product_key, customer_key, store_key, quantity, revenue)
Then you can query trends like:
- revenue by month
- revenue by region over time
- customer segment trends year over year
10) Key design principles
- Model at the lowest useful grain
- Preserve history where it matters
- Use SCD Type 2 for changing descriptive data
- Add a rich date dimension
- Separate detailed facts from aggregated summaries
If you want, I can also show:
- a sample star schema diagram,
- SQL DDL for historical tables, or
- a concrete example for sales, inventory, or customer history.