You can use Looker's datagroup and persist_with parameters to specify a query caching policy.

If you are using cascading derived tales in your LookML model, you can use datagroup_trigger in the Persistent Derived Table (PDT) definition to specify when to rebuild the PDTs. These datagroups can also be shared in the model file to specify the query caching policy by using persist_with.

File Structure

Here is an example file structure that uses the user_facts_etl datagroup to specify when to rebuild PDTs and reset the cache for the user_stuff Explore. This example also shows how a datagroup_trigger is used to specify a datagroup caching policy for rebuilding the PDT.

Model File

  datagroup: user_facts_etl {
    sql_trigger: SELECT max(ID) FROM etl_jobs ;;
  }
  explore: user_stuff {
    ## join all user PDTs here ##
    persist_with: user_facts_etl
  }

View Files

  view: users_facts_pdt_1 {
    derived_table: {
      sql:
      	SELECT ...
    datagroup_trigger: user_facts_etl
    }
  }
  view: user_facts_pdt_2 {
    derived_table: {
      sql:
        SELECT ...
        FROM ${users_facts_pdt_1.SQL_TABLE_NAME} ;;
    datagroup_trigger: user_facts_etl
    }
  }

How Cascading PDTs Are Built Using Datagroups

Starting in Looker 7.8, admins can set a level of concurrency for building PDTs that don't depend on other PDTs with the Max PDT Builder Connections setting on the Connection Settings admin page.

1. Datagroups have two states: ready and triggered.
2. If the value returned by the sql_trigger query is different from the result of the query in the prior check, the datagroup goes into a triggered state. In this case, the etl_jobs table has been updated and user_facts_etl is now triggered.
3. Once in a triggered state, all PDTs using that datagoup as their datagroup_trigger will be rebuilt: user_facts_pdt_1 will rebuild and then user_facts_pdt_2 will follow.

   If the PDTs share the same datagroup_trigger, the PDT regenerator does a topological sort to make sure inner PDTs are built before the outer PDTs.

4. Once all PDTs in the datagroup have been rebuilt, the datagroup will go back to a ready state.
5. When the datagroup goes from triggered to ready, the cache for all models/Explores that persist_with that datagroup will be reset. That is, the user_stuff Explore cache is now reset.

If you have PDTs that are dependent on other PDTs, be careful to not specify incompatible datagroup caching policies.