Updated August 2026. The 2023 version of this article described SQL-based derived tables and called them "native." This version corrects the definition and shows real native derived tables built with explore_source.
Looker has two kinds of derived tables, and the names matter:
- A SQL-based derived table is defined with
derived_table: { sql: ... }. You write the SQL by hand, in the dialect of your warehouse. - A native derived table (NDT) is defined with
derived_table: { explore_source: ... }. You describe the query in LookML terms, by naming an existing Explore and the fields, filters and sorts you want, and Looker generates the SQL for you from the model.
Either kind can be ephemeral (rebuilt in a subquery on every run) or persistent (materialized in the warehouse as a PDT, triggered by a datagroup or a timer). Persistence is orthogonal to native-versus-SQL: an NDT with datagroup_trigger is a PDT. The old claim that NDTs "do not persist" was simply wrong.
Why native
With a SQL-based derived table you are maintaining two definitions of the same business logic: the measure in your view and the aggregation in your derived SQL. When the measure changes, someone has to remember to change the SQL. An NDT is built from the Explore, so it reuses the dimensions, measures, joins and even access filters you already defined. That gives you:
- One definition of each metric. Change
orders.total_revenueand every NDT that uses it changes with it. - Dialect independence. The same LookML builds the table on BigQuery, Snowflake or Postgres.
- Validation in the IDE. Typos in field names are caught by the LookML validator, not by a failed PDT build at 3 a.m.
A real NDT
Suppose you have an orders Explore joined to users. A customer-level summary table as an NDT looks like this:
view: customer_summary {
derived_table: {
explore_source: orders {
column: customer_id { field: users.id }
column: country { field: users.country }
column: first_order_date { field: orders.first_created_date }
column: order_count { field: orders.count }
column: lifetime_revenue { field: orders.total_revenue }
derived_column: average_order_value {
sql: lifetime_revenue / NULLIF(order_count, 0) ;;
}
filters: [orders.status: "complete"]
timezone: "America/Chicago"
}
}
dimension: customer_id {
type: number
primary_key: yes
}
dimension: country { type: string }
dimension_group: first_order {
type: time
timeframes: [date, month, year]
sql: ${TABLE}.first_order_date ;;
}
dimension: order_count { type: number }
dimension: lifetime_revenue { type: number }
dimension: average_order_value { type: number }
measure: customers { type: count }
measure: avg_lifetime_revenue {
type: average
sql: ${lifetime_revenue} ;;
}
}
Things to notice:
- Each
column:names an output column and the Explore field that produces it. If the column name matches the field's short name you can omitfield:, but being explicit is clearer. derived_column:adds SQL computed after the Explore query, on the result columns, which is where you put ratios of aggregates.filters:andsorts:use the same syntax as an Explore'salways_filter.- Dimensions in the NDT view that share a name with a column can omit
sql:; Looker maps them to${TABLE}.column_name. - The
timezoneparameter matters when a column is a date: set it explicitly so the PDT does not build in UTC and then get compared against user-timezone queries.
Persisting an NDT
Persistence is the same as for any derived table. Prefer a datagroup so the table rebuilds only when the source changes:
datagroup: orders_etl {
sql_trigger: SELECT MAX(loaded_at) FROM orders ;;
max_cache_age: "24 hours"
}
view: customer_summary {
derived_table: {
explore_source: orders { ... } # as above
datagroup_trigger: orders_etl
indexes: ["customer_id"] # partition_keys / cluster_keys on BigQuery
}
}
Do not combine datagroup_trigger with persist_for; they are mutually exclusive strategies. Note also that the source Explore must exist in the same model and must be accessible to the PDT builder; PDTs are built by a Looker process, not the querying user, so an access_filter on the source Explore is not applied during the build. If the NDT needs row-level security, add it on the NDT's Explore, not inside the table.
Passing filters through: bind_filters and bind_all_filters
An ephemeral NDT can pick up filters from the query that uses it, which is how you build "top N per group" and similar patterns without hard-coding a date range:
view: top_products_by_month {
derived_table: {
explore_source: order_items {
column: created_month { field: order_items.created_month }
column: product_id {}
column: revenue { field: order_items.total_revenue }
bind_filters: {
from_field: order_items.created_date
to_field: order_items.created_date
}
sorts: [order_items.total_revenue: desc]
limit: 10
}
}
}
bind_all_filters: yes forwards every filter from the outer query. Bound filters only work for ephemeral NDTs; a persistent table cannot depend on the user's filter values.
Column pruning and when NDTs beat SQL
NDTs shine for pre-aggregation (customer, product, daily rollups), cohort and retention tables that are tedious to hand-write in SQL, and any table whose logic already exists as measures. Because Looker selects only the listed columns and applies the Explore's joins, the generated SQL is usually as lean as what you would write by hand.
Reach for a SQL-based derived table instead when you need warehouse-specific features an Explore cannot express: window functions with custom frames, UNNEST over arrays, recursive CTEs, or MERGE logic in an incremental PDT.
When to push the transformation down to dbt
NDTs and PDTs are a fine way to keep analytics-shaped rollups inside Looker. They are a poor substitute for a transformation layer. If a derived table is:
- reused by other tools (a notebook, a reverse-ETL sync, Data Studio),
- more than a few hundred lines of logic, or
- something finance reconciles against,
it belongs in dbt (or Dataform, or whichever transformation tool you run), with tests, documentation and a Git history of its own. LookML then models the dbt output and owns the metrics, joins and access control. We wrote up the split in more detail in dbt + Looker in 2026.
Summary
- Native means
explore_source, not "ephemeral." - NDTs reuse your model's definitions; SQL derived tables duplicate them.
- Persist with a datagroup; never mix
persist_foranddatagroup_trigger. - Bind filters for dynamic ephemeral tables; persist for stable rollups.
- Heavy transformation belongs in dbt; metrics belong in LookML.
Struggling with PDT sprawl or a model where every view is a hand-written derived table? Our LookML consultants can help; contact us.