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

> Learn how to build cumulative tables for analytics to speed up queries. Discover patterns for storing progressive aggregates and avoid costly recomputation.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-08

---

**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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/2-fact-data-modeling/tables/users_cumulated.sql).

```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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.

```sql
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:

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/users_cumulated.sql) stores compact date lists for fast "days active" queries.
- The **window-function pattern** in [`window_based_analysis.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/users_cumulated.sql) uses arrays for date lists, while [`window_based_analysis.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.