Data modeling · Advanced Modeling

Transactional fact vs periodic snapshot

Mediumtransactional factperiodic snapshotsemi-additivegrain

Interview question

What is the difference between a transactional fact and a periodic snapshot fact?

How to answer

Transactional fact

Records discrete events as they happen. New events → new rows. Ideal for "what happened?"

Grain examples: 1 row per payment, 1 row per order line, 1 row per click.

Periodic snapshot fact

Records the state of something at regular intervals (end of day, end of month). Ideal for "what was the position?"

Grain examples: 1 row per account per day (balance), 1 row per SKU per warehouse per day (on_hand).

Additivity

Transactional amounts are usually fully additive. Snapshot balances are often semi-additive: sum across products/stores, not across time.

Interview tip

Pick from the question: events → transactional; balances / on-hand / headcount over time → periodic snapshot.