Usage

explore: explore_name {  aggregate_table:table_name {    query: {      dimensions: [dimension1, dimension2, ... ]      measures: [measure1, measure2, ... ]      sorts: [field1: asc, field2: desc, ...]      filters: [field1: "value1", field2: "value2", ... ]      timezone:timezone    }    materialization: {      ...    }  }  ...}

Definition

Theaggregate_table parameter is used to create aggregate tables that will minimize the number of queries required for the large tables in your database.

Looker usesaggregate awareness logic to find the smallest, most efficient aggregate table that's available in your database to run a query while still maintaining correctness. (See theAggregate awareness documentation page for an overview of and strategies for creating aggregate tables.)

For very large tables in your database, you can create smaller aggregate tables of data, grouped by various combinations of attributes. The aggregate tables act as roll-ups or summary tables that Looker can use for queries whenever possible, instead of the original large table.

Once you create your aggregate tables, you can run queries in the Explore to see which aggregate tables Looker uses. For more information, see theDetermining which aggregate table is used for a query section on theAggregate awareness documentation page.

See theTroubleshooting section on theAggregate awareness documentation page for common reasons that aggregate tables aren't used.

Defining an aggregate table in LookML

Eachaggregate_table parameter must have a name that is unique within a givenexplore.

Theaggregate_table parameter has thequery andmaterialization subparameters.

query

Thequery parameter defines the query for the aggregate table, including which dimensions and measures to use. Thequery parameter includes the following subparameters:

query can also be used as part of anexplore, as described on thequery parameter documentation page.

materialization

Thematerialization parameter specifies the persistence strategy for your aggregate table, as well as other options for distribution, partitioning, indexes, and clustering that may be supported by your SQL dialect.

To be accessible for aggregate awareness, your aggregate table must bepersisted in your database. Thematerialization parameter of your aggregate table must have one of the following subparameters to specify the persistence strategy:

• datagroup_trigger
• sql_trigger_value
• persist_for (not recommended)

In addition, the followingmaterialization subparameters may be supported for your aggregate table, depending on your SQL dialect:

• cluster_keys
• distribution
• distribution_style
• indexes
• partition_keys
• publish_as_db_view
• sortkeys

To create anincremental aggregate table, use the followingmaterialization subparameters:

• increment_key
• increment_offset

datagroup_trigger

Use thedatagroup_trigger parameter to trigger the regeneration of the aggregate table based on an existingdatagroup defined in the model file:

explore: event {  aggregate_table: monthly_orders {    materialization: {      datagroup_trigger: order_datagroup    }    query: {      ...    }  }  ...}

sql_trigger_value

Use thesql_trigger_value parameter to trigger the regeneration of the aggregate table based on a SQL statement that you provide. If the result of the SQL statement is different from the previous value, the table is regenerated. Thissql_trigger_value statement will trigger regeneration when the date changes:

explore: event {  aggregate_table: monthly_orders {    materialization: {      sql_trigger_value: SELECT CURDATE() ;;    }    query: {      ...    }  }  ...}

persist_for

Thepersist_for parameter is also supported for aggregate tables. However, thepersist_for strategy may not give you the best performance for aggregate awareness. This is because when a user runs a query that relies on apersist_for table, Looker checks the age of the table against thepersist_for setting. If the table is older than thepersist_for setting, the table is regenerated before the query is run. If the age is less than thepersist_for setting, the existing table is used. So, unless a user runs a query within thepersist_for time, the aggregate table must be rebuilt before it can be used for aggregate awareness.

explore: event {  aggregate_table: monthly_orders {    materialization: {      persist_for: "90 minutes"    }    query: {      ...    }  }  ...}

Unless you understand the limitations and have a specific use case for thepersist_for implementation, it is better to usedatagroup_trigger orsql_trigger_value as a persistence strategy for aggregate tables.

cluster_keys

Thecluster_keys parameter lets you add a clustered column topartitioned tables on BigQuery or Snowflake. Clustering sorts the data in a partition that is based on the values in the clustered columns and organizes the clustered columns in optimally sized storage blocks.

See thecluster_keys parameter documentation page for more information.

distribution

Thedistribution parameter lets you specify the column from an aggregate table on which to apply a distribution key.distribution works only with Redshift and Aster databases. For other SQL dialects (such as MySQL and Postgres), useindexes instead.

See thedistribution parameter documentation page for more information.

distribution_style

Thedistribution_style parameter lets you specify how the query for an aggregate table is distributed across the nodes in a Redshift database:

• distribution_style: all indicates that all rows are fully copied to each node.
• distribution_style: even specifies even distribution, so that rows are distributed to different nodes in a round-robin fashion.

See thedistribution_style parameter documentation page for more information.

indexes

Theindexes parameter lets you apply indexes to the columns of an aggregate table.

See theindexes parameter documentation page for more information.

partition_keys

Thepartition_keys parameter defines an array of columns by which the aggregate table will be partitioned.partition_keys supports database dialects that have the ability to partition columns. When a query is run that is filtered on a partitioned column, the database will scan only those partitions that include the filtered data, rather than scanning the entire table.partition_keys is supported only with Presto and BigQuery dialects.

See thepartition_keys parameter documentation page for more information.

publish_as_db_view

Thepublish_as_db_view parameter lets you flag an aggregate table for querying outside of Looker. For aggregate tables withpublish_as_db_view set toyes, Looker creates a stable database view on the database for the aggregate table. The stable database view is created on the database itself, so that it can be queried outside of Looker.

See thepublish_as_db_view parameter documentation page for more information.

sortkeys

Thesortkeys parameter lets you specify one or more columns of an aggregate table on which to apply a regular sort key.

See thesortkeys parameter documentation page for more information.

increment_key

You can createincremental PDTs in your project if yourdialect supports them. An incremental PDT is apersistent derived table (PDT) that Looker builds by appending fresh data to the table, instead of rebuilding the table in its entirety. See theIncremental PDTs documentation page for more information.

Aggregate tables are a type of PDT, and they can be built incrementally by adding theincrement_key parameter. Theincrement_key parameter specifies the time increment for which fresh data should be queried and appended to the aggregate table.

See theincrement_key parameter documentation page for more information.

increment_offset

Theincrement_offset parameter defines the number of previous time periods (at the increment key's granularity) that will be rebuilt when appending data to the aggregate table. Theincrement_offset parameter is optional for incremental PDTs and aggregate tables.

See theincrement_offset parameter documentation page for more information.

Getting aggregate table LookML from an Explore

As a shortcut, Looker developers can use an Explore query to create an aggregate table and then copy the LookML into the LookML project:

1. In your Explore, select all the fields and filters that you want to include in your aggregate table.
2. ClickRun to get the results.
3. SelectGet LookML from the Explore's gear menu. This option is available for Looker developers only.
4. Click theAggregate Table tab.
5. Looker provides the LookML for an Explorerefinement that will add the aggregate table to the Explore. Copy the LookML and paste it into the associated model file, which is indicated in the comment that precedes the Explore refinement. If the Explore is defined in aseparate Explore file, and not in a model file, you can add the refinement to the Explore's file instead of the model file. Either location will work.

If you need to modify the aggregate table LookML, you can do so with the parameters that are described in theDefining an aggregate table in LookML section on this page. You can rename the aggregate table without changing its applicability to the original Explore query. However, any other changes to the aggregate table may affect the ability for Looker to use the aggregate table for the Explore query. See theDesigning aggregate tables section of theAggregate awareness documentation page for tips on optimizing your aggregate tables to ensure that they are used for aggregate awareness.

Getting aggregate table LookML from a dashboard

Another option for Looker developers is to get the aggregate table LookML for all tiles on a dashboard and then copy the LookML into the LookML project.

Creating aggregate tables can drastically improve the performance of a dashboard, especially for tiles that query huge datasets.

If you havedevelop permission, you can get the LookML to create aggregate tables for a dashboard by opening the dashboard, selectingGet LookML from the dashboard's three-dot menu, and choosing theAggregate Tables tab:

For each tile that isn't already optimized with aggregate awareness, Looker provides the LookML for an Explorerefinement that will add the aggregate table to the Explore. If the dashboard includes multiple tiles from the same Explore, Looker puts all the aggregate tables in a single Explore refinement. To reduce the number of generated aggregate tables, Looker determines whether a generated aggregate table could be used for more than one tile and, if so, drops any redundant aggregate tables that can be used for fewer tiles.

Copy and paste each Explore refinement into the associated model file, which is indicated in the comment preceding the Explore refinement. If the Explore is defined in aseparate Explore file, and not in a model file, you can add the refinement to the Explore file instead of the model file. Either location will work.

If a dashboard filter is applied to a tile, Looker will add the filter's dimension to the tile's aggregate table so that the aggregate table can be used for the tile. This is because aggregate tables can be used for a query only if the query's filters reference fields that are available as dimensions in the aggregate table. See theAggregate awareness documentation page for information.

If you need to modify the aggregate table LookML, you can do so with the parameters that are described in theDefining an aggregate table in LookML section on this page. You can rename the aggregate table without changing its applicability to the original dashboard tile, but any other changes to the aggregate table may affect the ability for Looker to use the aggregate table for the dashboard. See theDesigning aggregate tables section of theAggregate awareness documentation page for tips on optimizing your aggregate tables to ensure that they are used for aggregate awareness.

Example

The following example creates amonthly_orders aggregate table for theevent Explore. The aggregate table creates a monthly count of orders. Looker will use the aggregate table for order count queries that can leverage the monthly granularity, such as queries for yearly, quarterly, and monthly order counts.

The aggregate table is set up with persistence using thedatagrouporders_datagroup. Also, the aggregate table is defined withpublish_as_db_view: yes, which means that Looker will create a stable database view on the database for the aggregate table.

The aggregate table definition looks like the following:

explore: event {  aggregate_table: monthly_orders {    materialization: {      datagroup_trigger: orders_datagroup      publish_as_db_view: yes    }    query: {      dimensions: [orders.created_month]      measures: [orders.count]      filters: [orders.created_date: "1 year", orders.status: "fulfilled"]      timezone: America/Los_Angeles    }  }}

Things to consider

See theDesigning aggregate tables section of theAggregate awareness documentation page for tips on strategically creating your aggregate tables:

• Timeframe factors
• Time zone factors
• Filter factors
• Field factors
• Measure type factors

Dialect support for aggregate awareness

The ability to use aggregate awareness depends on the database dialect that your Looker connection is using. In the latest release of Looker, the following dialects support aggregate awareness: