The Mental Model
Marts are the tables business users and dashboards should trust. Facts store measurable events; dimensions store descriptive context.
If staging is clean ingredients and intermediate is prep work, marts are the served plates. This is the layer people actually consume.
Dataset reference: ecommerce tables and grain assumptions.
Choose the time meaning of a dimension
Customer C7 ordered on January 10 while living in France, then moved to Germany on February 1. A current-state customer dimension classifies the old order as Germany. An order-time dimension classifies it as France. Both can be valid, but they answer different questions.
| Relation | Grain | Key rule |
|---|---|---|
| fct_orders | One row per completed order | Unique, non-null order_id |
| dim_customers_current | One row per customer | Unique customer_id |
| dim_customers_history | One row per customer version | Non-overlapping validity intervals |
For historical reporting, join on customer_id and the order timestamp within [valid_from, valid_to). A customer-only join to the history table duplicates orders. Specify whether the metric uses current attributes or order-time attributes before choosing the table.
Acceptance check: an order matches exactly one dimension version, including at a validity boundary.
Interactive Check
Question: Should customer country live in fct_orders or dim_customers?
Reveal the answer
Usually dim_customers owns customer country. fct_orders can join to it for analysis, but the descriptive customer attributes belong in the customer dimension.
Practice: Design an Ecommerce Mart
Create a simple star schema with fct_orders, dim_customers, and dim_products.
Use the guided lab below to record your result, assumptions, and the check that would catch an incorrect result.