spectacles icon indicating copy to clipboard operation
spectacles copied to clipboard

Looker doesn't `group by` in certain cases when primary key is present in query

Open DylanBaker opened this issue 3 years ago • 0 comments

From the Looker docs:

Looker also avoids unnecessary GROUP BY clauses in some cases. If one of the dimensions in your query has been defined as the primary key (by using the primary_key parameter) of the Explore you're using, the GROUP BY clause will be dropped.

This behaviour is normally totally fine (and actually speeds up our queries on Snowflake) but if you have a dimension that points to a BigQuery array column, this will actually miss an error which tells you that you can't group by array fields, which would come up in normal use of Looker if the dimension is not hidden. This means that a Spectacles run might show an explore as passing, even though one of these array dimensions could error in normal use.

I believe there are two actions here:

  1. We should confirm with Looker what the exact criteria are for the group by field being excluded from a query as the docs as a bit light here.
  2. We should probably change Spectacles to run primary key dimensions alone, so that it doesn't have this impact on the queries. It's not clear yet if this is all primary keys in the query or just the primary key from the top-level view of the explore. This should be clarified with Looker in (1).

cc: @ian-thompson-king

DylanBaker avatar Dec 02 '22 10:12 DylanBaker