A fact table stores measurable business events or snapshots at a declared grain. Rows are usually many; columns are keys to dimensions plus numeric measures.
fct_order_item date_key | customer_sk | product_sk | order_id | qty | amount
Three classic types
1. Transactional fact One row per business event (order line, payment, click). Grain example: one row per order line. Measures are usually additive (SUM(amount)).
2. Periodic snapshot fact One row per entity per time bucket (account balance at end of day, inventory on hand each morning). Grain example: one row per product per warehouse per day. Measures may be semi-additive (sum across products, not across days without care).
3. Accumulating snapshot fact One row that tracks a process with milestones (order placed → shipped → delivered). Columns update as steps complete (ship_date, deliver_date, lag metrics). Grain example: one row per order fulfillment lifecycle.
Transactional: insert-heavy event log of sales Periodic snapshot: daily inventory photo Accumulating: order pipeline with milestone dates updated in place
Interview tip
Name the type from the business question. "What sold?" → transactional. "What was on hand yesterday?" → periodic snapshot. "Where is this order in the pipeline?" → accumulating snapshot.