Cumulative table design: el patrón de Zach Wilson

Descripción

Esta lección cambia de patrón por completo. fact_sessions describía un proceso con principio y fin —una sesión nace, avanza, y eventualmente se detiene (compró o no)—. La pregunta que esta lección empieza a responder es distinta: "¿cuántos días activos tuvo cada tienda de Kiosko en los últimos 7 días?" no describe un proceso que termina — describe una serie continua que nunca deja de crecer mientras la tienda siga abierta. Zach Wilson, ingeniero de datos que trabajó en Facebook/Meta y luego fundó DataExpert.io, documentó el patrón que resuelve este tipo de pregunta sin releer meses de historia en cada consulta: el cumulative table design. Esta lección construye el ejemplo más pequeño posible —tres días de una sola tienda— antes de que la lección 6 lo escale a las tres tiendas y los siete días completos de Kiosko.

Conexión con el módulo. Esta lección introduce el segundo patrón central del módulo, con el mismo tipo de ejemplo mínimo que usó la lección 2 para el accumulating snapshot. La lección 6 escala este mecanismo a fact_store_activity completo; la lección 7 lo usa para responder la pregunta de negocio real: activos de 7 y 30 días por tienda.

Una analogía: el resumen del almacén, que crece un día a la vez

Piensa en cómo un encargado de almacén lleva la cuenta de cuántas cajas movió en los últimos siete días, sin releer siete recibos completos cada vez que alguien pregunta. Al cierre de cada día, toma una hoja con los últimos siete números —el resumen de ayer—, le agrega el número de hoy al frente de la lista, y si la lista ya tiene ocho números, tacha el más viejo. La hoja de mañana se va a construir exactamente igual: tomando la hoja de hoy, no releyendo ningún recibo de hace una semana. El encargado nunca necesita "empezar de cero" — cada hoja nueva hereda el trabajo de la hoja anterior, y solo le agrega el dato de un día.

Eso es, con precisión, el cumulative table design: cada fila de fact_store_activity para el día de hoy se construye tomando la fila de ayer (que ya trae, dentro de un arreglo, el resumen de los últimos días) y agregándole solo el revenue de hoy. Nunca hace falta un SUM(revenue) sobre siete días completos de fact_orders — esa suma ya está hecha, acumulada, dentro del arreglo.

Ejemplo trabajado: tres días de la tienda S01, un arreglo que crece

Los datos de origen son el revenue diario de S01 (Bogotá) para los primeros tres días de la semana de Kiosko, calculado desde fact_orders —la misma tabla de 40 filas del módulo 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[]
    )
""")

# Dia 1 (2026-08-03): no existe "ayer" -- el arreglo nace con un solo valor
con.execute("""
    INSERT INTO fact_store_activity_preview VALUES ('S01', DATE '2026-08-03', 8.45, [8.45])
""")
print("=== Dia 1: sin fila de ayer, el arreglo nace con un solo valor ===")
con.sql("SELECT * FROM fact_store_activity_preview").show(max_width=200)

# Dia 2 (2026-08-04): toma el arreglo de AYER y le antepone el valor de HOY
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=== Dia 2: list_prepend(3.45, arreglo_de_ayer) -> [3.45, 8.45] ===")
con.sql("SELECT * FROM fact_store_activity_preview WHERE activity_date = DATE '2026-08-04'").show(max_width=200)

# Dia 3 (2026-08-05): el mismo mecanismo, otra vez -- nunca se relee el dia 1 directamente
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=== Dia 3: list_prepend(0.55, arreglo_de_ayer) -> [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=== Las tres filas juntas, con list_sum(...) verificado contra el revenue acumulado ===")
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)

Qué esperar. Al correr python3 cumulative_demo.py, la salida es exactamente esta:

=== Dia 1: sin fila de ayer, el arreglo nace con un solo valor ===
┌──────────┬───────────────┬───────────────┬──────────────────┐
│ store_id │ activity_date │ daily_revenue │ revenue_array_7d │
│ varchar  │     date      │    double     │     double[]     │
├──────────┼───────────────┼───────────────┼──────────────────┤
│ S01      │ 2026-08-03    │          8.45 │ [8.45]           │
└──────────┴───────────────┴───────────────┴──────────────────┘

=== Dia 2: list_prepend(3.45, arreglo_de_ayer) -> [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]     │
└──────────┴───────────────┴───────────────┴──────────────────┘

=== Dia 3: list_prepend(0.55, arreglo_de_ayer) -> [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] │
└──────────┴───────────────┴───────────────┴────────────────────┘

=== Las tres filas juntas, con list_sum(...) verificado contra el revenue acumulado ===
┌──────────┬───────────────┬───────────────┬────────────────────┬───────────┐
│ 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 │
└──────────┴───────────────┴───────────────┴────────────────────┴───────────┘

Fíjate en el orden dentro del arreglo: [0.55, 3.45, 8.45], el valor de hoy primero, el más viejo al final. Esta es una decisión de diseño, no un accidente — un arreglo donde el elemento más reciente siempre está en la posición [1] (la primera, en el indexado de DuckDB) hace que operaciones como "el revenue de ayer" o "el revenue de hace dos días" sean un simple arr[1], arr[2], sin tener que saber cuántos elementos tiene el arreglo completo. list_sum(revenue_array_7d) da 12.45 en el día 3 — la suma de los tres días, sin que ninguna consulta haya vuelto a tocar fact_orders para calcularlo.

Diagrama: el arreglo que crece por el frente

Dia 1                Dia 2                      Dia 3
┌────────┐           ┌──────────────┐            ┌────────────────────┐
│ [8.45] │  prepend   │ [3.45, 8.45] │  prepend   │ [0.55, 3.45, 8.45]  │
└────────┘  ───────>  └──────────────┘  ───────>  └────────────────────┘
             agrega 3.45              agrega 0.55
             al FRENTE                al FRENTE

Cada paso: arreglo_de_hoy = list_prepend(revenue_de_hoy, arreglo_de_ayer)
Nunca se relee ningun daily_revenue anterior directamente desde fact_orders.

Profundización: por qué el patrón real de Zach Wilson usa FULL OUTER JOIN, y Kiosko no lo necesita (todavía)

El repositorio cumulative-table-design de DataExpert-io describe el mecanismo de producción con más precisión que el ejemplo de esta lección: "hacemos un FULL OUTER JOIN entre la tabla acumulativa de ayer y los datos de hoy, y construimos nuestros arreglos de métricas para cada usuario". La palabra clave ahí es FULL OUTER JOIN, no un simple JOIN — y la razón es real: en el caso original de Zach Wilson (usuarios activos de una app), un usuario que estuvo activo ayer puede no aparecer en absoluto en los datos de hoy —no generó ningún evento—, y un usuario que aparece hoy puede ser alguien que nunca había aparecido antes. Un FULL OUTER JOIN captura ambos casos: al usuario de ayer que no tiene fila hoy, le agrega un 0 al frente de su arreglo (sigue existiendo, solo que inactivo hoy); al usuario nuevo de hoy, le crea un arreglo desde cero, exactamente como el "Día 1" de esta lección.

El ejemplo de Kiosko de esta lección se simplifica a propósito: las tres tiendas (S01, S02, S03) existen los siete días completos de la semana —ninguna tienda "aparece" o "desaparece" de un día para otro—, así que no hace falta un FULL OUTER JOIN para decidir si una tienda ya tenía fila el día anterior. La lección 6 usa un bucle explícito sobre las tres tiendas conocidas para cada uno de los siete días, que logra el mismo resultado sin la complejidad de un OUTER JOIN — una simplificación honesta, no una desviación del patrón. Si Kiosko abriera una tienda nueva a mitad de semana, o cerrara una, el patrón de esta guía necesitaría el FULL OUTER JOIN completo de DataExpert-io para manejarlo correctamente.

Errores comunes

Sumar el arreglo sin ROUND() y encontrarse con errores de punto flotante. Qué pasa: alguien calcula list_sum(revenue_array_7d) directamente, sin redondear, y obtiene un número como 11.899999999999999 en vez de 11.9. Por qué pasa: DOUBLE en DuckDB (como en casi cualquier lenguaje) representa números decimales en binario, y sumas repetidas de valores como 3.45 y 8.45 pueden acumular un error de redondeo minúsculo pero visible. Cómo detectarlo: si tu "Qué esperar" muestra una cadena larga de nueves o ceros donde esperabas un número limpio de dos decimales, es este problema. Cómo corregirlo: la misma disciplina que esta guía aplicó a revenue desde el módulo 1 —ROUND(..., 2)— aplica igual a cualquier suma sobre un arreglo de montos: ROUND(list_sum(revenue_array_7d), 2), nunca list_sum(...) a secas cuando el resultado se muestra o se compara.

Anteponer el valor nuevo al final del arreglo en vez de al frente. Qué pasa: alguien escribe list(yesterday_array) + [today_value] en vez de [today_value] + list(yesterday_array), dejando el valor más reciente al final. Por qué pasa: en muchos contextos de programación, "agregar a una lista" significa agregar al final (append), y ese hábito es fácil de trasladar aquí sin pensarlo. Cómo detectarlo: si revenue_array_7d[1] (la primera posición) no corresponde al día más reciente, algo se anotó al revés — cualquier consulta que asuma "la posición 1 es hoy" (como la lección 7 va a hacer) daría resultados incorrectos sin ningún error visible. Cómo corregirlo: esta guía adopta la convención de "más reciente primero" de forma consistente — usa siempre list_prepend(valor_nuevo, arreglo_de_ayer) o el equivalente [valor_nuevo] + list(arreglo_de_ayer), nunca al final.

Confundir "el arreglo tiene 3 elementos" con "han pasado 3 días de actividad". Qué pasa: alguien asume que la longitud del arreglo (len(revenue_array_7d)) siempre representa cuántos días tuvo actividad la tienda, sin distinguir entre "días transcurridos desde que se empezó a trackear" y "días con revenue mayor a cero". Por qué pasa: en el ejemplo de esta lección, los tres días de S01 tuvieron revenue positivo, así que ambas cantidades coinciden por casualidad. Cómo detectarlo: si una tienda tuviera un día sin ninguna venta (revenue 0.0, un caso real que vas a ver en la lección 6 para S03), su arreglo seguiría creciendo un elemento por día transcurrido, pero no todos esos elementos representarían un día "activo". Cómo corregirlo: la longitud del arreglo mide días transcurridos dentro de la ventana (hasta el tope de 7 o 30); contar días activos —revenue mayor a cero— necesita una columna o cálculo separado, exactamente lo que active_days_7d/active_days_30d van a resolver en las lecciones 6 y 7.

Ejercicios

Ejercicio 1 — Continúa el arreglo un día más. El revenue de S01 el 2026-08-06 fue 6.7. Calcula, a mano primero y luego con código, cuál sería revenue_array_7d para ese cuarto día, y verifica su suma con list_sum.

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'
"""))

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

Ejercicio 2 — Explica por qué el ejemplo de esta lección no necesita FULL OUTER JOIN, usando tus propias palabras. Sin releer la profundización, escribe 2-3 frases explicando qué tendría que ser distinto sobre las tiendas de Kiosko para que este ejemplo sí necesitara un FULL OUTER JOIN como el del repositorio original de Zach Wilson.

Ejercicio 3 — Calcula cuántos elementos tendría el arreglo después de 10 días seguidos, si nunca se recorta. Sin código, usando solo el mecanismo de list_prepend que aprendiste en esta lección, responde: si Kiosko siguiera agregando un valor por día durante 10 días sin ningún límite de tamaño, ¿cuántos elementos tendría el arreglo el día 10? ¿Por qué la lección 6 va a necesitar recortarlo?

Resumen y siguiente paso

Esta lección introdujo el cumulative table design de Zach Wilson/DataExpert con el ejemplo más pequeño posible: tres días de revenue de S01, construidos uno a la vez con list_prepend(valor_de_hoy, arreglo_de_ayer), sin releer nunca fact_orders completo para recalcular una suma que ya estaba acumulada. Viste, con list_sum verificado, que el arreglo acumula correctamente (12.45 después de tres días), y entendiste por qué el patrón real de producción usa FULL OUTER JOIN —para manejar entidades que aparecen o desaparecen— mientras que Kiosko, con sus tres tiendas fijas, puede simplificarlo con un INSERT directo.

Antes de avanzar deberías poder: explicar la diferencia mecánica entre un accumulating snapshot (lecciones 2-4) y un cumulative table design (esta lección); calcular a mano el siguiente elemento de un arreglo dado el arreglo de ayer y el valor de hoy; y explicar por qué un arreglo sin recorte de tamaño deja de significar "los últimos N días".

La lección 6 escala este mecanismo a las tres tiendas y los siete días completos de Kiosko, con el recorte de tamaño (arr[1:7] para la ventana de 7 días, arr[1:30] para la de 30) que este ejemplo todavía no necesitó, construyendo fact_store_activity de punta a punta.

Recursos

• DataExpert-io — repositorio cumulative-table-design (Zach Wilson) — la fuente exacta de este patrón: "we FULL OUTER JOIN yesterday's cumulative table with today's daily data and build our metric arrays". github.com/DataExpert-io/cumulative-table-design. En inglés.
• DuckDB — documentación de funciones de listas (list_prepend, list_sum), la base mecánica de esta lección. duckdb.org/docs/current/sql/functions/list. En inglés.
• DuckDB — documentación del tipo de dato LIST, incluida la declaración de columnas tipo arreglo (DOUBLE[]) usada en esta lección. duckdb.org/docs/current/sql/data_types/list. En inglés.