Cache rule cookbook

Recipes by intent, not by SQL feature. Pick the row in the table that matches what you're trying to achieve; the recipe below it spells out the cache key elements, TTL, and any invalidation linkage you'll want alongside.

Pick your intent

• Per-user isolation — keep cached results private when row-level security or column masking is in play.
• Freshness control — tune TTL by how stale a result can be.
• No-PII guardrail — refuse to cache queries against sensitive tables or columns.
• Hot-table acceleration — cache aggregate reads of a slow-changing dimension hard.
• Stale-while-revalidate — serve the previously cached value past TTL while a background refresh runs. The latency win for hot dashboards.
• Parameterized statements — share cache across similar queries that differ only in literal values.

Per-user isolation

Use when the warehouse enforces row-level security or column masking, and two users running the same SQL would legitimately see different rows. Caching the result globally would leak one user's view to another.

What to set. Include userId in the cache key elements. The Gateway hashes the identity into the key, so each user gets their own cached copy. Pair with standardizedSql so that whitespace and qualifier differences don't fragment the cache further than necessary.

{
  "id": "cache_per_user_analytics",
  "name": "Per-user dashboard reads",
  "enabled": true,
  "priority": 20,
  "mode": "all",
  "conditions": {
    "statementType": { "equals": "SELECT" },
    "schema":        { "equals": "analytics" }
  },
  "actions": {
    "cache": { "ttlSeconds": 1800 },
    "cacheKeyElements": ["userId", "standardizedSql"]
  }
}

Pair with an invalidation rule on the relevant base tables so that writes flip cached per-user results to stale. See Invalidation strategies.

Freshness control

Use when different parts of the workload have different tolerance for staleness. Aggregate dashboards can tolerate an hour; live operational reads can't.

What to set. The TTL on the action is the wall-clock lifetime of a cached entry. A long TTL with no invalidation rule means stale reads stay around; a long TTL paired with invalidation gives you write-triggered freshness without paying the warehouse cost of every read.

The rules workshop ships TTL presets — minute, hour, day, week — that cover the common cases. The custom field below the presets is for the rest.

Don't stack a tiny TTL on every rule "to be safe." That defeats the cache. Reach for invalidation rules when freshness matters; reach for short TTLs when an upstream signal isn't available.

No-PII guardrail

Use when a schema or table contains PII you would never want sitting in the cache, regardless of TTL or keying. A zero-TTL rule is a safety rail — it executes the query against the warehouse every time, never recording the result.

{
  "id": "no_cache_patient_records",
  "name": "Never cache patient_records",
  "enabled": true,
  "priority": 1,
  "mode": "either",
  "conditions": {
    "schema": { "equals": "patients" },
    "tables": { "includes": "patient_records" }
  },
  "actions": {
    "cache": { "ttlSeconds": 0 }
  }
}

Note the priority. Rules are evaluated low-priority-number first. Set guardrails at priority 1 so a later, broader cache rule can't accidentally pick this traffic up. See Priority and matching mode for the full evaluation order.

Hot-table acceleration

Use when a small set of tables get hammered by reads but change infrequently — reference data, dimension tables, scheduled-refresh marts.

What to set. Long TTL, broad cache key (just standardizedSql — the same query from any user can share the result, since RLS isn't a concern on shared reference data), and an invalidation rule keyed to the table's write traffic.

{
  "id": "cache_reference_dimensions",
  "name": "Cache reference dimensions",
  "enabled": true,
  "priority": 30,
  "mode": "all",
  "conditions": {
    "statementType": { "equals": "SELECT" },
    "schema":        { "equals": "dim" }
  },
  "actions": {
    "cache": { "ttlSeconds": 86400 },
    "cacheKeyElements": ["standardizedSql"]
  }
}

Stale-while-revalidate

Use when a dashboard or hot-path query can't afford warehouse latency on every miss, but also can't tolerate stale TTL-only behavior. Stale-while-revalidate (SWR) lets the Gateway return the previously cached value past its TTL while a background refresh runs against the warehouse. The first user past expiry pays no extra latency; the refreshed result populates for everyone after.

What to set. A normal TTL plus a staleWhileRevalidate block on the cache action. windowSeconds is the ceiling on how far past TTL the Gateway will serve a stale result — beyond it, the request falls through to a synchronous warehouse call. Setting windowSeconds to null means "serve stale until a refresh succeeds" — an explicit opt-in to unbounded staleness that's rarely the right answer.

{
  "id": "cache_orders_swr",
  "name": "SWR cache for orders",
  "enabled": true,
  "priority": 10,
  "mode": "all",
  "conditions": {
    "statementType": { "equals": "SELECT" },
    "tables":        { "includes": "orders" }
  },
  "actions": {
    "cache": {
      "ttlSeconds": 3600,
      "staleWhileRevalidate": {
        "enabled": true,
        "windowSeconds": 86400
      }
    },
    "cacheKeyElements": ["userId", "warehouse", "standardizedSql"]
  }
}

Pair with an invalidation rule on the same tables. Invalidation always wins: a write that fires an invalidation marker is honored immediately, even inside the SWR window. The stale-cached response is suppressed and the next read goes to the warehouse. SWR trades a bounded amount of staleness for tail latency; it doesn't trade correctness on writes.

{
  "id": "invalidate_orders_swr",
  "name": "Invalidate orders SWR cache on writes",
  "enabled": true,
  "priority": 5,
  "mode": "all",
  "conditions": {
    "statementType": { "in": ["INSERT","UPDATE","DELETE","MERGE"] },
    "tables":        { "includes": "orders" }
  },
  "actions": {},
  "invalidateRules": ["cache_orders_swr"]
}

When SWR isn't the right answer. If the first user past expiry should wait for a fresh result rather than receive a stale one and pop, leave SWR off. If staleness is acceptable for the full window without invalidation overrides, a longer TTL alone is simpler. SWR's sweet spot is hot reads where the average response should be fast and the staleness ceiling should still be tight.

Parameterized statements

Use when your BI tool or driver emits prepared statements with placeholders — WHERE date = ?, WHERE date = :p0, or WHERE date = $1 — rather than inlining literal values. Most JDBC and ODBC drivers do this by default; Tableau, Power BI, dbt, and the warehouse-vendor connectors all support it.

What's automatic. When the Gateway sees parameterized SQL, it pulls the placeholders out from the bound values. The placeholder shape is what gets hashed for the standardizedSql portion of the cache key; the bound values are folded into the key alongside, automatically — you don't list parameters as a cache key element. Three runs of the same statement with three different date values produce three cache entries that share the placeholder form, each reused on the next run with that same value.

What to set. Use a standard cache rule — the typical ["userId", "standardizedSql"] cache key already does the right thing for parameterized SQL.

{
  "id": "cache_parameterized_analytics",
  "name": "Parameterized analytics reads",
  "enabled": true,
  "priority": 25,
  "mode": "all",
  "conditions": {
    "statementType": { "equals": "SELECT" },
    "schema":        { "equals": "analytics" }
  },
  "actions": {
    "cache": { "ttlSeconds": 3600 },
    "cacheKeyElements": ["userId", "standardizedSql"]
  }
}

What if literals are inline. Hand-written SQL and some BI tools inline parameter values directly — WHERE date = '2026-05-01' rather than WHERE date = ?. Each unique literal then becomes a different standardizedSql and fragments the cache. Check whether your tool has a "use prepared statements" setting (Tableau exposes one on most connectors; JDBC drivers respect useServerPrepStmts=true) before reaching for a TTL-only workaround.

The recognized placeholder styles, in case you need to read SQL in the traffic page:

• Positional — ? (JDBC, most BI tools). The Gateway synthesizes parameter names p0, p1, p2 in order.
• Named — :customerId, :p0 (Databricks JDBC, Oracle drivers).
• Numbered — $1, $2 (PostgreSQL).

Common pitfalls

• Caching writes. Cache rules should be conditioned on statementType = SELECT unless you've deliberately set up something exotic. The default examples all do this.
• Forgetting userId. If your warehouse enforces RLS or masking, omitting userId from the cache key elements is a security incident waiting to happen. Pair every shared cache rule with a zero-TTL guardrail on PII tables.
• Stacking short TTLs. A one-minute TTL on every rule means almost no caching. Reach for invalidation rules when you need freshness on demand.
• Skipping the analyzer. The rules workshop runs the analyzer continuously; warnings are almost always real. See Handle rule issues.

Where to go next

• Invalidation strategies — pair each cache rule with the busting story it needs.
• Handle rule issues — read the analyzer's warnings before they bite.
• Rule schema reference — every field, every operator.