Looker derived tables: SQL, native and persistent

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.

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 for incremental PDTs and materialized views.

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.

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.

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.

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:

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

After:

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

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.

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.