Vistelio | Datagroups and Caching Policies: Fresh Looker Data Without Melting the Warehouse

Most Looker performance complaints are really caching complaints. Either the cache is too aggressive and someone is looking at yesterday's revenue on a dashboard that claims to be live, or there is effectively no cache and every dashboard tile fires a fresh warehouse query at 9am when three hundred people open their morning report. Both problems have the same fix: a deliberate caching policy expressed as datagroups in LookML, tied to how your data actually lands.

This tutorial covers how Looker caching works, how to write datagroups, how to wire them to PDTs and to your ETL pipeline, and how to verify the policy is doing what you think.

How Looker caching actually works

When Looker runs a query it stores the result set in its own cache, keyed by the exact SQL it generated. A later query with byte-identical SQL can be served from that cache instead of hitting the database. Three things follow from that:

Cache keys are SQL, not dashboards. Change a filter value, a timezone, or a field, and you get different SQL and therefore a cache miss.
Caching is per-query, not per-user, so two users with the same filters share a cached result. Row-level security changes the SQL (through access_filter), so users on different attribute values do not share a cache entry — which is what you want.
The cache is time-bounded by a policy you choose. By default Looker caches results for one hour. If you never set a policy, that default is your policy.

A caching policy answers one question: when does a cached result stop being acceptable? The answer is almost never "after N minutes". It is "when new data lands". Datagroups let you say exactly that.

Anatomy of a datagroup

A datagroup is a named caching policy declared in a model file:

datagroup: nightly_etl {
  label: "Nightly ETL"
  description: "Invalidated when the warehouse load stamps a new batch"
  sql_trigger: SELECT MAX(loaded_at) FROM etl_metadata.batch_log ;;
  max_cache_age: "24 hours"
}

The two parameters do different jobs and you usually want both:

sql_trigger runs a cheap query on a schedule (every 5 minutes by default, controlled by the connection's PDT and Datagroup Maintenance Schedule). If the returned value changes, the datagroup is triggered: cached results are invalidated and any PDTs built on it are rebuilt.
max_cache_age is a hard ceiling. Even if the trigger never fires, results older than this are discarded. Treat it as a safety net against a stalled trigger, not as your primary mechanism.

sql_trigger must return exactly one row and one column. Keep it cheap — it runs all day, on every connection that uses it.

Trigger queries that are worth copying

Best to worst, roughly:

# 1. Best: your pipeline writes a row when a load completes
sql_trigger: SELECT MAX(batch_id) FROM etl_metadata.batch_log WHERE status = 'SUCCESS' ;;

# 2. Good: warehouse metadata about the table itself (BigQuery)
sql_trigger: SELECT MAX(last_modified_time)
             FROM `project.dataset.__TABLES__`
             WHERE table_id = 'orders' ;;

# 3. Acceptable: max timestamp in the fact table
sql_trigger: SELECT MAX(created_at) FROM analytics.orders ;;

# 4. Clock-based: fires once an hour, or at 03:00 local time
sql_trigger: SELECT FLOOR((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP('1970-01-01'), MINUTE)) / 60) ;;
sql_trigger: SELECT EXTRACT(DATE FROM CURRENT_DATETIME('America/New_York')) ;;

Option 3 looks convenient but scans the fact table every few minutes; on BigQuery that is a real bill and on a clustered table it can still be expensive. Prefer a metadata table or __TABLES__. Option 4 is fine for genuinely time-based data, but note that a date-based trigger fires the moment the clock rolls over — which may be before your 03:15 load finishes. If the load is what matters, trigger on the load.

Applying datagroups: `persist_with`

Declaring a datagroup does nothing on its own. Attach it:

connection: "warehouse_prod"
include: "/views/**/*.view.lkml"

datagroup: nightly_etl { ... }
datagroup: streaming_events {
  sql_trigger: SELECT MAX(loaded_at) FROM etl_metadata.stream_watermark ;;
  max_cache_age: "30 minutes"
}

# model-level default for every explore in the file
persist_with: nightly_etl

explore: orders { }

explore: web_events {
  # override for the one explore fed by streaming data
  persist_with: streaming_events
}

Model level sets the default; explore level overrides it. Two rules of thumb:

Every model should have a persist_with. Falling back to the one-hour default means your caching policy is an accident.
Name datagroups after the pipeline, not the dashboard. nightly_etl, hourly_salesforce_sync, realtime_events — a dashboard-named datagroup will be wrong the moment a second dashboard uses the same tables.

Most mature projects need three to six datagroups. If you have twenty, you are modelling pipelines that do not exist.

Datagroups and PDTs: `datagroup_trigger`

The same datagroup should drive both the result cache and the persistent derived tables built from those tables. Otherwise you get the classic mismatch: a PDT rebuilt at 04:00 while the cache still serves results from 03:00.

view: order_facts {
  derived_table: {
    sql: SELECT user_id,
                COUNT(*) AS lifetime_orders,
                SUM(amount) AS lifetime_revenue
         FROM analytics.orders
         GROUP BY 1 ;;
    datagroup_trigger: nightly_etl
    partition_keys: ["created_date"]
    indexes: ["user_id"]
  }
}

datagroup_trigger is the preferred persistence strategy for PDTs. Avoid sql_trigger_value on the derived table itself (it duplicates logic that belongs in the datagroup) and avoid persist_for on anything shared, because persist_for starts its clock from build time rather than from data arrival — the table expires at an arbitrary moment relative to your loads.

When a datagroup triggers, Looker rebuilds dependent PDTs in dependency order, then invalidates the cache. A cascade of PDTs on one datagroup is fine; a cascade across several datagroups with different triggers is how rebuild storms start.

Invalidating the cache from your pipeline

If your orchestration tool (Airflow, dbt Cloud, Dagster, Cloud Composer) knows exactly when a load finished, you do not have to poll for it. Two options:

1. Write a watermark row and let sql_trigger see it. Simplest and most robust — the pipeline's final task inserts into etl_metadata.batch_log. Looker notices within one maintenance cycle (up to 5 minutes).

2. Call the Looker API to trigger immediately. Use the SDK to fire the datagroup as the last step of the DAG:

import looker_sdk
from looker_sdk import models40 as models

sdk = looker_sdk.init40()  # reads looker.ini or LOOKERSDK_* env vars

sdk.update_datagroup(
    datagroup_id="warehouse_prod::nightly_etl",
    body=models.WriteDatagroup(trigger_the_datagroup=True),
)

List the available IDs with sdk.all_datagroups(); they are formatted connection_name::datagroup_name. Give the service account a dedicated API key and a role limited to the manage_models / datagroup permissions rather than reusing an admin key.

The API route removes up to five minutes of latency and stops the trigger query running all day. The watermark route keeps working when someone forgets to add the API step to a new DAG. Plenty of teams run both.

The pieces datagroups do not control

A caching policy has edges. Know where they are:

Dashboard auto-refresh. A tile set to refresh every 5 minutes re-runs the query but still respects the cache — so it will happily re-render identical cached data. Auto-refresh is not a freshness guarantee; the datagroup is.
cache_only and the "Clear cache and refresh" button. Any user can force a fresh query from the Explore gear menu, bypassing the cache entirely. If that is unacceptable on an expensive explore, restrict it with permissions rather than hoping.
Scheduled deliveries. Schedules can be attached to a datagroup so a report is sent when the data lands rather than at a fixed time. This is one of the most under-used features in Looker: in the schedule editor choose Trigger: Datagroup, pick the datagroup, and the 6am report stops going out empty when the load runs late.
Development mode. Cached results in dev mode are keyed separately, so a dev-mode test does not prove the production cache behaves the same way.
Query-level differences. Timezone conversion, now-relative filters (last 7 days computed to the second), and user-attribute-driven SQL all fragment the cache. If your hit rate is poor, look there first: a filter of 7 days ago for 7 days caches beautifully, a filter using a live timestamp never will.

Verifying the policy

Do not assume. Measure, in System Activity:

History explore, dimension Cache Result (history.result_source, values cache / query), grouped by dashboard and by hour. A well-tuned business dashboard should be 60–90% cache after the morning warm-up.
Filter History to Source = scheduled_task and check runtimes — schedules that all fire at the same minute serialize behind each other in the query queue.
PDT Event Log / PDT Builds explores show when each PDT rebuilt and how long it took. Cross-check that rebuild times cluster right after your ETL completes, not randomly through the day.
On BigQuery, compare INFORMATION_SCHEMA.JOBS bytes billed before and after the change. A datagroup rollout on a busy instance routinely cuts scanned bytes by a third or more.

A quick sanity test on any explore: run a query, run it again and confirm the second run reports cache as the result source, trigger the datagroup through the API, then run once more and confirm you are back to query.

A checklist to steal

One datagroup per real pipeline, named after the pipeline.
sql_trigger reading a metadata or watermark table, never a full table scan.
max_cache_age on every datagroup as a safety net.
persist_with at model level, overridden per explore only where the data truly differs.
datagroup_trigger on every PDT; no stray persist_for on shared tables.
Schedules triggered by datagroups instead of by the clock, wherever the report depends on a load.
A System Activity dashboard tracking cache hit rate and PDT build duration, reviewed monthly.

Where teams get stuck

The common failure is not syntax, it is that nobody owns the mapping between pipelines and datagroups. Tables get added, a new source lands on a different schedule, and everything quietly inherits nightly_etl while the streaming explore serves twelve-hour-old numbers. Review the mapping whenever a new source is onboarded, and put the review in the same pull request that adds the views.

If your Looker instance is expensive, slow at 9am, or occasionally embarrassing about data freshness, a caching audit is usually the cheapest fix available — no new infrastructure, no re-modelling, just an explicit policy. Vistelio's Looker developers do this as part of a Looker health check and technical audit, or as a focused piece of work alongside PDT and query optimisation. If you would like a second pair of eyes on your caching strategy, get in touch.

Datagroups and Caching Policies: Fresh Looker Data Without Melting the Warehouse