Usage

view: my_view {
derived_table: { … }
}

Hierarchy

view

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:

A native derived table that is defined using the explore_source parameter
A SQL-based derived table using a raw SQL query that you define using the sql parameter

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:

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 Looker 22.6:

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 Looker 22.6:

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 Looker 22.6:

PDTs are not supported for Snowflake or Google BigQuery 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 Looker 22.6:

Articles in this section