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.
The PDT page in the Database section of the Admin menu provides an overview of the current state of PDTs. The page displays information about PDTs by connection, such as the PDT name, the build status, the LookML models it is included in, its persistence type, and the time of the last successful build. The PDT page shows only PDTs that meet the following criteria:
- The PDT is defined in a view file that is in production.
- The PDT is part of a model that is correctly configured.
- The PDT is part of a model for which you have data access.
Information on this page is based on an internal PDT event log, described in the PDT Event Log Model section below.
To access the page, select Persistent Derived Tables in the Database section of Looker’s Admin menu.
Reading the PDT Page
The PDT page lists all PDTs for the connection selected in the connection selector. The selector shows only connections to which users have data access.
Click the downward arrow next to the connection name to view the PDTs of a different connection:
By default, the PDT page displays only 25 PDTs on a single page. To view additional PDTs:
- Click the arrows at the bottom of the page to navigate to the next or previous page.
- Click the display selector to increase the number of results displayed on a single page.
The Name column displays the name of the PDT as defined in the
view parameter of the PDT’s LookML view file.
If the PDT has built successfully, or has previously built successfully, the PDT’s hash will appear in gray underneath its name.
The Status column displays the current build status of each listed PDT. There are three status types:
- Built indicates that a PDT has successfully built.
- Not Built indicates that a PDT is not currently built.
- Build Error indicates that an error has occurred during a build. See the More Options description to learn more about troubleshooting PDTs from the PDT page.
The Model column displays the name of the model file in which the PDT’s view file is included.
If a PDT view file is included in multiple model files that share the same connection, multiple models will appear in the Model column. If a PDT view file is included in multiple model files with different connections, the PDT will also appear in other connection PDT lists.
It is important to be explicit when including view files in models, as including all view files may clutter your database schema and cause multiple copies of PDTs to build on your database, or on multiple databases.
The Persistence Type column displays the type of persistence applied to a PDT, as defined in the PDT’s view file. There are two types of persistence displayed in the Persistence Type Column:
- Trigger is displayed for PDTs that are persisted with either a
- Persist is displayed for PDTs that are persisted with the
Last Successful Build
The Last Successful Build column indicates the time that has elapsed since the last successful PDT build.
The three-dot menu displays a list of more options, including:
- Go to LookML — Opens the view file in which the PDT is defined.
- Recent Build Events — Opens the PDT event log to a preset Explore showing all recent build events for a specific PDT.
- Recent Trigger Events — Opens the PDT event log to a preset Explore showing all trigger events for a specific PDT.
- PDT Details — Opens a pop-up containing more information and statistics for a specific PDT, including Latest Build Duration, Avg Build Duration, Build Reason, Persistence Type, Trigger Value, when a trigger was last checked, and when a PDT expires, if applicable. Any error messages will be displayed in a Failure Details box at the bottom of the pop-up.
The More Options three-dot menu is especially useful for troubleshooting unexpected behavior. You can check when tables were last built, check how long they took to build, compare the latest build time against the average build time, and check whether triggers are working correctly.
The Trigger Value field in the PDT Details pop-up will display a value of N/A for succesfully built PDTs persisted with a
datagroup_trigger. To see the most recent trigger value for a datagroup, see the Datagroups page in the Admin panel.
See the Help Center article Troubleshooting PDT Regeneration for more troubleshooting tips.
Searching and Filtering the PDT Page
You can refine the PDT list for a given connection by using the search bar and filters menu in the top right of the PDT page. You can use the search bar and filters at the same time for more precise searching.
You can use the search bar in the upper right hand corner of the PDT page to narrow the PDT list. Type a word in the search bar to limit the PDT page display by PDTs that contain that word, or search for a specific PDT by name. The results will show PDTs with the matching search term in bold.
The example below is a search for PDTs that contain the word test:
You can use the Filter button next to the search bar to choose which PDTs are shown on the PDT page. For example, you can filter by Status to view only the PDTs that are experiencing a build error, or you can filter by Model to limit the PDTs shown to a specific model.
To filter the PDT page:
- Click the Filter button.
- Select a filter option from the first filter selector in the filter menu. Options include:
- Not Triggered Since Minutes — Filters the PDT page by a specified number of minutes that has elapsed since a PDT has not triggered.
- Triggered Since — Filters the PDT page by a specified number of minutes that has elapsed since a PDT has triggered.
- Model — Filters the PDT page for PDTs included in a specified model.
- PDT Type — Filters the PDT page by PDT persistence type.
- Status — Filters the PDT page by a specified PDT status.
- Choose the value on which you want to filter the PDT page in the second filter selector. In the case of the Not Triggered Since Minutes or Triggered Since options, enter a number of minutes.
- Click Add Filter to add more filters, and repeat steps 2 and 3 for each filter you are adding.
- If at any point you wish to clear your filter selections and start over, click Clear All. To remove any individual additional filters, click Clear above the filter you wish to remove.
- Click Apply to apply the selected filter criteria to the PDT page.
You will see the applied filters at the top of the PDT page:
Click the X next to an applied filter to remove it from the PDT page.
PDT Event Log Model
Looker includes a prebuilt model named
i__looker to allow easy exploration of the PDT event log. The event log is a table in the temp schema that tracks the trigger and build activity of PDTs. In addition to the PDT page More Options three-dot menu, the model can be accessed from the Connections page in Looker’s Admin menu. To access the PDT event log Explore from the Connections page, select the Show PDT Event Log option from the gear menu drop-down to the far right of each connection:
From here, you can explore the model as with any other Looker Explore. When accessed from the Connections page, the PDT event log Explore will be filtered for the entire connection, rather than a specific PDT when accessed from the PDT page:
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 persistent table, the cause of a rebuild, the text of an error message, and so on.|
|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 UTC.|
|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.|