It is usually best to rebuild persistent derived tables on a consistent schedule, using either
sql_trigger_value. However, you might sometimes want to manually rebuild a derived table or a set of them, right when you want to query them, to ensure data is fresh.
This can be done by:
- Creating a query that uses the persistent derived table(s) you would like to rebuild.
- Clicking Rebuild Derived Tables and Run in the gear dropdown.
Note: For Looker-hosted instances, this option will only work if the table takes less than 1 hour to rebuild (due to browser timeouts).
This approach will rebuild all derived tables directly referenced in the query, as well as all derived tables those derived tables refer to.
Note: The user that clicked Rebuild Derived Tables and Run will have to wait for the PDT rebuild to finish before seeing the query results. Queries run by other users will use the existing table. However, all user queries will use the new version of the table once it has completed rebuilding. Other users may also be impacted by the database strain that the rebuild may cause.
If a user clicks Rebuild Derived Tables and Run while in development mode, the production mode PDT will be affected as noted above if the SQL is identical. If the PDT in the user's development mode is different, then the production version will be completely unaffected.
For Long-Running Queries
If your PDT takes longer than 1 hour to build, you can manually rebuild by following these steps:
- Go into Development Mode.
- Make a small change to the SQL of the derived table. This could be adding a comment in the body of the SQL block (be sure to comment in the format allowed by your dialect of SQL).
- Commit and Deploy this small change to production.
- Looker's PDT runner will notice the change to the SQL and start building a new version of the table within about 5 minutes (as long as there are no other PDTs being built at the same time).
Your PDT will now rebuild in the background. This method will only rebuild the table that was modified—not any dependent tables.
Note: This will cause the PDT to be unavailable to all users until it finishes rebuilding.