Materialization and Performance

How to make a Malloy model faster and cheaper to query with

Annotations in the model tell Credible to keep derived copies of your data, so queries on the model are faster and cheaper. Every copy is opt-in. Credible builds it, keeps it fresh, reuses it across versions where it can, and routes queries to it. A query that no copy covers runs live against your database, so a missing or stale copy makes a query slower, never wrong.

Derived copyMakesAnnotate
Materialized tableA source fast and cheap to queryThe source, with #@ persist
Pre-aggregateA measure fast at coarse grainsThe measure, with #@ preaggregate
Search indexA dimension's values searchableThe dimension, with #(index)

For which dimensions to index for search, see Value Indexing. How indexes are built is covered in Builds and Refreshes.

Credible reads the #@ persist and #@ preaggregate annotations described here. The #(materialize) and #(preaggregate) annotations in recent Malloy Publisher documentation are not available in Credible yet: a model that uses them compiles, and those sources and measures are answered live.

Deciding What to Persist

Persist a source

Queried often, or expensive to compute? Add #@ persist.

Pre-aggregate a measure

A busy measure queried at coarse grains? Add #@ preaggregate with its grain.

Index a dimension

Values people or agents search or filter by? Add #(index).

Leave it live

No annotation: queried live from your database.

Start with no annotations. Persist a source when its queries are slow or costly, pre-aggregate the measures dashboards hit at a few fixed grains, and index the dimensions people search by value. Add a freshness window only where data has a real staleness limit.

If an indexed dimension can't be indexed, for example because its source requires parameters, it is skipped and the publish returns a warning. Access-controlled sources have extra limits on persistence and search; see Value Search and Materialization.

Serving Behavior

A query is answered from a derived copy when one covers it, and otherwise runs live. A copy that isn't built yet, is past its freshness window, or is at a grain the query can't use is skipped.

On a materialized source, the source's search indexes and pre-aggregates are built from the materialized table rather than from your database, so:

  • Values are consistent: the index matches what queries return.
  • Refreshes line up: an index built from a table refreshes when the table does.
  • Builds are cheaper: no second scan of your database.

Materialized Tables

Put #@ persist on a source defined as a query (with ->):

#@ persist name="orders_daily"
source: orders_daily is orders -> {
  group_by: order_date is order_time.day, status
  aggregate: revenue is amount.sum()
}

#@ persist goes on a query source. A plain table source (warehouse.table(...) extend { }) has nothing to build, and the publish fails with persist-not-materializable. To keep a copy of a table, persist raw -> { select: * } and build on that.

Sources that read a materialized source, such as one that extends it or queries it with ->, read the materialized table too. To make a reader recompute live instead, annotate it with #@ -persist.

Annotations

Options are key="value" pairs on the annotation line. All are optional, except name on BigQuery.

OptionSets
nameThe table's name, optionally with its schema or dataset (name="analytics.orders_daily"). The schema must already exist. Without name, Credible names the table from the package, file and source. On BigQuery, name is required and must include the dataset (name="dataset.table"), because a BigQuery table can't live outside a dataset, unless the source uses storage=credible.
storageWhere the table is built. Omit it to build in the source's own database; storage=credible builds it in Credible's managed storage. See Where Tables Are Built.
refresh"full" (the default) rebuilds the table; "incremental" adds only new rows. See Incremental Refresh.
watermark, merge_keyHow an incremental table finds and applies new rows
freshness.window, freshness.fallbackHow stale this table may get, and what to do past that. See Freshness.

A misspelled annotation or an unknown key fails the publish, with a message naming what was meant. You don't need a ##! experimental.persistence line in the file; Credible adds it when it builds.

Where Tables Are Built

By default a materialized table is built in the source's own database, so the source's connection needs permission to create tables there.

With storage=credible, the table is built in storage Credible manages and queries on that source are answered from it:

#@ persist storage=credible
source: orders_daily is orders -> {
  group_by: order_date is order_time.day, status
  aggregate: revenue is amount.sum()
}

Use it when the connection can't or shouldn't hold tables (a read-only login, or a database you don't want Credible writing to), or to take query load off your database. Some sources can't be stored this way, for example one whose build query reads a given or a parameter, since the table would hold one caller's rows. Such a source is not built and is answered live, and its build reports why. Storage is metered; see What It Costs.

Package Manifest

How tables are shared across versions, and on what cadence they refresh, is set per package in publisher.json (see Publishing and Versions):

{
  "materialization": {
    "scope": "package",
    "freshness": { "window": "24h", "fallback": "live" }
  }
}
  • scope: "package" (the default): a table is reused across versions that define the same thing. Lowest cost.
  • scope: "version": each version keeps its own tables. Use it to own an exact rebuild schedule for a version.
  • freshness: a package-wide freshness objective.
  • schedule: a fixed rebuild time as a five-field cron expression in UTC, such as "0 6 * * *". It requires scope: "version".

Set either freshness or schedule, not both. scope belongs inside materialization; a scope at the root of the file is deprecated, and two different values fail the package load. The package page shows a version's schedule, but changing it means changing publisher.json and publishing a new version.

Incremental Refresh

A full refresh recomputes the whole table. For a large source that mostly gains rows, refresh="incremental" reads only rows past the last build.

Incremental refresh works only on sources in PostgreSQL, BigQuery and Snowflake (PostgreSQL 15 or later for a merge_key). Elsewhere the source isn't built and its queries run live, and the publish shows a warning; use refresh="full" there.

#@ persist refresh="incremental" watermark="order_date"
source: daily_revenue is orders -> {
  group_by: order_date
  aggregate: revenue is amount.sum()
}
KeyMeans
refresh="incremental"Advance the table with a delta instead of a full rebuild. Requires watermark.
watermarkAn output column of the source, such as an event time or order date, whose value for a row never decreases. Each refresh covers the range past the previous one.
merge_keyOnly for rows whose watermark can move (for example updated_at). One or more comma-separated output columns that identify a row, so a changed row replaces its old copy.

A declaration that is wrong as written (a missing watermark, a key that names no output column or names a measure) fails the publish with a message naming the problem.

A source that computes a window with calculate: is also not built incrementally; set refresh="full" or move the window into the query that reads it.

Choosing a Shape

SourceDeclareExample
Rollup or append-only factwatermark only. Each refresh replaces its range, including rows deleted upstream within it.#@ persist refresh="incremental" watermark="ingested_at"
Table whose rows changewatermark and merge_key#@ persist refresh="incremental" watermark="updated_at" merge_key="id"
No column that only increases, such as a small lookup tableLeave refresh unset#@ persist

Limits

  • Late data: a row that arrives with a watermark below the range already covered is not picked up.
  • Hard deletes: a deleted row never appears in a delta, so a merge_key source keeps it. Prefer soft deletes: keep a deleted flag in the persisted output and filter on it.

To repair either, correct the row upstream and move its watermark forward, or rebuild the table in full: Rebuild from scratch in the package page's Builds section, or forceFullRebuild on the runs API. A change to the source's definition rebuilds it in full on its own.

Pre-Aggregations

Annotate a measure with the grain to store it at. Credible keeps a rolled-up copy and answers coarse queries from it. No query names the pre-aggregate; queries that it covers are routed to it.

dimension: order_day is order_time.day

#@ preaggregate grain="order_day, category"
measure: total_revenue is amount.sum()
  • grain (required): the dimensions the pre-aggregate stores, named as dimensions the source declares. An inline expression such as order_time.day fails the publish, so declare the truncation as a dimension and name it. A query uses the pre-aggregate when everything it groups and filters by is in the grain, including coarser truncations of a stored time dimension (order_day.month over an order_day grain).
  • #@ -preaggregate clears the grains declared above it, so a source that extends another can opt out of the base's pre-aggregates.

Only measures whose partial results can be merged are pre-aggregated:

MeasurePre-aggregated
sum, count, min, max, including one filtered with { where: … } on the aggregateYes, at the declared grain or any coarser one
avg, distinct counts, median, percentilesNo; the publish fails. For an average, pre-aggregate its sum and count and divide in the query.

A query is answered live, not from a pre-aggregate, when:

  • the source has a join_many or join_cross (the pre-aggregates are skipped with a warning, because stored partials would double-count)
  • it groups by an expression instead of a declared dimension
  • it computes an aggregate that isn't one of the stored measures
  • the source's rows read a given, since the pre-aggregate would hold one caller's rows

Measures with the same grain share one pre-aggregate table. A pre-aggregate is never fresher than its base; a stale one is skipped.

Freshness

freshness.window is an objective, not a fixed refresh time: it sets how stale a table may get, and Credible schedules refreshes to meet it, skipping refreshes a recent publish or build already covered.

fallback sets what happens when a table is older than its window. live, the default, answers the query from your database instead. stale_ok keeps serving the stale table, for sources where computing live is the expensive part.

Set freshness on one source (#@ persist freshness.window="24h" freshness.fallback="live") or for the whole package in publisher.json. A table with no window is never treated as stale; it serves until its next build.

Builds and Refreshes

WhenWhat happens
On publishEvery materialized table, pre-aggregate and search index the new version needs is built, or reused if an identical one exists
On demandRebuild in the package page's Builds section rebuilds a version, or one source with or without its upstream materialized sources. From the CLI, cred rerun.
On a scheduleTables refresh to meet their freshness objective, or at the package's schedule

The Builds section lists each table and index build with its status. People with permission to update the package can see it.

What It Costs

Derived copies are metered as described on the pricing page:

  • Storage: tables and pre-aggregates in Credible's managed storage, and search indexes, billed per GB-month
  • Compute time: queries Credible answers from them, billed per second
  • Queries that run directly on your database: no compute charge from Credible. Tables built in your own database use its storage and compute, billed by your provider.

Storage Reclamation

Credible removes derived copies that no unarchived version uses. Archive versions you no longer serve to reclaim their storage; auto-archive does this on a retention window you set.

Next Steps

On this page