Rollups — datahashi

A Rollup is a stored pre-aggregation. It computes selected measures over chosen dimensions at a time grain. It saves the result as Parquet files.

When a query matches the stored fields, datahashi sends it to embedded DuckDB. DuckDB reads Parquet files from ENGINE_ROLLUP_STORE_DIR. A full hit does not touch the warehouse. A rollup with live_tail still reads the newest period from the warehouse.

flowchart TD
    Q["Semantic Query<br/>revenue by month and region"] --> Router
    Router{"Pre-agg router<br/>dimensions, measures and grains stored?<br/>caller row filter fits?"}
    Router -->|"Match"| Duck["DuckDB storage<br/>reads Parquet from ENGINE_ROLLUP_STORE_DIR"]
    Router -->|"Miss"| QR["Query Runner<br/>dispatches SQL to the warehouse"]

Routing follows two rules:

The engine uses a rollup only when the query groups by stored dimensions and reads stored measures. A metric counts when every measure it reads is stored.
On any mismatch, the engine sends the query to the warehouse. Routing never fails the query.

Declare a rollup

Declare rollups in the rollups: block of the model YAML:

rollups:
  - name: monthly_order_summary
    entity: Order
    dimensions:
      - status
      - { name: order_date, grain: day }
      - customer.region                    # Joined dimension through relationship
    measures:
      - revenue
      - order_count
      - customer_id_approx                 # count_distinct_approx
    sketches:
      - customer_id_approx                 # Enables re-aggregation at coarser grains
    refresh:
      every: 1h
      when_changed: updated_at             # Only rebuilds if newest timestamp moved
    build_range:
      dimension: order_date
      days: 365                            # Stores the last 365 days of data
    partition:
      by: month
      refresh_last: 2                      # Only re-scans the newest 2 months
      compact: quarter                     # Merge ended periods into a coarser file
    live_tail: true                        # Reads newest period from the warehouse
    sort_by:
      - order_date
      - customer.region

Features

1. Grain derivability

When a rollup stores a time dimension at day, it can answer day, week, month, quarter, year, and fiscal grains. DuckDB re-aggregates daily buckets into the requested grain.

2. Live tail

History is static. The current month still changes. With live_tail: true:

Historical periods come from Parquet files.
The open period comes from the warehouse through Query Runner.
DuckDB unions both sets into one result.

Answers stay current without a full rebuild on every change.

3. Sketches for non-additive measures

sum, count, min, and max re-aggregate across dimensions. Exact count_distinct does not. count_distinct_approx and percentiles (median, p90, p95, p99) do not, unless you sketch them.

List those measures under sketches:. A sketch is a HyperLogLog (about 1.6% error) or exact value counts for a percentile. A daily rollup can then answer a coarser grain. The sketched measure must also appear in measures. Its agg must be count_distinct_approx or a percentile.

4. Partitioning and compaction

partition.by is day, week, month, quarter, or year. One Parquet file per period. A scheduled refresh re-reads the newest refresh_last periods.
partition.compact merges ended periods into a coarser file. It is not a root key of the rollup.
partition.append: true reads only rows since the last refresh. It cannot be combined with live_tail. Every stored measure must be additive or listed in sketches.

5. Freshness markers

A blind rebuild of a large rollup wastes compute.

when_changed: updated_at. The engine runs SELECT max(updated_at) on the warehouse. If the value did not move, it skips the rebuild.
when_changed_sql. Custom SQL, for example SELECT max(batch_id) FROM etl_meta.
refresh: { manual: true } has no schedule. Call POST /v1/hooks/rollups/rebuild on the control plane, with a hook token, at the end of ETL. The engine does not serve that route.

6. Joined dimensions

A rollup can store a dimension of another entity, such as customer.region on an Order rollup. The path must be one chain of many_to_one or one_to_one relationships. The rollup then serves queries that join that entity.

7. Join rollups

A query can mix measures from two entities. Example: revenue from Order and tickets from Ticket by customer.region. You do not need one rollup that holds both.

When each entity has a fresh rollup with the same shared dimensions and joins, the engine reads one rollup per entity. It joins the results on the grouped dimensions. Live queries work the same way.

Declare one rollup per entity. Do not merge entities into one rollup.
It is all or nothing. If any entity has no matching rollup, the whole query runs live. One statement cannot mix DuckDB and the warehouse.
A query with count_distinct or percentile measures across entities never uses this path. A rollup with live_tail never uses it either.

Inspection

Every query response includes diagnostic metadata:

On a hit: meta.rollup is {"used": "monthly_order_summary"}. A live-tail hit also sets "live_tail": true. When several rollups were joined, joined lists them.
On a miss: meta.rollup.missed is a list of {rollup, reason}. Reasons include grain_not_derivable, outside_build_range, and row_security.

Next: Data roles and row-level security · PostgreSQL wire protocol.