derived_table

Stay organized with collections Save and categorize content based on your preferences.

Usage

view: my_view {
  derived_table: { ... }
}
Hierarchy
derived_table
Default Value
None

Definition

A derived table can be treated as if it is a normal table in your database. You can create either of the following types of derived tables:

Certain edge cases won't permit the use of the sql parameter. In such cases, Looker supports the following parameters for defining a SQL query for PDTs:

  • create_process
  • sql_create
  • With sql, create_process, or sql_create parameter, in all these cases you are defining the derived table with a SQL query, so these are all considered SQL-based derived tables.

    The derived_table parameter begins a section of LookML where you define how a derived table should be calculated, what indexes or keys it should use, and when it should be regenerated.

    Derived tables can be temporary derived tables that are calculated ad hoc as users request data from them, or they can be persistent derived tables (PDTs) that are persisted on your database using a persistence strategy.

    If your admin has enabled the Incremental PDTs Labs feature, and if your dialect supports them, you can use incremental PDTs in your project. An incremental PDT is a PDT that is built incrementally by appending fresh data to the table, instead of rebuilding the table in its entirety. See the Incremental PDTs documentation page for more information on incremental PDTs.

    See the Derived tables in Looker documentation page for more information on derived tables.

    Examples

    Create a customer_order_facts native derived table with the explore_source parameter:

    view: customer_order_facts {
      derived_table: {
        explore_source: order {
          column: customer_id { field: order.customer_id }
          column: lifetime_orders { field: order.count }
          column: lifetime_spend { field: order.total_spend }
        }
      }
    }
    

    Create a customer_order_facts derived table with the sql parameter:

    view: customer_order_facts {
      derived_table: {
        sql:
          SELECT
            customer_id,
            COUNT(*) AS lifetime_orders,
            SUM(total) AS lifetime_spend
          FROM
            order
          GROUP BY
            customer_id ;;
      }
    }
    

    Things to consider

    Avoid overusing derived tables to create pure SQL solutions

    Users who are particularly SQL savvy often use derived tables to solve problems with complex SQL queries, the results of which can then be exposed to users. While it may sometimes be necessary to do, it can also miss out on the modular, reusable nature of LookML and can lock users into rigid ways of exploring their data.

    Our general suggestion is to think about the underlying purpose of your SQL query and then convert that logic into LookML, rather than copying and pasting existing queries into a derived table. If you need assistance creating analyses that don't rely on derived tables, Looker support analysts are here to help with best practices.

    Supported database dialects for derived tables

    Supported database dialects for temporary derived tables

    For Looker to support derived tables in your Looker project, your database dialect must also support them. The following table shows which dialects support derived tables in the latest release of Looker:

    Supported database dialects for persistent derived tables

    For Looker to support PDTs in your Looker project, your database dialect must also support them.

    To support any type of persistent derived tables (either LookML-based or SQL-based), the dialect must support writes to the database, among other requirements. There are some read-only database configurations that don't allow persistence to work (most commonly Postgres hot-swap replica databases). In these cases, you can use temporary derived tables instead.

    The following table shows the dialects that support persistent SQL-based derived tables in the latest release of Looker:

    To support persistent native derived tables (which have LookML-based queries), the dialect must also support a CREATE TABLE DDL function. Here is a list of the dialects that support persistent native (LookML-based) derived tables in the latest release of Looker:

    PDTs are not supported for Snowflake connections that use OAuth.

    Supported database dialects for incremental PDTs

    For Looker to support incremental PDTs in your Looker project, your database dialect must support Data Definition Language (DDL) commands that enable deleting and inserting rows.

    The following table shows which dialects support incremental PDTs in the latest release of Looker: