Fact table
Also: facts
The central table in a star schema that stores measurable business events — one row per event (an order, a click, a payment) — with numeric measures and foreign keys to dimension tables.
A fact table records things that happen. In a data warehouse it sits at the center of a star schema and captures one business process — orders, payments, page views — with one row per event at a defined grain: one row per order line, one row per payment, one row per session. The table holds the quantitative measures you aggregate (quantity, amount, duration) plus foreign keys that point out to dimension tables.
Fact tables are tall and narrow: lots of rows, few columns. They're the table you SUM, COUNT, and AVG over, sliced by the descriptive attributes living in the dimensions. Keeping descriptive context out of the fact and in dimensions is what keeps the model clean and the queries fast.
There are three classic types of fact tables. A transaction fact table has one row per event as it happens — the most common and most granular. A periodic snapshot fact table captures the state of a process at regular intervals (daily account balances, end-of-day inventory). An accumulating snapshot fact table has one row per process instance that you update as it moves through milestones (an order placed → shipped → delivered, with a date column for each stage).
select
d.calendar_month,
c.customer_segment,
sum(f.line_amount) as revenue
from fct_order_lines f
join dim_dates d on f.date_key = d.date_key
join dim_customers c on f.customer_key = c.customer_key
group by 1, 2Almost every metric a business cares about — revenue, conversion, retention, utilization — is an aggregation over a fact table. Get the fact table right and the dashboards above it are trustworthy and fast; get it wrong and every number downstream is suspect.
The single most important decision is the grain — the exact definition of what one row represents. Declare it in one sentence and add a uniqueness test on the columns that define it before you build anything on top.
- Mixing grains in one fact table (e.g. order-level and line-level rows together), which silently double-counts every measure.
- Storing descriptive attributes (customer name, product category) on the fact instead of in dimensions — it bloats the table and duplicates data.
- Forgetting a uniqueness test on the grain, so duplicates creep in undetected.
- What is the difference between a fact table and a dimension table?
- A fact table stores measurable events with numeric measures and foreign keys; a dimension table stores the descriptive attributes (who, what, when, where) you slice those measures by. Facts are tall and narrow; dimensions are wide and short.
- What are the three types of fact tables?
- Transaction (one row per event), periodic snapshot (state captured at regular intervals), and accumulating snapshot (one row per process instance, updated as it hits milestones).
- ArchitectureImplementing Data Products in a Data Mesh: Essential Strategies and Practices
- ArchitectureWhat Is a Lakehouse? Architecture & Use-Cases Explained in Depth
- FundamentalsChange Data Capture Patterns for Analytics Pipelines: The Complete Guide
- FundamentalsSurrogate vs Natural Keys: Choosing the Right Primary Key for Databases
Learn this by building, not memorizing.
Definitions get you the vocabulary. The platform gets you the skill — graded exercises, real projects, and a portfolio capstone.