Cross-region BigQuery queries between Sydney and Melbourne: Latency and Egress math

1. The Core Problem: Data Gravity vs. Centralized Analytics

Historically, Google Cloud Platform (GCP) enforced strict geographical boundaries on data processing within BigQuery. If a company stored a dataset in the Sydney region (australia-southeast1) and another dataset in the Melbourne region (australia-southeast2), joining these tables in a single SQL statement was impossible. Data gravity dictated that compute must happen where the storage lives.

To solve this, data engineers had to build manual ETL/ELT pipelines using the BigQuery Data Transfer Service, Cloud Composer, or custom Dataflow jobs to copy data from one region to another before executing the query. This traditional replication approach leads to two significant problems: double storage costs and data synchronization delays.

Recently, BigQuery introduced Global Queries. This feature changes the architectural paradigm. It allows the BigQuery execution engine to read data from a remote region, apply filters locally in that remote region (predicate pushdown), and transfer only the necessary resulting rows across the network to the primary region for the final table join. This article explores the mathematical calculations, use cases, and technical configurations required to implement this architecture effectively, specifically focusing on the Oceania regions.

2. Real-World Architecture Use Cases

Implementing cross-region queries is not a universal solution for every data problem. It is designed for specific architectural constraints. Here are three real-world scenarios where this approach is necessary.

Case 1: Unified FinOps and Cloud Billing Analysis

A technology consultancy manages infrastructure for multiple clients across different geographic regions. To comply with local data residency requirements, detailed infrastructure logs and billing exports for a specific client are stored in Melbourne. However, the centralized FinOps engineering team operates out of Sydney and maintains a master dataset of historical pricing models and global organizational budgets. Instead of replicating terabytes of raw billing data to Sydney every day, the FinOps team can write a Global Query. The query filters the Melbourne data for specific anomalies or high-cost resources, aggregates the costs, and sends only a few megabytes of summarized data to Sydney to join with the global budget tables.

Case 2: Regulatory Compliance in the Financial Sector

Financial institutions often face strict compliance laws regarding Personally Identifiable Information (PII). Suppose a bank processes regional transactions in Melbourne. The raw transaction logs containing PII must remain physically stored in the australia-southeast2 data centers. However, the risk management team in Sydney needs to calculate daily fraud probability scores based on cross-regional transaction patterns. Using cross-region queries, the bank can execute a query from Sydney that processes the raw data in Melbourne. The Melbourne execution nodes strip the PII, perform the mathematical aggregations, and return only the anonymized, aggregated risk scores to Sydney. The raw PII never crosses the regional boundary, satisfying regulatory compliance while enabling global analytics.

Case 3: Disaster Recovery (DR) and Active-Passive Analytics

A retail company runs a highly fault-tolerant e-commerce platform. The primary database replicates to a secondary region (Melbourne) for disaster recovery. Normally, the analytics pipeline runs in Sydney. If the Sydney storage layer experiences a partial regional degradation, the compute nodes in Sydney can be temporarily re-pointed to query the replica datasets in Melbourne. While this introduces network latency, it ensures business continuity for critical BI dashboards without waiting for a full failover of the entire ETL pipeline.

3. Calculations: Costs, Timelines, and Architecture Comparison

When designing a cloud architecture, every decision must be backed by FinOps calculations. The choice between Global Queries and Dataset Replication heavily impacts both performance timelines and monthly cloud billing.

The Latency Timeline (Physics vs. Engine Orchestration)

The geographical distance between the Sydney and Melbourne Google Cloud data centers is approximately 900 kilometers. The physical speed of light through fiber optic cables dictates a minimum round-trip time (RTT) of about 9 to 10 milliseconds. In standard networking, this is negligible.

However, BigQuery is a distributed analytical engine, not a simple transactional database. When you execute a Global Query:

  1. The primary region (Sydney) parses the SQL and generates a distributed execution graph.
  2. It sends the sub-query instructions to the remote region (Melbourne).
  3. Melbourne allocates compute slots, scans the storage, and filters the data.
  4. Melbourne securely transfers the intermediate result set over the Google network backbone back to Sydney.
  5. Sydney performs the final join and returns the result.

This complex orchestration adds a strict baseline overhead of 5 to 10 seconds to the query execution time, regardless of how small the dataset is. Therefore, Global Queries are strictly for batch processing and backend ELT workloads. They are completely unsuitable for user-facing interactive dashboards that require sub-second load times.

If your service requires low latency, traditional Dataset Replication is mandatory.

FinOps Calculations: Egress and Storage

GCP billing for cross-region data movement falls under network egress charges. Both Sydney and Melbourne are located in the “Oceania” billing territory. The cost to transfer data between these two regions is $0.08 per Gigabyte (GB).

Additionally, standard BigQuery compute costs apply in both regions: $6.25 per Terabyte (TB) of data scanned (assuming on-demand pricing).

Let us compare the monthly costs of an automated daily ELT pipeline using both architectures.

The Scenario Parameters:

  • Source table size in Melbourne: 10 TB
  • Data added per day: 100 GB
  • Data needed in Sydney after SQL filtering (WHERE clause): 5 GB
  • Frequency: Query runs once per day (30 times a month).

Approach A: Dataset Replication (Data Transfer Service) To query the data locally in Sydney, you must replicate the new daily partitions.

  • Egress Cost: Transferring the 100 GB daily partition. (100 GB * $0.08) * 30 days = $240 / month.
  • Storage Cost: You must pay for active storage in Sydney for the replicated data. Assuming the table grows to 10 TB, you pay ~$0.02 per GB. 10,000 GB * $0.02 = $200 / month.
  • Compute Cost: Querying the local 10 TB table in Sydney. (10 TB * $6.25) * 30 days = $1,875 / month.
  • Total Cost: $2,315 per month.
  • Latency: Standard local speed (milliseconds to seconds).

Approach B: BigQuery Global Queries You do not replicate the data. You run a cross-region query that filters the data in Melbourne and only sends the 5 GB of results to Sydney.

  • Egress Cost: Transferring only the filtered 5 GB result. (5 GB * $0.08) * 30 days = $12 / month.
  • Storage Cost: Zero additional storage. The data only exists in Melbourne.
  • Compute Cost: The query scans 10 TB in Melbourne. (10 TB * $6.25) * 30 days = $1,875 / month.
  • Total Cost: $1,887 per month.
  • Latency: +5 to 10 seconds overhead per query.

Conclusion of Calculations: In this scenario, using Global Queries saves $428 per month in egress and storage costs, at the expense of query latency.

4. How to Enable and Configure Global Queries

Because cross-region queries involve moving data across geographical boundaries and incur network charges, Google Cloud requires explicit project-level opt-ins. You must use Data Definition Language (DDL) SQL statements in the BigQuery console to configure the project options.

Step 1: Project-Level SQL Configuration

You must define which region is allowed to execute the query (the compute layer) and which region is allowed to export its data (the storage layer).

Open the BigQuery UI and run the following statement to authorize the Sydney region to execute global queries:

SQL

ALTER PROJECT `your-gcp-project-id` 
SET OPTIONS (
  `region-australia-southeast1.enable_global_queries_execution` = TRUE 
);

Next, you must explicitly authorize the Melbourne region to allow its data to be accessed and transferred out during a global query:

SQL

ALTER PROJECT `your-gcp-project-id` 
SET OPTIONS (
  `region-australia-southeast2.enable_global_queries_data_access` = TRUE 
);

Note: These commands apply to the entire GCP project. You only need to run them once per region pairing. To disable the feature, run the same commands setting the value to FALSE.

Step 2: IAM (Identity and Access Management) Configuration

Simply enabling the project flags does not give all users the right to run cross-region queries. To prevent junior analysts from accidentally generating massive cross-region egress bills, GCP protects this feature with a specific IAM permission: bigquery.jobs.createGlobalQuery.

By default, only users with the BigQuery Admin role have this permission. Granting Admin access to analysts violates the principle of least privilege.

To configure this correctly using Infrastructure as Code (like Terraform) or the GCP Console:

  1. Navigate to IAM & Admin > Roles.
  2. Create a new Custom Role named BigQuery Global Query Executor.
  3. Add the single permission: bigquery.jobs.createGlobalQuery.
  4. Assign this custom role to the specific service accounts (for automated Airflow/Composer ETL jobs) or specific senior data engineers, alongside their standard BigQuery Data Viewer and BigQuery Job User roles.

5. Standard Errors and Troubleshooting Solutions

When deploying cross-region architectures, engineers frequently encounter specific orchestration and permission errors. Here is how to resolve the most common issues.

Error 1: IAM Permission Denied

The Error Message: Access Denied: Project [project-id] does not have the bigquery.jobs.createGlobalQuery permission. The Cause: The user or the service account executing the SQL statement lacks the specific IAM permission required to initiate a cross-region data transfer. Standard BigQuery Data Editors cannot run these queries by default. The Solution: Verify the active account using gcloud auth list. Check the IAM policies for that account and ensure it is assigned either the BigQuery Admin role or a custom role containing the bigquery.jobs.createGlobalQuery permission. If using a service account for an automated pipeline, update its IAM bindings.

Error 2: Data Access Flag Not Enabled

The Error Message: Query execution failed: Cross-region data access is not enabled for region [australia-southeast2]. The Cause: The engine in the primary region (Sydney) successfully attempted to contact the remote region (Melbourne), but the Melbourne region rejected the request because the project-level data access flag is set to false. This is a security feature to prevent unintentional data exfiltration between regions. The Solution: You must run the ALTER PROJECT SQL statement specifically for the remote region. Execute: ALTER PROJECT `your-project` SET OPTIONS (`region-australia-southeast2.enable_global_queries_data_access` = TRUE);. Ensure you specify the correct remote region name in the option string.

Error 3: The “SELECT *” Egress Trap (Financial Error)

The Issue: The query executes successfully, but the monthly cloud billing report shows a massive, unexpected spike in Google Cloud Inter-Region network egress charges. The Cause: The engineer wrote a query like SELECT * FROM project.dataset_melbourne.huge_table. Because there is no WHERE clause or aggregation, BigQuery cannot perform predicate pushdown. It is forced to transfer the entire, raw contents of the remote table across the network backbone to Sydney before displaying the results. You are paying $0.08 per GB for the entire table size. The Solution: Never use SELECT * in a cross-region query. You must strictly enforce the use of WHERE clauses, GROUP BY aggregations, and date-partition filters. The goal is to make the execution engine in the remote region do all the heavy lifting and filtering, so that only the smallest possible dataset is transferred across the network. Consider enforcing maximum bytes billed limits on the project level to prevent accidental financial drain.

Similar Posts