Derived Tables Pattern

With derived tables, you can perform analytics that require intermediate aggregations, increase the query performance of complex queries, create patterns to cleanse or normalize data, and many other beneficial functions. Simply put, a derived table is Looker's way of creating new tables that don't exist in your database. You define one by providing a SQL query whose result set becomes the derived table itself.

Looker provides two options for derived tables: ephemeral or persistent. Ephemeral derived tables are not stored in your database; Looker uses common table expressions or creates a temporary table every time this derived table is referenced. Alternatively, persistent derived tables (PDTs) are written to disk and refreshed at a frequency of your choosing. You provide Looker write access to a scratch schema in your database to facilitate this process. PDTs are very similar to materialized views. Any and all data can benefit from derived tables. Please note that depending on your database, PDTs may not be available; however, all supported dialects in Looker support ephemeral derived tables.