Fact tables are where the numeric measures of a data engineer's warehouse live. Each fact table has a declared grain (one row per X), FKs to dimensions that apply at that grain, and one or more numeric measures. Three fact table types appear in 2026 data engineer interviews: transaction fact, periodic snapshot fact, accumulating snapshot fact. Each fits a different analytical workload.

Transaction fact: one row per event at the moment it happens. One row per order line item, one row per impression, one row per click, one row per trip. The most common fact type, used for activity tracking and event aggregation. Measures are usually additive (revenue, quantity, duration). FKs point to dim_customer, dim_product, dim_date_time, dim_store. The grain is atomic and immutable: once a row is written, it does not change (corrections become new rows in a slowly-changing fact pattern).

Periodic snapshot fact: one row per entity per period, capturing state at the end of the period. One row per account per day with the daily balance, one row per inventory item per week with the weekly count. Measures are usually semi-additive: sum across customer or product (additive across entity), do not sum across time (you take the latest or average; summing a daily balance across 30 days does not produce a monthly balance). The benefit is fast period-over-period analysis without aggregating the transaction fact. The cost is data redundancy (the same daily-balance row repeats unchanged from yesterday if nothing happens).

Accumulating snapshot fact: one row per process instance, updated as the process moves through stages. One row per order with timestamps for order_placed, paid, shipped, delivered. The row starts with order_placed_ts populated and the other timestamps NULL; it updates as each milestone happens. Measures are durations (paid_ts minus placed_ts, shipped_ts minus paid_ts) and the row is mutable until the process ends. Useful for funnel analysis, lifecycle tracking, and SLA monitoring. Less common than transaction fact but distinct enough to be tested in data engineer modeling rounds at L5+.

Fact measure additivity is a recurring question. Additive measures sum across all dimensions (revenue, quantity, cost). Semi-additive measures sum across some dimensions but not others (account balance sums across customers but the daily balance does not sum across dates: you take the latest balance, not the sum of 30 daily balances). Non-additive measures (ratios, percentages, distinct counts) must be computed at the desired aggregation level (do not pre-compute a customer-day-level conversion rate and expect it to roll up to month: aggregate the raw counts, then compute the ratio). Identifying additivity per measure is part of the data engineer modeling rubric.

Degenerate dimensions on the fact: a natural key stored on the fact without a separate dim table because there are no attributes beyond the key itself. Order_number, invoice_number, transaction_id. Storing them on the fact preserves traceability back to the source system without the overhead of a single-column dim table. Senior data engineer modeling rounds explicitly ask for degenerate dimensions to be identified; junior rounds often skip this nuance.

Data Modeling (57)

• A/B Experiment Assignment Schema - medium - One user, one experiment, one variant. No exceptions.
• Where They Used to Live - medium - They moved. The data stayed behind.
• Airline Flight Operations Schema - medium - Flights, passengers, and routes. Before you draw a single table, tell me the grain.
• A Number for the Seller - easy - They want a total. Give them the right schema first.
• B2B Invoicing Data Model - easy - Invoices go out, partial payments trickle in, and some customers are three months overdue.
• Clickstream and Session Schema - medium - Millions of clicks, mostly anonymous.
• Cloud File Storage Metadata Schema - hard - A file is also a folder. A folder is also a file.
• Content Engagement Data Model - hard - Post published. Now measure everything that happens next.
• Content Search and Discovery Schema - hard - Searchable from every angle. Design it so nothing gets lost.
• Customer Address History - easy - People move. Sometimes twice in a month. How do you remember where everyone was, and when?