Module 6: Accumulating And Cumulative Patterns

Module introduction: two shapes of fact that aren't an order line

Next →

Why this module exists

Module 5 closed with a sentence that literally previewed this module's topic: "module 6 — accumulating-and-cumulative-patterns — changes topic completely: instead of joining facts against dimensions, it builds two different fact table patterns." That sentence is worth pausing on, because it marks a real break from everything you've done so far. The five previous modules revolved around a single fact table — fact_orders — and how to correctly join it against its dimensions: first declaring its grain (module 1), then giving it surrogate keys and a conformed calendar (module 2), then deciding its shape — star, snowflake, OBT — (module 3), then historizing the dimension that changes (module 4), and finally joining facts against that history without corrupting it (module 5). At no point did the kind of fact fact_orders is ever change: it was always a transactional fact table, in Kimball's vocabulary — one row per discrete business event, never modified after it's inserted.

This module introduces two kinds of fact table that aren't transactional, and that answer questions fact_orders can't answer no matter how many dimensions you add to it. The first question: "what stage of the purchase process did each of a Kiosko customer's browsing sessions get stuck at?" has no reasonable answer in a table that only records completed transactions — a session that never bought anything generates zero rows in fact_orders. The second question: "how many active days did each store have in the last 7 and 30 days?" also doesn't get answered well by a transactional table, because every time someone queries it, it would have to reread entire weeks or months of history to sum something it already summed yesterday.

Kimball solves the first question with the accumulating snapshot fact table: one row per complete process (a session, an order, an application), which gets inserted incomplete when the process starts and gets updated in place every time the process advances a step — never inserting a new row per advance. Zach Wilson, in the pattern he popularized as cumulative table design (documented in DataExpert-io's cumulative-table-design repository, the source this module cites), solves the second with array-typed columns: today's row gets built by taking yesterday's row and adding only today's data to it, never rereading any earlier day. This module builds both patterns over real Kiosko data: fact_sessions over the events you already know, and fact_store_activity over the revenue you already know from fact_orders.

Connection to the module. This module doesn't modify fact_orders, dim_store, dim_product, or dim_product_scd — the four tables modules 1 through 5 left complete. What it builds are two new fact tables, of a different kind from anything you've seen so far, over the same fixed Kiosko data (events, fact_orders) this guide already generated.

An analogy: the shipping label that gets stamped, and the summary that grows one day at a time

Think of a package's shipping label: when the package leaves the distribution center, someone stamps the "dispatched" box with today's date. When it arrives at the destination city, someone stamps "in local transit." When the courier delivers it, "delivered" gets stamped. At no point in that process does a new label get printed — it's the same label, stamped every time the package advances. If someone checks it midway, they see a label with two stamps and one empty box: that doesn't mean the package "doesn't exist," it means it hasn't reached that stage yet. That's exactly an accumulating snapshot fact table: a row born with some boxes empty, getting stamped — updated — as it goes, never multiplying.

Now think of how a warehouse keeps track of how many boxes it moved in the last 7 days, without rereading seven days of receipts every time someone asks: at the close of each day, someone takes yesterday's summary — the list of the last 7 numbers — adds today's number to the front, and drops the oldest number if there are already eight. Today's summary never needed to reread day 1's receipt; it only needed yesterday's summary and today's data. That's exactly cumulative table design: today's revenue_array_7d gets built from yesterday's revenue_array_7d plus today's revenue, never a SUM over seven complete days of fact_orders.

Worked example: this module's map, before building it

Before touching any real executable code, the same kind of map that opened modules 3, 4, and 5 before building their central patterns:

# module_map.py
CONCEPTS = [
    ("Accumulating snapshot fact table", "One row per process (Kimball). Milestones get filled with UPDATE, never a new INSERT."),
    ("INSERT at the start, UPDATE at each milestone", "The exact mechanism: the row is born incomplete and gets completed in place."),
    ("fact_sessions over events", "Kiosko's funnel: view_ts, add_to_cart_ts, purchase_ts, per session."),
    ("Cumulative table design (Zach Wilson/DataExpert)", "Rolling-window array: yesterday's summary + today's value, no rereading the full history."),
    ("LIST-typed columns and DuckDB array functions", "list_prepend, list_slice, list_sum, slicing [1:7]."),
    ("Fixed 7- and 30-day windows", "active_days_7d / active_days_30d, counting values > 0 in the array."),
]

LESSONS = [
    ("The accumulating snapshot fact table", "Kimball, EXECUTED: one session, 3 milestones, 1 row"),
    ("Modeling Kiosko's session funnel", "Complete fact_sessions, EXECUTED: 17 sessions"),
    ("Updating milestones in place", "32 events -> 17 INSERT + 15 UPDATE, EXECUTED"),
    ("Cumulative table design: the Zach Wilson pattern", "The yesterday+today pattern, EXECUTED over 3 days"),
    ("Rolling windows with array columns", "Complete fact_store_activity, EXECUTED: 21 rows"),
    ("Computing 7- and 30-day actives per store", "list_sum, active_days, EXECUTED"),
    ("Project: Kiosko's funnel and cumulative activity", "The 6 previous lessons integrated, EXECUTED"),
]

print("=== This module's six central concepts ===\n")
for name, description in CONCEPTS:
    print(f"- {name}")
    print(f"  {description}\n")

print("=== The seven lessons that build on them ===\n")
for i, (name, description) in enumerate(LESSONS, start=2):
    print(f"L{i}. {name}")
    print(f"    {description}\n")

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

=== This module's six central concepts ===

- Accumulating snapshot fact table
  One row per process (Kimball). Milestones get filled with UPDATE, never a new INSERT.

- INSERT at the start, UPDATE at each milestone
  The exact mechanism: the row is born incomplete and gets completed in place.

- fact_sessions over events
  Kiosko's funnel: view_ts, add_to_cart_ts, purchase_ts, per session.

- Cumulative table design (Zach Wilson/DataExpert)
  Rolling-window array: yesterday's summary + today's value, no rereading the full history.

- LIST-typed columns and DuckDB array functions
  list_prepend, list_slice, list_sum, slicing [1:7].

- Fixed 7- and 30-day windows
  active_days_7d / active_days_30d, counting values > 0 in the array.

=== The seven lessons that build on them ===

L2. The accumulating snapshot fact table
    Kimball, EXECUTED: one session, 3 milestones, 1 row

L3. Modeling Kiosko's session funnel
    Complete fact_sessions, EXECUTED: 17 sessions

L4. Updating milestones in place
    32 events -> 17 INSERT + 15 UPDATE, EXECUTED

L5. Cumulative table design: the Zach Wilson pattern
    The yesterday+today pattern, EXECUTED over 3 days

L6. Rolling windows with array columns
    Complete fact_store_activity, EXECUTED: 21 rows

L7. Computing 7- and 30-day actives per store
    list_sum, active_days, EXECUTED

L8. Project: Kiosko's funnel and cumulative activity
    The 6 previous lessons integrated, EXECUTED

Notice the order: lessons 2, 3, and 4 develop the first pattern end to end — Kimball's concept, the complete fact built all at once, and then that same fact built event by event to prove the real mechanism (INSERT + UPDATE) produces the same result as the aggregate calculation. Lessons 5, 6, and 7 make the same trip with the second pattern — Zach Wilson's concept, the array built day by day, and then analyzing those arrays to answer the real business question. Lesson 8 integrates both facts into a single project.

Diagram: where you were, where you're going to be

flowchart LR
    subgraph M5["Module 5 (already written)"]
        A["fact_orders + dim_product_scd\nPoint-in-time join\nverified"]
    end

    subgraph M6["This module (6 of 8)"]
        B["L2-L4: accumulating snapshot\nfact_sessions, EXECUTED"]
        C["L5-L7: cumulative table design\nfact_store_activity, EXECUTED"]
        D["L8: Integrated project\nEXECUTED"]
    end

    subgraph M7["Module 7 (next)"]
        E["Junk dimension,\nmore than one fact coexisting"]
    end

    A --> B --> C --> D --> E

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: the six concepts, before building them
L2        The accumulating snapshot fact table (Kimball), EXECUTED
L3        Modeling Kiosko's session funnel, EXECUTED
L4        Updating milestones in place, EXECUTED
L5        Cumulative table design (Zach Wilson), EXECUTED
L6        Rolling windows with array columns, EXECUTED
L7        7- and 30-day actives per store, EXECUTED
L8        Project: Kiosko's funnel and cumulative activity, EXECUTED

Going deeper: why these two patterns need data this module doesn't invent

This module, on purpose, generates no new data. fact_sessions gets built over the same 32 events dbt-analytics-engineering-guide already declared as its canonical source — the same event_id, session_id, event_type, event_ts, with no difference at all — because this module's goal is to teach the modeling pattern, not invent a different clickstream dataset for every sibling guide that touches it. The only piece this module does declare — because events never carries store_id — is a fixed session-to-store mapping, which lesson 3 declares explicitly before using it.

fact_store_activity gets built over fact_orders, the same 40-row table with 106.15 total revenue you've known since module 1. There's no new data to generate for that fact either — just a new way of summarizing it, grouping by store and day, and carrying that summary forward in an array instead of recalculating it from scratch on every query. This decision — reusing already-known data instead of inventing a new dataset — is deliberate: it lets you compare, number by number, the weekly revenue per store you already calculated in module 1 (S01: 38.3, S02: 38.8, S03: 29.05) against the sum of the 7-day array this module builds — and if those numbers don't match exactly, something in the new pattern is wrong, not in the data.

Common mistakes

Thinking fact_sessions and fact_store_activity replace fact_orders. What happens: someone, seeing two new fact tables in a single module, assumes Kiosko's warehouse is going to have a single "definitive" fact table from here on, and that the others become obsolete. Why it happens: the previous modules revolved around fact_orders so much that it's easy to assume any new fact replaces it. How to spot it: if you expect fact_orders to disappear or stop being updated after this module, you have this confusion. How to fix it: a real dimensional warehouse almost always has several fact tables, each describing a different business process, often with a different grain and a different kind (transactional, accumulating snapshot, cumulative). fact_orders keeps existing, unchanged, describing sales; fact_sessions describes browsing sessions; fact_store_activity describes daily per-store activity. All three coexist — module 7 names this explicitly as "a domain with more than one fact."

Confusing accumulating snapshot with "a table that gets updated a lot." What happens: someone generalizes the pattern to "any table where I do UPDATE instead of INSERT," including, for example, module 4's dim_product_scd (which also uses MERGE/UPDATE). Why it happens: both patterns use UPDATE at some point, and it's tempting to group them by that surface similarity. How to spot it: if you can't explain the difference between "updating a dimension to close an old version and open a new one" (SCD-2) and "updating a fact row to fill in a milestone that was missing" (accumulating snapshot), you're missing this distinction. How to fix it: SCD-2 never overwrites an existing row — it closes the old one (valid_to, is_current = false) and inserts a new row. The accumulating snapshot does overwrite columns of the same row, closing nothing and inserting nothing new. They're nearly opposite mechanisms that, by coincidence, both use the word UPDATE at some point.

Expecting fact_store_activity to need 30 days of real data to work. What happens: someone, seeing Kiosko only has one week of orders, assumes the 30-day window pattern "can't be demonstrated" with this data. Why it happens: 30 is bigger than 7, the days of data available, so it seems like information is missing. How to spot it: if you expect lesson 6 or 7 to fail or fake data to reach 30 days, you're going to be surprised when the 30-day array simply, honestly, has the same 7 values as the 7-day one — because that's exactly what's there. How to fix it: the rolling-window pattern doesn't need the window to be "full" to work — a business that opened 3 days ago correctly has a 30-day array with 3 elements, not an error. This module states that behavior explicitly instead of hiding it.

Exercises

Exercise 1 — Name, from memory, the mechanical difference between an accumulating snapshot and a transactional table like fact_orders. Without rereading the introduction, write 2-3 sentences explaining what makes fact_orders "transactional" and what's going to make fact_sessions not be.

See solution

fact_orders is transactional because each row gets inserted complete, exactly once, at the moment the sale happens — no fact_orders row gets modified after being inserted, and no column is ever left to fill in later. fact_sessions, on the other hand, is going to be born incomplete: a session's row gets inserted at the moment of the first event (page_view), with add_to_cart_ts and purchase_ts still empty, and those columns are going to get filled in with UPDATE as the session progresses — the same row, updated several times, never a new row per additional event.

Exercise 2 — Explain in your own words why cumulative table design avoids rereading the entire history. Using the warehouse-summary analogy, describe in 2-3 sentences what information fact_store_activity's "today" row needs to be built, and what information it does not need.

See solution

Today's row only needs two things: yesterday's row (which already carries the array of the last several days, previously calculated) and today's data (the day's revenue). It doesn't need to reread all of fact_orders or recalculate the sum of any day before today — that sum already lives, calculated, inside the array yesterday's row brought along. It's exactly the difference between summing seven receipts again every day, and simply adding today's receipt to a summary that already had the six previous ones.

Exercise 3 — Predict how many rows fact_sessions is going to have at the end of the module, and why that number isn't 32. You know events has 32 rows (17 page_view, 9 add_to_cart, 6 purchase) and that every session starts with a page_view. Without looking at lesson 3, predict how many rows fact_sessions will have and explain your reasoning.

See solution

17 rows — one per distinct session, not one per event. fact_sessions has the grain of "one complete session," so its rows get counted by distinct session_id, not by event: the 32 events are distributed across 17 sessions (some with 1 event, others with up to 3), and the accumulating snapshot pattern guarantees each session, no matter how many events it has, produces exactly one row. This is the central difference between this module's pattern and a transactional table: in a transactional table, more events means more rows; in an accumulating snapshot, more events for the same session means more UPDATEs on the same row.

Summary and next step

This module introduces two kinds of fact table you didn't see in the five previous modules: Kimball's accumulating snapshot fact table — one row per process, born incomplete and completed with UPDATE as the process advances, never inserting new rows — and Zach Wilson/DataExpert's cumulative table design — array-typed columns built day by day, taking yesterday's summary and adding only today's data. Both get built over data you already know: fact_sessions over Kiosko's canonical events, fact_store_activity over fact_orders's revenue.

Before moving on you should be able to: name this module's six central concepts; explain the mechanical difference between a transactional table, an accumulating snapshot, and a dimension historized with SCD-2; and predict why fact_sessions is going to have 17 rows, not 32.

Lesson 2 starts with the first of the two patterns: what an accumulating snapshot fact table formally is, according to Kimball, with a first small, executed example — a single session, stamped three times, never multiplying.

Resources

← PreviousMini-project: Kiosko's correct historical revenueNext →The accumulating snapshot fact table