On this Page
Docs Menu

Go to View Parameter List


   derived_table is a child of
view: view_name
sql: SELECT ...
derived_table requires a
sql parameter


derived_table 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 begin with a raw SQL query that you write (using the sql parameter), the result of which can then be treated as if it was a normal table in your database. They can be calculated on-the-fly, as users request data from them, or they can be pre-calculated using the persist_for or sql_trigger_value parameters. Derived tables are described in more detail on this page.


Create a customer_order_facts derived table:

- view: customer_order_facts derived_table: sql: | SELECT customer_id, COUNT(*) AS lifetime_orders 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 as a means to solve problems with complex SQL queries, the results of which can then be exposed to users. While this is indeed necessary in some cases, it can lead to solutions that do not take advantage of the modular, reusable nature of LookML. This can lock users into rigid data exploration, and in some ways, negates the usefulness and purpose of Looker.

Our general suggestion is to think about the underlying purpose of your SQL query, then to convert that logic into LookML, rather than copying and pasting existing queries into a derived table. If you are struggling to create analyses that do not rely on derived tables feel free to reach out to Looker support analysts for best practices.

Still have questions?
Go to Discourse - or - Email Support