How Data Engineers Can Use SQL to Estimate BigQuery Storage Costs

Data engineers can leverage SQL statements to fetch database metadata in order to calculate costs incurred with PaaS products like BigQuery.

Below, you’ll learn:

• How to access table metadata in BigQuery
• How to use standard SQL to convert bytes to GB and TB
• How to calculate per gigabyte rates

Note: The price per gigabyte rates used in any of the below calculations are current as of the publication date, but may change in the future.

Build Your Pipeline To A Data Engineering Career

You’ve reached the limit of the public preview. The full version of this post includes the implementation details: The code, the edge cases, and the "why" behind the architecture.

When you join PipelineToDE, you get:

• The DA → DE Pathway Course: A structured roadmap to bridge the gap between analysis and engineering.
• Weekly Senior Deep Dives: Fresh, tactical insights on Python, Cloud (GCP/AWS), and modern orchestration delivered every week.
• Production-Ready Blueprints: Access to 80+ protected stories and code repos from my time in the trenches as a Senior DE
• The DE Job Board (Coming Soon): Exclusive access to a curated board of high-agency Data Engineering roles.

Accessing Metadata

BigQuery provides methods to access metadata at the dataset and table levels so users can understand which data are consuming the most resources. To access datasets in BigQuery, users must access INFORMATION_SCHEMA.

INFORMATION_SCHEMA provides views for tables, columns and partitions. Below, I’ll compose a meta query to determine the table name, table type, eligibility for insertion and creation date of my Reddit News table.

SELECT
* EXCEPT(creation_time),
EXTRACT(DATE
FROM creation_time) AS date
FROM (
SELECT
* EXCEPT(is_typed,
ddl,
table_catalog,
table_schema)
FROM
reddit_news.INFORMATION_SCHEMA.TABLES)

It yields this output:

We can also use the INFORMATION_SCHEMA.COLUMNS method to get even more granular information relating to individual columns; this can be a quick way to check data types and null status instead of looking at the schema.

SELECT * FROM (
SELECT* EXCEPT(
table_catalog,
table_schema,
ordinal_position,
is_generated,
generation_expression,
is_stored,
is_hidden,
is_updatable,
is_system_defined,
is_partitioning_column,
clustering_ordinal_position)
FROM
reddit_news.INFORMATION_SCHEMA.COLUMNS)

Determining Billable Bytes

Google prices its services based on usage. For our purposes, we’ll concentrate on long-term (tables not modified in 90+days) and active (tables modified within the past 90 days) storage. The first 10 GB in each tier are free.

Here’s a meta query for determining whether your data is currently in active or long-term storage, along with total billable bytes.

SELECT storage_tier, SUM(total_billable_bytes) AS total_billable_bytes
FROM reddit_news.INFORMATION_SCHEMA.PARTITIONS
GROUP BY 1

While Google provides documentation on how to obtain the total number of bytes per table there is a small problem: Google prices by GB but only offers a field for bytes.

Converting Bytes to GB, TB

Unfortunately, converting bytes to GB isn’t as easy as, say a metric conversion. So, let’s walk through the process.

To convert from bytes to gigabytes, we’ll need to divide by 1024*1024*1024.

SELECT table_name, ROUND(SUM(total_billable_bytes) / (1024*1024*1024),2) AS size_gb
FROM reddit_news.INFORMATION_SCHEMA.PARTITIONS
GROUP BY 1

To convert from bytes to terabytes, we’ll simply replicate and divide by 1000.

SELECT table_name, ROUND(SUM(total_billable_bytes) / (1024*1024*1024),2) AS size_gb, ROUND(SUM(total_billable_bytes) / (1024*1024*1024),2) / 1000 AS size_tb 
FROM reddit_news.INFORMATION_SCHEMA.PARTITIONS
GROUP BY 1

Determining Costs and Next Steps

Once we complete the conversion, we now have a dataset size that is in gigabytes, so it is easy to estimate overall cost by multiplying everything by the per gigabyte rate for active storage data.

WITH table_info AS (
SELECT *, size_gb * 0.020 AS total_cost
FROM (
SELECT table_name, ROUND(SUM(total_billable_bytes) / (1024*1024*1024),2) AS size_gb, ROUND(SUM(total_billable_bytes) / (1024*1024*1024),2) / 1000 AS size_tb
FROM reddit_news.INFORMATION_SCHEMA.PARTITIONS
GROUP BY 1
))
SELECT * FROM table_info
ORDER BY total_cost DESC

Let’s build on the prior query to determine overall cost.

WITH
table_info AS (
SELECT *,
size_gb * 0.020 AS total_cost
FROM (
SELECT
table_name,
ROUND(SUM(total_billable_bytes) / (1024*1024*1024),2) AS size_gb,
ROUND(SUM(total_billable_bytes) / (1024*1024*1024),2) / 1000 AS size_tb
FROM
reddit_news.INFORMATION_SCHEMA.PARTITIONS
GROUP BY 1 ))# This is the added querySELECT *,
total_gig * 0.03 AS total_price
FROM (
SELECT *,
SUM(size_GB) AS total_gig,
SUM(size_TB) AS total_TB
FROM (
SELECT *
FROM
table_info)
GROUP BY 1, 2, 3, 4)

Meta queries can help data engineering teams take inventory of their data warehouses while also helping quantify resource needs for stakeholders. Since resulting tables can be exported, it is easy to export the results to a CSV or Google Sheet for leadership to review.

Even if a team is not trying to convince management to purchase additional storage, meta queries can help provide insight into an organization’s overall storage usage in BigQuery.

Keep Ingesting

You just finished a deep dive into this topic, which is one piece of the larger engineering puzzle. If you're ready to stop browsing and start following a structured roadmap, head over to the DA → DE Pathway Course.

Your Recommended Module:

• Module 7: Cost & Performance Optimization — Reduce cloud spend and latency through efficient data modeling.

Remember: As a member, you have full access to the source code for every project in the course. No extra fees, just execution.