Persistent derived tables solve the "this query is too expensive to run interactively" problem, but they create a new one: every time the trigger fires, Looker rebuilds the entire table. If your events PDT scans three years of history to add one day of new rows, you are paying for 1,095 days of compute to get 1 day of data — every single night, forever.
Incremental PDTs fix that. Instead of rebuilding from scratch, Looker builds only the newest slice of the table and appends it to what already exists. This tutorial covers when incremental PDTs are the right call, how increment_key and increment_offset actually behave, the SQL Looker generates on your behalf, and the failure modes that cause silent data drift.
What an incremental PDT actually does
A normal PDT rebuild is: run the sql, write the result to a new scratch table, swap it in. An incremental PDT changes step one. Looker inspects the existing table, works out the most recent increment (a day, a week, a month — whatever you declared), deletes those rows plus any rows covered by the offset, re-runs your sql with a WHERE clause restricted to that window, and inserts the result.
Two parameters control it:
increment_key— the time granularity at which the table is extended. It must reference adimension_grouptimeframe on the same view, such asevent_datefor adimension_group: eventwith adatetimeframe.increment_offset— how many additional previous increments to rebuild alongside the current one. This is your late-arriving-data allowance.
A minimal example:
view: events_daily {
derived_table: {
datagroup_trigger: etl_datagroup
increment_key: "event_date"
increment_offset: 3
distribution_style: even
sql:
SELECT
TIMESTAMP_TRUNC(e.created_at, DAY) AS event_date,
e.account_id,
e.event_type,
COUNT(*) AS event_count,
COUNT(DISTINCT e.session_id) AS sessions
FROM raw.events AS e
GROUP BY 1, 2, 3 ;;
}
dimension_group: event {
type: time
timeframes: [raw, date, week, month, quarter, year]
datatype: timestamp
sql: ${TABLE}.event_date ;;
}
dimension: account_id { type: string sql: ${TABLE}.account_id ;; }
dimension: event_type { type: string sql: ${TABLE}.event_type ;; }
measure: total_events { type: sum sql: ${TABLE}.event_count ;; }
measure: total_sessions { type: sum sql: ${TABLE}.sessions ;; }
}
Note what is not in that SQL: any date filter. You do not write the incremental WHERE clause yourself. Looker wraps your query with a predicate on the column backing increment_key, which is why the increment key must be a real, filterable column in the derived table's own output. If event_date were only a computed dimension with no underlying column, the increment would fail.
With increment_offset: 3, a build on 14 March rebuilds 11, 12, 13 and 14 March. Rows older than that are never touched.
Choosing the increment key and offset
The increment key is a business decision disguised as a technical one. Ask two questions:
- How late does data arrive? If mobile events can be uploaded 48 hours after they occur, a daily key with
increment_offset: 2(minimum) is required, and 3 is safer. If a billing system restates a whole month at close, a daily key will never be correct — useincrement_key: "event_month"with an offset of 1. - How large is one increment? The point is cheap builds. If one day is 4 TB, consider an hourly key. If one day is 200 rows, the incremental machinery is overhead you do not need — a plain rebuild is simpler and simpler is cheaper to maintain.
A rule we apply on client projects: offset must cover your worst realistic lateness, and you must have an independent full-rebuild schedule. Incremental tables accumulate small errors — a backfill in the source, a mutated row outside the offset window, a deploy that changed the aggregation logic. Plan a full rebuild (weekly or monthly) rather than trusting an append-only table forever.
Verifying it works before you trust it
Do not judge an incremental PDT by looking at a dashboard. Check three things directly.
1. Read the generated SQL. In development mode, open the Explore, run a query, and use SQL in the query panel, then check the PDT build SQL under Admin → Persistent Derived Tables → your table → Show details. You should see your query wrapped with a predicate on the increment column and an INSERT into the existing table rather than a CREATE TABLE AS of the whole history.
2. Reconcile a window against the source. Run the same aggregation directly against the raw table for a period inside the offset window and a period outside it, and compare:
SELECT DATE(created_at) AS d, COUNT(*) AS c
FROM raw.events
WHERE created_at >= '2026-02-01' AND created_at < '2026-02-08'
GROUP BY 1 ORDER BY 1;
Any drift on old dates means data mutated outside the offset — a signal your offset is too short or you need a scheduled full rebuild.
3. Watch build times and bytes. In System Activity, the PDT build events and your warehouse's job history should show a step change downward after the switch. If build cost did not drop meaningfully, the incremental predicate probably is not being pushed down to a partition — see below.
Partitioning is what makes this cheap
On BigQuery, an incremental PDT that is not partitioned on the increment column still scans the whole table on every build, because the WHERE clause has nothing to prune against. Declare the partition explicitly:
derived_table: {
datagroup_trigger: etl_datagroup
increment_key: "event_date"
increment_offset: 3
partition_keys: ["event_date"]
cluster_keys: ["account_id"]
sql: ... ;;
}
The equivalents elsewhere: distribution and sortkeys on Redshift, indexes on Postgres, cluster_keys on Snowflake. The increment key should almost always match the partition key. If it cannot, question whether an incremental PDT is buying you anything at all.
The failure modes worth knowing
- Changing the SQL does not rebuild history. If you edit the aggregation logic, the old rows keep the old logic and the new rows use the new one — with no error. Any logic change to an incremental PDT must be followed by a deliberate full rebuild. Treat this as part of the deploy checklist, not an afterthought.
- Dev/prod scratch schemas diverge. In development mode Looker builds a separate dev copy. A dev incremental table built once from a small window will not match production; validate in a controlled environment or after deploy.
- Offset is counted in increments, not days. With
increment_key: "event_week",increment_offset: 3rebuilds three weeks, not three days. This is the most common misconfiguration we see in audits. increment_offsetcannot fix deletes. If rows are hard-deleted in the source outside the window, the PDT keeps them. Prefer soft deletes upstream, or rebuild in full on a schedule.- Triggers still matter. Incremental builds run on the same
datagroup_trigger/sql_trigger_valuemechanism as any PDT. If the trigger fires hourly but the increment key is daily, you rebuild the same day repeatedly — usually harmless, occasionally expensive.
When not to use an incremental PDT
Reach for something else when the table is small (just rebuild it), when the underlying data is heavily restated (use a full rebuild, or move the transformation into your ETL/dbt layer), when correctness matters more than cost on a table that feeds finance reporting, or when your warehouse supports materialized views that solve the same problem with fewer moving parts inside Looker.
Incremental PDTs are a sharp tool: enormous savings on append-heavy event and log tables, and a quiet source of wrong numbers on anything that mutates. The teams that get value from them are the ones that pair every incremental table with a documented offset rationale, a partition key, and a scheduled full rebuild.
Need a second pair of eyes on your PDT strategy, warehouse spend, or LookML architecture? Vistelio's senior Looker developers do exactly this work — get in touch or read about our Looker health check and technical audit.