Loading lesson...
Transaction, periodic, or accumulating snapshot: the wrong choice corrupts every downstream metric
Transaction, periodic, or accumulating snapshot: the wrong choice corrupts every downstream metric
Topics covered: "What Kind of Fact Table Would You Use?", Transaction Facts: One Row Per Event, Periodic Snapshots: One Row Per Period, Accumulating Snapshots: Lifecycle Tracking, Factless Facts and Coverage Tables
Classify in 30 Seconds: The Interviewer Is Timing You The 60-Second Framework Step 4 bridges to the Additive Measures pattern. Naming additivity unprompted when you define measures shows the interviewer you are thinking about how this table will be aggregated, not just how it will be loaded. Why Companies Care Cite these in your answer: 'At Uber, the ride fact was a transaction. When the business needed lifecycle tracking (requested, assigned, in-progress, completed, canceled), a transaction fac
Transaction facts are the most common fact table type and the one most candidates get right. The grain is one row per discrete business event. Rows are inserted and never updated. This is the natural model for clicks, payments, orders, log entries, and any event stream. The Schema the Interviewer Expects The Trap: When the Interviewer Asks for Current State Transaction facts cannot answer 'what is the current state?' questions. They can tell you every event that happened, but not the net result
The interviewer asks: 'What was the total account balance on March 31?' If you reach for SUM(balance) across the month, you have just failed. Balance is semi-additive: you can SUM it across accounts but never across time. This is the trap that separates candidates who understand periodic snapshots from candidates who only know transaction facts. The Schema the Interviewer Expects The Semi-Additive Trap Correctly identifying balance as semi-additive and explaining why you cannot SUM it across tim
Accumulating snapshots are the least understood fact table type and the one that creates the most interview separation. The grain is one row per lifecycle instance (one order, one loan application, one support ticket). The row is created at the first milestone and updated as subsequent milestones are reached. This is the only fact table type where rows are updated. The Schema the Interviewer Expects The Update Pattern: Why This Is the Only Updatable Fact Type The Idempotency Guard You Must Menti
Factless facts are the interview curveball. A fact table with no measures. It records that a relationship existed, not that a measurable event occurred. Most candidates have never encountered one, and it trips them up because it violates the assumption that fact tables contain numbers. The Tell: Words That Signal This Pattern Two Types of Factless Facts The Power Query: What Did NOT Happen Your factless fact answer: 'The coverage fact lets me answer what did NOT happen. Products on promotion tha