Data Studio
2.3K members online now
2.3K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

BigQuery - Best/Most Cost Effective Practices

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi all,

 

Just wondering what everyones approaches are to make sure Data Studio is as cost effective as possible when using Big Query data.

 

For instance, is a view massively cost-inefficient because its re-queried, or does Data Studio cache that? Is anyone running some daily updates to create tables? That sort of thing.

 

Curious more than anything!

 

Tom

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by Dimitris N (Google Employee)
November 2016

BigQuery - Best/Most Cost Effective Practices

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

We ended up creating some aggregated tables which are updated daily. Costs are next to nothing as each day is only worth a few thousand lines (not totally aggregated as there are a few count distincts). Works well.

View solution in original post

Re: BigQuery - Best/Most Cost Effective Practices

Google Employee
# 2
Google Employee
Data Studio caches report data for about 12 hours or until a user request for a cache refresh.

That can help with BQ costs.

BigQuery - Best/Most Cost Effective Practices

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

We plan to make aggregated tables for each day.

 

This way we would only have to process the raw data for one day. While datastudio access only the small aggregated Dataset.

 

If you display only the last day, it would not help much, but if you display the last month or longer it should be worth it.

BigQuery - Best/Most Cost Effective Practices

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

I think the answer to this question depends on how often new data is added to your BQ tables, how many filters your report has, and whether your BQ tables are using a "federated" external source like Google Sheets or a native table like a csv upload. 

If you use federated tables, either Data Studio, Bigquery or Sheets has an API quota that will be reached within a couple refreshes and the widgets will stop working almost immediately...I have not found which platform is the culprit yet. 
Hopefully this will be fixed soon because the queries are basically prohibitively inefficient with federated tables right now. 

If you are using native Bigquery tables that were made from non-federated sources, you can basically make an unlimited amount of queries from Data Studio.  You can also quickly run up a bill if your data is regularly updated and/or if your report allows users to customize the queries with filters. Since the entire table is re-queried at least once every 12 hrs and every time a user changes the underlying data with a filter option, it can be difficult to predict or control the volume of data being queried. The number of pages you have, and whether your filters are page level or report level, may also affect query volume but this is still unclear to me. 

Hopefully the Data Studio team can find solutions for making the Bigquery integration more efficient and more predictable, especially when using federated tables. Until then, you may want to enable billing limits for your Bigquery projects to be safe. 


Marked as Best Answer.
Solution
Accepted by Dimitris N (Google Employee)
November 2016

BigQuery - Best/Most Cost Effective Practices

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

We ended up creating some aggregated tables which are updated daily. Costs are next to nothing as each day is only worth a few thousand lines (not totally aggregated as there are a few count distincts). Works well.

BigQuery - Best/Most Cost Effective Practices

Google Employee
# 6
Google Employee

That's great Picasso.

 

Can you share more about how you are updating your aggregated tables on a daily basis?

BigQuery - Best/Most Cost Effective Practices

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Very simply actually - we have a small micro server on GCP that runs a 'crontab' / cronjob - that simply runs queries everyday at 5am which append themselves to the aggregated tables.

 

I'm going to write an article on it this week as it's so easy to do its mad not to.

BigQuery - Best/Most Cost Effective Practices

Follower ✭ ☆ ☆
# 8
Follower ✭ ☆ ☆

Hey, did you ever write this article? Would love to see