Persistent Derived Tables: When and How to Use Them

Derived tables can be a powerful tool in a Looker developer's arsenal. They allow you to create tables on-the-fly from existing tables, essentially enabling you to perform ETL (extract, transform, load) operations directly within Looker. Looker's LookML provides two types of derived tables: non-persistent and persistent. In this post, we will focus on Persistent Derived Tables (PDTs), understand when to use them, and delve into the how.

Understanding Persistent Derived Tables

Persistent Derived Tables, as the name suggests, are derived tables that persist in your database, unlike non-persistent derived tables that exist only for the duration of the query. The main advantage of PDTs is that they allow you to save the results of expensive calculations and complex queries, and reuse these results in multiple queries or even across user sessions. This can greatly improve the performance of your Looker instance, especially when dealing with large amounts of data.

PDTs are defined in LookML, using the derived_table parameter in a view file:

view: my_derived_table {
  derived_table: {
    sql: SELECT column1, column2, COUNT(*)
         FROM my_table
         GROUP BY column1, column2 ;;
    persist_for: "24 hours"
  }
}

In this example, my_derived_table is a PDT that persists for 24 hours. The sql parameter defines the query used to generate the PDT, and the persist_for parameter defines how long the PDT should persist in the database.

When to Use Persistent Derived Tables

While PDTs are powerful, they're not always the best solution. Here are some considerations to help you decide when to use PDTs:

1. Complex transformations: If you need to perform complex transformations that are not feasible to do in real-time, PDTs can be an excellent choice.
2. Costly operations: If your query includes expensive operations like window functions or joins on large tables, creating a PDT might help to improve performance.
3. Heavy data computations: If your operations include heavy data computations like aggregations over large datasets, a PDT can be used to store the results, reducing the load on the database for subsequent queries.
4. Frequent reuse of results: If the results of a query are frequently reused in different parts of your Looker instance, it might be more efficient to create a PDT.

Building and Using Persistent Derived Tables

Creating a PDT involves defining it in a LookML view file, as shown in the previous example. Once defined, the PDT can be used like any other table in Looker.

Here's an example of a more complex PDT:

view: order_stats {
  derived_table: {
    sql: SELECT o.order_id, o.customer_id, COUNT(*) as items, SUM(i.price) as total_price
         FROM orders AS o
         JOIN order_items AS i ON o.order_id = i.order_id
         GROUP BY o.order_id, o.customer_id ;;
    persist_for: "24 hours"
    datagroup_trigger: order_datagroup
  }
  
  dimension: order_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }
  
  dimension: customer_id {
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  
  measure: items {
    type: number
    sql: ${TABLE}.items ;;
  }
  
  measure: total_price {
    type: number
    sql: ${TABLE}.total_price ;;
  }
}

In this example, the PDT order_stats aggregates data from the orders and order_items tables. It persists for 24 hours, and it's also set to refresh whenever the order_datagroup datagroup is triggered (typically, this might happen when new data is loaded into the orders or order_items tables).

Once you've created a PDT, you can use it in Explores, LookML dashboards, and other views, just like you would with a base table. Remember, the derived table is accessed via ${TABLE} in LookML.

In conclusion, PDTs are a powerful feature of Looker that allow you to enhance performance, handle complex transformations, and manage costly operations. Remember, though, that like any powerful tool, they need to be used wisely. Be mindful of how often your PDTs refresh and how much storage they use, to ensure that you don't put unnecessary load on your database.