User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
derived_table

Usage

view: my_view {
  derived_table: {}
}

Hierarchy

derived_table

Default Value

None

Definition

A derived table can be treated as if it was a normal table in your database. You can create either:

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 calculated on the fly, as users request data from them, or they can be precalculated using a datagroup, the persist_for parameter, or the sql_trigger_value parameter. Derived tables are described in more detail on this page.

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 Know

Avoid Over-using 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

Looker’s ability to provide derived tables depends on whether the database dialect supports them. The following list shows which dialects support derived tables in the most recent Looker release:

Supported Database Dialects for PDTs

Looker’s ability to provide PDTs depends on whether the database dialect supports them. The following list shows which dialects support PDTs in the most recent Looker release:

PDTs are not supported for Snowflake connections that use OAuth.

Top