Persistent Derived Tables (PDTs) are an important Looker feature that enables complex analysis within Looker. Looker offers admin features that can help to track and troubleshoot PDT behavior.
Using the PDT Panel
Information about build time and cause, current status, errors, and more is exposed in the PDT Panel. This panel provides an overview for the current state of all PDTs. Information in this panel is based on an internal PDT event log, described below.
The panel can be accessed on the PDTs page of Looker’s Admin section. It shows the model, view, type, and name of the derived table, as will as information about when it was last built, when the trigger was last checked, build time, and more. If the PDT is not currently built, the information will be greyed out and “not built” will appear under the table name.
When Errors Occur
The PDT panel is especially useful for troubleshooting unexpected behavior. You can check when tables were last built, how long they took, and whether the triggers are working correctly. The image below shows a number of different states for a PDT to be in.
This table is currently built, but its trigger has an error (in this case, the
CURDATE()function has been misspelled). If there is an error with a trigger, PDTs will not be regenerated on schedule. The table will persist indefinitely, rebuilding only after being dropped.
There is an error in the SQL for this derived table. It cannot be built, and cannot be queried.
This table, based on
sql_trigger_value, is currently built and has no problems.
This table, based on
persist_for, is currently built. It will expire in 11 hours.
The table has not been built, because it is based on
persist_forand has not been queried since its last build expired.
The PDT Event Log (described below) continues to be accessible, either from the Connections page, or by clicking on the gear menu as shown here:
PDT Event Log Model
Looker also includes a pre-built model called “i__looker” to allow easy exploration of the PDT event log. The event log is a table in the temp schema that tracks trigger and build activity of PDTs. The model can be accessed from the Connections page in the Admin section of Looker:
From here, you can explore the model as with any other Looker exploration.
This is a brief guide to the fields that are available:
|action||Describes the action that occurred; this may include regeneration, drop, creation and reaping activity.|
|action_data||Provides more specific detail about an action, including the trigger being used, the value of a trigger, the expiration time for a persisted table, the cause of a rebuild, the text of an error message, etc.|
|connection||The name of the connection that the derived table exists on.|
|hash||Each derived table contains a hash of the SQL that was written to create it.|
|looker_id||The unique ID of the Looker instance that generated the PDT. In many cases, there will be only one Looker instance pointing at a database, so you will only see a single ID. However, if you have a staging instance, or something of that nature, you may see multiple IDs.|
|model_name||The name of the model through which the table was generated.|
|occurred (time, date, hour)||The UTC time of the event.|
|table_name||The full name of the PDT, including the table-type prefix, a hash, and the view name.|
|table_type||trigger: a table whose persistence is controlled by
persist_for: a table whose persistence is controlled by
dev_trigger: a development mode copy of the table
|tid||The transaction ID.|
|view_name||The view name for the derived table.|