When you replicate data across regions with the goal of switching to a secondary during an outage, the Recovery Point Objective (RPO) is critical, as it defines the maximum acceptable data loss.
For Standard & Enterprise BigQuery editions, Google does not offer a service-level agreement (SLA) on replication time.
However, if a low RPO is critical for your business, the Enterprise Plus edition offers a Turbo Replication feature, which commits to a replication RPO of 15 minutes.
Naturally, a powerful feature like this isn’t free. Before enabling replication, it’s crucial to understand the associated costs to evaluate if the benefit justifies the expense.
Here are the items to consider:
- Data storage in the secondary region(s). The good news is that replication preserves the storage class (long-term or active) and the pricing model (logical or physical) of your data.
- Network egress between the primary and secondary region(s). You can find the pricing details here.
While estimating the storage cost is fairly simple (if you have one primary and one secondary region, your storage costs will roughly double), the cost of ongoing network egress from incremental changes is not documented.
Underestimating network costs when working with a large-scale data warehouse can be a costly mistake.
According to the documentation, network egress charges are based on the volume of physical data transferred, not logical. You can estimate the current physical size of your tables using the INFORMATION_SCHEMA.TABLE_STORAGE view with this query:
SELECT
project_id, table_schema, table_name, total_physical_bytes
FROM
`.region-.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE
table_schema = ""
But what about the recurring cost of the inserts, updates, and deletes that BigQuery has to replicate?
There are no immediate metrics available for this, either in INFORMATION_SCHEMA or in Cloud Monitoring.
However, a clever solution proposed by of Google France provides a path forward by using the DML statistics in the INFORMATION_SCHEMA.JOBS view.
Indeed, the JOBS view contains insert, update, and delete DML statistics for each completed job. The algorithm works as follows:
- Calculate the average physical size of a single row for each table.
- Aggregate the number of inserted, updated, and deleted rows from all jobs over a representative period (e.g., one week), grouped by the destination table.
- For each table, multiply the number of rows changed by the average physical row size.
This method will produce a good estimate, though it won’t be exact.
For instance, a DELETE operation likely transmits a simple instruction rather than the entire row’s data.
Therefore, you should consider the result a “worst-case scenario” estimate. Still, a well-informed estimate is far better than flying blind.
Here is the final query to estimate your ongoing egress costs:
DECLARE target_project_id STRING DEFAULT '';
DECLARE target_dataset_id STRING DEFAULT '';
DECLARE gcp_region STRING DEFAULT '';
DECLARE start_date TIMESTAMP DEFAULT ' 2025-08-01T00:00:00Z>';
DECLARE end_date TIMESTAMP DEFAULT ' 2025-08-07T23:59:59Z>';DECLARE nb_day_period INT64 DEFAULT DATE_DIFF(DATE(end_date), DATE(start_date), DAY) + 1;
EXECUTE IMMEDIATE FORMAT("""
-- Step 1: Get the average size of a row for each table
WITH TableRowSizes AS (
SELECT
table_schema,
table_name,
SAFE_DIVIDE(total_physical_bytes, total_rows) AS avg_row_size_bytes
FROM
`%s.region-%s.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE
total_rows > 0
AND table_schema = @dataset_id
),
-- Step 2: Use DML statistics from jobs to get the number of changes
DmlJobsAnalysis AS (
SELECT
j.referenced_tables[OFFSET(0)].table_id AS target_table,
(COALESCE(j.dml_statistics.inserted_row_count, 0) * trs.avg_row_size_bytes) / POWER(1024, 3) AS estimated_inserted_gb,
(COALESCE(j.dml_statistics.deleted_row_count, 0) * trs.avg_row_size_bytes) / POWER(1024, 3) AS estimated_deleted_gb,
(COALESCE(j.dml_statistics.updated_row_count, 0) * trs.avg_row_size_bytes) / POWER(1024, 3) AS estimated_updated_gb
FROM
`%s.region-%s.INFORMATION_SCHEMA.JOBS` AS j
JOIN
TableRowSizes AS trs
ON
j.referenced_tables[OFFSET(0)].table_id = trs.table_name AND
j.referenced_tables[OFFSET(0)].dataset_id = trs.table_schema
WHERE
j.creation_time BETWEEN @start_date AND @end_date
AND j.job_type = 'QUERY'
AND j.state = 'DONE'
AND j.error_result IS NULL
AND j.statement_type IN ('INSERT', 'UPDATE', 'DELETE', 'MERGE')
AND ARRAY_LENGTH(j.referenced_tables) > 0
AND j.referenced_tables[OFFSET(0)].project_id = @project_id
AND j.referenced_tables[OFFSET(0)].dataset_id = @dataset_id
)
-- Step 3: Aggregate the estimated data volume per table
SELECT
target_table,
ROUND(SUM(estimated_inserted_gb), 5) AS total_volume_insert_gb,
ROUND(SUM(estimated_deleted_gb), 5) AS total_volume_delete_gb,
ROUND(SUM(estimated_updated_gb), 5) AS total_volume_update_gb,
-- Total volume for the period
ROUND(SUM(estimated_inserted_gb + estimated_deleted_gb + estimated_updated_gb), 5) AS grand_total_volume_gb,
-- Number of days in the period
@nb_days AS nb_day_period,
-- Average daily volume
ROUND(SAFE_DIVIDE(SUM(estimated_inserted_gb + estimated_deleted_gb + estimated_updated_gb), @nb_days), 5) AS avg_daily_volume_gb
FROM
DmlJobsAnalysis
GROUP BY
target_table
ORDER BY
grand_total_volume_gb DESC;
""",
-- Arguments for FORMAT()
target_project_id, gcp_region,
target_project_id, gcp_region
)
USING
start_date AS start_date,
end_date AS end_date,
target_project_id AS project_id,
target_dataset_id AS dataset_id,
nb_day_period AS nb_days;
You can now confidently estimate the cost of cross-region data replication in BigQuery.
While this feature is primarily designed for a Disaster Recovery Plan (DRP), it unlocks other powerful use cases:
- Migration: If you need to change your BigQuery data’s primary region, you can now easily replicate massive volumes of data in one simple step.
- Cross-Region Queries: BigQuery requires data to be in the same physical location for queries. By replicating data to the region where your query originates, BigQuery automatically uses the local replica. This eliminates query-time data transfer latency and egress costs on each query.
- Read-Only Replicas: You can treat a secondary region as a read-only replica, protecting the data from accidental modifications by users or applications that only require read access.
Business-critical analytical applications can now be confidently deployed across multiple regions with BigQuery.
The setup is remarkably simple, but always be mindful of the costs, which can escalate quickly in a big data environment.
Armed with the strategy in this article, you are now well-equipped to meet this challenge and replicate your data with confidence.
Source Credit: https://medium.com/google-cloud/bigquery-cross-region-replication-dont-overlook-the-network-costs-66aa28d0b769?source=rss—-e52cf94d98af—4
