Query Cache

Why cache matters

Every dashboard load triggers Malloy queries against your source — a billed scan on BigQuery, database round-trips on Postgres or MySQL. Without cache, the same aggregations run on every page view — which means cost and latency scale with traffic, not with how often your data actually changes.

For most analytical workloads, data changes once a day or less. Adding a cache with a reasonable TTL makes dashboards fast for users and predictable in cost for you.

How it works: the sidecar file

Cache is configured per model using a sidecar file placed alongside the .malloy file. The sidecar has the same name as the model, with a .cache.yml suffix:

content/
  models/
    ec_revenue.malloy
    ec_revenue.cache.yml    ← cache config for ec_revenue.malloy
    ec_performance.malloy
    ec_performance.cache.yml

If no sidecar exists, queries run live on every request.

Working example

The production shape declares the policy once under defaults and lists the cacheable views under queries — one key per view, each with an empty {} body meaning "inherit the defaults":

# content/models/ec_revenue.cache.yml

model: models/ec_revenue.malloy

defaults:
  cache:
    mode: auto
    ttl_seconds: 1800

queries:
  over_time: {}
  by_category: {}
  top_categories: {}
  by_brand: {}

Field by field:

• model: path to the model this cache config applies to, relative to content/ (e.g. models/ec_revenue.malloy). The platform's source of truth is the sidecar's filename next to the model; this field is a self-reference label that validation checks against a real file.
• defaults.cache.mode: auto caches query results keyed by query name and parameter combination; live declares the model deliberately uncached (used for always-fresh views like live balances — omit ttl_seconds with it). Two further modes (cache_only, stale_while_revalidate) are accepted by the schema but currently behave as auto.
• defaults.cache.ttl_seconds: how long cached results are valid. After this time, the next request triggers a fresh query and repopulates the cache. 1800 = 30 minutes.
• queries: one key per view name. A query body may also carry its own cache: node with the same mode / ttl_seconds keys to override the defaults for that view; in practice, production sidecars keep every body {} and split models when views need different policies.

An always-live sidecar:

model: models/wallet_balances.malloy

defaults:
  cache:
    mode: live

queries:
  balance_summary: {}

Choosing a TTL

Match TTL to how often the underlying data actually changes:

• Daily batch pipelines: ttl_seconds: 86400 (24 hours)
• Hourly refreshes: ttl_seconds: 3600 (1 hour)
• Near real-time: skip cache or use ttl_seconds: 300 (5 minutes)
• Reports and document dashboards: ttl_seconds: 1800 (30 minutes) is a safe default

Cache and dashboard filters

Cache keys include the query parameters passed by dashboard filters. A user filtering by "2024" gets a cached result for that specific parameter combination. A user filtering by "2023" triggers a separate cache entry the first time, then hits cache on subsequent loads.

This means dashboards with many distinct filter combinations will have a larger cache warm-up cost. For document dashboards with a fixed default date, the cache is typically warm within one load.

What invalidates a cached entry

Each cached entry is scoped to one query in one model with one specific parameter combination. Editing the .malloy file invalidates every cached entry for queries inside it on the next request — there is no manual eviction step.

Adapter differences

Cache behavior itself is the same on all three adapters. Cost implications differ:

• BigQuery — every uncached run is a billed scan; favouring longer TTLs on stable queries dominates the cost equation.
• Postgres and MySQL — the cost is mostly per-roundtrip latency; caching helps less when the underlying tables are small and well-indexed.

See the source adapter comparison.