BigQuery Omni in Google Cloud
A beginner-friendly guide to analyzing data that lives in other clouds, using the familiar BigQuery interface and SQL, without first copying that data into Google Cloud.
Imagine a global company that keeps its sales records in one warehouse, its customer data in another, and its website logs in a third, each belonging to a different landlord in a different city. To answer a simple question such as “which customers bought what, and from which web page did they arrive?”, an analyst would normally have to drive to every warehouse, copy the papers, and bring them back to one office. That is slow, costly, and sometimes not even allowed.
A smarter idea is to send your analyst to the warehouses with a portable desk, so that the reading happens where the papers already sit. BigQuery Omni is that portable desk: it takes BigQuery’s analytics engine to data that lives in other clouds. This guide teaches it from zero, in simple words, with an analogy for each big idea.
1What Is BigQuery Omni?
First the basic words, then the service.
What is BigQuery?
BigQuery is Google Cloud’s serverless data warehouse. A data warehouse is a system built to answer analytical questions over huge amounts of data using SQL, the common language for asking questions of tables. “Serverless” means you do not set up or manage servers. You write a query and BigQuery finds the computing power to run it.
The multi-cloud reality
Many companies use more than one cloud. They may run an online store on Amazon Web Services (AWS), a business system on Microsoft Azure, and analytics on Google Cloud. Mergers, history, and team choices all lead to multi-cloud setups. Their data is then scattered across cloud storage systems such as Amazon S3 and Azure Blob Storage.
What is BigQuery Omni?
BigQuery Omni is a multi-cloud analytics solution. It lets you run BigQuery queries on data stored in Amazon S3 or Azure Blob Storage, using the same BigQuery console, the same SQL, and the same security model, without first moving that data to Google Cloud. The word “Omni” means “all”, hinting at analytics across all clouds.
BigQuery Omni is a mobile analytics team that travels to other buildings, sets up its familiar tools inside each building, studies the documents where they sit, and sends back only the summary, instead of carrying the whole filing room back to headquarters.
console and SQL
Google, AWS, Azure
just to query
BigQuery Omni is evolving. Supported regions, features, query limits, and pricing options change over time, and some BigQuery features are not available on Omni. This guide teaches the ideas and avoids exact limits. Check the current Google Cloud documentation before planning.
2Why Does BigQuery Omni Exist?
The problems of data stuck in other clouds.
Data gravity
Large data is hard to move. This idea is sometimes called data gravity: the bigger a pile of data becomes, the more it pulls applications and services toward it, and the harder it is to relocate. Moving terabytes between clouds takes time and effort.
The cost of copying
- Egress fees: clouds usually charge for data leaving them, so repeated copies can be expensive.
- Storage duplication: keeping the same data twice means paying for two copies.
- Stale data: copies become out of date unless pipelines keep them fresh, and pipelines require upkeep.
- Delays: insights wait for the copy to finish.
- Complexity: each copy pipeline is one more thing that can fail.
Rules and regulations
Some data is not allowed to leave a certain region, country, or provider because of privacy laws, contracts, or company policy. Copying it into another cloud may be forbidden.
Many tools, many skills
Analysts who know BigQuery’s SQL and tools would otherwise need to learn separate analytics tools for each cloud. Different tools also mean different security models and different reports.
The Omni answer
Omni lets you bring compute to the data. You query in place, in the cloud where the data lives, with one familiar toolset. You only move small results, such as summaries, when you truly need them.
Imagine a library whose rare books must not leave their building. Rather than photocopying thousands of pages, a visiting scholar reads them on site and takes home only short notes. Omni is that visiting scholar.
Query in place
Avoid bulk copies and the fees and delays they bring.
Data stays where it lives
Helpful when rules say data must remain in a region or provider.
Same SQL and tools
Analysts use the BigQuery they already know.
Current data
Queries read the latest data in the source storage.
3Core Concepts You Must Know
The vocabulary of BigQuery Omni.
Omni location
BigQuery datasets live in a location. For Omni, there are special locations that correspond to AWS and Azure regions, such as an AWS region in the United States or an Azure region in the United States. Your BigQuery dataset is created in that location, so that the compute runs in the same cloud and region as the data.
BigLake table
A BigLake table is a table definition that points to files in object storage, such as Parquet or CSV files in an S3 bucket, and lets BigQuery query them with SQL. BigLake adds fine-grained security and performance features on top of these external files. For Omni, BigLake tables are the main way to read S3 and Blob Storage data.
Connection
A connection is a secure link that allows BigQuery to read your data in the other cloud. For AWS, it is based on an AWS identity role that BigQuery is allowed to assume. For Azure, it uses an application identity registered in your Azure tenant. A connection means you do not store passwords in your queries, and you can limit what the link can read.
Cross-cloud operations
- Query in place: run SQL over data in S3 or Blob Storage, with results shown in BigQuery.
- Cross-cloud transfer: copy selected data or query results from the other cloud into BigQuery in Google Cloud when you need them for joins with Google Cloud data.
- Cross-cloud join: combine data from other clouds with data in Google Cloud in a single query, using specific supported methods.
- Export: write query results out to S3 or Blob Storage.
Metadata caching
Metadata caching stores information about the files, such as their names and sizes, so queries do not need to list thousands of files each time. This makes queries on large file collections faster.
An Omni location is the building where your visiting team works. A BigLake table is the index card that says “these boxes of files on shelf B hold the sales data”. A connection is the visitor badge that lets the team into that building. Metadata caching is a pre-written shelf list so the team does not recount the boxes every time.
Location places compute next to data. Connection grants safe access. BigLake tables describe the files. Cross-cloud operations move only what you choose.
4Architecture and Components
How Omni brings BigQuery to other clouds.
The two planes again
Like other Google Cloud services, Omni separates a control plane, where you work and where the service is managed, from a data plane, where the data is processed. Omni’s control plane stays in Google Cloud: you use the same console, API, and command line. Its data plane, the compute that reads data and runs queries, runs in the other cloud, close to your data.
How the compute runs
Omni runs BigQuery’s query engine on Kubernetes-based infrastructure inside the AWS or Azure region, using technology from Google’s multi-cloud platform. Google manages this infrastructure. You do not create clusters or manage servers there. Your data does not leave the storage account. The query workers read it where it sits.
Main components
- BigQuery console and API: where you write and manage queries.
- Omni control plane in Google Cloud: routes your job and manages the service.
- Omni data plane in AWS or Azure: the managed compute that executes queries next to the data.
- Object storage: Amazon S3 or Azure Blob Storage holding your files.
- Connection and identity: secure access using AWS roles or Azure application identities.
- BigLake tables and datasets: definitions in BigQuery that map to the files.
- Result storage and transfer: where query results are held or sent.
flowchart LR
USER["Analyst: BigQuery console, SQL, BI tools"] --> CP["BigQuery control plane in Google Cloud"]
CP --> DP1["Omni data plane in AWS region"]
CP --> DP2["Omni data plane in Azure region"]
DP1 --> S3["Amazon S3: Parquet, CSV, JSON files"]
DP2 --> BLOB["Azure Blob Storage: files"]
DP1 --> RES["Query results"]
DP2 --> RES
RES --> USER
The control plane is the head office that receives the analyst’s request and manages the project. The data plane is the field office in another city, which actually reads the documents on site. The head office never needs the whole filing room, only the finished report.
5How a Query Works Internally
The story of one query from your keyboard to your result.
Set up once
You create a connection to AWS or Azure, grant it read access to the right storage, and create a dataset in the Omni location with a BigLake table pointing to the files.
You write SQL
In the BigQuery console or any tool, you write a normal SQL query against the BigLake table.
The job is routed
The control plane sees that the table lives in an Omni location and sends the job to the matching data plane in AWS or Azure.
Compute reads the files
Query workers in the other cloud read the needed files, using the connection’s permissions, and apply filters and calculations locally.
Results are produced
The engine combines worker output into a result set.
You see the results
The result appears in BigQuery. You can view it, save it, export it to the cloud storage, or transfer it to Google Cloud for further use.
sequenceDiagram
participant A as Analyst
participant C as BigQuery control plane
participant D as Omni workers in AWS
participant S as Amazon S3
A->>C: Run SQL on BigLake table in AWS location
C->>D: Send query job to AWS data plane
D->>S: Read needed Parquet files using connection role
S-->>D: File data
D->>D: Filter, join, and aggregate locally
D-->>C: Result set
C-->>A: Show results in console
Only what is needed is read
With columnar formats such as Parquet, workers read only the columns the query needs, and may skip whole file sections using stored statistics. Partitioned folder layouts, such as one folder per date, let the engine skip folders that do not match a date filter. This saves time and cost.
It is like asking a librarian to find all mentions of one name. A good index lets the librarian open only the relevant pages instead of reading every book from cover to cover.
6Setting Up: Connections and Tables
The one-time preparation that makes everything work.
Setup in plain steps
- Choose the cloud, region, and bucket where your data lives, and note the file formats.
- Create a connection in BigQuery for that cloud and region. BigQuery gives you an identity to trust.
- Create the identity and access policy on the other cloud, such as an AWS role or an Azure application, that allows read access to only the needed bucket or container, and trusts the BigQuery identity.
- Create a dataset in the matching Omni location.
- Create a BigLake table in that dataset that points to the files, with the format and optionally the partitioning and the metadata cache.
- Grant BigQuery permissions to analysts through IAM, with optional row and column security.
- Test with a small query.
Supported file formats
Typical formats include Parquet and ORC, which are columnar and efficient, plus Avro, CSV, and JSON. Columnar formats give the best speed and cost. Check the current documentation for the full list.
Tools that can query
Because Omni data appears as BigQuery tables, any tool that works with BigQuery can use it, such as the console, command line, client libraries, notebooks, and BI tools like Looker. Some tools may have restrictions for Omni locations, so check compatibility.
Setting up a connection is like arranging visitor access for a partner company: the receptionist in their building creates a badge that opens only the rooms you need, and you agree on exactly who may use that badge.
Give the cloud-side role read-only access to the specific bucket or folder needed. Never give a connection broad administrator power across your whole AWS account or Azure subscription.
7Cross-Cloud Transfers, Joins, and Materialized Views
Combining data from different clouds.
Why combine?
Often the real insight comes from joining data across clouds: web logs in AWS with customer records in Google Cloud, for example. Omni offers several patterns, each with different trade-offs.
Pattern 1: query in place, move only results
Run a query in the Omni location that filters and aggregates the large dataset to a small result, then use only that result. This is the cheapest and most common approach.
Pattern 2: cross-cloud transfer
Use SQL commands to load data from S3 or Blob Storage into a BigQuery table in Google Cloud, or to create a table in Google Cloud from a query on an Omni table. You decide which subset to copy, so you can avoid moving full raw data. Cross-cloud transfers have limits on size and incur network charges, so use filters.
Pattern 3: cross-cloud join
Some features let you write a single query that joins a table in an Omni location with a table in a Google Cloud location. Under the hood, a smaller side is moved to the other location for the join. Because this moves data, it is best when one side is small, or after you have reduced it with filters.
Pattern 4: cross-cloud materialized views
A materialized view stores the precomputed result of a query and keeps it fresh. Cross-cloud materialized views allow you to keep a regularly updated, summarized copy of Omni data in Google Cloud, so dashboards read from a fast, local table while the raw data remains in the other cloud. This reduces repeated transfer.
flowchart TD
RAW["Raw logs in Amazon S3: very large"] --> Q["Omni query: filter and aggregate in AWS"]
Q --> SUM["Small summary result"]
SUM --> MV["Cross-cloud materialized view or transfer into BigQuery in Google Cloud"]
CUST["Customer tables in Google Cloud BigQuery"] --> JOIN["Join in Google Cloud"]
MV --> JOIN
JOIN --> DASH["Dashboards and reports"]
Cross-cloud work is like two offices working on one report. The best method is for each office to summarize its own papers and send over a short page, rather than faxing thousands of pages across the country.
Always reduce data before it crosses clouds. Filter by date, select only needed columns, and aggregate where possible. This cuts transfer time, network charges, and query cost.
8Security and Governance
One security model across clouds.
Identity and access
- BigQuery IAM controls who can query datasets and tables, create connections, and manage Omni resources, using Google identities.
- Cloud-side roles control what BigQuery’s connection can read in AWS or Azure.
- Separation of duties: cloud administrators control the storage-side access, while data teams control BigQuery-side access.
Fine-grained access
BigLake tables support row-level security, where a user sees only the rows they are allowed to see, and column-level security, where sensitive columns are hidden or masked. Dynamic data masking can show partial values, such as the last four digits of a number. The key benefit is that analysts do not need direct access to the raw files in S3 or Blob Storage. They query through BigQuery, which enforces the policy.
Encryption
Data in the storage accounts stays under the other cloud’s encryption and key management, which you control. Data in transit between components is encrypted. Check the current documentation for options such as customer-managed keys, which differ by feature.
Data location and sovereignty
Because the compute runs in the same cloud region as your data, raw data does not leave that region during query processing. This can help satisfy data residency rules. Only results leave, and you decide where to send them. Always review what results contain.
Auditing and governance
Query activity is recorded in Cloud Audit Logs. Dataplex and the data catalog capabilities can describe and govern Omni datasets, adding labels, owners, and policies. Use consistent labels to track data across clouds.
Row and column security are like a visitor card that opens only certain drawers in a cabinet, and shows blurred versions of private pages. Visitors get the information they need without seeing everything stored in the building.
Even if raw data stays in place, query results leave the region. Check that results do not include personal or regulated fields that should not cross borders, and apply masking and filters before exporting or transferring them.
9Performance and Optimization
Making Omni queries fast and cheap.
Use columnar, compressed formats
Parquet and ORC files let BigQuery read only needed columns and skip chunks of data. CSV and JSON force reading everything. Converting to Parquet is one of the biggest speed gains.
Partition your data
Organize files into folders by a commonly filtered field, such as date. Queries that filter by that field read only the matching folders. Use hive-style partitioning, where folder names look like “date=2025-03-01”, so BigQuery recognizes them as partition columns.
Use metadata caching
For tables with a very large number of files, enable metadata caching so the engine does not list files on every query. Choose a refresh schedule that matches how often files change.
File sizes
Aim for medium-to-large files, rather than millions of tiny files. Many tiny files slow queries because of the overhead of opening each one. A common guide is files in the range of tens to hundreds of megabytes, though the best size depends on your data.
Write good SQL
- Select only the columns you need, never “all columns” on a wide table.
- Filter early, especially on partition columns.
- Aggregate before joining across clouds.
- Use query previews and dry runs to estimate bytes processed before running large queries.
Capacity
Omni compute has capacity models, either on demand or reserved, depending on your pricing choice. Reserved capacity gives predictable performance for steady workloads. Check current documentation for how slots work in Omni locations.
Good file layout is like a well-organized filing cabinet with labeled drawers by date. Asking for last week’s receipts means opening one drawer. A pile of loose paper means searching the whole room.
10Limitations and Things to Watch
Omni is powerful but not identical to regular BigQuery.
Feature differences
Omni supports a large subset of BigQuery SQL and features, but not all of them. Some capabilities available in standard BigQuery regions may not be available in Omni locations, such as certain functions, table types, loading methods, or integrations. This list changes as the product grows, so always check the current limitations page before relying on a feature.
Limits on results and transfers
Query result sizes returned to the console and transfer sizes may be limited. Queries are expected to reduce large raw data to manageable results. Plan for aggregation rather than fetching billions of rows.
Region coverage
Omni is available in selected AWS and Azure regions, not all of them. If your data is in a region that is not supported, you may need to use a nearby supported region or copy data.
Cross-cloud networking and charges
Moving results or data between clouds can create network egress charges from the source cloud. Omni does not remove the other cloud’s fees for data leaving, so keep transfers small and targeted.
Operational dependencies
Omni depends on the other cloud’s storage and identity systems. If those have an outage or a permission change, Omni queries may fail. Make sure the right teams in each cloud are involved.
Omni is good at
- Querying large files in place
- Aggregating before moving data
- Keeping raw data in its home region
- Reusing BigQuery skills and tools
Omni is not ideal for
- Returning huge raw datasets to Google Cloud
- Features not supported in Omni locations
- Workloads needing very low-latency lookups
- Regions without Omni support
The mobile analytics team can bring most of its tools to other buildings, but not every single machine. Before starting a project, check the equipment list so you are not surprised on site.
11Monitoring, Logging, and Cost
Keeping watch on activity and spend.
Monitoring and logging
- Job history: BigQuery shows every query, its status, duration, and bytes processed, including Omni jobs.
- INFORMATION_SCHEMA views: special views let you analyze job and table metadata with SQL.
- Cloud Logging and audit logs: record who ran queries and who changed settings.
- Cloud Monitoring: dashboards and alerts for slot usage and query errors where available.
- Cloud-side logs: AWS and Azure record access to storage, which helps verify what Omni read.
What drives cost
- Query compute: charged either by the amount of data processed, called on demand, or by reserved capacity, depending on your plan and the current Omni pricing options.
- Storage in the other cloud: you keep paying AWS or Azure for the data itself.
- Cross-cloud transfers: data movement fees from the source cloud and any charges in BigQuery.
- Metadata caching and materialized views: small additional costs for storing and refreshing.
- Downstream use: storing transferred data in BigQuery and running dashboards.
Omni pricing options and regions change, so use the pricing page and calculator.
Ways to save
- Use columnar formats and partitioning to reduce data read.
- Aggregate before transferring or exporting.
- Avoid repeated full-table scans. Use materialized views for repeated summaries.
- Set cost controls, such as maximum bytes billed per query, and budgets and alerts.
- Choose reserved capacity for steady, predictable workloads.
- Delete unneeded copies and old materialized views.
On-demand pricing is like paying a taxi per kilometer: great for occasional trips. Reserved capacity is like hiring a driver for the month: cheaper per trip if you travel daily.
12Advantages, Disadvantages, and Trade-offs
An honest report card.
Advantages
- Query AWS and Azure data with familiar BigQuery SQL
- No bulk copying just to analyze
- Data stays in its home cloud and region during processing
- Single security and governance approach
- Cross-cloud joins and materialized views
- Reduces duplicated pipelines
- Works with BigQuery tools, including BI and notebooks
Disadvantages
- Not every BigQuery feature is available
- Limited to supported clouds and regions
- Cross-cloud movement still has fees and size limits
- Needs setup work on both clouds
- Performance depends on file formats and layout
- Pricing and features evolve
Compared with neighbors
| Option | Style | Pick it when |
|---|---|---|
| BigQuery Omni | Query data in place in AWS and Azure with BigQuery | You want BigQuery analytics over multi-cloud object storage without bulk copies |
| Standard BigQuery with loaded data | Copy data into BigQuery storage | You want maximum performance and the full feature set, and moving the data is acceptable |
| BigQuery external tables or BigLake in Google Cloud | Query Cloud Storage data in place | Your files are already in Google Cloud Storage |
| Storage Transfer Service | Bulk copy between clouds | You decide to move the data permanently or for heavy repeated use |
| Amazon Athena or Azure Synapse | Native query services in other clouds | Your analytics are fully centered in that cloud |
| Data virtualization or lakehouse tools from other vendors | Cross-platform query engines | You standardize on a third-party engine across clouds |
13Design Patterns and Anti-patterns
Patterns that work, and traps to avoid.
Good patterns
Summarize at the source
Aggregate big data in place, and move only summaries.
Hot and cold split
Keep frequently used summaries in BigQuery on Google Cloud, with raw data left in the other cloud.
Unified semantic layer
Use Looker or similar tools to give business users one view across clouds.
Regional residency
Keep regulated raw data in its required region and share only approved results.
Governed access layer
Let analysts query only through BigQuery with row and column rules, not through raw storage.
Gradual migration
Analyze in place first, and copy over only the data you truly need later.
flowchart TD
AWS["AWS: S3 raw data"] --> OM1["Omni queries and summaries"]
AZ["Azure: Blob raw data"] --> OM2["Omni queries and summaries"]
GC["Google Cloud: BigQuery core tables"] --> BQ["BigQuery in Google Cloud"]
OM1 --> BQ
OM2 --> BQ
BQ --> LK["Looker or BI tools"]
BQ --> ML["Vertex AI and notebooks"]
BQ --> GOV["Dataplex: catalog and governance"]
Anti-patterns
What people do
They use Omni to pull entire raw tables back to Google Cloud every day.
Why it hurts
It recreates the heavy copying and egress costs that Omni is meant to avoid.
Do this instead
Aggregate in place, and transfer only needed subsets or use materialized views.
What people do
They leave data as millions of tiny CSV files with no partitioning.
Why it hurts
Queries read far more data than needed and run slowly at high cost.
Do this instead
Convert to Parquet, compact files, and partition by common filters.
What people do
They give the connection broad administrator rights on the other cloud.
Why it hurts
A mistake or compromise could expose far more than the intended data.
Do this instead
Grant read-only access to the specific buckets or containers required.
14Best Practices, Common Mistakes, and Real-World Uses
Your checklist, the usual stumbles, and where Omni shines.
Best practices
- Start with a clear question that benefits from querying in place.
- Check region and feature support first.
- Prepare data well: columnar formats, sensible partitions, and reasonable file sizes.
- Use least-privilege connections and review them regularly.
- Apply row and column security for sensitive data.
- Reduce data before moving it across clouds.
- Enable metadata caching for large file collections.
- Set cost controls and alerts on queries and transfers.
- Document ownership across clouds, including who manages each connection.
- Test with samples before large production queries.
Common mistakes
Wrong location for the dataset
The dataset is created in a standard region instead of the Omni location that matches the bucket, so the setup fails. Match them carefully.
Permission mismatches
Queries fail because the cloud-side role does not trust the BigQuery connection identity, or lacks list and read permissions. Re-check both sides.
Assuming every feature works
A team builds a pipeline around a function that is not supported in Omni locations. Review limitations first.
Ignoring egress charges
A daily export of large results from AWS to Google Cloud creates unexpected network bills. Estimate transfer volumes.
Real-world uses
Retail with mixed clouds
A retailer keeps website clickstream data in S3 and sales data in BigQuery. Omni summarizes clicks by product in AWS, then joins the small summary with sales in BigQuery to measure conversion.
Financial services with residency rules
A bank must keep transaction files in a specific cloud region. Analysts query them through Omni with row and column security, and export only anonymized aggregates.
Mergers and acquisitions
After acquiring a company whose data lives in Azure, the analytics team gets fast insights from that data through Omni while a longer migration plan is prepared.
Log and security analytics
Cloud logs stored in S3 are analyzed in place to find suspicious patterns, avoiding the cost of copying massive log volumes.
Marketing analytics
Campaign data from multiple platforms stored in different clouds is combined into a single view for reporting by using summaries and cross-cloud materialized views.
Data science exploration
Data scientists explore large Parquet datasets in S3 with SQL, select useful features, and move only those into Google Cloud for model training.
15Frequently Asked Questions
16Summary and Key Takeaways
You started with an analyst who had to drive to every warehouse and copy the papers. You now understand that BigQuery Omni sends the analytics to the warehouse instead: you use familiar BigQuery SQL, while the work runs next to your data in AWS or Azure.
Key Takeaways
- BigQuery Omni is multi-cloud analytics that lets you query data in Amazon S3 and Azure Blob Storage with BigQuery, without bulk-copying it to Google Cloud first.
- Know the vocabulary: Omni locations place compute near the data, connections give secure access, and BigLake tables describe files for SQL.
- Control plane in Google Cloud, data plane in the other cloud: you use one console and API, and only results travel back.
- Move less, summarize first: aggregate and filter at the source, then use cross-cloud transfers, joins, or materialized views for small, targeted data.
- Prepare data for speed: use Parquet or ORC, partition by common filters, keep sensible file sizes, and enable metadata caching.
- Secure with layers: least-privilege connections, BigQuery IAM, row and column security, audit logs, and careful handling of results.
- Check limits and costs: not every BigQuery feature or region is supported, egress and storage fees remain, and details change, so confirm in the current documentation.