The short answer
A 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 | amountThree classic types
- 1Transactional fact
1 row per business event (order line, payment, click). Grain example: 1 row per order line. Measures are usually additive (SUM(amount)).
- 1Periodic snapshot fact
1 row per entity per time bucket (account balance at end of day, inventory on hand each morning). Grain example: 1 row per product per warehouse per day. Measures may be semi-additive (sum across products, not across days without care).
- 1Accumulating snapshot fact
1 row that tracks a process with milestones (order placed → shipped → delivered). Columns update as steps complete (ship_date, deliver_date, lag metrics). Grain example: 1 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 placeInterview 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.