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.
Use-Cases
Sales Product Marketing Customer Success Customer Support Finance Operations & Logistics
Industry
The Looker Block for User Loyalty segments customers and users by various attributes to highlight the factors that...
With the Looker Block for a Sessionization Funnels, organizations can track user flows to optimize user experience,...
The Looker Block for Affinity Analysis helps users understand how items or events relate to each other.