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
Build better games by examining the entire lifecycle of your players from acquisition, to monetization and retention.
The Looker Block for User Loyalty segments customers and users by various attributes to highlight the factors that...
The Looker Block for Sequencing Transactions makes it easy to identify trends among returning customers.