What happens when you click Rebuild Derived Tables

  • 30 April 2015
  • 1 reply

Userlevel 5

Note: for Looker-hosted instances, this option will only work if the table takes less than 1 hour to rebuild (due to browser timeouts).

When you query data from a persistent derived table in Explore, there will be an option in the gear dropdown in the upper right to Rebuild Derived Tables. Clicking this will cause all the persistent derived table(s) referenced in the query to immediately rebuild, as well as all PDTs that those PDTs depend on to rebuild.

Another way to look at it is that clicking Rebuild Derived Tables ensures that this query will be run with all the freshest possible data.

Note: Clicking Rebuild Derived Tables will cause the PDTs referenced in the Look to be inaccessible for only the user who clicked the button while they rebuild. While they are building, all other users will be able to query the already-built tables. Once the tables finish rebuilding, all users will be querying these new tables

1 reply

Userlevel 3

Another way to force your persistent derived tables to refresh is to make an arbitrary change to the SQL of the derived table, ie a comment or even a space, then to run a query against the table.

This will rebuild only the table you make change to rather than the table you click Rebuild Derived Tables and Run on plus any dependencies.

The cost of using this method vs clicking Rebuild Derived Tables and Run is that this table will be accessible to no one while it is rebuilding (because by changing the SQL we are changing the hash so as far as Looker is concerned this table no longer exists).

My two main use cases for this method are:

  1. Rebuilding a derived table without rebuilding all of it dependencies

  2. Trouble shooting.

    Though we are constantly rewriting our code to make it better it can sometimes happen that Looker gets into a state where Looker thinks a derived table exists when in fact it no longer does. This often results in queries against the table in question never completing thus making the Rebuild Derived Tables and Run button inaccessible. Using this method can help clear out these phantom tables and get Looker running with the latest data again.