Module 6: Accumulating And Cumulative Patterns

Cumulative table design: the Zach Wilson pattern

Next →

Description

This lesson changes pattern entirely. fact_sessions described a process with a beginning and an end — a session is born, advances, and eventually stops (bought or not). The question this lesson starts answering is different: "how many active days did each Kiosko store have in the last 7 days?" doesn't describe a process that ends — it describes a continuous series that never stops growing while the store stays open. Zach Wilson, a data engineer who worked at Facebook/Meta and later founded DataExpert.io, documented the pattern that solves this kind of question without rereading months of history on every query: cumulative table design. This lesson builds the smallest possible example — three days of a single store — before lesson 6 scales it to Kiosko's three stores and the full seven days.

Connection to the module. This lesson introduces the module's second central pattern, with the same kind of minimal example lesson 2 used for the accumulating snapshot. Lesson 6 scales this mechanism to the complete fact_store_activity; lesson 7 uses it to answer the real business question: 7- and 30-day actives by store.

An analogy: the warehouse summary that grows one day at a time

Think about how a warehouse manager keeps track of how many boxes they moved in the last seven days, without rereading seven full receipts every time someone asks. At the close of each day, they take a sheet with the last seven numbers — yesterday's summary — add today's number to the front of the list, and if the list already has eight numbers, they cross out the oldest one. Tomorrow's sheet is going to get built exactly the same way: starting from today's sheet, never rereading any receipt from a week ago. The manager never needs to "start from zero" — every new sheet inherits the previous sheet's work, and only adds one day's worth of data to it.

That's, precisely, cumulative table design: each row of fact_store_activity for today gets built by taking yesterday's row (which already carries, inside an array, the summary of the last few days) and adding only today's revenue to it. You never need a SUM(revenue) over seven complete days of fact_orders — that sum is already done, accumulated, inside the array.

Worked example: three days of store S01, an array that grows

The source data is S01's (Bogota) daily revenue for the first three days of Kiosko's week, calculated from fact_orders — the same 40-row table from module 1:

2026-08-03: revenue 8.45
2026-08-04: revenue 3.45
2026-08-05: revenue 0.55

# cumulative_demo.py
import duckdb

con = duckdb.connect()
con.execute("""
    CREATE TABLE fact_store_activity_preview (
        store_id VARCHAR,
        activity_date DATE,
        daily_revenue DOUBLE,
        revenue_array_7d DOUBLE[]
    )
""")

# Day 1 (2026-08-03): there's no "yesterday" -- the array is born with a single value
con.execute("""
    INSERT INTO fact_store_activity_preview VALUES ('S01', DATE '2026-08-03', 8.45, [8.45])
""")
print("=== Day 1: no yesterday row, the array is born with a single value ===")
con.sql("SELECT * FROM fact_store_activity_preview").show(max_width=200)

# Day 2 (2026-08-04): takes YESTERDAY's array and prepends TODAY's value
yesterday_array = con.sql("""
    SELECT revenue_array_7d FROM fact_store_activity_preview
    WHERE store_id = 'S01' AND activity_date = DATE '2026-08-03'
""").fetchone()[0]
today_array = [3.45] + list(yesterday_array)  # list_prepend(3.45, yesterday_array)
con.execute(
    "INSERT INTO fact_store_activity_preview VALUES ('S01', DATE '2026-08-04', 3.45, ?)",
    [today_array],
)
print("\n=== Day 2: list_prepend(3.45, yesterday_array) -> [3.45, 8.45] ===")
con.sql("SELECT * FROM fact_store_activity_preview WHERE activity_date = DATE '2026-08-04'").show(max_width=200)

# Day 3 (2026-08-05): the same mechanism, again -- day 1 is never reread directly
yesterday_array = con.sql("""
    SELECT revenue_array_7d FROM fact_store_activity_preview
    WHERE store_id = 'S01' AND activity_date = DATE '2026-08-04'
""").fetchone()[0]
today_array = [0.55] + list(yesterday_array)
con.execute(
    "INSERT INTO fact_store_activity_preview VALUES ('S01', DATE '2026-08-05', 0.55, ?)",
    [today_array],
)
print("\n=== Day 3: list_prepend(0.55, yesterday_array) -> [0.55, 3.45, 8.45] ===")
con.sql("SELECT * FROM fact_store_activity_preview WHERE activity_date = DATE '2026-08-05'").show(max_width=200)

print("\n=== All three rows together, with list_sum(...) verified against accumulated revenue ===")
con.sql("""
    SELECT store_id, activity_date, daily_revenue, revenue_array_7d,
           ROUND(list_sum(revenue_array_7d), 2) AS sum_check
    FROM fact_store_activity_preview ORDER BY activity_date
""").show(max_width=200)

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

=== Day 1: no yesterday row, the array is born with a single value ===
┌──────────┬───────────────┬───────────────┬──────────────────┐
│ store_id │ activity_date │ daily_revenue │ revenue_array_7d │
│ varchar  │     date      │    double     │     double[]     │
├──────────┼───────────────┼───────────────┼──────────────────┤
│ S01      │ 2026-08-03    │          8.45 │ [8.45]           │
└──────────┴───────────────┴───────────────┴──────────────────┘

=== Day 2: list_prepend(3.45, yesterday_array) -> [3.45, 8.45] ===
┌──────────┬───────────────┬───────────────┬──────────────────┐
│ store_id │ activity_date │ daily_revenue │ revenue_array_7d │
│ varchar  │     date      │    double     │     double[]     │
├──────────┼───────────────┼───────────────┼──────────────────┤
│ S01      │ 2026-08-04    │          3.45 │ [3.45, 8.45]     │
└──────────┴───────────────┴───────────────┴──────────────────┘

=== Day 3: list_prepend(0.55, yesterday_array) -> [0.55, 3.45, 8.45] ===
┌──────────┬───────────────┬───────────────┬────────────────────┐
│ store_id │ activity_date │ daily_revenue │  revenue_array_7d  │
│ varchar  │     date      │    double     │      double[]      │
├──────────┼───────────────┼───────────────┼────────────────────┤
│ S01      │ 2026-08-05    │          0.55 │ [0.55, 3.45, 8.45] │
└──────────┴───────────────┴───────────────┴────────────────────┘

=== All three rows together, with list_sum(...) verified against accumulated revenue ===
┌──────────┬───────────────┬───────────────┬────────────────────┬───────────┐
│ store_id │ activity_date │ daily_revenue │  revenue_array_7d  │ sum_check │
│ varchar  │     date      │    double     │      double[]      │  double   │
├──────────┼───────────────┼───────────────┼────────────────────┼───────────┤
│ S01      │ 2026-08-03    │          8.45 │ [8.45]             │      8.45 │
│ S01      │ 2026-08-04    │          3.45 │ [3.45, 8.45]       │      11.9 │
│ S01      │ 2026-08-05    │          0.55 │ [0.55, 3.45, 8.45] │     12.45 │
└──────────┴───────────────┴───────────────┴────────────────────┴───────────┘

Notice the order inside the array: [0.55, 3.45, 8.45], today's value first, the oldest one at the end. This is a design decision, not an accident — an array where the most recent element is always at position [1] (the first one, in DuckDB's indexing) makes operations like "yesterday's revenue" or "the revenue from two days ago" a simple arr[1], arr[2], without having to know how many elements the full array has. list_sum(revenue_array_7d) gives 12.45 on day 3 — the sum of the three days, without any query having touched fact_orders again to compute it.

Diagram: the array that grows from the front

Day 1                 Day 2                      Day 3
┌────────┐           ┌──────────────┐            ┌────────────────────┐
│ [8.45] │  prepend   │ [3.45, 8.45] │  prepend   │ [0.55, 3.45, 8.45]  │
└────────┘  ───────>  └──────────────┘  ───────>  └────────────────────┘
             adds 3.45                 adds 0.55
             to the FRONT              to the FRONT

Each step: todays_array = list_prepend(todays_revenue, yesterdays_array)
No previous daily_revenue is ever reread directly from fact_orders.

Going deeper: why Zach Wilson's real pattern uses FULL OUTER JOIN, and Kiosko doesn't need it (yet)

DataExpert-io's cumulative-table-design repository describes the production mechanism with more precision than this lesson's example: "we FULL OUTER JOIN yesterday's cumulative table with today's data and build our metric arrays for each user." The key word there is FULL OUTER JOIN, not a plain JOIN — and the reason is real: in Zach Wilson's original case (a app's active users), a user who was active yesterday might not appear at all in today's data — they generated no event — and a user who appears today might be someone who never appeared before. A FULL OUTER JOIN captures both cases: for yesterday's user with no row today, it prepends a 0 to their array (they still exist, just inactive today); for today's new user, it creates an array from scratch, exactly like this lesson's "Day 1."

This lesson's Kiosko example is deliberately simplified: the three stores (S01, S02, S03) exist all seven full days of the week — no store "appears" or "disappears" from one day to the next — so no FULL OUTER JOIN is needed to decide whether a store already had a row the previous day. Lesson 6 uses an explicit loop over the three known stores for each of the seven days, achieving the same result without the complexity of an OUTER JOIN — an honest simplification, not a deviation from the pattern. If Kiosko opened a new store mid-week, or closed one, this guide's pattern would need DataExpert-io's full FULL OUTER JOIN to handle it correctly.

Common mistakes

Summing the array without ROUND() and running into floating-point errors. What happens: someone computes list_sum(revenue_array_7d) directly, without rounding, and gets a number like 11.899999999999999 instead of 11.9. Why it happens: DOUBLE in DuckDB (like in nearly any language) represents decimal numbers in binary, and repeated sums of values like 3.45 and 8.45 can accumulate a tiny but visible rounding error. How to spot it: if your "What to expect" shows a long string of nines or zeros where you expected a clean two-decimal number, this is the problem. How to fix it: the same discipline this guide has applied to revenue since module 1 — ROUND(..., 2) — applies just the same to any sum over an array of amounts: ROUND(list_sum(revenue_array_7d), 2), never plain list_sum(...) when the result gets displayed or compared.

Prepending the new value to the end of the array instead of the front. What happens: someone writes list(yesterday_array) + [today_value] instead of [today_value] + list(yesterday_array), leaving the most recent value at the end. Why it happens: in many programming contexts, "adding to a list" means adding at the end (append), and that habit is easy to carry over here without thinking about it. How to spot it: if revenue_array_7d[1] (the first position) doesn't correspond to the most recent day, something got recorded backwards — any query that assumes "position 1 is today" (as lesson 7 is going to do) would give wrong results with no visible error. How to fix it: this guide consistently adopts the "most recent first" convention — always use list_prepend(new_value, yesterdays_array) or the equivalent [new_value] + list(yesterdays_array), never at the end.

Confusing "the array has 3 elements" with "3 days of activity have passed." What happens: someone assumes the array's length (len(revenue_array_7d)) always represents how many days the store had activity, without distinguishing between "days elapsed since tracking started" and "days with revenue greater than zero." Why it happens: in this lesson's example, S01's three days all had positive revenue, so both quantities coincide by chance. How to spot it: if a store had a day with no sales at all (revenue 0.0, a real case you're going to see in lesson 6 for S03), its array would keep growing one element per elapsed day, but not every one of those elements would represent an "active" day. How to fix it: the array's length measures days elapsed within the window (up to the cap of 7 or 30); counting active days — revenue greater than zero — needs a separate column or calculation, exactly what active_days_7d/active_days_30d are going to solve in lessons 6 and 7.

Exercises

Exercise 1 — Continue the array one more day. S01's revenue on 2026-08-06 was 6.7. Calculate, by hand first and then with code, what revenue_array_7d would be for that fourth day, and verify its sum with list_sum.

See solution 
yesterday_array = con.sql("""
    SELECT revenue_array_7d FROM fact_store_activity_preview
    WHERE store_id = 'S01' AND activity_date = DATE '2026-08-05'
""").fetchone()[0]
today_array = [6.7] + list(yesterday_array)
con.execute(
    "INSERT INTO fact_store_activity_preview VALUES ('S01', DATE '2026-08-06', 6.7, ?)",
    [today_array],
)
print(con.sql("""
    SELECT activity_date, revenue_array_7d, ROUND(list_sum(revenue_array_7d), 2) AS sum_check
    FROM fact_store_activity_preview WHERE activity_date = DATE '2026-08-06'
"""))

Expected output:

┌───────────────┬──────────────────────────┬───────────┐
│ activity_date │     revenue_array_7d     │ sum_check │
│     date      │         double[]         │  double   │
├───────────────┼──────────────────────────┼───────────┤
│ 2026-08-06    │ [6.7, 0.55, 3.45, 8.45] │     19.15 │
└───────────────┴──────────────────────────┴───────────┘

[6.7, 0.55, 3.45, 8.45] — the newest day at the front, the previous three preserved in the same order. The array now has four elements; it hasn't yet reached the cap of 7 that lesson 6 is going to apply with list_slice/slicing.

Exercise 2 — Explain why this lesson's example doesn't need FULL OUTER JOIN, in your own words. Without rereading the deep dive, write 2-3 sentences explaining what would have to be different about Kiosko's stores for this example to actually need a FULL OUTER JOIN like the one in Zach Wilson's original repository.

See solution

The original pattern's FULL OUTER JOIN exists to handle entities (users, in Zach Wilson's case) that can appear or disappear from one day to the next — active yesterday but not today, or new today with no prior history. Kiosko's three stores don't have that problem in this guide: all three exist, every day of the week, so there's always a "yesterday" row for each store to start from. If Kiosko opened a fourth store mid-week (with no "yesterday" row for it) or closed an existing one, this lesson's simple INSERT would no longer be enough, and the full FULL OUTER JOIN would be needed to decide, case by case, whether each store has a previous row to inherit the array from.

Exercise 3 — Calculate how many elements the array would have after 10 consecutive days, if it's never trimmed. Without code, using only the list_prepend mechanism you learned in this lesson, answer: if Kiosko kept adding one value per day for 10 days with no size limit, how many elements would the array have on day 10? Why is lesson 6 going to need to trim it?

See solution

Without any trimming, the array would have exactly 10 elements on day 10 — one for each day elapsed — and would keep growing indefinitely while the store keeps operating: 30 elements by day 30, 365 after a year. That contradicts the purpose of a column named revenue_array_7d, which by business definition must represent only the last 7 days, not one more. Lesson 6 is going to solve this by trimming the array to a fixed size at each step — with DuckDB slicing, arr[1:7] — discarding the oldest value as soon as the array exceeds the cap. Without that trimming, the column would stop meaning "the last 7 days" and would start meaning "every day since tracking began" — a silent change in meaning that no downstream query would expect.

Summary and next step

This lesson introduced Zach Wilson/DataExpert's cumulative table design with the smallest possible example: three days of S01's revenue, built one at a time with list_prepend(todays_value, yesterdays_array), never rereading the complete fact_orders to recalculate a sum that was already accumulated. You saw, with list_sum verified, that the array accumulates correctly (12.45 after three days), and understood why the real production pattern uses FULL OUTER JOIN — to handle entities that appear or disappear — while Kiosko, with its three fixed stores, can simplify it with a direct INSERT.

Before moving on you should be able to: explain the mechanical difference between an accumulating snapshot (lessons 2-4) and a cumulative table design (this lesson); calculate by hand the next element of an array given yesterday's array and today's value; and explain why an array without a size trim stops meaning "the last N days."

Lesson 6 scales this mechanism to Kiosko's three stores and the full seven days of the week, with the size trim (arr[1:7] for the 7-day window, arr[1:30] for the 30-day one) that this example didn't need yet, building fact_store_activity end to end.

Resources

← PreviousUpdating milestones in placeNext →Rolling windows with array columns