---
title: "Looker derived tables: SQL, native and persistent | Metacenta"
description: "A Looker derived table is a query Looker treats as a table. How SQL, native and persistent derived tables differ, and when the logic belongs in dbt."
url: "https://metacenta.com/rules/lookml-sql-derived-table"
updated: "2026-10-14"
tokens: 1006
---

BI semantic layer · rule lookml-sql-derived-table

# Looker derived tables: SQL, native and persistent

A Looker derived table is a query whose results Looker treats as a table in your database. You define it in a view file with `derived_table`, using either LookML or SQL.

A Metacenta review checks this under the rule **Transformation logic lives in dbt, not the BI layer**. Everything below applies whether or not you ever commission one.

## SQL and native derived tables

A native derived table uses `explore_source` and lists its columns from fields in your model. A SQL derived table uses the `sql` parameter and can't refer to LookML dimensions or measures. Give each SQL column an `AS` alias, because your dimensions reference those names.

## Temporary and persistent derived tables

With no persistence setting, a derived table isn't written to the database. Looker runs its SQL at query time unless cached results exist, so a slow one can strain the database.

A persistent derived table (PDT) is written to a scratch schema and rebuilt by one of these strategies:

- `datagroup_trigger` rebuilds when a named datagroup triggers.
- `sql_trigger_value` rebuilds when a SQL statement's result changes.
- `interval_trigger` rebuilds on a time interval.
- `persist_for` keeps the table for a set time, then drops it.

See [Looker's derived table docs](https://docs.cloud.google.com/looker/docs/derived-tables) for incremental PDTs and materialized views.

## When to use a derived table

A derived table suits a short-lived analysis, or logic that depends on a user's filter at query time. Shared business logic is better upstream in dbt, where it can be tested and documented.

## What this rule checks

This rule flags each Looker view built from a SQL derived table, a `derived_table` with `sql:` of its own. A native derived table built with `explore_source` is never flagged. A persisted one is rated higher, because Looker builds and stores it.

## Why it matters

Logic here is invisible to dbt: not tested, not documented and not in the lineage. A model change upstream can break it silently. A persisted one is a second pipeline, with a warehouse cost no dbt build report shows.

## How to fix it

**Review the transformation logic living in Looker derived tables.** Move the query into a dbt model and point the view at it. If it is only an aggregate of the model, rewrite it as a native derived table with `explore_source`.

Before:

```lookml
view: customer_order_counts {
  derived_table: {
    sql: select customer_id, count(*) as order_count
         from analytics.fct_orders group by 1 ;;
  }
}
```

After:

```lookml
view: customer_order_counts {
  derived_table: {
    explore_source: orders {
      column: customer_id { field: orders.customer_id }
      column: order_count { field: orders.count }
    }
  }
}
```

## When it is fine to leave

A SQL derived table is legitimate LookML and sometimes the right call. Examples include a short-lived analysis, or logic that depends on a user's filter at query time. For that reason it does not count towards the score. Name an owner and tell us, and it drops.

## What we need to check it

The `.lkml` files alone. The reader drops the derived table's SQL before any check runs, so the finding names the view and its line only.

[Ask about a review](https://metacenta.com/enquire?src=rule-lookml-sql-derived-table&via=md)

## Related pages

- [dbt semantic models reading unbuilt columns](https://metacenta.com/rules/semantic-column-not-built)
- [dbt semantic model keys with no unique test](https://metacenta.com/rules/semantic-primary-entity-unasserted)
- [Looker views reading tables dbt does not know](https://metacenta.com/rules/lookml-view-unbuilt-table)
- [Looker views reading raw dbt sources](https://metacenta.com/rules/lookml-view-reads-raw-source)
- [A sample review, as a client reads it](https://metacenta.com/sample)
- [All published rules](https://metacenta.com/rules)
- [How a review is assessed](https://metacenta.com/standard)