5 tips for building a better dashboard with Google Data Studio and BigQuery

… or 5 tricks to ease the pain and do things better

My first BigQuery bill :)

If you are in data analytics this article is for you.

is f̶u̶l̶l̶ ̶o̶f̶ ̶b̶u̶g̶s̶ a free tool to build custom reports and visualise your data (which Google claims is beautiful). My data is not so beautiful. I would say building dashboards with it is a struggle. And this post is about how to build you reports quicker with no sudden surprises like the one you can see at the top.

Yup :) This was my first BigQuery invoice. I’ve managed to claim it back but it still soars…

So if you want to do things more efficiently with no sudden surprises just have a look at these tips below.

1. Use better partitioning

Do it first. Define partitions just before you load your data into BigQuery. It saves time and money later.

See that magic number of £3002.72 ? Yeah, that happens when you do a . Don’t do it. Use partitioning.

By default BigQuery will partition your table by . Read more in Google docs about it. However, often your contain data with different , for example, from yesterday.

So why not just partition by when loading data into BigQuery? This approach simplifies things when you run custom queries from Google Data Studio with date params.

The trick is you can set column in load jobs:

Create a function and that will work beautifully like in this demo:

2. Stop using filters in Google Data Studio

Filter custom datasets using instead.

That’s my favourite one. Built-in Data Studio filters might do a trick for two or three things to filter out but for something bigger you can use spreadsheets.

Even though this type of tables (based on Google sheets) is slow this trick saves time. Just copy data to a new table if you need a better performance and then do a JOIN to filter your dataset.

INNER JOIN `your-project.reference-dataset.filter` f ON f.id = c.productId

3. Load data in source JSON format

That’s a complete hack. I love it and I brought it from Snowflake .

Parsing timestamps, etc. with function. It’s all simple now. You just load data you have into one field in source format (which is JSON).

of your schema.yaml file to load data into BigQuery:

yaml file you use to load data

resulting table with SOURCE formatted data

Then you just create a view. Simple, like so:

4. Pivot tables in Google Data Studio

is not supported in BigQuery SQL and it’s a big miss. Of course you can do something like in this stackoverflow post.

Just use pivot tables in Google Data Studio. It’s really cool and makes things with dashboard prototyping a lot faster.

5. Be careful with cache

In BigQuery if you make the same requests multiple times the result is cached and is charged only for the first time.

This won’t necessary work for your Google Data Studio report :)

The thing is that when you connect data to your report you should really consider the type of your data source. BigQuery, Firebase, Google Analytics, AdWords, for example, are dynamic ones which means that your report will update the data in your reports automatically every time you refresh or load the page.

Data Studio makes different requests to build every single chart in your report and does not use cached results.

Use data extract feature There is tiny little bug here but don’t use filters anymore, right? :)

New Data Studio BI engine might be a worthwhile looking at too.

Thanks for reading!

  1. https://support.google.com/datastudio/threads?hl=en&thread_filter=(pinned:true)

https://support.google.com/datastudio/answer/6370296?hl=en&ref_topic=7332343

https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-big-query

https://support.google.com/datastudio/answer/6268208?hl=en

https://cloud.google.com/bi-engine/docs/

https://support.google.com/datastudio/answer/7020039

https://support.google.com/datastudio/thread/25196948

BigData Engineer | Full stack dev | I write about ML/AI in Digital marketing. | linktr.ee/mshakhomirov | @MShakhomirov

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store