When should logic live in a Looker PDT rather than an upstream warehouse table?

## What a PDT actually is A derived table in LookML is a view whose source is a query rather than a table: ``` view: order_facts { derived_table: { sql: SELECT user_id, COUNT(*) AS lifetime_orders, SUM(amount) AS lifetime_value FROM public.orders GROUP BY 1 ;; datagroup_trigger: nightly_etl } dimension: user_id { primary_key: yes sql: ${TABLE}.user_id ;; } measure: total_ltv { type: sum sql: ${TABLE}.lifetime_value ;; } } ``` Without a persistence parameter it is *ephemeral*: the SQL is inlined as a subquery every time. Add persistence and Looker writes a real table into a scratch schema on the connection and points queries at it. Persistence comes in three flavours: - `datagroup_trigger:` — rebuild when a named `datagroup` fires. The datagroup is declared in the model with a `sql_trigger:` query (typically `SELECT MAX(updated_at) FROM …`) and a `max_cache_age:`. This is the disciplined option, because it ties rebuilds to actual upstream data movement and can also govern query cache invalidation via `persist_with:`. - `sql_trigger_value:` — rebuild when a scalar query's result changes. Same idea, declared inline on the table. - `persist_for:` — rebuild after a duration. Simplest and least aware of reality; a table that expires hourly rebuilds whether or not upstream data changed. Some dialects also support `materialized_view: yes`, handing persistence to the database. And `aggregate_table:` builds summary tables Looker can transparently substitute for a detail query — aggregate awareness — which is a legitimate reason for persistence to live in Looker, since only Looker knows which queries to reroute. ## The case for keeping it in Looker **Iteration speed.** A Looker developer edits LookML in a Git branch, sees results in dev mode immediately, and deploys with a commit. No pipeline PR, no orchestration change, no waiting for the next run. **BI-only shaping.** Some reshaping exists purely to make an explore work: flattening a nested column, a bridge table for a many-to-many join, a per-user rollup only this dashboard uses. Pushing that into the warehouse adds artifacts other consumers must ignore. **Aggregate awareness.** Summary tables whose purpose is to accelerate Looker queries are naturally owned by Looker, because the substitution logic lives there. ## The case for moving it upstream **Reuse.** The moment a second consumer — another BI tool, a reverse-ETL job, a notebook, a downstream model — needs the same definition, a PDT is the wrong home. It lives in a scratch schema whose names and lifecycle are Looker's business, and nothing outside Looker should depend on it. **Testing and lineage.** Warehouse transformation frameworks give you assertions, documented lineage and a build graph. A PDT has LookML validation and nothing else; the first sign the logic is wrong is usually a wrong number on a dashboard. **Operational visibility.** PDT builds are warehouse compute triggered on Looker's schedule, often invisible in the pipeline owner's dashboards. When a data engineer investigating an overnight cost spike cannot see what ran, the organization has a coordination problem, not just a technical one. **Cascades and downtime.** PDTs can depend on other PDTs. A rebuild of a base table forces dependents to rebuild, and until they finish, queries either wait or serve the previous build. A long chain turns a small upstream change into a wide window of stale or slow content. **Correctness under concurrency.** A rebuild triggered while dashboards are being read is a race the BI tool is managing on your behalf. Fine at small scale, uncomfortable at large scale. ## How I decide Three questions, in order: 1. **Who else needs this result?** More than one consumer → upstream, no argument. 2. **Is it a business fact or a presentation detail?** "Active customer", "net revenue", "churn" are facts and belong where they can be tested and versioned with the rest of the model. Flattening, bridging and BI-specific rollups are presentation details. 3. **What does the rebuild cost, and who feels it?** A one-minute nightly build is noise. An hourly rebuild scanning a large fact table is a pipeline job wearing a BI costume and should be scheduled and monitored like one. A fourth, practical one: PDTs are an excellent *prototyping* surface. Build it as a PDT to prove the shape and the value, then graduate the stable ones upstream. The anti-pattern is never graduating them — an organization discovering that its real transformation layer is a hundred undocumented derived tables inside its BI tool. ## What to say at interview Name the mechanism precisely (derived table, persistence triggers, scratch schema), then argue the boundary on ownership rather than technology: logic that defines the business belongs where the business's data pipeline is tested and orchestrated; logic that only serves one tool's rendering can live in that tool.