Unlocking Business Insights with Looker PDT (Persistent Derived Tables) (~9 min read)

In today's data-driven world, businesses rely on data analytics and business intelligence tools to make informed decisions and gain a competitive edge. One such tool that has gained popularity in recent years is Looker , a powerful platform that empowers organizations to explore, analyze, and visualize their data. Central to Looker's capabilities is the concept of Persistent Derived Tables (PDTs), a feature that takes data analysis to the next level.

The Foundation of Looker PDT

At its core, Looker PDTs are a way to transform and materialize data for analysis. They allow you to create custom tables within Looker's database, providing a structured and optimized way to store and retrieve data. PDTs serve as a valuable bridge between raw data sources and meaningful insights, offering several advantages:

1. Data Transformation: PDTs enable data transformation, allowing you to clean, filter, and shape your data according to your specific needs. This ensures that your data is in the right format for analysis, eliminating the need for repetitive data cleaning tasks.
2. Performance: By storing precomputed and aggregated data, PDTs significantly improve query performance. Complex calculations and aggregations can be done once and stored for quick retrieval, reducing query processing times.
3. Reusability: PDTs are modular and reusable. Once you've created a PDT, you can use it in multiple reports and dashboards across your organization. This promotes consistency and ensures that everyone is working with the same clean and prepared data.

Building PDTs: The LookML Way

Creating PDTs in Looker is accomplished using LookML, Looker's modeling language. LookML allows you to define the logic and structure of your PDTs, making it a powerful tool for data modeling and transformation.

The process typically involves the following steps:

1. Define a PDT: Start by specifying the SQL logic for your PDT. You can define the necessary joins, calculations, and filters to shape your data as needed.
2. Create LookML Models: LookML models serve as the blueprint for your data. They define the relationships between PDTs and the dimensions and measures available for analysis.
3. Explore and Visualize: Once your PDTs and models are set up, you can use Looker's user-friendly interface to explore and visualize your data. Looker's drag-and-drop functionality makes it easy to create reports and dashboards without needing extensive SQL knowledge.

Here's an example of using Looker LookML to create a Persistent Derived Table (PDT) for sales analysis.

In this example, we'll create a PDT that aggregates sales data by month.

view: sales_pdt {
  derived_table: {
    sql: | 
      SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(sales_amount) AS total_sales
      FROM
        sales
      GROUP BY
        month
    ;;  # SQL query to create the PDT
    
    persist_for: "24 hours"  # Specifies how often the PDT is updated (e.g., every 24 hours)
  }

  dimension: month {
    type: date
    sql: ${TABLE}.month ;;
    description: "Month of the sale"
  }

  measure: total_sales {
    type: sum
    sql: ${TABLE}.total_sales ;;
    description: "Total sales for the month"
  }
}
        

In this LookML code:

• We define a view named sales_pdt, which will represent our Persistent Derived Table.
• Inside the derived_table block, we write a SQL query that aggregates sales data by truncating the order_date to the month and calculating the total sales for each month. This is the data transformation step that PDTs are designed for.
• The persist_for parameter specifies how often the PDT should be refreshed. In this example, we refresh it every 24 hours, but you can adjust the frequency according to your data update needs.
• We define a dimension called month, which represents the month of the sale. It uses the SQL expression ${TABLE}.month to reference the month column from the PDT.
• We also define a measure called total_sales, which calculates the sum of total_sales for each month using ${TABLE}.total_sales.

Once this LookML code is defined and deployed in Looker, you can use the sales_pdt view to create reports, dashboards, and explore data insights based on the aggregated sales data by month. This PDT will help you analyze sales trends and make data-driven decisions in your organization.

Use Cases for PDTs

PDTs are versatile and can be applied to various use cases across different industries:

1. Sales and Revenue Analysis: PDTs can be used to create tables that aggregate sales data, allowing sales teams to track performance, identify trends, and make data-driven decisions.
2. Customer Segmentation: By transforming and segmenting customer data, businesses can tailor marketing campaigns, personalize customer experiences, and enhance retention strategies.
3. Financial Reporting: PDTs can simplify financial reporting by providing precomputed tables for budgeting, forecasting, and financial analysis.
4. Product Analytics: PDTs can help product teams analyze user behavior, track feature adoption, and optimize product development efforts.
5. Supply Chain Optimization: For companies in logistics and supply chain management, PDTs can assist in tracking inventory levels, monitoring supplier performance, and optimizing routes.

Challenges and Best Practices

While PDTs offer substantial benefits, there are also challenges to consider:

1. Data Freshness: PDTs are not always real-time. Depending on how frequently they're updated, there might be a delay in reflecting the latest data changes.
2. Resource Consumption: Creating and maintaining PDTs can consume storage and processing resources. Regularly review and optimize your PDTs to ensure efficiency.
3. Version Control: As PDTs are part of your LookML codebase, it's essential to implement version control practices to track changes and collaborate effectively.
4. Data Governance: Ensure that data in PDTs adheres to your organization's data governance policies to maintain data accuracy and compliance.

In conclusion, Looker PDTs are a powerful feature that enhances data analytics by simplifying data transformation, improving performance, and promoting reusability. Leveraging Looker's capabilities with PDTs can provide organizations with valuable insights, enabling better decision-making and driving business success in the data-driven era.