Rollups & Aggregations

💼 Business Context

• Raw event-level queries don't scale to interactive dashboards at billions of rows — rollups are what makes reporting fast and affordable
• Standardizes how metrics like 'daily active users' are defined and computed, so different teams don't get different numbers for the same metric
• Owned by Data Engineering / Analytics Engineering

🔌 Technical Overview

Rollups are defined as dbt models (SQL, materialized as incremental Iceberg tables) rather than hand-rolled batch jobs. A thin .NET Core scheduler, packaged as a Docker container on Azure Container Apps Jobs, triggers dbt run --select tag:rollups on a schedule; dbt's incremental materialization processes only the current day/week partition, and dbt test runs automatically afterward (row-count and not-null checks) so a broken aggregation never silently reaches the marts zone. Teams standardized on a cloud warehouse can point the same dbt project at Snowflake via its adapter instead of the lakehouse, without rewriting the SQL.

💾 dbt Model — Rollup

-- models/marts/revenue_by_category_daily.sql
{{ config(materialized='incremental', unique_key=['event_date','category']) }}

select
  event_date,
  product_category as category,
  sum(amount) as revenue
from {{ ref('stg_order_events') }}
{% if is_incremental() %}
where event_date >= (select max(event_date) from {{ this }})
{% endif %}
group by 1, 2

🔗 Integration Points

• dbt Core — SQL-based transformation and testing framework, version-controlled alongside application code
• Dockerized .NET Core scheduler (Azure Container Apps Jobs) — triggers dbt run / dbt test on a schedule
• Data Lakehouse analytics-marts zone, or Snowflake via dbt's adapter — write destination
• Query & Analytics Engine — the primary consumer of rollup tables for dashboards

🧰 Services Consumed

• Owning microservice — Cxos.Processing.Api (see the Full Application Service Map)
• Database — ADLS Gen2 (Iceberg) + Cosmos DB Table API (stream checkpoints)

⚠️ Non-Functional Considerations

• Scale: dbt's incremental materialization processes only the current day/week partition rather than the full historical table, keeping runtime predictable as data grows
• Latency: rollups are typically available a few hours after the day/week closes — not real-time by design
• Reliability: idempotent, date-partitioned writes make reruns and backfills safe
• Security/Privacy: rollups aggregate away individual-level PII by construction — inherently more privacy-safe than raw event access

🎯 Enterprise Example

The weekly executive dashboard reads from a pre-computed revenue-by-category dbt model instead of scanning a billion-row raw event table on every page load. When a currency-conversion bug briefly corrupts the aggregation, a dbt test assertion (revenue must be non-negative) fails the run automatically — preventing the bad numbers from ever reaching the dashboard.