Bigquery - Analysis costs growing very fast - Data Studio Report

Question

I am using the BigQuery to create reports into Data Studio Report from google cloud. The "BigProblem" :) is:

After that, the costs of Analysis start to grow very fast, as you can see in this image:

I am using views to create these reports, Views Like that:

SELECT
      footwear.style as style,
      SUM(footwear.MSRP_USD) as revenue,
      SUM(CAST(order_line.quantity AS NUMERIC)) as units_sold,
      SUM((SELECT SUM(order_line_refund.quantity) FROM myproject.shopify.ORDER_LINE_REFUND AS order_line_refund WHERE order_line_refund.order_line_id = order_line.id)) AS return_quantity,
FROM myproject.shopify.ORDER as orders
INNER JOIN myproject.shopify.ORDER_LINE as order_line on orders.id = order_line.order_id
INNER JOIN myproject.catalogDB.footwear_catalog as footwear on order_line.sku = footwear.sku
LEFT JOIN myproject.shopify.ORDER_LINE_REFUND as order_line_refund on order_line.id = order_line_refund.order_line_id
LEFT JOIN myproject.shopify.DISCOUNT_APPLICATION as discount_app on orders.id = discount_app.order_id
WHERE footwear.style IN ('Serena','Caro','Sloane') AND (discount_app.value < 100 OR discount_app.value IS NULL) AND orders.cancelled_at IS NULL
GROUP BY style

This simple query cost: Query complete (5.3 sec elapsed, 6.9 MB processed)

And I have others views much bigger than that.

I don't know if it is possible to turn off automatic data refresh in Data Studio. and I am quite sure to use views is not a Good Idea.

I can also consider using an extracted data source? So I can schedule when the data is refreshed, it is a better idea?

Kunal Deo Kunal Deo · Accepted Answer · 2020-02-10T17:53:16

There are multiple ways to save costs:

Materialise the report queries using a batch job into a separate table and run reports off that.
Ensure that the tables are partitioned and then use the partition key in all your queries to filter the unnecessary data out.
You can use BI engine as the dynamic caching engine for your queries. This allow you to avoid reading from BigQuery.

PS: Check Controlling BigQuery Costs as well.

Bigquery - Analysis costs growing very fast - Data Studio Report

1 Answers