80% Cost Reduction in Google Cloud BigQuery | Tips and Tricks | Big Query Demystified
1. Install BQ mate — plugin for chrome in order to see cost per query in dollars before running. Key challenge in your organization team’s will be to bare cost in mine while working.
2. Log in to your BigQuery console to get started. However, you might want to consider using superQuery IDE for more advanced features over BigQuery, Increased productivity and cost reduction. We use it in Investing.com
3. Run sample query , ALWAYS use a date in the where clause. Assuming the table is partitioned, this will save you costs by only scanning the relevant data as oppose to the entire table.
select * from
where date = '2019-11-01'
4. AS rule of thumb, NEVER DO the below:
select * from t
or
select * from t limit 100
as this will incur unintended full scans of table data and thus create huge billing costs. Instead, use:
Select ColName1, ColName2 from T
and Preview table instead of LIMIT
5. As a rule of thumb , ALWAYS :
If you are running a query which costs more than 1$ — you are most probably doing something wrong.
6. Learn Bigquery in great details. I have prepared a presentation with over 100 slides called it BigQuery Demystified. Learning BigQuery features will help optimize queries, and thus save costs. For example: did u know import/export from bigQuery is completely FREE! Another example is how to query nested data, and windows function.
7. Analize cost per query per user in big query periodically.
8. For the full article (slides and Video) of 80% cost reduction on GCP
If you like my tips, or have any opinion whatsoever, please contact me via linkedin.
To learn more about Superquery: