home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
Admin settings - Persistent Derived Tables

Looker’s persistent derived tables (PDTs) functions enable you to perform complex analysis within Looker. Looker displays several admin features that can help track and troubleshoot PDT behavior on the Persistent Derived Tables page, which admins and users with the appropriate permissions can access from the Database section of Looker’s Admin panel. (See the Derived tables in Looker documentation page for information on troubleshooting PDTs.)

The Persistent Derived Tables page shows only the connections that have PDTs enabled and only the PDTs that meet the following criteria:

Information on this page is based on an internal PDT event log, described in the PDT Event Log model section on this page.

Customizing the table

By default, the Persistent Derived Tables page displays 25 PDTs on the page and loads PDTs for all connections for which you have data access on the Looker instance. There are several ways you can change the data displayed in the table:

  1. Click the arrow next to All Connections to view the PDTs from a specific connection only. The selector shows only connections that have PDTs enabled and to which you have data access.
  2. Type keywords in the search box to narrow the PDT list to PDTs whose names include the keyword. The table will show the PDTs with the matching search term in bold. Click the X in the search bar to clear your search query terms. You may need to refresh the Persistent Derived Tables page to see the full list of PDTs.
  3. Click on the name of a column to sort the table by that column. Click the column name a second time to reverse the sort order.
  4. Click the Filters icon to define a filter for the table.
  5. Click on the Select columns to display icon to hide or display columns in the table.
  6. Click the arrows at the bottom of the page to navigate to the next or previous page (if the table is longer than a single page).
  7. Use the display selector to choose the number of results that are displayed on a single page.

Filtering

You can use the Filters icon next to the search bar to choose which PDTs are shown on the Persistent Derived Tables page. For example, you can filter by Last Build Status to view only the PDTs that are experiencing a build error, or you can filter by Model to limit the PDTs that are shown to a specific model.

To filter the Persistent Derived Tables page:

  1. Click the Filters icon.
  2. Select a filter option from the first filter selector in the filter menu. The following options are available:
    • Not Triggered in the Last — Filters the Persistent Derived Tables page by PDTs that have not been triggered in a specified number of hours and minutes.
    • Triggered in the Last — Filters the Persistent Derived Tables page by PDTs that have been triggered in a specified number of hours and minutes.
    • Model — Filters the Persistent Derived Tables page for PDTs included in a specified model.
    • Persistence Rule — Filters the Persistent Derived Tables page by PDT persistence type.
    • Last Attempt Status — Filters the Persistent Derived Tables page by a specified PDT status.
    • Published as Stable View — A Boolean that filters the Persistent Derived Tables page for PDTs and displays Yes for PDTs that were published as a stable view, and No for PDTs that were not published as a stable view, as determined by their publish_as_db_view parameter value.
    • Last Build Duration Longer Than — Filters the Persistent Derived Tables page by PDTs whose builds took longer than a specified number of seconds.
    • Project — Filters the Persistent Derived Tables page by PDTs from the specified LookML project.
  3. Choose the value on which you want to filter the Persistent Derived Tables page in the second filter selector. For the Not Triggered in the Last or Triggered in the Last options, enter a number of hours or minutes. For the Last Build Duration Longer Than option, enter a number of seconds.
  4. Click Add Filter to add more filters, and repeat steps 2 and 3 for each filter you are adding.
    • To clear your filter selections and start over at any point, click Clear All.
    • To remove any individual additional filters, click Clear above the filter you want to remove.
  5. To apply the selected filter criteria to the Persistent Derived Tables page, click Apply.

You will see the applied filters at the top of the Persistent Derived Tables page:

Click the X next to an applied filter to remove it from the Persistent Derived Tables page. Click Clear All to clear all filters.

Understanding the PDT page

The following sections describe the information on the Persistent Derived Tables page.

Production and Development tabs

If you are a LookML developer in Development Mode, the Persistent Derived Tables table will have two tabs:

Looker creates a development PDT when a LookML developer in Development Mode makes changes that affect the data in the PDT or the way that the PDT is queried. These changes prompt Looker to create the development PDT, but Looker doesn’t actually build the PDT unless the PDT is queried after the changes are made. The Development tab can help you determine which development PDTs Looker has created and whether they have been built.

TIP: You can also view unbuilt PDTs in your project using the Looker IDE.

See the Derived tables in Looker documentation page for more information on what prompts Looker to create development PDTs and how long development PDTs are persisted on your database.

Table columns

The following sections describe the table columns on the Persistent Derived Tables page. You click the Select columns to display icon to hide or display some of the table columns. For more information, see the Customizing the table section on this page.

PDT Name

The PDT Name column displays the name of the PDT as defined in the view parameter of the PDT’s LookML view file.

The PDT Name column displays this additional information under the PDT name, when applicable:

Last Attempt Status

The Last Attempt Status column displays the status of the last attempt to build each listed PDT:

Last Attempted At

The Last Attempted At column indicates the time of the last attempted PDT build.

Last Successful Build

The Last Successful Build column indicates the time of the last successful PDT build.

Last Build Duration

The Last Build Duration column displays the amount of time in seconds that it took for the latest build of that PDT and how long it takes to build the PDT on average in seconds.

Persistence Rule

The Persistence Rule column displays the type of persistence applied to a PDT, as defined in the PDT’s view file. It also indicates the last time a successfully built PDT was checked (for trigger type PDTs) or when a successfully built PDT is due to expire (for persist type PDTs). There are two types of persistence displayed in the Persistence Rule column:

Project

The Project column indicates the name of the LookML project where the PDT is defined.

Connection

If All Connections is selected from the connection select, the Connection column appears and displays the name of the connection on which the PDT is enabled.

Model

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.

Options menu

The three-dot Options menu is especially useful for troubleshooting unexpected behavior. The options it presents allow you to 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. You can select from:

See the Troubleshooting PDT regeneration Help Center article for more troubleshooting tips.

PDT details modal

Click the PDT Details option from the PDT’s three-dot Options menu to see the PDT details modal:

The information in the modal depends on the configuration of the PDT. Here is the information you may see:

PDT Activity dashboard

The PDT Activity dashboard shows information about the PDT, its rebuilds, and its queries:

The PDT Activity dashboard defaults to showing activity information from the previous four weeks. You can change the time period shown using the filter bar at the top of the dashboard. The PDT Activity dashboard includes tiles that show the following information:

PDT Event Log model

Looker includes a pre-built model named system_activity that allows easy exploration of the PDT event log, which is a table in a database connection’s temp schema that tracks the trigger and build activity of PDTs. You can access the model with the Recent Build Events and Recent Trigger Events links in the Options three-dot menu of the Persistent Derived Tables page, or from the Connections page in the Looker Admin panel. 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:

You can explore the model as with any other Looker Explore. When accessed from the Connections page, the PDT Event Log Explore is filtered for the entire connection. When accessed from the Persistent Derived Tables page, the PDT Event Log Explore is filtered for a specific PDT.

This is a brief guide to the available fields:

FieldDescription
ActionDescribes the action that occurred; this may include regeneration, drop, creation, and reaping activity.

See the Understanding PDT log actions documentation page for more information about viewing and understanding PDT log actions and their corresponding action data.
Action DataProvides 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.

See the Understanding PDT log actions documentation page for more information about viewing and understanding PDT log actions and their corresponding action data.
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 see only 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.
Top