Understanding total_logical_bytes vs total_billable_bytes in BigQuery

If you're using Google BigQuery for analytics or reporting, you've probably noticed terms like total_logical_bytes and total_billable_bytes in your billing dashboard or job metadata. While they sound similar, they impact your costs in very different ways.

Understanding these two metrics is essential for cost optimization in BigQuery.

 

total_logical_bytes  (Think: “Storage Cost”)

It represents the uncompressed size of your data stored in BigQuery. This is the size before Google’s internal compression kicks in.

 

Google use it to calculate storage charges. Google charges you based on how much logical bytes you store per month.

 

For example, if your table is 100 GB uncompressed, but compressed internally to 30 GB, you're billed based on the 100 GB logical size.

 

total_billable_bytes — Think: “Query Cost”

It specifies the amount of data scanned/processed by your SQL query.

 

It is used for calculating query costs under the on-demand pricing model. You pay per the data processed, not results returned.

 

If you run a query that only selects 2 columns out of 20 in a 1 TB table, and BigQuery only scans those 2 columns, you’ll be billed for a much smaller number of bytes — maybe just a few GBs!

 

When you submit a job in BigQuery to run a query, you can retrieve the total number of billable bytes that the query will process. If you want to check this without actually running the query, BigQuery provides a dry run feature. This allows you to estimate the query cost ahead of time by analyzing how much data would be scanned, without incurring any cost or triggering the query execution.

QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(sqlQuery.getQuery())
    .setDryRun(true) // Enable dry run mode
    .build();

// Submit the job with dry run enabled
Job job = bigQuery.create(JobInfo.of(queryConfig));

// Extract statistics from the dry run job
QueryStatistics queryStats = job.getStatistics();

// Get the total bytes BigQuery would process
long totalBytesProcessed = queryStats.getTotalBytesProcessed();

 

How can I see these values for a table?

Execute following command to see the total logical and billable bytes for a given table.

 

SELECT 
    *
FROM my_project.my_dataset.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'my_table_name';

Following image shows the data for a non-partitioned table.

 

For a partitioned table, you can see the data at partition level.

 

Previous                                                    Next                                                    Home