The Problem

One or more of my PDTs isn't building, even though the trigger value should have changed.

Troubleshooting Steps

At a high level, the things to check are:

• The state of that PDT.
• The state of the regenerator overall for the failing PDT's connection.

For both of these items, we'll start in the Persistent Derived Tables page of the Admin menu and then dig deeper as necessary.

Checking the State of the PDT

Starting in Looker 7.6, the PDT page displays PDT information by connection and offers greater search and filter capability.

There's quite a bit of helpful information under each entry on the PDT page. Starting in version 7.6, this information can be found in the PDT Details option in the More Options three-dot menu.

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 PDT page, or, for 7.6 and later, in the Failure Details box in the PDT Details three-dot menu option. You can click the LookML button or, for 7.6 and later, the Go to LookML button in the More Options three-dot menu, to open the LookML file for the PDT and fix any errors.
• Is the PDT marked as a trigger type PDT in the Type, or, for 7.6 and later, Persistence Type column on the PDT page? PDTs of type persist rebuild 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_value or a datagroup trigger.
• Has the trigger value actually changed? The most recent trigger value change is shown on the PDT page, or, for 7.6 and later, in the PDT Details option in the More Options three-dot menu. Getting the sql_trigger_value SQL from the view and running that in SQL Runner can confirm whether there is a new trigger value that should have prompted a change.

Checking the State of the Regenerator

Starting in Looker 7.8, it's possible for more than one PDT to build in parallel on each connection, by adjusting the Max PDT Builder Connections setting.

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:

1. 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.

2. What is the regenerator doing right now? Filtering the PDT page for currently regenerating tables 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 for that connection. If this is the case, consider doing the following:

   • Check the SQL of this PDT to make sure the SQL is performant.
   • Have this PDT build less frequently or during a time with minimal database usage.
   • Move this PDT to a regular ETL process.
   • Kill this PDT in the Queries page of the Admin menu to free up the regenerator.

Note: Permissions and Locking

Looker expects that the Looker database user specified on the **Connections** page has permissions to create and drop PDTs. If the Looker user is unable to perform these functions, then error messages may be generated on the PDT page or even the Explore page. This can happen in cases where PDTs are used in processes outside of Looker, which can cause potential locking on the database side. Errors can include:

• Relation already exists
• Cannot rename table
• Access denied

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.