Looker

Marketplace

Develop for Marketplace

Blocks

Derived Tables Pattern

By Looker
Often times our database doesn't store information optimally for analytics. Rather than writing inefficient dimensions or measures to calculate what we want, derived tables allow us to create temporary tables directly in our database.
Often times our database doesn't store information optimally for analytics. Rather than writing inefficient dimensions or measures to calculate what we want, derived tables allow us to create temporary tables directly in our database.

Overview

This is not an officially supported Google product.

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.

Category

Blocks

Related Content

Amazon Redshift Administration

GoogleFine-tune your Redshift deployment with a comprehensive view of performance and query analysis.Blocks

BigQuery Information Schema Performance Monitoring

GoogleMonitor and Explore your BigQuery Usage and PerformanceBlocks

© 2025 Looker Data Sciences, Inc.
Privacy | Terms | Cookies