Cross-Cloud Analytics with BigQuery Omni: Querying S3 and Azure Data Without Copying It

Most BigQuery estates are not the whole picture. There is a bucket of Parquet in Amazon S3 that a SaaS vendor drops files into, an Azure Data Lake Storage container owned by a team that standardised on Microsoft, and a warehouse in Google Cloud where the reporting actually happens. The default answer — copy everything nightly into BigQuery — costs egress, adds hours of latency, and creates a second copy of data that someone will eventually have to govern.

BigQuery Omni is the other answer. It runs BigQuery compute in AWS and Azure regions, against data that never leaves those regions, and lets you join the results to your Google Cloud tables with ordinary GoogleSQL. This tutorial walks the full setup: connections, external tables, local queries, cross-cloud joins, CREATE TABLE AS SELECT transfers, and the cost and quota traps that catch teams the first month.

When Omni is the right tool (and when it isn't)

Use Omni when:

• The data is large, lives in S3 or ADLS, and only a filtered or aggregated slice is interesting to your Google Cloud reporting.
• Data residency or contractual rules say the raw data must stay in its current cloud/region.
• You want to retire a nightly copy job whose only job is to feed one dashboard.

Do not use Omni when:

• You need sub-second dashboards over that data. Omni queries pay a cross-cloud planning tax; BI Engine does not accelerate them.
• You need DML, streaming inserts, or Iceberg-style table maintenance on the external data. Omni external tables are read-oriented.
• The whole dataset is small and stable. A one-off LOAD DATA into a native BigQuery table is cheaper and faster to operate.

A useful rule: if you are reading more than about half the bytes every day anyway, transfer once and query natively. Omni pays off when the read is selective.

Step 1 — Create a connection to AWS

Omni authenticates with workload identity federation, not long-lived keys. You create a BigQuery connection, BigQuery hands you a Google identity, and you trust that identity in an AWS IAM role.

Create the connection in an Omni-enabled region (aws-us-east-1, aws-ap-northeast-2, azure-eastus2, and friends — the list grows, so check the docs for your region):

bq mk --connection \
  --connection_type='AWS' \
  --iam_role_id='arn:aws:iam::123456789012:role/bq-omni-reader' \
  --location=aws-us-east-1 \
  omni_aws_conn

Then read back the Google identity BigQuery generated for it:

bq show --connection --location=aws-us-east-1 omni_aws_conn

The output contains an identity field that looks like a long numeric subject. Paste it into the trust policy of bq-omni-reader in AWS:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Federated": "accounts.google.com" },
    "Action": "sts:AssumeRoleWithWebIdentity",
    "Condition": {
      "StringEquals": { "accounts.google.com:sub": "<identity-from-bq-show>" }
    }
  }]
}

Attach a permissions policy that grants only s3:GetObject and s3:ListBucket on the specific prefix you intend to expose. Resist the urge to grant the whole bucket; the connection is a standing cross-cloud trust and deserves least privilege.

Azure is the same shape with a different vocabulary: --connection_type='Azure', a tenant ID, and an Azure AD application you grant Storage Blob Data Reader on the container.

Step 2 — Create the dataset and the external table

The dataset must live in the same Omni region as the connection:

bq mk --location=aws-us-east-1 --dataset myproject:omni_aws

Now define an external table over the S3 prefix. Hive-style partition paths are worth using — they are what keeps you from scanning the entire history on every query:

CREATE OR REPLACE EXTERNAL TABLE omni_aws.orders_raw
WITH PARTITION COLUMNS (
  dt DATE
)
OPTIONS (
  format = 'PARQUET',
  uris = ['s3://acme-datalake/orders/*'],
  hive_partition_uri_prefix = 's3://acme-datalake/orders',
  require_hive_partition_filter = TRUE
);

Two options there earn their keep:

• hive_partition_uri_prefix tells BigQuery that s3://acme-datalake/orders/dt=2026-05-01/... encodes a dt column, so WHERE dt = '2026-05-01' prunes files instead of listing millions of objects.
• require_hive_partition_filter = TRUE makes an unfiltered query fail rather than silently scanning the lake. On a shared external table this single line has saved more budgets than any dashboard.

For CSV or JSON, add skip_leading_rows, an explicit schema, and max_staleness if you enable metadata caching. Metadata caching (metadata_cache_mode = 'AUTOMATIC') is strongly recommended for prefixes with many files: without it, every query re-lists objects in S3, and object listing — not scanning — is often the slow part.

Step 3 — Query locally, in place

A query that touches only the Omni dataset runs entirely in the AWS region:

SELECT
  dt,
  region,
  COUNT(*)          AS orders,
  SUM(order_total)  AS revenue
FROM omni_aws.orders_raw
WHERE dt BETWEEN '2026-05-01' AND '2026-05-31'
GROUP BY dt, region;

Nothing crossed a cloud boundary except the small result set. You are billed BigQuery Omni compute in that region (on-demand per TiB, or via an Omni reservation), plus whatever AWS charges for the GetObject requests.

Step 4 — Cross-cloud joins

Here is the part people actually want: joining S3 data to native BigQuery tables. Omni supports this, but understand the mechanics before you write the query. BigQuery runs the AWS-side portion of the plan in AWS, transfers only the intermediate result to your Google Cloud region, and finishes the join there.

Which means: the shape of your query determines your egress bill.

-- Good: aggregate on the remote side first, join a small result.
WITH aws_daily AS (
  SELECT dt, customer_id, SUM(order_total) AS revenue
  FROM omni_aws.orders_raw
  WHERE dt BETWEEN '2026-05-01' AND '2026-05-31'
  GROUP BY dt, customer_id
)
SELECT
  c.customer_name,
  c.segment,
  a.dt,
  a.revenue
FROM aws_daily a
JOIN `myproject.core.dim_customer` c
  USING (customer_id);

Compare that to the naive version — SELECT * FROM omni_aws.orders_raw JOIN dim_customer ... — which asks BigQuery to move every raw order row across the internet. Same result, wildly different cost. The discipline is the old distributed-query discipline: push filters and aggregations down, move only what you need up.

Check that it worked by reading the query plan; the EXPLAIN-style stages in the job details will show a cross-cloud transfer stage with a byte count. If that byte count is in the hundreds of gigabytes, rewrite the query.

There is also a hard ceiling: cross-cloud transfers have a per-query and per-day byte quota (documented, and revised periodically). Exceeding it fails the job rather than quietly costing you money — a merciful design, but one that will page someone at 3am if a partition filter regresses.

Step 5 — Cross-cloud materialisation, three ways

Sometimes the right answer is to land the slice in Google Cloud and query it natively afterwards.

CREATE TABLE AS SELECT across clouds. The simplest transfer: the source is Omni, the destination is a normal BigQuery dataset.

CREATE OR REPLACE TABLE `myproject.staging.orders_may`
AS
SELECT dt, customer_id, order_total, region
FROM omni_aws.orders_raw
WHERE dt BETWEEN '2026-05-01' AND '2026-05-31';

LOAD DATA from S3. For bulk backfills where you want the raw rows, LOAD DATA INTO with the Omni connection copies files without spending Omni slot time on a full scan:

LOAD DATA INTO `myproject.staging.orders_backfill`
FROM FILES (
  format = 'PARQUET',
  uris = ['s3://acme-datalake/orders/dt=2026-05-*/*']
)
WITH CONNECTION `myproject.aws-us-east-1.omni_aws_conn`;

Cross-cloud materialized views. Define a materialized view in your Google Cloud region over the Omni base table; BigQuery keeps the aggregate incrementally refreshed and you query the small, local copy. This is the best pattern for a dashboard that must be fast but whose source of truth is in another cloud.

Step 6 — Reservations, and why on-demand Omni surprises people

Omni compute is billed separately from your Google Cloud compute, and slot reservations do not span clouds. If you have a 2,000-slot Enterprise reservation in us, it does nothing for a query in aws-us-east-1. You either accept per-TiB on-demand pricing in the Omni region or buy an Omni reservation there.

Practical guidance:

• Start on-demand. Measure a month with INFORMATION_SCHEMA.JOBS scoped to the Omni region.
• If Omni spend is steady and daily, an Omni reservation with a small baseline and autoscaling usually wins.
• Tag Omni workloads so their cost is attributable. A label on the job (--label team:analytics) makes the JOBS view answer "who is spending this" without archaeology.

And the monitoring query you will want on day one:

SELECT
  DATE(creation_time) AS day,
  user_email,
  COUNT(*) AS jobs,
  SUM(total_bytes_processed) / POW(1024, 4) AS tib_processed
FROM `region-aws-us-east-1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY day, user_email
ORDER BY tib_processed DESC;

Note the region qualifier: region-aws-us-east-1, not region-us. Omni jobs do not appear in your Google Cloud region's INFORMATION_SCHEMA, which is the single most common reason teams think Omni is free for the first month.

Limitations worth writing on the whiteboard

• No DML on external Omni tables. Reads and CREATE TABLE AS SELECT only.
• No BI Engine acceleration for Omni queries. Materialize into Google Cloud first if you need sub-second response.
• No streaming ingestion into Omni datasets.
• Datasets cannot be joined across two different Omni regions in one step — an AWS table and an Azure table need a staging hop.
• Results of cross-cloud queries are subject to transfer quotas, and a query that exceeds them fails.
• Feature lag. New BigQuery SQL features generally reach us/eu before Omni regions. Check the release notes before you depend on something brand new.

A migration pattern that works

The most common successful use of Omni is not "permanent multi-cloud analytics" — it is staged migration.

1. Point an Omni external table at the AWS or Azure data as it sits today. No pipeline, no copy, no change to the producing team.
2. Build the reporting model in Google Cloud against a cross-cloud materialized view or a nightly CTAS. Prove the numbers match.
3. Cut consumers over to BigQuery.
4. Only then negotiate moving the producer — with working dashboards as evidence rather than a promise.

This sequence converts a political migration into a technical one, and it lets you deliver value in week one instead of quarter three.

Checklist before you call it production

• Partition filters required on every external table (require_hive_partition_filter = TRUE).
• Metadata caching enabled with a max_staleness your stakeholders have agreed to.
• IAM role scoped to one prefix, with the federated trust condition pinned to the connection's identity.
• Cross-cloud transfer bytes checked in the job plan for every recurring query.
• Cost monitoring query scheduled against the Omni region's INFORMATION_SCHEMA.
• A documented answer to "what happens if the AWS side becomes unavailable" — usually: the dashboard serves the last materialized view refresh.

---

Cross-cloud analytics is one of those capabilities that is technically straightforward and operationally easy to get wrong: the setup takes an afternoon, and the bill takes a month to teach you anything. If you are weighing Omni against a full migration, or you have a cross-cloud query that costs more than it should, get in touch — we do this work on a project basis, and a short architecture review is usually enough to tell which of the two you actually need.