One or more of my PDTs isn't building, even though the trigger value should have changed.
At a high level, the things to check are:
- The state of that PDT
- The state of the regenerator overall
For both of these items, we'll start in the Persistent Derived Tables section of the Admin panel and then dig deeper as necessary.
Checking the State of the PDT
There's quite a bit of helpful information under each entry in the PDT panel. Here's an example with two entries, one errored and one healthy:
Check the following things to make sure the PDT is able to build:
- Is there an error listed for the PDT? A SQL syntax error in either the trigger value or the derived table itself will show up in red on the Persistent Derived Tables page. You can click the LookML button to open the LookML file for the PDT and fix any errors.
- Is the PDT marked as a
triggertype PDT in the Type column on the Persistent Derived Tables page? PDTs of type
persistrebuild only when a user requests the table and are not built by the regenerator. A PDT will listen to a trigger only if it uses a
sql_trigger_valueor a datagroup trigger.
- Has the trigger value actually changed? The most recent trigger value change is shown on the Persistent Derived Tables page. Getting the
sql_trigger_valueSQL from the view and running that in SQL Runner can help confirm whether there is a new trigger value that should have prompted a change.
Checking the State of the Regenerator
The regenerator is a process that checks PDT trigger values and also builds PDTs. The important thing to remember about the regenerator is that it only does one task at a time per connection. This means that if a trigger value or PDT build takes a long time, other processes for that connection will have to wait. Some things to check are:
- How frequently is the regenerator actually running against the database? The default is every 5 minutes, but this can be changed in the Connections Admin page, under PDT Maintenance Schedule.
What is the regenerator doing right now? Searching for currently regenerating tables in the PDT panel can help pinpoint a potential bottleneck in the regenerator.
If a table is regenerating for a long time, it will hold up the regenerator and prevent it from checking other trigger values or building other tables. If this is the case, consider doing the following:
- Check the SQL of this PDT to make sure it is performant.
- Have this PDT build less frequently or during a time with minimal usage.
- Move this PDT to a regular ETL process.
- Kill this PDT in the Queries section of the Admin panel to free up the regenerator.
Note: Permissions and Locking
Looker expects that the Looker database user specified in the Connections panel has permissions to create and drop PDTs. If the Looker user is unable to perform these functions, then error messages may manifest in the PDT panel or even the Explore page. This can happen in cases where PDTs are used in processes outside of Looker, causing potential locking on the database side. Errors indicating this can include:
Relation already exists
Cannot rename table
For these cases, check against the database itself to make sure that no other processes are using the table and that the Looker database user does have access to drop the table. In some cases, the table may need to be manually dropped from the database in order to get the regenerator back on track.