This article started with a task at work: assess a possible migration to Apache Iceberg. As I compared it with native BigQuery storage, a few results caught my attention. The storage comparison looked promising, but queries processing the same number of bytes used more slot time. A MERGE finished faster while consuming more compute.
These trade-offs were worth a closer look. They led me to investigate how storage billing, query execution, and capacity pricing interact, and which changes could actually reduce our BigQuery costs.
In an Iceberg migration assessment, one of our tables had roughly 153 GB of logical data in native BigQuery storage. Its Iceberg copy occupied 4.51 GB in Cloud Storage. Taken on its own, that comparison made a storage migration look attractive.
The query results complicated the decision. Across ten baseline queries, bytes processed were identical between the two formats, while Iceberg consumed between 1.9 and 4.8 times as much slot time. A subsequent MERGE finished faster on Iceberg but used more compute.
Those measurements describe one workload. They also illustrate the problem with optimizing BigQuery from a single number: smaller storage, fewer scanned bytes, lower slot consumption, and shorter execution time answer different questions.
My starting point is to identify which of those quantities affects the bill, then compare changes against that baseline. Before changing the storage architecture, I would investigate the storage billing model, recurring expensive jobs, retention settings, and reservation configuration.
1. Choose the metric that matches your pricing model
BigQuery supports on-demand compute billing, measured in bytes processed, and capacity-based billing, where you allocate processing capacity through reservations. Both models can coexist across workloads. This means a cost review needs to identify how each workload is assigned and billed before ranking optimization opportunities. Workload management documentation
For an on-demand workload, billed bytes are the starting point. For capacity, I would examine resource consumption alongside allocated capacity and the resulting charges. Execution time remains relevant in both cases because an optimization still has to meet the workload’s delivery requirements.
I would keep three separate views:
| View | Question it should answer |
|---|---|
| Individual job | How much work did this execution perform, and how long did it take? |
| Recurring workload | How much does this pipeline or query pattern consume over the review period? |
| Billing | Which charges actually increased or decreased? |
This separation matters when interpreting an improvement. A lower resource requirement can create capacity for other jobs even when the immediate invoice stays unchanged. Conversely, a faster query can require more resources, as the Iceberg MERGE later in this article demonstrates.
Define the intended outcome before making the change: lower spend, more throughput within existing capacity, or a shorter processing window. Record the other two as constraints. Otherwise, it is easy to declare success using whichever metric improved.
2. Find expensive jobs, then investigate repeated work
INFORMATION_SCHEMA.JOBS_BY_PROJECT exposes project job metadata. The following diagnostic query ranks completed query jobs by consumed slot-hours. Replace the project and region, and execute it in the matching location. It requires permissions to create jobs and list all project jobs. The SQL is an illustrative diagnostic, not one of the migration benchmark queries.
SELECT
job_id,
user_email,
reservation_id,
cache_hit,
error_result.reason AS error_reason,
total_bytes_billed / POW(1024, 4) AS billed_tib,
total_slot_ms / 3600000.0 AS consumed_slot_hours,
TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) / 1000.0
AS execution_seconds
FROM `your-project.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND job_type = 'QUERY'
AND state = 'DONE'
AND (statement_type IS NULL OR statement_type != 'SCRIPT')
ORDER BY consumed_slot_hours DESC
LIMIT 50;
For on-demand analysis, sort by billed_tib. Some statistics can be unavailable for queries involving row-level security. The script filter avoids counting parent summaries alongside child jobs.
Treat this ranking as an investigation queue. Review both large individual executions and accumulated consumption from repeated runs. For each candidate, identify its owner, schedule, required freshness, and output consumers. That context determines whether the useful intervention is a query change, fewer executions, or removing unnecessary work.
Keep failed executions visible during the investigation. A report limited to successful output gives an incomplete picture of the work submitted to the platform. BigQuery’s troubleshooting guidance also recommends bounded time filters and retaining telemetry separately when longer-term analysis is needed. Information schema troubleshooting
3. Compare logical and physical storage billing before migrating
BigQuery offers two storage billing models. Logical billing measures uncompressed data; physical billing measures compressed storage. This is a dataset-level metering choice. Changing it does not convert the table format or change query performance. The change takes 24 hours to apply, and another change requires a 14-day wait. Storage billing models
That distinction changes how to read the opening example. The 153 GB and 4.51 GB figures compared native logical bytes with Iceberg files in Cloud Storage. They did not compare equivalent physical footprints or complete monthly costs. The assessment also recorded that native physical storage was roughly 20% smaller than the Iceberg copy.
The first alternative to evaluate was therefore native BigQuery with physical billing. It offered a way to test the storage-cost hypothesis while keeping the existing table format.
Physical billing still needs a complete forecast. Time travel and fail-safe storage are billed separately under this model; logical billing includes them in its base storage charge. Compression alone does not settle the comparison. Storage billing models
This setting also leaves on-demand query billing unchanged: BigQuery continues to calculate it from logical bytes. A smaller billed storage footprint does not itself reduce the bytes charged for reading the table.
The documented forecast uses these components:
| Model | Components to price |
|---|---|
| Logical | Active logical bytes and long-term logical bytes |
| Physical | Active physical bytes plus fail-safe physical bytes, and long-term physical bytes |
ACTIVE_PHYSICAL_BYTES already includes time travel. Adding TIME_TRAVEL_PHYSICAL_BYTES again would double-count it. Convert bytes to GiB and apply the corresponding rates for the location. The TABLE_STORAGE documentation provides the calculation and explains its limitations. Storage forecasting
Use repeated observations across a representative processing cycle. A freshly loaded table and the same table after substantial updates can present different storage requirements. The metadata is a delayed snapshot, while billing reflects storage over time. Clones and snapshots also need care because their displayed sizes can overstate the incremental storage billed.
My decision would depend on the forecast after normal writes and retention have been included. A compression ratio measured immediately after loading is useful evidence, but it is only one part of that decision.
4. Treat retention as an explicit engineering decision
Native BigQuery time travel defaults to seven days and can be configured between two and seven days. Shortening the window can reduce physical storage charges. It does not produce the same saving under logical billing. Fail-safe adds a separate, non-configurable seven-day period for emergency recovery through Cloud Customer Care.
The relevant question is how long the team needs to detect and recover from an incorrect change. Choose the window against that requirement. A shorter window has a concrete operational consequence, so I would document the recovery expectation alongside the saving.
Also review the lifetime of the data itself. BigQuery supports table and partition expiration, including defaults for newly created tables. Setting a dataset default later does not retroactively clean up all existing tables. Google’s storage guidance also suggests retaining aggregates when detailed historical records are no longer required. Storage optimization
I would start this review with temporary development outputs and intermediate results whose owners can explain how they are reproduced. For production data, confirm the required history and recovery process first. The objective is an intentional retention policy that can be maintained, rather than a one-off deletion exercise whose savings disappear as tables accumulate again.
5. Verify the familiar query optimizations
Projection, partitioning, clustering, and inefficient SQL deserve a place in the review. The useful work is checking whether they are effective for the actual workload.
Select the columns that the query needs. Before a large join, investigate whether filtering or aggregation can reduce its inputs without changing the result. Inspect the execution plan to see where intermediate output expands. Also remember that a common table expression is not a promise that BigQuery will compute an intermediate result once and reuse it.
For partitioned tables, check the predicates used by production queries. BigQuery can skip partitions when the filter supports partition elimination. The require_partition_filter option can enforce such a predicate, but it does not establish a small scan budget: a filter can still cover a broad range. I would inspect the selected time window as well as the presence of a filter.
For clustered tables, compare the clustering columns and their order with frequent filters. BigQuery uses block metadata to skip irrelevant blocks, and column ordering affects that opportunity. Choosing clustering keys from actual access patterns is more defensible than adding commonly used identifiers without checking the queries.
After each change, compare the same workload and verify the output. A lower scan volume is useful, but an optimization that changes which records reach an aggregation has changed the product as well as its cost.
6. Reduce repeated computation where the economics work
For repeated analytical queries, examine whether materialized views can reuse a useful result. BigQuery charges for querying, maintaining, and storing them. Non-incremental materialized views run the full defining query on refresh, so refresh behavior belongs in the comparison.
I would compare the total repeated query work with the proposed refresh work, storage, and downstream reads. Required freshness is a constraint in that calculation. A cheaper refresh schedule is only acceptable if its output is still timely enough for the consumers.
Result caching is another mechanism to inspect. Cached results are generally retained for approximately 24 hours on a best-effort basis. Changes to referenced data, non-deterministic functions, and other documented conditions can prevent reuse. Check actual cache hits instead of assuming that repeated SQL receives cached results. Query result caching
For controlled performance comparisons, disable result caching so that both alternatives execute the work. For the operational cost review, keep the workload’s real caching behavior visible. These are different measurements, and both are useful.
7. Add controls that match the workload
For on-demand queries, a dry run estimates scanned bytes before execution. maximumBytesBilled adds a per-query limit: a query whose estimate exceeds it fails without a query charge. Clustered-table estimates can be conservative, so an overly tight limit may reject a query that would ultimately scan less. Project and user daily query quotas provide a broader boundary.
Do not use a result LIMIT as a general scan-cost safeguard. In particular, on non-clustered tables it does not reduce the bytes read merely because fewer rows are returned.
I would set limits according to the purpose of the workload. Interactive exploration and a scheduled production backfill have different expectations. The limit should make unexpected work visible while leaving an understood route for legitimate larger executions.
Controls also need owners. Someone must investigate a rejected job, decide whether the query or limit is wrong, and update the configuration when the workload changes. Otherwise, the control can become another recurring pipeline failure.
8. Review how reservation capacity is purchased and used
For capacity-based workloads, the distinction between consumed and billed slot-hours is essential. Baseline slots are charged even when unused. Autoscaling charges apply to scaled capacity, which is allocated in increments of 50 slots. Summing job total_slot_ms therefore does not reconstruct the reservation invoice. Slots and autoscaling
Review the baseline, maximum capacity, and workload timing together. I would evaluate whether lower resource consumption permits a smaller baseline or less autoscaling, and whether that change preserves acceptable completion times under concurrent load.
BigQuery also documents fluid scaling. Standard autoscaling has a one-minute minimum duration by default; fluid scaling removes that duration minimum while retaining the 50-slot increments. This is a configuration worth evaluating for brief bursts, with measurements from the actual reservation. Slots and autoscaling
For billing analysis, use reservation-level information. Google’s monitoring documentation provides queries using RESERVATIONS_TIMELINE and separate calculations for capacity covered by commitments. Compare those results with Cloud Billing, allowing for the documented rounding and retention limitations. Reservation monitoring
The slot recommender can support the review with options based on the previous 30 days, including pay-as-you-go and commitment alternatives. Check whether that period represents the future workload and whether its autoscaling assumptions match your configuration. The documentation identifies a fluid-scaling adoption threshold below which recommendations can still assume the one-minute minimum. Slot recommendations
I would optimize the workload before committing to its long-term capacity requirement. A discount deserves comparison against the amount of capacity that will remain necessary after the known inefficiencies are removed.
9. Evaluate Iceberg against the whole workload
The migration assessment behind the opening example had a broader purpose: unified storage and metadata access across multiple query engines. BigQuery-managed Iceberg was the preferred option in that assessment. Cost was one part of the decision.
The test used a table with approximately 701 million rows, partitioned by event_dt. The plan controlled project, region, and time window, disabled result caching, and specified at least five executions of each query. The source notes contain results for the baseline, a MERGE, and reads after that MERGE. They do not contain completed results for the planned post-compaction comparison.
What the measurements showed
| Measurement | Recorded result |
|---|---|
| Baseline reads | Identical bytes processed across ten queries; Iceberg used 1.9–4.8× the native slot time |
| MERGE execution time | 35 seconds for Iceberg, 49 seconds for native BigQuery |
| MERGE compute consumption | 7.95 consumed slot-hours for Iceberg, 6.62 for native BigQuery |
| MERGE bytes processed | 241 GB for Iceberg, 163 GB for native BigQuery |
| Reads after MERGE | Identical bytes processed; Iceberg used 1.8–4.7× the native slot time |
The MERGE is the clearest example of why execution time alone is insufficient. Iceberg completed sooner while consuming about 20% more slot time. These are consumed slot-hours, so the increase should not be presented as a measured 20% increase in the reservation bill.
Across the read tests, the largest relative slot overhead appeared in small, heavily pruned queries. The notes suggest a fixed component of file or metadata handling as a possible explanation. The measurements establish the difference; they do not isolate its internal cause.
The post-MERGE tests also challenged an initial expectation. The recorded runs did not show a consistent Iceberg read regression after that single large MERGE. That result should remain in the analysis even if it weakens the anticipated fragmentation argument.
The scope matters. There was one table, one set of queries, and one update pattern. The results justify examining compute overhead for this migration. They do not establish a universal performance multiplier for Iceberg or predict the cost of another team’s workload.
Include costs outside the foreground queries
BigQuery-managed Iceberg stores data in Cloud Storage, where historical files also incur storage charges. Automatic table management, including compaction and clustering, is billed in Data Compute Units. Depending on the operations and location, storage processing and transfer charges can also apply. These belong alongside query compute in the comparison.
Metadata freshness also needs an explicit configuration. Current documentation describes manual metadata export, scheduled refresh, and an opt-in automatic refresh path. A universal 90-minute visibility delay is therefore not a sound assumption for the architecture. Iceberg metadata snapshots
My conclusion from the available evidence is to compare three alternatives: the existing native configuration, native storage with physical billing, and the proposed Iceberg design. Keep the multi-engine requirement explicit, then price the storage, normal reads, writes, retention, and background work for each applicable alternative.
A practical order of work
I would begin with a representative baseline that connects job activity, storage measurements, and the bill. Then investigate the largest recurring consumers and compare logical with physical storage billing. Those steps make the current cost structure understandable before a migration or model change introduces additional variables.
Next, verify partition and clustering effectiveness, remove avoidable repeated computation, and establish retention and query limits appropriate to each workload. For capacity billing, review reservations again after these changes, because the resource requirement may have changed.
Finally, evaluate architectural changes against the complete requirement. Iceberg may provide valuable multi-engine access. The decision should show what that capability costs, what it replaces, and which measurements remain incomplete.
A useful cost optimization report ends with a specific outcome: the billed amount changed, the platform can process more work within the same capacity, or an agreed processing window became shorter. Recording which outcome was achieved makes the next decision much easier to defend.
This article is a little different from what I usually write. It started with a specific task at work, but the findings were interesting enough that I wanted to explore the cost implications beyond the Iceberg migration itself. That led me to take a broader look at BigQuery pricing, storage, and compute, and put those findings together in an article worth sharing.