How to Build Cumulative Tables for Analytics: Patterns from the Data Engineer Handbook

Cumulative tables store progressive aggregates—such as running totals, rolling averages, or arrays of active dates—to eliminate expensive window function recomputation in downstream analytics queries.

Cumulative tables are a foundational pattern for time-based analytics in modern data warehouses. This guide examines production-grade implementations from the DataExpert-io/data-engineer-handbook repository, demonstrating how to build scalable cumulative tables using array aggregation and window functions to power fast, deterministic reporting.

Core Patterns for Cumulative Table Design

The repository demonstrates two primary architectural approaches for building cumulative analytics tables. Each pattern optimizes for different query patterns and computational constraints.

Array-Based Date Lists

The array-based pattern stores a compact list of dates for each entity, enabling fast "days active" calculations without scanning raw event logs. This implementation resides in intermediate-bootcamp/materials/2-fact-data-modeling/tables/users_cumulated.sql.

CREATE TABLE users_cumulated (
    user_id      BIGINT,
    dates_active DATE[],               -- Array of all dates the user was active
    date        DATE,                  -- Current date (for partitioning)
    PRIMARY KEY (user_id, date)
);

INSERT INTO users_cumulated
SELECT
    user_id,
    ARRAY_AGG(DISTINCT event_date ORDER BY event_date) AS dates_active,
    CURRENT_DATE AS date
FROM raw_events
GROUP BY user_id;

ARRAY_AGG collects every distinct event_date for a user, ordered chronologically. Downstream queries can use CARDINALITY(dates_active) for counts or UNNEST(dates_active) for detailed analysis without joining to the original fact table.

Window-Function Rolling Aggregates

For running totals and rolling metrics, the repository uses window functions defined in intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/window_based_analysis.sql. This pattern materializes progressive sums to avoid recomputing windows on every query.

SELECT
    user_id,
    event_date,
    COUNT(*) AS daily_count,
    SUM(COUNT(*)) OVER (
        PARTITION BY user_id
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_cumulative_sum,
    SUM(COUNT(*)) OVER (
        PARTITION BY user_id
        ORDER BY event_date
        ROWS BETWEEN 30 PRECEDING AND CURRENT ROW
    ) AS rolling_cumulative_sum,
    SUM(COUNT(*)) OVER (
        PARTITION BY user_id
        ORDER BY DATE_TRUNC('month', event_date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS monthly_cumulative_sum
FROM raw_events
GROUP BY user_id, event_date;

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause creates a running total from the first event, while ROWS BETWEEN 30 PRECEDING AND CURRENT ROW generates rolling 30-day metrics essential for retention and churn analysis.

Step-by-Step Implementation Guide

Building production cumulative tables requires careful attention to grain definition and refresh strategies. Follow these six steps derived from the Data Engineer Handbook patterns:

1. Identify the grain – Choose the primary key(s) and time dimension (e.g., user_id + date). This guarantees uniqueness and enables deterministic aggregations.

2. Materialize the base events – Load raw events into a staging table. Keeping the source immutable isolates transformation logic and supports incremental processing.

3. Define the cumulative expression – Use ARRAY_AGG(... ORDER BY ...) for date lists, or SUM(...) OVER (PARTITION BY … ORDER BY …) for rolling totals. Window functions provide running totals; ARRAY_AGG provides compact date lists.

4. Create the target table – Execute CREATE TABLE … AS SELECT … with the cumulative column(s). Include a primary key that matches the grain to ensure idempotent inserts.

5. Index and partition – Add clustering keys on the grain or partition by date for large tables. This improves query performance and reduces scan size.

6. Choose a refresh strategy – Select between full rebuild (DROP + CREATE) for simplicity, or incremental upserts (MERGE on the grain) to minimize data movement in high-volume pipelines.

Incremental Upserts for High-Volume Tables

For tables requiring frequent updates without full rebuilds, use a MERGE statement to append new data to existing arrays. This pattern updates users_cumulated with new activity while preserving historic date lists:

MERGE INTO users_cumulated AS target
USING (
    SELECT
        user_id,
        ARRAY_AGG(DISTINCT event_date ORDER BY event_date) AS dates_active,
        CURRENT_DATE AS date
    FROM new_events
    GROUP BY user_id
) AS src
ON target.user_id = src.user_id
WHEN MATCHED THEN
    UPDATE SET dates_active = target.dates_active || src.dates_active
WHEN NOT MATCHED THEN
    INSERT (user_id, dates_active, date)
    VALUES (src.user_id, src.dates_active, src.date);

The concatenation operator || appends new dates to the existing array, enabling incremental loads that preserve computational efficiency.

Summary

• Cumulative tables eliminate redundant window function computation by materializing progressive aggregates.
• The array-based pattern (ARRAY_AGG) in users_cumulated.sql stores compact date lists for fast "days active" queries.
• The window-function pattern in window_based_analysis.sql supports running totals, rolling 30-day sums, and monthly cumulative metrics.
• Defining a strict primary key on the grain (e.g., user_id, date) ensures idempotent inserts and simplifies incremental processing.
• Partitioning by the grain or date column keeps computations scoped to O(N) complexity rather than O(N²).
• Use MERGE statements for incremental upserts in high-volume pipelines to minimize data movement.

Frequently Asked Questions

What is the difference between cumulative tables and regular fact tables?

Regular fact tables store individual events or transactions, requiring window functions to calculate running totals at query time. Cumulative tables pre-compute these progressive aggregates, storing the results in dedicated columns. According to the DataExpert-io/data-engineer-handbook source code, this materialization pattern reduces query latency by eliminating repetitive computation of SUM(...) OVER operations on large datasets.

When should I use ARRAY_AGG versus window functions for cumulative metrics?

Use ARRAY_AGG when you need to store a history of discrete values—such as specific dates of activity—to support queries like "how many distinct days was the user active." Use window functions when you need mathematical aggregates like running sums or rolling averages. The repository demonstrates both approaches: users_cumulated.sql uses arrays for date lists, while window_based_analysis.sql uses window functions for cumulative sums.

How do I handle late-arriving data in cumulative tables?

Late-arriving data requires recomputing the cumulative state for affected partitions. If using the array-based pattern, you must either rebuild the partition containing the late data or use the MERGE pattern to append the new dates and resort the array. For window-function-based tables, you typically rebuild the affected time window to ensure the ROWS BETWEEN calculations include the late-arriving events.

What is the recommended refresh strategy for high-volume cumulative tables?

For high-volume tables, implement incremental upserts using MERGE statements rather than full rebuilds. As shown in the Data Engineer Handbook examples, incrementally appending new events to existing cumulative arrays (using target.dates_active || src.dates_active) minimizes data movement and warehouse compute costs. Full rebuilds are acceptable only for small dimension tables or during initial development.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}