A transaction fact table holds one row per business event - a sale, a click, a payment, a shipment. It's the most common and most intuitive fact pattern: rows are inserted once when the event happens and never changed, and the measures are fully additive, so they sum correctly across every dimension. When people picture a fact table, this is usually the one they have in mind.

Event grain, insert-only

The grain is the individual event, declared as precisely as possible - "one row per line item on a sales order," not the vaguer "one row per order." Each row records the dimensional context of the event (when, what, who, where) and its measures. Once written, a transaction row is immutable: the event happened, and it doesn't un-happen. That insert-only nature makes loads simple - append new events, never update - and makes the table the largest in the warehouse by row count, since it grows with raw activity.

fact_sales (
  sale_id,                       -- degenerate dimension (the receipt number)
  date_key, time_key,
  product_key, store_key, customer_key, promotion_key,
  quantity, unit_price, discount_amount, sales_amount   -- all additive
)
-- grain: one row per product sold per transaction

Fully additive measures

Transaction measures are fully additive - quantity, sales amount, discount - meaning they can be summed across any combination of dimensions and the total is always meaningful. This is what makes transaction tables so flexible: "revenue by product by region by month" is a straightforward sum-and-group-by, with no semi-additive caveats to worry about. Store ratios and prices as their additive components (sum the amounts, sum the quantities, divide at query time) rather than as pre-computed averages that can't be re-aggregated.

Strengths and limits

Transaction tables answer the widest range of questions because they retain the full event detail - you can always roll up, filter, and slice. Their limits are the flip side: they're large, and questions about state ("what was the balance?") or process duration ("how long from order to delivery?") are awkward to answer from raw events. Those are where the periodic snapshot and accumulating snapshot patterns earn their place, and where an aggregate fact table speeds up the common rollups.

Frequently asked questions

What is the grain of a transaction fact table? A single business event, declared as specifically as the source allows - typically the lowest-level atomic event such as a sales line item, so the table can answer questions at any coarser level by aggregation.

Are transaction fact tables ever updated? Almost never - they're insert-only by design. Corrections are usually handled by inserting reversing rows rather than mutating history.

What's a degenerate dimension here? An identifier like an order or receipt number that lives on the fact row with no separate dimension table - it groups line items of the same transaction without needing its own table. See degenerate dimensions.

Why are these tables so large? Because they grow one row per real-world event. Manage size with partitioning by date and, for repeated rollups, pre-built aggregates.