persist_for (for derived tables)

This page refers to the persist_for parameter that is part of a derived_table.

persist_for can also be used as part of an Explore, described on the persist_for (for Explores) parameter documentation page.

persist_for can also be used as part of a model, described on the persist_for (for models) parameter documentation page.

Usage

view: my_view {
  derived_table: {
    persist_for: "24 hours"
    ...
  }
}
Hierarchy
persist_for
Default Value
None

Accepts
A string containing an integer followed by a timeframe (seconds, minutes, or hours)

Definition

Consider instead using a datagroup parameter and datagroup_trigger parameter, described in the documentation on caching queries.

persist_for lets you set the maximum amount of time that a persistent derived table may be used before it is regenerated. When a user runs a query that relies on a persist_for derived table, Looker checks the age of the table against persist_for. If the age is greater than the persist_for setting, the derived table is regenerated before the query is run. If the age is less than the persist_for setting, the existing derived table is used.

persist_for for a PDT runs independently of persist_for parameters for models and Explores.

If your admin has given you develop permission, you can force a derived table to regenerate before its persist_for age has been reached. Select the Rebuild Derived Tables & Run option from the Explore actions gear drop-down menu.

See the Derived tables in Looker documentation page for further details about the Rebuild Derived Tables & Run option.

Examples

Regenerate the derived table if it is older than 1 hour

persist_for: "1 hour"

Regenerate the derived table if it is older than 1.5 hours

persist_for: "90 minutes"

Regenerate the derived table if it is older than 1 day

persist_for: "24 hours"

Things to consider

persist_for requires that you have enabled persistent derived tables

persist_for will have no effect unless you have enabled persistence for derived tables on your Looker instance. Most customers do set up persistent derived tables when they initially configure Looker. The most common exception to this rule is for customers that connect Looker to a PostgreSQL read-only, hot-swap replica database.

persist_for works differently between Development Mode and Production Mode

persist_for should work as expected in Production Mode. In Development Mode all derived tables are persisted for a maximum of 24 hours, even if you set persist_for to a longer value.

See the Persisted tables in Development Mode section of the Derived tables in Looker documentation page for more information.

Alternatives to persist_for

Once the persist_for timeframe expires, Looker does not automatically regenerate a new derived table. Rather, the table is dropped, and a new derived table will be generated the next time a user queries it. Instead of waiting for a user query to trigger the generation of a derived table, you can schedule the automatic regeneration of a derived table using sql_trigger_value.

Difference between datagroup and max_cache_age

Using the datagroup parameter with a datagroup_trigger parameter gives you more flexibility in triggering your PDT rebuild. However, the max_cache_age parameter only invalidates the cache; it does not cause PDTs to expire. Use persist_for with a derived table if you want to set a maximum time period before dropping a PDT from the scratch schema.