Knowledge Drop

Can I use existing tables or PDTs with the aggregate awareness feature?

  • 7 July 2021
  • 1 reply

Userlevel 3

Last tested: Jun 3, 2020

No, the Aggregate Awareness feature new to 7.8 must be used with Looker-materialized tables defined in LookML.

Below is the reasoning as to why aggregate awareness currently must use Looker-materialized aggregate tables:

A key aspect of AA is that it depends on a Looker-defined query instead of pure SQL to define the aggregate tables. This is really important because it gives us a ton of information that would otherwise have to be explicitly modeled, for example but not limited to:

  • which columns from the agg table map to which fields in the explore
  • what explore the agg table is applicable to
  • what explore-level filters need to be considered (like access_filters)
  • how long the table is considered good for before it becomes out of date

Also, by owning the PDTs we can take some liberties with the table creation that yield better features or user experience, for example:

  • decomposing averages into one column for the numerator and one for the denominator (and in 7.10, decomposing composite measures into constituent measure columns)
  • auto-converting count distinct into HLL sketches (given consent with 2% error)

To support customer-ETL'd agg tables would require a tremendous amount of new LookML to make all this information available, and it would be very brittle in that the user would be on the hook to make sure that the model and agg table never diverged. The possibility of this happening is very high and the consequences are high as well (bad data, data in the hands of the wrong people if access filters fail, etc).


This content is subject to limited support.                



1 reply

Big gap IMO.  Lot of discussion on “the boards” about use of dbt/other as more robust approach to managing pdt’s, then feature like this requires them.