Google BigQuery Best Practices – How to Optimize the Cost? 

Querying BigQuery Involves cost.  We are going to discuss the best practices to optimize the cost.

Google BigQuery Best Practices

  • Best Practice 1 –  Select Command:  Don't use Select * command to query data. It involves huge cost. Instead, Query Specific fields to optimize the cost
     
  • Best Practice 2 – BigQuery Scheme: Check the BigQuery Scheme before creating and submitting the query. Because if the field that is requested is wrong, BigQuery will query and fetch no results, but that query involves cost
     
  • Best Practice 3 – Query Time Window: Query the data for shorter time window (Example 1-day data query) then expand the time window to optimize the cost
     
  • Best Practice 4 –  BigQuery Result Row Limit: Query Result Max Row Limit 16000. If the query result is more than 16000, no use  of querying. Then Switch to DataStudio and query the data
     
  • Best Practice 5 Big query is no sql datbase. So,understand the query attritubes and frame the query accordingly
     
  • Best Practice 6 – Query the data once and build the data repository (Data lake) in Local environment. This will reduce query duplication and cost
     
  • Best Practice 7 – While pulling the hourly data, ensure the hours is in GMT or in Local time (By default, it is GMT). Don't  forget to convert from GMT to Local time
     
  • Best Practice 8 – Dataset Optimization: Accommodate multiple platform (Native, Hybrid, PWA, WEB) data of a domain in one dataset. So that it is easy to query the multi-platform in one shot


Image Courtesy: wpengine.netdna-ssl.com