Prepare and test your skills

Prepare and test your skills

Advanced Analytics and Integrated Machine Learning in BigQuery (opens in a new tab) means high-speed analysis and machine learning happen together inside one serverless warehouse. BigQuery is a fully managed, AI-ready data platform with a scalable, distributed engine that can query terabytes in seconds and petabytes in minutes with zero infrastructure management. Analysts use familiar SQL and Python to run business intelligence, ad hoc exploration, geospatial analysis, and machine learning on structured and unstructured data where the data already lives.
High-Speed Analytics with Standard SQL in BigQuery is the ability to run large-scale queries with ANSI-standard GoogleSQL directly where data is stored. A query flows from the user to the serverless compute layer, the compute layer pulls data from the separate storage layer over Google's petabit-scale network, and results flow back to the user without provisioning servers. GoogleSQL supports joins, nested and repeated fields, aggregation and analytic functions, multi-statement queries, and spatial functions for geospatial analysis.
BigQuery analytics also connects querying to sharing and visualization. Analysts can create views to share results, use BI Engine with Looker, Looker Studio, Google Sheets, Tableau, and Power BI, and work in BigQuery Studio with Python notebooks and version control for notebooks and saved queries. Data does not always have to be loaded first, because federated queries and external tables let a query run against data where it lives in Cloud Storage, Bigtable, Spanner, or Google Sheets.
BigQuery supports four related uses in one place:
BigQuery ML (opens in a new tab) is the integrated capability that lets teams create, evaluate, and run machine learning models with SQL inside the warehouse. A model is created with a SQL query and stored in a BigQuery dataset like tables and views, so training data never has to leave BigQuery for a separate Python framework. SQL practitioners can then generate predictions with SQL and use the results in existing business intelligence tools, spreadsheets, and notebooks.
The in-warehouse workflow follows a fixed order where each step depends on the one before it:
Teams can work with BigQuery ML through the Google Cloud console query editor and model UI, the bq command-line tool, the BigQuery REST API, integrated Colab Enterprise notebooks, and external tools such as Jupyter notebooks or business intelligence platforms. BigQuery ML can also access Vertex AI platform models and Cloud AI APIs for tasks such as text generation and translation.
Why Bringing ML to the Data Matters is that training and prediction compute comes to the warehouse instead of copying large datasets to another system. This approach reduces complexity because fewer tools and data moves are required, and it increases speed to production because reformatting data for outside frameworks is avoided. It also lets data analysts who understand the data but do not code in Python or Java build predictive models that guide business decisions.
Exam tip: Choose BigQuery ML when the model should be trained and run with SQL where the warehouse data already lives; moving and reformatting data for an outside Python framework is not required.
Multicloud Data Analysis with BigQuery Omni (opens in a new tab) is the ability to analyze data stored in other clouds through one BigQuery interface without moving the data. BigQuery Omni runs analytics on data stored in Amazon Simple Storage Service (Amazon S3) or Azure Blob Storage by running BigQuery compute locally to that storage. Because data stays in place, organizations avoid data movement and egress costs while reducing data silos.
How BigQuery Omni Works is that serverless BigQuery analytics is extended to external cloud storage through a reference table and local compute. An organization first connects BigQuery to Amazon S3 or Azure Blob Storage, then creates a BigLake table (opens in a new tab) that references the external data. The query runs in a BigQuery Omni region, which places compute near the external storage, and results can be joined with data in a standard BigQuery region or moved into BigQuery when needed.
The workflow follows this order:
This order keeps data in place for analysis and avoids movement and egress costs. If data must be brought into BigQuery, transfer tools can move results or load data directly, so the choice depends on whether the organization needs to query data in place or bring it into BigQuery.
BigQuery Omni Tools for Different Needs are four options for one-time querying, repeated replication, or moving data into BigQuery. The right tool depends on whether data should stay in place, be replicated incrementally, or be moved into BigQuery. The tools differ in what they do and when to use them:
| Tool | What it does | Suggested use |
|---|---|---|
| BigQuery Omni joins | Runs a query from a BigQuery region that can join data from a BigQuery Omni region | One-time querying where external data is joined with local tables or data is joined between two BigQuery Omni regions, such as AWS and Azure Blob Storage, when data is not large and caching is not key |
| BigQuery Omni materialized views | Uses materialized view replicas to continuously replicate data from BigQuery Omni regions and supports data filtering | Repeated or scheduled queries that continuously transfer external data incrementally when caching is a key requirement |
| BigQuery Omni transfer using SELECT | Runs CREATE TABLE AS SELECT or INSERT INTO SELECT in a BigQuery Omni region and moves the result to a BigQuery region | Moving query results from a BigQuery Omni region into a BigQuery region |
| BigQuery Omni transfer using LOAD | Uses LOAD DATA statements to load data directly from Amazon S3 or Azure Blob Storage into BigQuery | Loading external data directly into BigQuery |
Strategic Advantages for Multicloud Analysis with BigQuery Omni come from analyzing data where it lives. Because compute runs locally to storage on AWS or Azure, data can stay in Amazon S3 or Azure Blob Storage without moving between clouds. This avoids egress costs, lets teams query multiple clouds through a unified interface, and supports cross-cloud analysis by joining data between two different BigQuery Omni regions, such as AWS and Azure Blob Storage regions. BigQuery Omni is part of the serverless, fully managed BigQuery platform, so users do not manage servers or infrastructure, though it may not be available with reservations created with certain BigQuery editions.
Exam tip: BigQuery Omni joins analyze external data in place for one-time use, while materialized views continuously replicate external data incrementally when caching is important.
The Impact of Serverless and Managed Infrastructure in BigQuery (opens in a new tab) is that teams focus on data and decisions instead of servers, scaling, and maintenance. BigQuery is a fully managed, serverless data warehouse and analytics platform where teams use familiar SQL and Python to load, process, and analyze data. Google handles the underlying systems, so a team can start querying in minutes and grow from small tests to very large analysis without redesigning infrastructure.
What Serverless Means in BigQuery is that there are no servers to provision, no capacity to manually scale, and no software updates to manage. The BigQuery engineering team handles updates and maintenance behind the scenes, and resources scale automatically with the size of the workload. This model leaves analysts, engineers, and scientists free to focus on delivering value instead of traditional database management tasks.
Serverless operation removes three traditional responsibilities from the user:
How Separation of Storage and Compute Enables Scaling in BigQuery is that the storage layer and compute layer operate and scale independently. The storage layer ingests, stores, and optimizes data, while the compute layer provides distributed analytics power for queries. The two layers communicate over Google's petabit-scale network, so each layer can dynamically allocate resources without impacting the performance or availability of the other.
Analyst app queries BigQuery warehouse, which uses isolated compute and storage layers linked by Google's petabit network, unlike a legacy database shared pool.
This separation is different from legacy databases, which force different kinds of work to share one resource pool:
| System | How resources work | Effect on performance |
|---|---|---|
| Legacy databases | Read, write, analytical, and management tasks share resources | Queries slow down during writes or permission changes because of resource conflicts |
| BigQuery | Storage layer and compute layer allocate resources independently | Queries and storage work scale without blocking each other |
Storage and compute improvements can also be deployed independently without downtime or loss of performance. That independence lets the platform evolve faster while users experience a continuously available warehouse.
Why Managed Infrastructure Improves Performance and Availability in BigQuery is that automatic scaling and managed storage work together without manual tuning. The scalable, distributed analysis engine expands compute automatically for each query, which allows querying terabytes in seconds and petabytes in minutes. Storage uses a columnar format optimized for analytical queries and is automatically replicated across multiple locations to provide high availability.
Together, automatic scaling and managed storage mean performance stays consistent as data grows and as more queries run at the same time. Teams do not balance storage against query power or plan maintenance windows, since the storage layer keeps data durable and available while the compute layer grows to meet demand.
Exam tip: BigQuery separates storage from compute so each scales independently, while legacy databases share one pool for reads, writes, and analytics.
BigQuery ML is the integrated capability that lets teams create, evaluate, and run machine learning models with SQL inside the BigQuery warehouse. A model is created with a SQL query and stored in a BigQuery dataset like tables and views, so training data never has to leave BigQuery for a separate Python framework. The workflow follows train, then evaluate model quality against held-out data, then predict by sending new warehouse data to the stored model and returning predictions as query output.
BigQuery Omni analyzes multicloud data by running BigQuery compute locally to storage in Amazon Simple Storage Service (Amazon S3) or Azure Blob Storage so data stays in place. An organization connects BigQuery to the external storage, creates a BigLake table that references the external data, and runs analytics in a BigQuery Omni region where compute runs near that storage. This avoids data movement and egress costs while allowing results to be joined with standard BigQuery tables or moved into BigQuery when needed.
Serverless in BigQuery means there are no servers to provision, no capacity to manually scale, and no software updates to manage because Google handles updates, maintenance, and automatic scaling. Storage and compute operate and scale independently and communicate over Google's petabit-scale network, with storage ingesting and optimizing data while compute provides distributed analytics power. This separation lets BigQuery query terabytes in seconds and petabytes in minutes without queries and storage work blocking each other, unlike legacy databases where reads, writes, and analytics share one resource pool.
A retail enterprise stores petabytes of historical transaction data in Amazon S3 and Azure Blob Storage while running its primary analytics workloads in Google Cloud. The company wants to analyze the data stored in these external clouds using standard GoogleSQL queries, subject to the following requirements:
How does BigQuery Omni help the enterprise meet these requirements?
By using the BigQuery Data Transfer Service to replicate the raw tables into standard BigQuery storage on an automated schedule without incurring transfer costs
By running the serverless BigQuery query engine directly in the external cloud regions where the data resides, processing queries in-place without moving the raw data
By provisioning customer-managed virtual machine clusters inside the organization's external cloud accounts to preprocess and export data to Cloud Storage
By establishing a dedicated Cross-Cloud Interconnect link to stream raw datasets into Google Cloud compute regions without triggering outbound bandwidth charges
Last updated