PDTs

On this Page
Docs Menu

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.

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.

  1. This table is currently built, but its trigger has an error (in this case, the CURDATE() 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. It cannot be built, and cannot be queried.

  3. This table, based on sql_trigger_value, is currently built and has no problems.

  4. This table, based on persist_for, is currently built. It will expire in 11 hours.

  5. The table has not been built, because it is based on persist_for and has not been queried since its last build expired.

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:

Field Description
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.
looker_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.
occurred (time, date, hour) The UTC time of the event.
table_name The full name of the PDT, including the table-type prefix, a hash, and the view name.
table_type trigger: a table whose persistence is controlled by sql_trigger_value or sql_trigger_if in production mode
persist_for: a table whose persistence is controlled by persist_for in production mode
dev_trigger: a development mode copy of the table
tid The transaction ID.
view_name The view name for the derived table.

Still have questions?
Go to Discourse - or - Email Support
Top