Data modeling · Foundations

Fact table and three fact types

Mediumfact tabletransactionalperiodic snapshotaccumulating snapshot

Interview question

What is a fact table? What are the three main types of facts?

How to answer

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.

Output
fct_order_item
  date_key | customer_sk | product_sk | order_id | qty | amount

Three classic types

  1. 1Transactional fact

1 row per business event (order line, payment, click). Grain example: 1 row per order line. Measures are usually additive (SUM(amount)).

  1. 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).

  1. 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.

Output
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.