How to Optimize Your Looker Studio Connection with BigQuery

There are two different models of paying for BigQuery analysis: flat-rate, which is being replaced by editions, and on-demand. In this article, I will focus on the on-demand cost model, but the practices described apply to both models.

The easiest way to see the impact of your visualization tools on BigQuery processing is to review your personal history or project history in BigQuery. Queries from outside services, such as Looker Studio, are usually easy to spot as they use distinct syntax and job ids. Just like with queries run in the UI, you can see their speed (duration) and associated cost (bytes billed).

 

 

 

 

Sharded Tables

When working with sharded tables, take full advantage of BigQuery's table suffix. Table suffixes will most often have a useful meaning and may even be replicated in the table as a column. To optimize your connection with Data Studio, expose the suffix and give it an alias.

Bad Example

A custom query that feeds Looker Studio by using all of the date sharded tables and then filtering by an attribute called date.

SELECT
  event_date,
  event_name,
  user_pseudo_id,
  (SELECT value.int_value FROM  unnest(event_params) WHERE key = "ga_session_id") as sessionId
FROM analytics_1234567.events_*

Fix it by moving _table_suffix to the SELECT statement and using it as a date attribute.

SELECT
	_table_suffix as event_date,
	event_name,
	user_pseudo_id,
	(SELECT value.int_value FROM unnest(event_params) WHERE key = "ga_session_id") as sessionId
FROM analytics_1234567.events_*

 

 

 

 

We are not only selecting considerably fewer rows (31 out of 800 tables), but we are also getting rid of an expensive string column and replacing it with a "free" metadata column (extracted from the table name). This makes the above query over 22.5 times cheaper and faster as well. Now your reports will cost less and load much quicker.

*Looker Studio is smart enough to connect to the raw events tables using the _table_suffix in the WHERE clause, but be careful as this may not be a default behavior for other sharded tables and other business intelligence tools may not have those capabilities.

 

 

Reporting tables

Build reporting tables that get updated on an interval. For most visualizations, it makes sense to connect to pre-aggregated tables. Instead of having similar widgets query against a raw table, you will often find common denominators and should be able to build a reporting table. Reporting tables can either be updated on a certain time schedule or by an event (such as an update to the base table). When building larger reporting tables make sure to also take advantage of clustering and partitioning.

Clustering

Take advantage of clustering when dealing with high cardinality attributes that you expect to filter by in your reports. This will not only help with reducing the amount of data scanned when filters are used but will also speed up the reports. You can use multiple fields when configuring clustering.

Partitioning

Partitioning splits the table into smaller segments and can only be used on a single field of the table. Partitioning in BigQuery can be done by an integer, time or date and can be combined with clustering to optimize your queries further.

Example: