Understanding Google BigQuery Pricing - Best practices to query the cloud without burning your wallet
Google big query is the cloud native data warehouse fully managed by Google. Bigquery is fast, efficient, and is undoubtedly one of the major cloud data-warehouse platform. As a business, it is essential to understand how the pricing works in BigQuery and how to use it to yield optimal Return on analytics from your cloud data warehouse.
Google BigQuery’s cost is based on usage, so the cost incurred is based on what you use. There are primarily 2 items that would cost you on BigQuery. They are:
- Storage
- Quering / Retrieval
BigQuery Complete Pricing matrix:
(Prices are for US Multi region as of Dec-2019)
| Operation | Cost |
| DDLs - Creating tables (except CTAS), views, datasets, stored procedures | FREE |
| DDLs - Dropping tables, views, datasets, stored procedures | FREE |
| Loading data | FREE - When you load data into BigQuery from Cloud Storage, you are not charged for the load operation, but you do incur charges for storing the data in Cloud Storage |
| Copying data | FREE - You are not charged for copying a table, but you do incur charges for storing the new table and the table you copied |
| Exporting data from BigQuery to GCS | FREE - but you do incur charges for storing the data in Cloud Storage |
| Storage space used by tables | 2 cents per GB per month |
| Storage space used by tables (at partition level) that weren't accessed in the last 90 days | 1 cents per GB per month |
| Querying / Retrieval | $5.00 per TB of data scanned by the query (Not the data returned by the query) |
| Storage API Pricing | $1.10 per TB read |
| Streaming Inserts | $0.010 per 200 MB |
| DML (Insert, update, merge) | Charged based on the number of bytes processed by the query |
Tips to Controlling your BigQuery Costs:
First, lets take advantage of the FREE Services 🤑:
- Use the Google Bigquery WebUI for data exploration - You can see the list of tables, Row count, sample data, table size, All for FREE. Whereas if you want to check the sample data from your desktop client it will cost you $$
- Loading data in BigQuery is FREE. Use regular ETL and go for streaming inserts only for real-time scenarios
- Reading full table ? Avoid the APIs, use the export table option instead, Because its FREE
- Taking backups, Avoid INSERT...SELECTS, Just do CP command (Again Its FREE)
Optimizing BigQuery for Cost 🤖:
- Caching in bigquery works at user level and not across users. So, its a good practice to use a service account for your reporting dashboards instead of individual user ids
- Create partitioned tables and use the partition key in the filter clause of your SQL Queries wherever possible
- Create cluster keys and use them in the SQL Filters wherever appropriate
- Always use the query cost estimator to find out how much your query will cost you before you execute it
What techniques do you use to control your Cloud Bill ? Tell us in the comments
DataFreak
posted on 01 Dec 19Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment