Study guide

3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud

Google Cloud Certified Professional Data Engineer Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Security, IAM, and Data Protection

1.1 Cloud IAM, Service Accounts, and Resource Hierarchy in GCP Data Systems 1.2 Data Encryption, Key Management (CMEK), and Cloud KMS 1.3 Sensitive Data Protection: Cloud DLP, De-identification, and Masking

2 Chapter 2: Reliability and Compliance Architecture

2.1 High Availability, Regional vs Multi-Regional Deployments, and Disaster Recovery 2.2 Data Residency, Sovereignty, and Regulatory Compliance (HIPAA, GDPR) 2.3 Modern Data Architecture Patterns: Data Mesh, Lakehouse, and Modern Data Stack

3 Chapter 3: Cross-Cloud and Federated Governance

3.1 BigLake Architecture: Unified Multicloud Storage and Fine-Grained Access Control 3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud 3.3 Analytics Hub and Federated Computational Governance

4 Chapter 4: Data Ingestion and Pipeline Planning

4.1 Data Pipeline Planning: Sources, Sinks, and Network Architecture (VPC, PSC, Interconnect) 4.2 Batch vs Streaming Processing Trade-Offs and Architecture Selection 4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion

5 Chapter 5: Streaming Ingestion with Cloud Pub/Sub

5.1 Cloud Pub/Sub Core Architecture: Topics, Subscriptions, and Message Lifecycle 5.2 Pub/Sub Message Ordering, Exactly-Once Delivery, and Deduplication 5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics

6 Chapter 6: Pipeline Processing with Apache Beam and Cloud Dataflow

6.1 Apache Beam Programming Model: Pipelines, PCollections, and Core PTransforms 6.2 Cloud Dataflow Architecture: Autoscaling, Worker Allocation, and Dataflow Prime 6.3 Windowing Strategies in Streaming Dataflow: Fixed, Sliding, and Session Windows

7 Chapter 7: Advanced Dataflow: Robustness and Exactly-Once Semantics

7.1 Watermarks, Triggers, Accumulating vs Discarding Panes, and Late Data Handling 7.2 Side Inputs, Broadcast Joins, and Stateful Stream Processing 7.3 Pipeline Robustness: Dead-Letter Queues, Error Handling, and Pipeline Lifecycle Management

8 Chapter 8: Managed Data Processing: Cloud Dataproc and Cloud Data Fusion

8.1 Cloud Dataproc: Architecture, Ephemeral vs Persistent Clusters, and Spark Tuning 8.2 Dataproc Serverless and Autoscaling Policies for Batch Processing 8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage

9 Chapter 9: Storage Selection and Cloud Storage Architecture

9.1 GCP Storage Selection Decision Matrix: Relational, NoSQL, Object, and Analytical Stores 9.2 Cloud Storage Architecture: Storage Classes, Lifecycle Rules, and Object Versioning 9.3 Cloud Storage Security: Bucket Lock (WORM), Access Control, and Data Migration Tools

10 Chapter 10: Relational Cloud Databases: Cloud Spanner and Cloud SQL

10.1 Cloud Spanner Architecture: TrueTime, External Consistency, and Global Scalability 10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning 10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery

11 Chapter 11: Scalable NoSQL Databases: Cloud Bigtable and Firestore

11.1 Cloud Bigtable Architecture: Storage Model, Tablet Splitting, and Cluster Management 11.2 Cloud Bigtable Row Key Design: Lexicographical Ordering, Salt Keys, and Anti-Patterns 11.3 Cloud Firestore: Document Model, Real-Time Sync, and ACID Collections

12 Chapter 12: Enterprise Data Warehousing with BigQuery

12.1 BigQuery Architecture: Capacitor Columnar Format, Jupiter Network, and Borg Slots 12.2 BigQuery Partitioning: Ingestion-Time, Date/Timestamp, and Integer Range Strategies 12.3 BigQuery Clustering: Cardinality, Multi-Column Ordering, and Performance Optimization 12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions

13 Chapter 13: Advanced Analytical Queries and Transformations in BigQuery

13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT 13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation 13.3 BI Engine In-Memory Acceleration and Enterprise Looker/LookML Semantic Modeling

14 Chapter 14: Machine Learning and Feature Engineering with BigQuery ML

14.1 BigQuery ML Architecture: In-Database Training, Model Topologies, and SQL Analytics 14.2 Feature Engineering: ML.TRANSFORM, Preprocessing Pipelines, and Vertex AI Feature Store 14.3 MLOps, Model Governance, Continuous Monitoring, and Deployment Lifecycles

15 Chapter 15: Operations, Orchestration, and Data Governance

15.1 Workflow Orchestration with Cloud Composer: Managed Airflow, DAG Design, and Dependencies 15.2 Unified Data Governance with Dataplex: Lakes, Zones, Data Quality, and Lineage 15.3 Pipeline Observability, FinOps, and Cost Optimization Across GCP Data Services

3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud

Key Takeaways Explain This Topic AI

BigQuery Omni executes analytical SQL queries locally in AWS and Azure using Google-managed Anthos (GKE Enterprise) clusters, completely eliminating cross-cloud network egress fees for query computation.

Authentication to foreign cloud storage uses short-lived tokens via OpenID Connect (OIDC) federation with AWS IAM Roles or Azure Active Directory/Entra ID, eliminating static access keys and credential rotation risks.

A single SQL query in BigQuery Omni cannot execute a distributed join between tables residing in disparate cloud regions (e.g., AWS S3 us-east-1 and GCP us-central1); queries must execute strictly within the dataset's local region.

Moving multi-cloud data back to Google Cloud is achieved either via SQL EXPORT DATA OPTIONS targeting Cloud Storage buckets or via automated, scheduled BigQuery Data Transfer Service (DTS) pipelines.

BigQuery Omni capacity is governed through regional edition reservations (Enterprise or Enterprise Plus) established specifically within the foreign cloud region (e.g., aws-us-east-1 or azure-eastus2).

Last updated: September 2026

3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud

Exam Focus: The Google Cloud Professional Data Engineer exam expects you to understand the architecture of BigQuery Omni , evaluate the cost and egress trade-offs of multi-cloud data strategies, configure credential-less OpenID Connect (OIDC) trust relationships with AWS IAM and Azure Entra ID, navigate regional compute isolation constraints , and design low-egress data consolidation architectures using EXPORT DATA OPTIONS .

Enterprise data landscapes are increasingly distributed across multiple public cloud providers. A common architecture finds telemetry, web applications, and point-of-sale systems generating petabytes of raw transactional logs in Amazon S3 or Azure Data Lake Storage Gen2, while enterprise business intelligence, machine learning, and centralized reporting standardized on Google Cloud's BigQuery.

Historically, analyzing data across cloud providers required one of two flawed approaches:

Full Bulk Data Ingestion (Continuous Egress): Replicating raw datasets from AWS or Azure to Google Cloud Storage via network transfers or third-party ETL tools. Moving hundreds of terabytes or petabytes across cloud boundaries incurs massive, continuous data egress charges from the source cloud provider (typically $0.05 to $0.09 per gigabyte transferred out of AWS or Azure), introduces pipeline latency, and creates duplicate storage liabilities.

Siloed Analytical Stacks: Operating independent analytical warehouses (such as Amazon Athena/Redshift in AWS, Azure Synapse/Fabric in Azure, and BigQuery in GCP). This model fragments business logic, multiplies licensing costs, requires maintaining disparate security and governance policies, and forces data engineers to master divergent SQL dialects.

BigQuery Omni solves this dilemma by decoupling the BigQuery control plane from the BigQuery query execution plane. While users manage datasets, submit queries, and view results through the unified Google Cloud Console, APIs, or command-line tools, the actual query compute clusters execute natively inside the foreign cloud provider's infrastructure.

BigQuery Omni Architectural Mechanics and Anthos Clusters

The fundamental innovation of BigQuery Omni is the deployment of Google-managed Anthos (GKE Enterprise) clusters inside AWS and Azure regions (such as AWS aws-us-east-1 or Azure azure-eastus2 ). +-------------------------------------------------------------------------+

  • | GOOGLE CLOUD CONTROL PLANE |
  • | - Cloud Console & BigQuery Studio - BigQuery Query Coordinator |
  • | - Metadata & Security Catalog - Cloud Monitoring & Logging |
  • +-------------------------------------------------------------------------+
  • | |
  • Query Dispatch | (Metadata / Results Only) | Query Dispatch
  • v v
  • +------------------------------------+ +---------------------------------+
  • | AMAZON WEB SERVICES | | MICROSOFT AZURE |
  • | Region: aws-us-east-1 | | Region: azure-eastus2 |
  • | | | |
  • | [BigQuery Omni Compute Cluster] | | [BigQuery Omni Compute Cluster]|
  • | (Google-Managed Anthos/K8s) | | (Google-Managed Anthos/K8s) |
  • | | | | | |
  • | v | | v |
  • | [ Amazon S3 Bucket ] | | [ Azure ADLS Gen2 Account ] |
  • | (Query executes LOCALLY; | | (Query executes LOCALLY; |
  • | Zero AWS Egress for Scans) | | Zero Azure Egress for Scans) |
  • +------------------------------------+ +---------------------------------+
  • The Control Plane vs. Data Plane Boundary

Control Plane (Google Cloud): Handles query parsing, query optimization, cost estimation, IAM authorization, and query orchestration. The control plane resides entirely within Google Cloud infrastructure.

Data Plane (Foreign Cloud): When a user executes a query targeting an Omni dataset (e.g., in aws-us-east-1 ), the Google Cloud control plane compiles the execution plan and dispatches the execution instructions to the BigQuery Omni compute cluster running inside AWS us-east-1 .

Local Data Processing: The Anthos-based Omni workers read the Parquet, ORC, Avro, or CSV files directly from the customer's Amazon S3 bucket via high-speed AWS internal network backbones. Filtering, projection, aggregations, and joins occur entirely within AWS . Raw bytes never leave the AWS network perimeter.

Egress Minimization: Only the small, final aggregated query result set (or query status messages) is returned over encrypted TLS connections to the user in Google Cloud, reducing network egress charges by up to 99.9% compared to moving the raw input data.

Zero-Credential Identity Federation

A critical security requirement for the Professional Data Engineer exam is eliminating static, long-lived credentials. BigQuery Omni does not use AWS Access Key IDs and Secret Access Keys, nor does it use Azure storage account keys. Storing long-lived foreign credentials in Google Cloud creates severe compliance and exfiltration risks.

Instead, BigQuery Omni relies strictly on credential-less OpenID Connect (OIDC) identity federation .

AWS IAM Role Federation Workflow

Create Omni Connection in BigQuery: A BigQuery connection of type AWS is created in the target region (e.g., aws-us-east-1 ).

Generate Google Identity: BigQuery automatically provisions a dedicated, unique Google Cloud service account identity and a Google Identity Provider string for that connection.

Configure AWS IAM Role Trust Policy: In the AWS account, an IAM Role is created. Its trust relationship policy is configured to trust Google's identity provider ( accounts.google.com ) using sts:AssumeRoleWithWebIdentity : {

  • "Version": "2012-10-17",
  • "Statement": [
  • {
  • "Effect": "Allow",
  • "Principal": {
  • "Federated": "accounts.google.com"
  • },
  • "Action": "sts:AssumeRoleWithWebIdentity",
  • "Condition": {
  • "StringEquals": {
  • "accounts.google.com:sub": "GOOGLE_SERVICE_ACCOUNT_UNIQUE_ID",
  • "accounts.google.com:aud": "https://bigquery.googleapis.com"
  • }
  • }
  • }
  • ]
  • }

Grant S3 Permissions in AWS: The AWS IAM Role is assigned an IAM policy granting read permissions (e.g., s3:GetObject , s3:ListBucket ) on the target S3 bucket.

Dynamic Exchange: During query execution, the Omni Anthos cluster exchanges short-lived Google OIDC tokens with AWS Security Token Service (STS) to obtain temporary AWS credentials valid only for the duration of the query step.

Azure Active Directory / Entra ID Federation Workflow

For Microsoft Azure, BigQuery Omni creates an Azure Connection in an Omni region (e.g., azure-eastus2 ). Google generates an Application Client ID. Inside Azure Entra ID (formerly Azure AD), the administrator creates an App Registration and configures Federated Identity Credentials trusting the Google token issuer. The Azure Service Principal is then granted the Storage Blob Data Reader role on the specific Azure Storage Account or ADLS Gen2 container.

Query Execution Model and Regionality Rules

A fundamental architectural rule governing BigQuery Omni is strict regional compute isolation .

The Regional Join Constraint

In BigQuery, all datasets and external connections possess a concrete regional location. For BigQuery Omni, locations are explicitly prefixed with the cloud provider (e.g., aws-us-east-1 , aws-eu-central-1 , azure-eastus2 ).

Core Exam Rule: A single SQL query cannot execute a distributed join between tables located in different regions or different clouds.

  • Consider the following query: -- THIS QUERY WILL FAIL WITH A LOCATION MISMATCH ERROR
  • SELECT
  • c.customer_name,
  • SUM(t.transaction_amount) AS total_spent
  • FROM `gcp_project.us_central1_dataset.customers` c
  • JOIN `gcp_project.aws_us_east_1_dataset.raw_s3_transactions` t
  • ON c.customer_id = t.customer_id
  • GROUP BY 1;

Reason for Failure: BigQuery's execution engine cannot stream distributed shuffles across disparate clouds within a single execution plan without violating network security boundaries and latency constraints. The query coordinator will reject this query with an Access Denied: Location mismatch or Cannot join tables across regions exception.

Cross-Cloud Data Movement and Aggregation Strategies

To perform holistic enterprise analytics when data is distributed across AWS, Azure, and Google Cloud, data engineers utilize two official, recommended patterns:

Pattern 1: In-Region Aggregation and SQL Export ( EXPORT DATA OPTIONS )

Under this pattern, BigQuery Omni executes complex filtering, joining, and aggregation locally inside AWS or Azure. The highly condensed result set is then exported directly to a cross-cloud destination using the EXPORT DATA SQL statement. -- Execute query locally in AWS and export aggregated results directly to Google Cloud Storage

  • EXPORT DATA OPTIONS(
  • uri='gs://enterprise-central-lake/cross_cloud_sales/*.parquet',
  • format='PARQUET',
  • overwrite=true
  • ) AS
  • SELECT
  • date_trunc(transaction_timestamp, DAY) AS transaction_date,
  • store_country,
  • product_category,
  • COUNT(transaction_id) AS total_orders,
  • SUM(order_value_usd) AS daily_revenue
  • FROM `enterprise_omni_aws.s3_raw_orders`
  • WHERE transaction_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  • GROUP BY 1, 2, 3;

Why this is optimal: The raw dataset in S3 might be 50 terabytes, which would cost thousands of dollars to egress directly. The aggregated daily summary is only 50 megabytes. By aggregating locally in AWS with BigQuery Omni and exporting only the summary to GCS, egress costs are reduced to pennies.

Pattern 2: BigQuery Data Transfer Service (DTS) for Scheduled Cross-Cloud Ingestion

When raw or semi-aggregated data from Amazon S3 or Azure Blob Storage must be periodically replicated into native BigQuery managed storage in Google Cloud, the BigQuery Data Transfer Service (DTS) provides automated scheduling, backfilling, and error retries.

Automated Ingestion: DTS can be scheduled to run hourly, daily, or on custom cron expressions.

Data Parity: DTS pulls new or modified files matching glob patterns (e.g., s3://telemetry-bucket/logs/YYYY-MM-DD/* ) directly into a native BigQuery table in us-central1 .

Authentication: Uses AWS IAM Role ARN federation identical to Omni's trust model.

Capacity Management and Editions Reservations in Omni Regions

BigQuery compute pricing operates under Google Cloud's Editions model (Standard, Enterprise, and Enterprise Plus). For BigQuery Omni, capacity is managed through dedicated regional slot reservations:

Edition Reservations: You must purchase BigQuery Enterprise or Enterprise Plus reservations specifically within the target Omni region (e.g., aws-us-east-1 or azure-eastus2 ).

Autoscaling Slots: Omni reservations support autoscaling slot pools with configured baseline and maximum slot limits, allowing compute clusters in AWS or Azure to scale dynamically based on query complexity.

Admin Project Scoping: Slot reservations are created within a designated administration project and assigned to workload projects via BigQuery reservation assignments.

Comparative Evaluation: Cross-Cloud Analytics Strategies

Architectural Option Compute Location Cross-Cloud Egress Cost Setup Complexity Best For

BigQuery Omni Local Querying Local to AWS/Azure Near Zero (only query results return to GCP) Moderate (OIDC IAM trust setup) In-place exploratory analytics, ad-hoc queries, local aggregation pipelines

BigQuery Omni SQL Export Local to AWS/Azure Extremely Low (egress charged only on condensed output) Moderate (Cross-cloud storage bucket access) Feeding summarized remote metrics into a centralized BigQuery warehouse

Cloud Storage Transfer Service / DTS Moves data to GCP High (full raw data egress paid to source cloud) Low (managed UI/API connectors) Workloads requiring deep integration with BigQuery ML, Search Indexing, or sub-second BI

3rd-Party ETL (e.g., Fivetran) Intermediate engine High (egress + vendor compute fees) Low Multi-source SaaS integration with small-to-medium volumes

Open-Source Trino / Presto Self-hosted VMs/GKE Very High (cross-cloud shuffle egress) Very High (cluster tuning, VPC peering) Legacy architectures with existing distributed Hadoop investments

  • Concrete Exam Scenario
  • Scenario: Global E-Commerce Telemetry Consolidation

A global e-commerce enterprise runs its microservices on AWS Elastic Kubernetes Service (EKS) in us-east-1 , writing 40 TB of uncompressed JSON application logs daily to an Amazon S3 bucket ( s3://production-event-logs-useast1/ ). The central data analytics and executive reporting team operates in Google Cloud BigQuery in the us-central1 multi-region.

The Vice President of Analytics demands a daily executive dashboard showing error rates per microservice, total transactions, and gross merchandise value (GMV). The cloud finance team issues an immediate freeze on cross-cloud network spend after the previous month's data replication pipeline generated over $35,000 in AWS data transfer out charges.

The Incorrect Approaches

Approach A (Full Replication): Setting up a continuous Cloud Storage Transfer Service job to copy all 40 TB/day into a GCS bucket in us-central1 . This fails the cost mandate immediately because AWS will bill full egress fees on 1.2 petabytes per month.

Approach B (Cross-Cloud SQL Join): Creating a BigQuery Omni table over S3 in aws-us-east-1 and writing a scheduled query in BigQuery us-central1 that directly joins the AWS Omni table with the GCP marketing table. This fails because BigQuery rejects cross-region joins.

The Certified Data Engineer Solution

Deploy BigQuery Omni Connection: Establish an Omni connection in Google Cloud with location aws-us-east-1 . Obtain the Google service account unique ID.

Configure OIDC Trust in AWS: In AWS IAM, create a role trusting accounts.google.com with the Google service account ID. Attach an IAM policy granting s3:GetObject and s3:ListBucket on s3://production-event-logs-useast1/ .

Define BigLake External Table in Omni Dataset: Create a BigQuery dataset in aws-us-east-1 . Define a BigLake table pointing to the S3 bucket using the connection.

Execute Local Aggregation and SQL Export: Schedule a daily BigQuery SQL job in the aws-us-east-1 region using EXPORT DATA OPTIONS . The query aggregates the 40 TB of raw logs down to microservice error counts and daily GMV (generating a 15 MB Parquet file) and writes it directly to gs://central-executive-metrics-uscentral1/daily_aggregates/*.parquet .

Load to Central BigQuery: A native BigQuery table in us-central1 reads the 15 MB daily Parquet file. Total monthly egress charges drop from $35,000 to less than $5.00, satisfying all executive latency, governance, and financial constraints.

Common Exam Pitfalls and Gotchas

Pitfall 1: Expecting BigQuery Omni to Join Across Clouds in One Step: Exam questions often test whether you can run SELECT * FROM omni_aws_table JOIN gcp_native_table . You cannot. You must materialize or export intermediate data to the target cloud first.

Pitfall 2: Attempting to Use Long-Lived AWS Access Keys: Any option suggesting you create an AWS IAM User, generate an Access Key and Secret Key, and upload them to BigQuery is an anti-pattern. BigQuery Omni mandates OIDC web identity federation.

Pitfall 3: Billing and Capacity Misunderstandings: Query processing in BigQuery Omni requires BigQuery editions (Enterprise or Enterprise Plus) with slot reservations created in the specific Omni region (e.g., aws-us-east-1 ). On-demand per-TB pricing models do not operate identically to standard multi-regions.

Pitfall 4: Storage Account Network Firewalls: If an Azure Storage account or AWS S3 bucket has public access completely blocked without allowing the trusted Microsoft/AWS identity endpoints, BigQuery Omni will fail with connection timeout errors. S3 bucket policies must permit the assumed IAM Role ARN.

Loading diagram... BigQuery Omni Multi-Cloud Processing and Low-Egress Aggregation Pattern