Normally, with BigQuery as a data source of Data Studio, it’s should be:

The problems:

• Users (of Data Studio Dashboard) might end up generating a lot of queries on your behalf — and that means you can end up with a huge BigQuery bill.
• It’s taken so long to refresh data when you change something in development mode.
• The easiest way to avoid pushing new queries from Data Studio into BigQuery is to materialize the results of your queries into Data Studio. But right now that feature is still in development, and it will be subject to certain limitations — so we also shared some other options to create an inexpensive layer between Data Studio and BigQuery.

Google Sheets as a Caching layer

With the Sheets data connector for BigQuery, you can cache the calculated data for Data Studio. Then, use Data Studio connects to that sheet to reduce the time load and avoid pushing queries to BigQuery, and Spreadsheet is free.

Sheets data connector for BigQuery

1. Google Sheets

   

2. Choose Billing Project

   

3. Write a Query

   

4. Refresh Manually

   

5. Schedule automatic updates: You’ll be able to use tools like Apps Script and the macro recorder to schedule automatic updates to the connected BigQuery data.

Good luck!

References

• Analyze hefty data sets with BigQuery and Google Sheets
• Think macro: record actions in Google Sheets to skip repetitive work
• Connect Data Studio to BigQuery tables.