Persistent derived tables (PDTs) are an important Looker feature that enable 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 displays information about PDTs, such as the PDT name, the build status, the LookML models it is included in, its persistence type, the time of the last successful build, and options for viewing additional details.

The Persistent Derived Tables page shows only the connections that have PDTs enabled and only the 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 on this page.

Understanding the PDT page

The Persistent Derived Tables page lists all PDTs for the connection selected in the connection selector. The selector shows only connections that have PDTs enabled and to which users have data access.

Click the downward arrow next to the connection name to view the PDTs of a different connection, or select All Connections to view a consolidated list of all PDTs for connections to which you have data access on that Looker instance:

All Connections is the default option in the connection selector. A status message below the selector indicates the progress of the PDTs loading for each connection that has PDTs enabled. Click the Show newly loaded PDTs link to display the results for PDTs that have fully loaded while all PDTs are being retrieved.

By default, the Persistent Derived Tables page displays 25 PDTs on a single page. To view additional PDTs, you can perform one of the following actions:

1. Click the arrows at the bottom of the page to navigate to the next or previous page.
2. Click the display selector to increase the number of results displayed on a single 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:

• Old Build indicates that the row is displaying information about an old PDT build. See the PDT Details pop-up in the Options menu for more information about this message.
• Incremental indicates that the PDT is an incremental PDT.

Last Build Status

The Last Build Status column displays the current build status of each listed PDT:

• regenerating indicates that the PDT can be queried and that there is an updated table currently building. A timestamp indicates when the PDT began building.
• success indicates that a PDT has successfully built.
  
• building indicates that a PDT is currently building and cannot be queried until the build is completed.
• not built indicates that a PDT is not currently built.
  
• failed indicates that an error has occurred during a build. You can click Build Error to see the error’s source and navigate to the PDT’s LookML if you have the appropriate permissions to see LookML. See the Options section on this page to learn more about troubleshooting PDTs from the Persistent Derived Tables page.
  

Last Successful Build

The Last Successful Build column indicates the time that has elapsed since 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:

• Persist is displayed for PDTs that are persisted with the persist_for parameter.
• Trigger is displayed for PDTs that are persisted with either a datagroup_trigger or a sql_trigger_value parameter. You can click the trigger type to view the trigger’s SQL statement. For datagroup triggers, this is the sql_trigger subparameter value for the datagroup parameter. For SQL triggers, this is the sql_trigger_value value.

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

The three-dot Options 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 three-dot menu displays a list of more options, including:

• Go to LookML — Opens the view file in which the PDT is defined if you have the appropriate permissions to see LookML.
• PDT Activity Dashboard — Opens the PDT Activity dashboard filtered to show activity for the selected PDT over the last four weeks.
• PDT Details — Opens a pop-up containing more information and statistics for a specific PDT:
  • Table Name: The hash of the latest successfully built PDT.
  • Model: The name of the model file in which the PDT’s view file is included.
  • Stable Name: If the PDT has been published as a stable view, as determined by its publish_as_db_view parameter value, the name of the stable database view.
  • Connection: The name of the connection on which the PDT is enabled.
  • Old Build: A Boolean that displays Yes if a build is an old PDT build or No if it is not.
    • Table Type: For old builds, this field appears and shows the table type. Values include Old Generation Table and Standin.
  • Incremental PDT: A Boolean that displays Yes if a PDT is an incremental PDT or No if it is not.
  • Status: Gives the Last Build Status. For failed builds, provides a SQL error message and a link to the model’s LookML if the user has the appropriate permissions to see LookML.
  • Build Info:
    • Latest Successful Build: The date and time of the most recent successful PDT build.
    • Latest Build Duration: How long the most recent PDT build took (in seconds; displays – if the table has not yet been built).
    • Average Build Duration: How long it takes to build the PDT on average (in seconds; displays – if the table has not yet been built).
    • Build Reason: The reason a PDT was built (inception for an initial build; datagroup triggered if the PDT is persisted with a datagroup; trigger value change if the PDT’s SQL trigger value has changed).
    • Increment Key: The increment_key parameter for PDTs that use incremental builds.
    • Increment Offset: The increment_offset parameter for PDTs that use incremental builds.
    • Increment Build Added/Removed Rows: The number of rows added or removed to the table when the table was last incremented (displays – if no rows were added or removed when the table’s persistence strategy last triggered an increment).
  • Persistence Rule
    • Type: The value from the Persistence Rule column.
    • Persist For: For Persist type PDTs, the persistence duration value. Not applicable for Trigger type PDTs.
    • Datagroup: For Trigger type PDTs, the trigger’s SQL statement; for datagroup triggers, this is the sql_trigger subparameter value for the datagroup parameter.
    • Trigger Value: For Trigger type PDTs, the value that triggered the PDT build (displays – for successfully 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).
    • Last Checked: For Trigger type PDTs, when the trigger value was last checked.
    • Expires: When the PDT expires, if applicable (not applicable for datagroup triggers).

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

Searching, sorting, and filtering the PDT page

You can refine the PDT list by using the search bar and filters menu in the top right of the Persistent Derived Tables page — even use the search bar and filters in conjunction for more precise searching. You can also sort your results by specific columns.

Searching

You can use the search bar in the upper right corner of the Persistent Derived Tables page to narrow the PDT list. Type a word in the search bar to limit the Persistent Derived Tables 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 following example is a search for PDTs that contain the word orders:

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.

Sorting

Click the heading names for PDT Name, Last Build Status, Last Successful Build, Last Build Duration, or Connection to sort the list of PDTs by any of those columns.

Sorting by the PDT Name column, Last Build Status column, or Connection (if All Connections is selected in the connection selector) column arranges the list of PDTs in alphabetical or reverse alphabetical order by name, build status, or connection, respectively.

Sorting by the Last Successful Build column arranges the list of PDTs in chronological order from most recent build or least recent build.

Filtering

You can use the Filter 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 shown to a specific model.

To filter the Persistent Derived Tables page:

1. Click the Filter 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.
   • PDT Type — Filters the Persistent Derived Tables page by PDT persistence type.
   • 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.
3. Choose the value on which you want to filter the Persistent Derived Tables page in the second filter selector. In the case of the Not Triggered Since Minutes or Triggered Since options, enter a number of hours or minutes. In the case of 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.

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:

• A summary of the PDT that includes the name of the LookML model and view that define the PDT, the name of the database connection that the PDT uses, the total number of the PDT’s successful and failed build attempts, and the percentage of total build attempts that were failures.
• The date and time of the most recent full rebuild of the PDT.
• If the PDT is an incremental PDT, the date and time of the most recent incremental rebuild of the PDT.
• If the PDT is an incremental PDT, the number of incremental rebuilds that have occurred since the most recent full rebuild.
• The average time taken to rebuild the PDT.
• A visualization showing all the create events that have occurred recently, grouped by a summary of the types of actions that caused the events.
• A list of all the PDT rebuilds that have occurred during the dashboard’s time period, including the date each rebuild was completed, the number of seconds taken to complete each rebuild, whether rebuilds were incremental builds, the trigger that caused each rebuild, and the number of PDT builds.
• A list of all failed rebuild attempts, including the date and time the failed rebuild began, the type of error that caused the failure, the error message, and the number of create failures.
• A list of all the PDT rebuild trigger events, including the event ID number, the date of the event, the type of event, and the data included with the trigger event.
• A list of all the PDT rebuild events, including the event ID number, the date of the event, the type of event, and the data included with the rebuild event.
• The number of queries that directly queried a field, grouped by the source of the query and the average runtime for each query source.
• The total number of queries run on the PDT.
• A list of all the PDT fields queried, including the LookML view and model in which the field is defined, the field name, the name of the Explore run that included the field, and the number of times the field was included in a query.

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: