Selecting the Right Fact Table for an Analytical Process

Hello! Last lesson, you declared a fact table’s grain as a precise statement of what one row records. You also tested whether each proposed measure belongs to that observation. Now you can make a related design choice: what kind of observation is the table meant to retain?

A row might record an individual event, the state of something at a scheduled interval, or the progress of one case through a workflow. Sometimes the occurrence itself is all you need to record, with no numeric measure at all. This lesson will help you select among transaction, periodic snapshot, accumulating snapshot, and factless fact tables. Allow about 40 minutes, including the video and two short readings.

---

Choose by the question the row answers

Imagine an order-fulfillment operation. You could reasonably ask four different questions about it:

• Which order lines were placed yesterday?
• How many units remained in stock at each day’s close?
• How long did each order line take to reach delivery?
• Which products were eligible for a promotion on a given day?

These questions do not call for four alternative layouts of the same row. They describe different observations. The first concerns an event, the second a state sampled repeatedly, the third a process with milestones, and the fourth a recorded condition of eligibility. A good selection starts by finishing the sentence “Each row records one…” before deciding on columns or keys.

Watch The 3 Common Types of Fact Tables | Data Modeling 101 from Kahan Data Solutions for the visual distinction between events, states, and workflows. The video covers the three time-based patterns; we will then add factless tables, which describe what a row contains rather than introducing a fourth time pattern.

The comparison image below gives you a compact reference. Read its grain, date dimension(s), and fact table updates rows first. Its snapshot columns describe common designs, not mandatory table sizes or universal performance rankings.

Ralph Kimball’s Fact Tables, from the Kimball Group, places these patterns in the context of the measurement event. Read its short “Three Kinds of Fact Tables” section, particularly the difference between the unpredictable occurrence of transactions and the deliberately scheduled coverage of periodic snapshots.

---

Transaction fact: one captured event

A transaction fact table records an occurrence at the grain you declared. If your invoicing source captures issued invoice lines, a suitable sentence is:

Each row records one issued invoice line, identified by source system, invoice ID, and line number.

The row might hold the billed product and customer keys, invoice date key, quantity, and extended billed amount. Once the line is issued and loaded, you ordinarily leave the row unchanged except for a correction. A later credit note can be recorded as its own event if that is how the business process captures corrections.

Transaction rows are sparse with respect to possible combinations. A product with no invoice lines yesterday simply has no row for yesterday; you do not insert a zero-quantity invoice line to make a complete product-by-day grid. Events also arrive irregularly. One day may generate thousands of lines, another very few.

The distinguishing question is: Do you need to preserve each occurrence separately? If you need to trace an invoice line, count issued lines, or analyze the exact billed amount at the line level, choose a transaction fact. A monthly revenue report can then aggregate those lines. As the previous lesson showed, an order-wide charge does not become a valid invoice-line measure simply because it appears beside every line in a source extract.

Not every transaction fact needs an amount. That point will matter when we reach factless tables.

---

Periodic snapshot: one entity’s state for each scheduled period

A periodic snapshot fact table samples an entity at a regular interval. For inventory, you might declare:

Each row records the end-of-day stock position for one product in one store on one calendar date.

stock_on_hand is meaningful as of that date. If a product has 12 units at Monday’s close and 9 at Tuesday’s close, adding those balances gives 21, which is not stock on hand on either day. By contrast, summing the Monday balances across stores may give a meaningful Monday total, provided the units and product scope are compatible. The measure’s aggregation rules deserve their own attention in the next lesson.

A snapshot is not just a transaction table with fewer rows. Its row means something different: what was the condition at the observation point? You may derive that condition from stock movements, but you can also obtain it from an authoritative daily balance source. Likewise, a monthly account snapshot might contain an end-of-month balance and the month’s deposit total. Both belong to the same account-month row, yet they answer different questions: a closing state versus activity during the period.

A deliberate snapshot design also specifies coverage. If you promise one row per active product and store each day, insert a row even when no movement occurred, carrying forward the closing position where appropriate. Without that rule, an absent row is ambiguous: was stock zero, was the product inactive, or did the load fail? You need not materialize every conceivable product-store combination—only the reporting population your grain and coverage policy define.

Choose a periodic snapshot when regularly comparable states or period summaries are central to the analysis. Its regularity makes trend reporting straightforward; its coarser grain cannot recover individual stock movements or invoice lines that were never retained elsewhere.

Do not infer additivity from the table label. An end-of-day balance is not additive across days. A units_received_during_day measure stored alongside it can be summed across days if its definition supports that calculation. “Periodic” describes the observation schedule, not a single aggregation rule for every column.

---

Accumulating snapshot: one case across a bounded workflow

A transaction asks what happened at a particular occurrence; a periodic snapshot asks what the state was at a scheduled observation. An accumulating snapshot instead asks how one identifiable case progressed through a process.

Suppose an order line normally passes through placement, shipment, and delivery. Declare:

Each row records the current milestone status of one order line from placement through delivery or cancellation.

You insert the row when the order line enters the process. It might initially have a placement date but no shipment or delivery date. When shipment occurs, you update that same row with the shipment milestone; delivery produces another update. Multiple date keys represent different business roles, not multiple grains. The design supports questions such as “How many days elapsed between placement and delivery?” and “Which lines are still awaiting shipment?”

This pattern works when the process has a recognizable start, end, and useful set of milestones. The path need not be identical for every case—a cancellation may end one line early—but the milestones you intend to analyze must be sufficiently defined to give the columns stable meanings. If the process is open-ended, repeatedly cycles through the same stage, or requires a complete history of every state change, a single accumulating row alone will not retain that history. You would need event records as well.

The important operational consequence is mutability. Unlike an ordinary transaction fact, an accumulating row is revisited as new milestones arrive. A delayed shipment event must update the correct order-line observation, and a date not yet reached must be distinguishable from a date that does not apply. We will design milestone-date columns in detail later in the course; for now, use that requirement to recognize when this pattern fits.

---

Factless fact: the occurrence or condition is the information

A factless fact table has dimension keys, and perhaps an event identifier, but no numeric measure column. You obtain a count by counting rows at its declared grain.

Read the “Factless fact tables” subsection in Microsoft Learn’s Modeling Fact Tables in Warehouse. It gives the basic event interpretation; we will distinguish it from eligibility or coverage immediately afterward.

Consider an exam-attendance record. If the business question is “How many students attended each subject’s exam?”, a row can establish the attendance occurrence without storing a redundant attendance_count = 1.

There are two related uses worth separating:

• Observed event: One row means something happened—one student attended one exam sitting, for example. Count rows to count those observations. If a student can attend multiple sittings on one date, add a sitting or attempt identifier to preserve the grain.
• Coverage or eligibility: One row means a combination was in scope—one product was eligible for one promotion in one store on one date. It does not mean a sale occurred. Comparing eligibility with separately recorded sales could later reveal eligible products that did not sell.

Factless is not mutually exclusive with the first three patterns. An attendance occurrence is event-like and can have transaction grain while being factless. Scheduled daily eligibility can have a date-by-product-by-store coverage grain while being factless. Ask two questions independently: What does a row represent over time? and Does that row have a numeric measure worth storing?

An absent factless event row proves only that no event was recorded under the table’s capture rules. To call it a “missed” event, you also need a defined population of expected or eligible events. That distinction prevents a sparse attendance table from being mistaken for a complete attendance register.

---

Make the selection explicit

Here is a compact decision guide you can apply before designing the columns:

If the analytical process asks primarily…	Select…	Declare one row as…
What individual actions occurred?	Transaction	One captured event, such as an issued invoice line
What was each entity’s state at a regular interval?	Periodic snapshot	One entity or defined combination for one scheduled period
How far has each case progressed through a defined workflow?	Accumulating snapshot	One case across its milestone lifecycle
Which occurrences or eligible combinations existed, with no numeric measurement needed?	Factless	One observed occurrence or one covered combination

The choices can coexist. An order-line transaction fact preserves placement; an accumulating snapshot gives the current fulfillment milestones for that same line. Daily inventory snapshots give closing positions, while stock-movement transactions—if retained—explain individual changes. They are not duplicates merely because they share product or date dimensions: their row sentences differ.

Selection also does not guarantee correctness. For each proposed table, check its source observation, row identifier, date meaning, and expected load behavior. If you cannot say whether a row should appear on a day with no activity, or whether a shipment changes an existing row, the pattern has not yet been specified tightly enough for an ETL design.

---

Conclusion

Use the row’s business meaning and lifecycle to choose a fact-table pattern. Transactions preserve individual occurrences; periodic snapshots record regularly sampled states or period summaries; accumulating snapshots update one case as defined milestones arrive. A factless table records an event or coverage condition without a numeric measure, and that property can overlap a time-based pattern.

These distinctions build directly on your grain declaration: “one invoice line,” “one product-store-day closing position,” and “one order line through fulfillment” lead to different tables even when they reference similar dimensions. Next, you will classify the measures placed in those tables as additive, semi-additive, or non-additive and specify where summing them is valid.