User Guide Getting Started Help Center Documentation Community Training
Admin Settings - PDTs

Persistent Derived Tables (PDTs) are an important Looker feature. They enable complex analysis within Looker. Looker has admin features that can help track and troubleshoot PDT behavior.

Using the PDT Panel

The PDT panel displays information about PDTs, such as build time and cause, current status, and errors. This panel provides an overview of the current state of PDTs. The PDT panel shows only PDTs that meet the following criteria:

Information in this page is based on an internal PDT event log, described below.

To access the page, select Persistent Derived Tables in the Database section of Looker’s Admin menu. It shows the model, view, type, and name of the derived table, as well 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.

If a PDT is marked with Using old version, this indicates that this PDT contains ${view_name.SQL_TABLE_NAME} syntax to reference a second PDT, and this second PDT’s SQL has changed.

Thus the original PDT’s results will be outdated until the second PDT is rebuilt and, subsequently, the original PDT is rebuilt.

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:

  1. Click the Filter button.
  2. The Filter button’s drop-down menu lets you choose how to filter the PDT panel.
  3. In the next drop-down 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 PDT states.

  1. 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.
  2. There is an error in the SQL for this derived table (in this case, the SELECT statement has been misspelled). It cannot be built, and cannot be queried.
  3. This table, based on sql_trigger_value, is currently built and has no problems.
  4. The table has not been built, because it is based on persist_for and has not been queried since its last build expired.
  5. 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 prebuilt 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 Explore.

This is a brief guide to the fields that are available:

ActionDescribes the action that occurred; this may include regeneration, drop, creation, and reaping activity.
Action DataProvides 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, and so on.
ConnectionThe name of the connection that the derived table exists on.
HashEach derived table contains a hash of the SQL that was written to create it.
IDThe 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 NameThe name of the model through which the table was generated.
Occur DateThe date and time the event occurred.
Occur Utc Display DateThe date and time the event occurred in UTC.
SequenceA step number in the PDT build.
Short HashA truncated version of the hash of the SQL that was written to create the derived table.
Table NameThe full name of the PDT, including the table-type prefix, a hash, and the view name.
TidThe transaction ID.
View NameThe view name for the derived table.