01 Dec 19 · DataFreak · #Bigquery ·   Bookmark   ×

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)

DDLs - Creating tables (except CTAS), views, datasets, stored proceduresFREE
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 tables2 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


posted on 01 Dec 19

Enjoy 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