Module 6: Accumulating And Cumulative Patterns

The accumulating snapshot fact table

Next →

Description

Kimball describes three kinds of fact table: transactional (one row per discrete event, fact_orders is this kind), periodic snapshot (one row per time period, for example an account balance at the close of every month — this guide doesn't build one), and accumulating snapshot — the kind this lesson introduces. An accumulating snapshot describes a process with a beginning and an end, made up of predictable steps (milestones), where each row summarizes the complete process and gets updated as it advances. This lesson builds the smallest possible example — a single Kiosko session, stamped three times — before lesson 3 scales it to all seventeen complete sessions.

Connection to the module. This lesson develops the first concept from lesson 1's map: what an accumulating snapshot fact table formally is, and why it's a different kind of fact from fact_orders. Lesson 3 scales this same mechanism to every Kiosko session; lesson 4 rebuilds it event by event, in the real chronological order they would arrive in production.

An analogy: the shipping label that gets stamped, never reprinted

Pick back up the module introduction's shipping label. The package leaves the distribution center: "dispatched" gets stamped, with the exact date and time. Two days later it arrives at the destination city: "in local transit" gets stamped, on the same label, not a new one. The day it gets delivered: "delivered" gets stamped. If someone checks the label midway — after the first stamp, before the second — they see a label with one box filled and two empty. That's not an error or an incomplete row that needs to be discarded: it's exactly the real state of the process at that moment. The label never gets duplicated, never gets reprinted — it gets stamped, in place, as many times as the process has steps.

fact_sessions, the table this module builds, works the same way: every browsing session on Kiosko is a "package" with up to three possible stamps — viewed a page (view_ts), added something to the cart (add_to_cart_ts), bought (purchase_ts). The row is born with the first stamp. The following stamps, if they happen, get applied to the same row, never to a new one.

Worked example: one session, stamped three times

The source data is SESS-01's first three events, exactly as dbt-analytics-engineering-guide declared them as Kiosko's canonical source — same event_id, session_id, event_type, event_ts, with no difference at all:

E5001  page_view    SESS-01  2026-08-03T08:00:12
E5002  add_to_cart  SESS-01  2026-08-03T08:02:45
E5003  purchase     SESS-01  2026-08-03T08:03:10

SESS-01 corresponds to store S01 — lesson 3 declares the complete session-to-store mapping you need before building fact_sessions for all seventeen sessions; for now, for this single-session example, it's enough to know SESS-01 -> S01.

# accumulating_snapshot_demo.py
import duckdb

con = duckdb.connect()
con.execute("""
    CREATE TABLE fact_sessions (
        session_id VARCHAR PRIMARY KEY,
        store_id VARCHAR,
        session_date DATE,
        view_ts TIMESTAMP,
        add_to_cart_ts TIMESTAMP,
        purchase_ts TIMESTAMP,
        is_converted BOOLEAN
    )
""")

# Milestone 1: E5001 page_view -- the session starts, INSERT of an incomplete row
con.execute("""
    INSERT INTO fact_sessions VALUES
    ('SESS-01', 'S01', DATE '2026-08-03', TIMESTAMP '2026-08-03 08:00:12', NULL, NULL, false)
""")
print("=== After milestone 1 (page_view) -- INSERT ===")
con.sql("SELECT * FROM fact_sessions").show(max_width=300)
print("rows in fact_sessions:", con.sql("SELECT COUNT(*) FROM fact_sessions").fetchone()[0])

# Milestone 2: E5002 add_to_cart -- the session advances, UPDATE of the SAME row
con.execute("""
    UPDATE fact_sessions
    SET add_to_cart_ts = TIMESTAMP '2026-08-03 08:02:45'
    WHERE session_id = 'SESS-01'
""")
print("\n=== After milestone 2 (add_to_cart) -- UPDATE, not INSERT ===")
con.sql("SELECT * FROM fact_sessions").show(max_width=300)
print("rows in fact_sessions:", con.sql("SELECT COUNT(*) FROM fact_sessions").fetchone()[0])

# Milestone 3: E5003 purchase -- the session converts, UPDATE again
con.execute("""
    UPDATE fact_sessions
    SET purchase_ts = TIMESTAMP '2026-08-03 08:03:10', is_converted = true
    WHERE session_id = 'SESS-01'
""")
print("\n=== After milestone 3 (purchase) -- UPDATE, is_converted -> true ===")
con.sql("SELECT * FROM fact_sessions").show(max_width=300)
print("rows in fact_sessions:", con.sql("SELECT COUNT(*) FROM fact_sessions").fetchone()[0])

What to expect. Running python3 accumulating_snapshot_demo.py, the output is exactly this:

=== After milestone 1 (page_view) -- INSERT ===
┌────────────┬──────────┬──────────────┬─────────────────────┬────────────────┬─────────────┬──────────────┐
│ session_id │ store_id │ session_date │       view_ts       │ add_to_cart_ts │ purchase_ts │ is_converted │
│  varchar   │ varchar  │     date     │      timestamp      │    timestamp   │  timestamp  │   boolean    │
├────────────┼──────────┼──────────────┼─────────────────────┼────────────────┼─────────────┼──────────────┤
│ SESS-01    │ S01      │ 2026-08-03   │ 2026-08-03 08:00:12 │ NULL           │ NULL        │ false        │
└────────────┴──────────┴──────────────┴─────────────────────┴────────────────┴─────────────┴──────────────┘

rows in fact_sessions: 1

=== After milestone 2 (add_to_cart) -- UPDATE, not INSERT ===
┌────────────┬──────────┬──────────────┬─────────────────────┬─────────────────────┬─────────────┬──────────────┐
│ session_id │ store_id │ session_date │       view_ts       │   add_to_cart_ts    │ purchase_ts │ is_converted │
│  varchar   │ varchar  │     date     │      timestamp      │      timestamp      │  timestamp  │   boolean    │
├────────────┼──────────┼──────────────┼─────────────────────┼─────────────────────┼─────────────┼──────────────┤
│ SESS-01    │ S01      │ 2026-08-03   │ 2026-08-03 08:00:12 │ 2026-08-03 08:02:45 │ NULL        │ false        │
└────────────┴──────────┴──────────────┴─────────────────────┴─────────────────────┴─────────────┴──────────────┘

rows in fact_sessions: 1

=== After milestone 3 (purchase) -- UPDATE, is_converted -> true ===
┌────────────┬──────────┬──────────────┬─────────────────────┬─────────────────────┬─────────────────────┬──────────────┐
│ session_id │ store_id │ session_date │       view_ts       │   add_to_cart_ts    │     purchase_ts     │ is_converted │
│  varchar   │ varchar  │     date     │      timestamp      │      timestamp      │      timestamp      │   boolean    │
├────────────┼──────────┼──────────────┼─────────────────────┼─────────────────────┼─────────────────────┼──────────────┤
│ SESS-01    │ S01      │ 2026-08-03   │ 2026-08-03 08:00:12 │ 2026-08-03 08:02:45 │ 2026-08-03 08:03:10 │ true         │
└────────────┴──────────┴──────────────┴─────────────────────┴─────────────────────┴─────────────────────┴──────────────┘

rows in fact_sessions: 1

Stop on the number that repeats three times: rows in fact_sessions: 1. Three distinct events, three distinct moments in time, and the table never had more than one row for SESS-01. That's the accumulating snapshot in its purest form — the same session_id, the same physical row, each time with more columns filled in, until the process (the session) reaches its natural end (a purchase, or the user simply stops interacting).

Diagram: the row that gets stamped three times

Moment 1 (08:00:12)         Moment 2 (08:02:45)          Moment 3 (08:03:10)
┌─────────────────┐         ┌─────────────────┐          ┌─────────────────┐
│ SESS-01 | S01    │         │ SESS-01 | S01    │          │ SESS-01 | S01    │
│ view: 08:00:12   │  --->   │ view: 08:00:12   │  --->    │ view: 08:00:12   │
│ cart: NULL       │  UPDATE │ cart: 08:02:45   │  UPDATE  │ cart: 08:02:45   │
│ purchase: NULL   │         │ purchase: NULL   │          │ purchase: 08:03:10│
│ converted: false │         │ converted: false │          │ converted: true  │
└─────────────────┘         └─────────────────┘          └─────────────────┘
      INSERT                                                  the SAME row,
   (1 new row)                                               updated 2 times
                                                              (0 new rows)

Going deeper: why milestone order matters, and what happens if one gets skipped

Kimball's accumulating snapshot assumes the milestones happen in a predictable order — in Kiosko's funnel, no one buys (purchase) without having viewed a page (view) first, and adding to the cart (add_to_cart) always happens between those two. This assumption isn't a universal SQL rule, it's a business rule: the model assumes it because that's genuinely how Kiosko's delivery app works. If the real business process allowed skipping a step — for example, a direct purchase with no cart involved — the model would keep working exactly the same way: the row would be born with view_ts filled, add_to_cart_ts would stay NULL forever, and purchase_ts would get filled in anyway. Nothing in the UPDATE requires the columns to fill in a fixed order — each milestone is an independent column that gets filled when its corresponding event arrives, regardless of what other columns are already filled or empty.

This has an important practical consequence for lessons 3 and 4: a fact_sessions row with add_to_cart_ts filled and purchase_ts empty (as you're going to see in several Kiosko sessions) isn't a "broken" row, or "incomplete in the sense of missing data that should exist" — it's a row that describes, precisely, a session that made it to the cart and stopped there. A NULL in fact_sessions has a precise business meaning: "this milestone hasn't happened yet," never "we failed to capture this data."

Common mistakes

Expecting an accumulating snapshot to have a "current status" column instead of a date column per milestone. What happens: someone, familiar with other systems, expects fact_sessions to have a single status column ('viewed', 'added_to_cart', 'purchased') instead of three independent timestamp columns. Why it happens: a single status column is a common pattern in transactional systems (an order "is" in one state at a time). How to spot it: if you try to write fact_sessions with a single status column, you're going to lose information — you couldn't answer "how much time passed between viewing the page and adding to the cart?" without storing both timestamps separately. How to fix it: Kimball's pattern stores one date/time column per milestone, not a single status — that's what lets you compute durations between steps and know exactly when each one happened, not just which was the last one.

Thinking is_converted can be computed without ever looking back at purchase_ts. What happens: someone treats is_converted as an independent column that has to be kept manually in sync, instead of one derived directly from whether purchase_ts is filled or not. Why it happens: having two columns — one a date, one a boolean — that "say the same thing" feels redundant, and it's easy to update one without the other. How to spot it: if at any point in your code you update purchase_ts without updating is_converted in the same statement, your table can end up with a session that has purchase_ts filled but is_converted = false — an internal contradiction. How to fix it: in this lesson's example, the same UPDATE that fills purchase_ts also sets is_converted = true, in a single statement — never two separate steps that could drift out of sync.

Assuming the accumulating snapshot needs to know, up front, how many milestones the process is going to have. What happens: someone thinks this pattern only works if the number of steps is fixed and known for every case (like Kiosko's funnel's three milestones), and doesn't know how to apply it to a process with a variable number of steps. Why it happens: this lesson's example has exactly three milestones, always the same three, which can suggest the pattern depends on that regularity. How to spot it: if you find yourself asking "what would happen if a process sometimes had 3 steps and sometimes 5?", that's a sign you're overgeneralizing from a single example. How to fix it: the pattern keeps working with a variable number of milestones — Kimball documents examples with up to a dozen date columns (for example, an order's complete lifecycle: ordered, paid, packed, dispatched, in transit, delivered). The only thing that changes is how many columns the row has, not the mechanism of INSERT once and UPDATE for each milestone that actually happens.

Exercises

Exercise 1 — Repeat the example with a session that doesn't buy. Using SESS-02 (page_view at 2026-08-03T08:05:00, no other event in events), write the corresponding INSERT and confirm what values remain in the milestone columns that never happened.

See solution 
con.execute("""
    INSERT INTO fact_sessions VALUES
    ('SESS-02', 'S02', DATE '2026-08-03', TIMESTAMP '2026-08-03 08:05:00', NULL, NULL, false)
""")
con.sql("SELECT * FROM fact_sessions WHERE session_id = 'SESS-02'").show(max_width=300)

Expected output:

┌────────────┬──────────┬──────────────┬─────────────────────┬────────────────┬─────────────┬──────────────┐
│ session_id │ store_id │ session_date │       view_ts       │ add_to_cart_ts │ purchase_ts │ is_converted │
│  varchar   │ varchar  │     date     │      timestamp      │    timestamp   │  timestamp  │   boolean    │
├────────────┼──────────┼──────────────┼─────────────────────┼────────────────┼─────────────┼──────────────┤
│ SESS-02    │ S02      │ 2026-08-03   │ 2026-08-03 08:05:00 │ NULL           │ NULL        │ false        │
└────────────┴──────────┴──────────────┴─────────────────────┴────────────────┴─────────────┴──────────────┘

add_to_cart_ts and purchase_ts stay NULL forever — not because data is missing, but because those milestones, given the events evidence available, never happened for SESS-02. This row is just as complete and correct as SESS-01's; it describes, precisely, a session that stopped at the first stage of the funnel.

Exercise 2 — Verify that two consecutive UPDATEs on the same session never create a duplicate row. Run the milestone 2 UPDATE (add_to_cart_ts) on SESS-01 twice in a row, with the same value, and confirm with COUNT(*) that the table still has exactly one row for that session.

See solution 
con.execute("UPDATE fact_sessions SET add_to_cart_ts = TIMESTAMP '2026-08-03 08:02:45' WHERE session_id = 'SESS-01'")
con.execute("UPDATE fact_sessions SET add_to_cart_ts = TIMESTAMP '2026-08-03 08:02:45' WHERE session_id = 'SESS-01'")
print(con.sql("SELECT COUNT(*) AS rows_for_sess_01 FROM fact_sessions WHERE session_id = 'SESS-01'"))

Expected output:

┌──────────────────┐
│ rows_for_sess_01 │
│      int64       │
├──────────────────┤
│                1 │
└──────────────────┘

A repeated UPDATE with the same value changes nothing — it's still a single row, with the same content. This is an important property of the pattern: it's idempotent with respect to row count, no matter how many times the same event arrives (something you're going to connect directly to module 5's deduplication if the same event ever gets resent by mistake).

Exercise 3 — Explain why a duplicated INSERT really would be a problem, even though a duplicated UPDATE isn't. In 2-3 sentences, explain what would happen if this lesson's mechanism used INSERT at every milestone instead of INSERT only at the first one and UPDATE at the following ones.

See solution

If every milestone inserted a new row instead of updating the existing one, fact_sessions would end up with up to three rows per complete session — one per event — instead of a single accumulated row. That would break the fact's grain: instead of "one row per session," you'd have "one row per session event," which is exactly the original events table's grain, not an accumulating snapshot. The pattern's entire purpose — summarizing a complete process into a single queryable row — would disappear, and any query that counts sessions (COUNT(*)) would count events instead, inflated by sessions with more than one milestone.

Summary and next step

This lesson built the smallest possible example of an accumulating snapshot fact table: a single Kiosko session (SESS-01), stamped three times — INSERT at the first milestone, UPDATE at each of the following two — always ending with exactly one row. You saw, with executed evidence, the central property of Kimball's pattern: the number of events that happen doesn't determine the table's row count — the number of distinct processes (sessions) does, no matter how many milestones each one reaches.

Before moving on you should be able to: explain the difference between INSERT (the process is born) and UPDATE (the process advances) in this pattern; interpret a NULL in a milestone column as "hasn't happened yet," not as missing data; and predict what would happen if the mechanism used INSERT at every milestone instead of UPDATE.

Lesson 3 scales this same mechanism to Kiosko's seventeen complete sessions, using an aggregate query (GROUP BY + MAX(CASE WHEN ...)) that builds the complete table all at once — the equivalent of a "complete recalculation" (full refresh), useful for understanding the final result before lesson 4 rebuilds it event by event, the way it would really happen in production.

Resources

← PreviousModule introduction: two shapes of fact that aren't an order lineNext →Modeling Kiosko's session funnel