BigQuery is a particularly cost-effective cloud data warehouse but you could still be spending more than you need to on it. Here are 3 quick tips on how to make sure you're not spending unnecessarily.
Tip 1: Use partitioned tables in BQ to save on storage costs
BigQuery has two significant costs, reading data is charged at $5 per TB and storage is split between active storage (Read in the last 90 days) at $0.020 per GB and Passive storage at $0.010 per GB.
By not using partitioned tables, all data remains in the Active Storage tier and will be charged at the active storage rate. By using partitioned tables, data that is not queried can be stored at half the price.
Google BigQuery generously provides an always-free tier. This allows up to 10GB of free storage per month and 1TB of queried data.
Tip 2: Only include the columns that you require in your BigQuery query
BigQuery has its origins from a columnar database system. While it has far outgrown this simplistic model, the ability to save costs by assuming this architecture remains.
Since data is stored in columns and not in rows, a large saving on the amount of data returned can be realised by not including all of the fields in the schema in your queries.
i.e. Considering all the fields here will query 791GB of data:
While if we only want the number of views to each wiki, this query will query a quarter of the data
Tip 3: Store data in Google Cloud Storage
Using External tables allows querying of data directly from Google Cloud Storage. While this does have a performance impact, if data is already stored in Google Cloud Storage, then it might not make sense to duplicate the storage cost.
This is an example of how to create an external table: You can use glob syntax to import all files that match a certain pattern, which is useful.
Note that there is limited data checking using this approach, but it can be vastly cheaper than duplicating the data for large data sets.
If your customer demand has changed, your cloud costs should too.
Many companies are still overspending on their Google Cloud Platform. As a leading Google Cloud Partner, we offer free evaluations to help identify potential savings so companies like yours can start growing again–contact us today.
