Creating Persistent Derived Tables (PDTs) in Looker: An Overview of Errors and Solutions

This article explores the creation of Persistent Derived Tables (PDTs) in Looker, detailing why we use them, the process of creating them, and how to effectively set up and utilize PDTs through real examples.

What is a PDT (Persistent Derived Table)?

A PDT in Looker is a powerful feature that allows for the creation of temporary tables based on the results of complex SQL queries. These tables can then be directly used in data modeling. Utilizing PDTs can make data processing and analysis more flexible and efficient.

Q. Can’t we just create tables in the database?
A. While it is possible to manually create tables in the database, using PDTs in Looker offers several advantages. The main reasons for using PDTs include:

• Ease of Maintenance: Defined within the LookML model, PDTs can be centrally managed along with the rest of the data model. This simplifies maintenance, especially since ETL processes are usually managed by engineers, making it more convenient for analysts.
• Automatic Updates: PDTs are automatically refreshed based on set triggers, ensuring the data remains up-to-date without the need for manual table updates.
• Performance Optimization: Looker optimizes PDT creation to minimize database load and improve performance by only including necessary data. Direct table management might require additional efforts for performance optimization.
• Security and Access Control: With PDTs, Looker’s permission system allows for detailed control over who can access the PDTs, which can be more complex at the database level.
• Development Speed: Defining PDTs using LookML allows for easier writing and management of complex SQL queries, accelerating the development process and enabling the data team to quickly derive results.

Checking PDT Settings in Looker Admin

Before creating PDTs, it’s important to ensure that the database connection settings allow for PDT usage (Enable PDTs).

Check the database connection settings in the Looker admin screen and review the necessary permissions. Specifically, ensure the service account used by Looker has sufficient permissions to perform tasks like table creation in the database (minimum: BigQuery Data Editor or BigQuery Job User).

If not properly set, you might encounter an error message like this:

derived_table generated_dates creation failed: SQL Error in CREATE TABLE as SELECT: Error getting access token for service account: 400 Bad Request POST https://oauth2.googleapis.com/token {"error":"invalid_grant","error_description":"Invalid JWT Signature."}, iss:

In my case, although the settings were correctly configured, there seemed to be an issue with the service account key file. Re-uploading the key file resolved the issue.

Creating a PDT in Looker

To create a PDT, use the derived_table parameter in the LookML view section. Below is an example of creating a PDT named generated_dates, which generates all dates between January 1, 2010, and December 31, 2025, assigning a unique ID to each date.

Please check here for various settings for sql_trigger_value: https://cloud.google.com/looker/docs/reference/param-view-sql-trigger-value

view: generated_dates {
  derived_table: {
    sql_trigger_value: SELECT 1;;  # never updates 
    sql:
      SELECT
        DATE_DIFF(date, '2010-01-01', DAY) + 1 AS dwh_date_id,
        FORMAT_DATE('%Y-%m-%d', date) AS date_string
      FROM UNNEST(GENERATE_DATE_ARRAY('2010-01-01', '2025-12-31', INTERVAL 1 DAY)) AS date ;;
  }

  dimension: dwh_date_id {
    type: number
    sql: ${TABLE}.dwh_date_id ;;
  }

  dimension: date_string {
    type: string
    sql: ${TABLE}.date_string ;;
  }
}