Fact vs Dimension Tables: How to Tell Them Apart (With Examples)

In a dimensional model every table is either a fact table or a dimension table. Fact tables record measurements of a business process: an order was placed for 3 units at 19.99, a payment of 250.00 cleared, a patient was admitted. Dimension tables hold the descriptive context you use to filter, group and label those measurements: which customer, which product, which day, which hospital.

The distinction sounds obvious, but real source data rarely arrives labelled. A customers table has a lifetime value column. An orders table has a status. A subscriptions table is part event log, part state. This guide gives you practical tests for deciding which is which, then covers the three classic types of fact table and the mistakes that come from mixing them up.

The short version

How to tell them apart

Test 1: Is it a verb or a noun?

Business processes are verbs: order, ship, pay, enrol, admit, transfer. Each produces a fact table. The participants in those processes are nouns: customer, product, account, patient, branch. Each becomes a dimension.

If you are not sure whether something is an event, ask whether it has a timestamp that marks when it happened, as opposed to when the record was created. An order happened on a date. A product did not "happen"; it exists.

Test 2: Would you sum it or group by it?

For each column, ask how a report would use it.

• quantity, net_amount, duration_minutes: you sum or average these. They are measures and belong in a fact.
• product_category, customer_segment, region: you group by or filter on these. They are attributes and belong in a dimension.

Some numbers are attributes. A product's list_price or a customer's credit_limit describes the entity; you rarely sum it across rows. Put it on the dimension. If you also need the price actually charged on a sale, that is a separate measure on the fact.

Test 3: Does it change by updating or by appending?

A customer moves house: you update (or version) the customer row. A customer places a second order: you append a new order row. Things that are versioned are dimensions. Things that accumulate are facts.

Test 4: What happens at the grain?

Every fact table has a declared grain, such as one row per order line. A column belongs in that fact if it has exactly one value per row at that grain and is a measurement. Descriptive values that are single-valued at the grain are reached through a dimension foreign key instead.

A worked example: an e-commerce orders extract

Suppose your source gives you this denormalised export:

order_id, order_ts, customer_email, customer_name, customer_country,sku, product_name, category, qty, unit_price, discount, status

Classifying each column:

• order_id: identifier at the order level. It becomes a degenerate dimension on the fact: kept on the fact row for grouping and drill-through, with no dimension table of its own.
• order_ts: becomes order_date_key (to dim_date) and possibly an order time.
• customer_email, customer_name, customer_country: dim_customer.
• sku, product_name, category: dim_product.
• qty, unit_price, discount: measures on the fact.
• status: depends. If it is the current status of the order, it describes the order and could live in a small dim_order_status or as a degenerate attribute. If you need to measure how long orders spend in each status, that is a separate fact (see accumulating snapshots below).

The resulting star:

create table dim_customer (    customer_key     bigint primary key,    customer_id      varchar(40) not null,    email            varchar(255),    full_name        varchar(200),    country_code     char(2));​create table dim_product (    product_key      bigint primary key,    sku              varchar(40) not null,    product_name     varchar(200),    category         varchar(100));​create table fct_order_line (    order_date_key   integer not null,    customer_key     bigint  not null,    product_key      bigint  not null,    order_id         varchar(40) not null,   -- degenerate dimension    line_number      integer not null,    quantity         integer not null,    unit_price       numeric(12,2) not null,    discount_amount  numeric(12,2) not null,    net_amount       numeric(12,2) not null);

The three types of fact table

Kimball identifies three fundamental fact table types. Knowing which one you are building decides the grain, the load pattern and which measures are additive.

1. Transaction fact tables

One row per event, at the moment it happens. Order lines, card transactions, page views, lab results.

• Grain: one row per event.
• Load pattern: insert-only. Rows are not updated after the fact (corrections arrive as new reversing rows).
• Measures: usually fully additive.
• Sparse: if nothing happened, there is no row.

-- banking: one row per posted card transactioncreate table fct_card_transaction (    posted_date_key     integer not null,    account_key         bigint  not null,    merchant_key        bigint  not null,    transaction_id      varchar(64) not null,    amount              numeric(14,2) not null,    is_reversal         boolean not null);

2. Periodic snapshot fact tables

One row per entity per period, capturing state at a regular interval. Daily account balances, monthly inventory levels, month-end MRR per subscription.

• Grain: one row per entity per period.
• Load pattern: a full set of rows is inserted each period.
• Measures: often semi-additive. Balances sum across accounts but not across time.
• Dense: a row exists for every entity in every period, even if nothing changed.

-- banking: one row per account per business daycreate table fct_account_balance_daily (    snapshot_date_key   integer not null,    account_key         bigint  not null,    ledger_balance      numeric(16,2) not null,    available_balance   numeric(16,2) not null,    primary key (snapshot_date_key, account_key));

To report "average balance in March", use avg over the days, or sum balances across accounts and then average across days. Never sum across days.

3. Accumulating snapshot fact tables

One row per instance of a process with a defined start and end, updated as it moves through milestones. Order fulfilment (placed, paid, shipped, delivered), loan applications (submitted, underwritten, approved, funded), hospital stays (admitted, treated, discharged).

• Grain: one row per process instance.
• Load pattern: inserted when the process starts, then updated as milestones occur.
• Columns: multiple date keys, one per milestone, plus lag measures between them.

-- e-commerce: one row per order, tracking fulfilmentcreate table fct_order_fulfilment (    order_id                 varchar(40) primary key,    customer_key             bigint  not null,    placed_date_key          integer not null,    paid_date_key            integer,    shipped_date_key         integer,    delivered_date_key       integer,    days_placed_to_shipped   integer,    days_shipped_to_delivered integer,    order_amount             numeric(12,2) not null);

Milestone date keys are nullable until the milestone happens, or point to a special "not yet" row in dim_date. Most teams prefer the special row, because it keeps joins inner and filters simple.

Comparison

A fourth variant worth knowing is the factless fact table: a fact with foreign keys and no numeric measures. Student attendance (student, class, date) or product eligibility for a promotion are examples. You count rows instead of summing columns.

Additivity

Whether a measure can be summed is part of the fact design.

• Additive: sums correctly across every dimension. Sales amount, quantity.
• Semi-additive: sums across some dimensions but not time. Balances, inventory on hand, headcount.
• Non-additive: never sums. Ratios, percentages, unit prices, distinct counts.

Store non-additive values as their additive components. Instead of storing margin_pct, store revenue and cost and compute the ratio at query time. Averaging pre-computed percentages gives the wrong answer whenever the rows have different weights. This rule matters even more once you build a semantic layer on top; see how to define metrics in a semantic layer.

Common mistakes

Putting measures on dimensions. A total_orders or lifetime_value column on dim_customer looks convenient. It goes stale, it conflicts with what the fact table says, and it tempts people to sum it in a join, multiplying it by the number of fact rows. If you need it, compute it from the fact, or keep it explicitly as a dated snapshot attribute with a clear refresh rule.

Putting descriptive text on facts. Copying product_name onto every fact row bloats the fact and leaves you with old names on old rows and no clean way to restate history. Text belongs in dimensions.

Using a periodic snapshot as if it were a transaction fact. Summing a daily balance table across a month produces a number roughly 30 times too large. Label semi-additive measures clearly and define how they aggregate over time.

One fact table for several processes. Orders and returns are different processes with different grains and dates. A single fct_sales that holds both, with a type column, forces every query to filter correctly. Two facts sharing conformed dimensions is cleaner.

Snowflaking every dimension. Splitting dim_product into product, subcategory, category and department tables normalises it but adds joins and confuses users. On modern warehouses a wide, flat dimension is usually better. See star schema vs snowflake schema.

Practise in offdataai Learn

Classifying tables is a judgment skill and it improves with repetition on realistic sources. The Dimensional Modeling (Kimball) track in offdataai Learn gives you a business process and raw source columns, asks you to build the facts and dimensions in the Model Builder, and checks your work instantly: grain, keys, measure placement and fact type. It is hands-on and included in the Explorer plan at $1/month.

For reference designs, the dimensional modeling page and the industry templates show complete stars for retail, SaaS, banking and healthcare.