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.
Filtering the PDT Panel
You can use the Filter button in the top left corner of the page to filter the list of PDTs, letting you choose which PDTs are shown in the PDT Panel. For example, you can filter on Build Status to view only the PDTs that are experiencing a build error, or you could filter on LookML Model Name or View Name to limit the PDTs shown to a specific model or view.
To filter the PDT Panel:
- Click the Filter button.
- The Filter button’s dropdown menu lets you choose how to filter the PDT Panel.
- In the next dropdown menu, choose the value on which you want to filter the PDT Panel. In the case of the Triggered in the last or Not triggered in the last options, enter a number of minutes.
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
now()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 (in this case, the
SELECTstatement has been misspelled). It cannot be built, and cannot be queried.
- This table, based on
sql_trigger_value, is currently built and has no problems.
- The table has not been built, because it is based on
persist_forand has not been queried since its last build expired.
- This table, based on
persist_for, is currently built. It will expire in 9 hours.
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.|
|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.|
|Occur Date||The date and time the event occurred.|
|Occur Utc Display Date||The date and time the event occurred in UDT.|
|Sequence||A step number in the PDT build.|
|Short Hash||A truncated version of the hash of the SQL that was written to create the derived table.|
|Table Name||The full name of the PDT, including the table-type prefix, a hash, and the view name.|
|Tid||The transaction ID.|
|View Name||The view name for the derived table.|