The 3 Types of Facts (Data Modeling 101)

A fact table sits in the middle of a star schema and holds the metrics the business actually measures, and there are three common types, each answering a different question. A transaction fact table stores one row per event, inserted once and never updated, and it's the default starting point. A periodic snapshot stores the state of an entity at a fixed interval, like an account balance per day or inventory per month. An accumulating snapshot keeps one row per entity and updates it as that entity moves through a fixed set of steps, like order, ship, deliver, and most warehouses end up using two or all three of these together.

1. Transaction fact table

If you're building a star schema at your company, you need a fact table. Some are obvious and straightforward. Most require a deeper understanding of the data and, more importantly, of how the business actually uses and analyzes it.

The three types below cover the situations you'll run into most. Some you may already have. Others you may never need.

The default starting point

One row, one event. The transaction fact table is the standard approach and where you'll most likely start. One row in the table is a single event or action at a moment in time, inserted once and never updated.

The classic examples:

• fct_orders: one row per order.
• fct_order_line_items: one row per line on an order, a more granular version of the same thing.
• Payments and page views: the same idea, one row each time something happens.

Each of these represents something that happened, while a dimension provides the context around it.

What's in a transaction fact

A typical fct_orders looks simple. It holds two things:

• Keys to the related dimensions, like customer_key, product_key and order_date_key.
• Measures for that event: quantity, amount, discount, whatever you'll aggregate on.

That's it, keys and measures.

When you want the descriptions, you don't store them in the fact. You join out to the dimensions in a later query and pull in whatever you need.

The exception: a degenerate dimension

A degenerate dimension is a descriptive value that lives directly in the fact table because it has nothing else related to it. Order number is the usual example.

Imagine you've handled every description that matters through the keys: customer, product, location. You're left with the order number and you're wondering where it goes.

Creating a separate dim_orders doesn't help, because it would sit at the same grain as the fact table and there's nothing to add to it.

So you skip the join, avoid the waste, and put the order number straight on the fact. I covered this in more depth in the dimensions post linked below.

Why it matters

It's the most granular version of what happened. The goal with transaction facts is to get as granular as possible, because that gives you the most options.

You can always roll a low-level table up into something coarser. You can't go the other way.

2. Periodic snapshot fact table

Speaking of rolling up, the periodic snapshot is the next step. It's a fact table that captures the state of an entity at a fixed period of time.

The common examples:

• Account balance, daily.
• Inventory, monthly.
• Headcount, weekly.

You use it when you care about those specific groupings of time rather than every individual event.

Entity plus period is the grain

Take fct_account_balance_daily. Each row is one account on one day with its balance.

An account shows up many times across the table, but only once for any given day. The combination of account and day is what makes a row unique, and that's the grain.

Snapshot table or mart?

You could in theory get the same result from a transaction fact. Store every deposit and withdrawal, then roll them up later. If you follow a three-layer data model, that aggregation would live in the marts layer on top of the transaction fact.

Both approaches work. The question is whether this is something you'll always want to see this way, and whether it's easier to manage as its own fact table than to recompute in every mart. If so, build the rolled-up version as a periodic snapshot.

The point isn't that one option is better. It's that you have the option, and you need to understand what makes sense for your team and your use cases.

Why it matters

Some metrics only make sense as state, not as events. An account balance is a good example. You may never care about it at the transaction level, only what it was at the end of each day or each month.

Snapshots make that kind of trend reporting easy without re-aggregating a stream of events every time someone asks.

3. Accumulating snapshot fact table

The accumulating snapshot is the nuanced one, and it's fairly rare, because it's a fact table you actually update. You use it when you're tracking an entity through a fixed-step pipeline.

The word fixed is important. This isn't an open-ended process where you don't know how many steps there will be. There are clear, known steps, and the row updates as the entity moves through them.

One row per order, updated at each step

The common case is dates. Say you have fct_order_pipeline. You want one row per order, and you know the process has three clear steps: an order date, a ship date and a delivery date.

Instead of logging each of those as its own record, you keep one row for the order and update it each time the next event happens:

• When the order is placed, you insert the row with the order date key.
• When it ships, you stamp the ship date key.
• When it's delivered, you stamp the delivery date key.

Until each step happens, that key is null.

The role-playing join

This ties directly to the role-playing dimension from the dimensions post. The three date keys all point to the same dim_date, so when you want more information about any of them, you join to the same dimension three times under different aliases.

select
    p.order_id,
    od.full_date as order_date,
    sd.full_date as ship_date,
    dd.full_date as delivery_date
from fct_order_pipeline p
left join dim_date od on p.order_date_key    = od.date_key
left join dim_date sd on p.ship_date_key     = sd.date_key
left join dim_date dd on p.delivery_date_key = dd.date_key

Other processes with the same shape

Order pipelines are one example. Others that follow the same shape:

• Mortgage applications.
• Hiring funnels.
• Claims processing.

In each case there's a clear, fixed number of steps that something moves through, so you can go back and update the row with confidence.

Why it matters, and the trade-off

You get direct measurement of pipeline lag and conversion. On one row you can see the days from order to ship and from order to delivery without stitching events together.

The trade-off is that the rows are mutable. They're touched more than once after they're loaded, so you need update logic and update permissions on the table. More moving parts.

Before you build one, ask three questions:

• Do you need to track something through time?
• Is it a fixed number of steps?
• Do you have the permissions to update those rows?

A mental shortcut

Match the type to the question you're answering.

• Transaction is an event. The history of what happened. If you need to see every individual event, that's your answer.
• Periodic snapshot is state. What things looked like at regular intervals.
• Accumulating snapshot is a process. A fixed life cycle, funnel or pipeline you want to track step by step.

These are complementary, not competing. You'll probably use a few or all of them in the same warehouse.

The goal is to understand the options well enough to pick the right one for each situation. That's most of what data modeling is: nuance, customization and critical thinking before you build.

Related reading

• The 4 Types of Dimensions
• The Struggle of Data Modeling (Facts vs Dimensions)
• Do You Need a Date Dimension?
• Creating a Data Model with dbt: Facts

Final takeaway

Nearly every warehouse I help a team design starts with transaction facts, and the snapshot types show up once the business starts asking about balances, inventory levels or how long a process takes. Knowing the three types ahead of time means those questions get a modeled answer instead of a one-off query.