Looker estimates for Google BigQuery not showing correct size while doing pivot

  • 19 April 2017
  • 3 replies

Userlevel 2

Hi, I am using

Database: Google BigQuery

SQL: Standard SQL

Looker Version: 4.10.11

Looker estimates for query data processing almost doubles when we move object to pivot fields. But same pivot query shows same estimate in google bigquery UI. So does Looker runs same amount of data displays in Google BQ UI or it will run what’s display in Looker estimates?

For regular look without pivot shows lets say 353 GB, with Pivot it shows 706 GB. If I put same looker pivot query SQL in Google BigQuery UI, it displays 353 GB only.

3 replies

Userlevel 6

My guess is that you have row totals in your pivot. This will cause looker to run the query twice (once more to generate the totals).

Hi @llyodtabb,

Can’t we make looker calculate these row totals for pivoted looks instead of pushing the load to database? Some thing like table calculations?

Looker running the query twice (once more to generate the totals) is simply bloating the bytes processed and hence putting extra load on db.

Userlevel 6

We use a SQL query to perform total calculation because it works with all types of measures. average, count_distinct, min, max and total all work correctly with summary calculations when computed in SQL. They won’t work properly if we were to use tabular sums.