5 / 6 OLTP, OLAP & Analytical Stores

Mixed Grain

A proposed fact table for the warehouse, one row per order line:

column example value
order_line_id 88213
order_id 4471
date_key 20260317
customer_key 9021
product_key 553
quantity 3
line_amount_cents 4497
order_shipping_cents 899
customer_lifetime_orders 42

Analysts run SELECT sum(order_shipping_cents) ... GROUP BY month and the totals come out well above what finance reports. What explains it?

A

line_amount_cents and order_shipping_cents are both fully additive, so any query that does not also group by order_id will double count both of them.

B

order_shipping_cents is measured once per order, not per line, so an order with three lines repeats the same 899 three times and any sum() over lines multiplies it.

C

customer_lifetime_orders is a fact stored in the fact table rather than in the customer dimension, and it drifts as new orders arrive.

D

date_key is a plain integer rather than a foreign key into a date dimension, so grouping by month buckets rows against the wrong calendar.