Prepare and test your skills

Prepare and test your skills

BigQuery ML (opens in a new tab) is a capability of BigQuery that lets analysts build and run machine learning models directly inside the data warehouse by using standard SQL. A model created with BigQuery ML is stored in a BigQuery dataset like a table or view, so training data never has to leave the warehouse for a separate Python environment. This approach reduces complexity and speeds up development because data movement and reformatting are not required.
Analysts work with BigQuery ML through familiar interfaces such as the Google Cloud console, the bq command-line tool, the BigQuery REST API, and notebooks. Training data flows from BigQuery tables into the training statement, and prediction results flow from the stored model back into BigQuery tables. The stored model depends on warehouse tables for both training and scoring, which keeps ownership and governance inside BigQuery.
The SQL workflow in BigQuery ML is the ordered path that moves data from stored tables through training to prediction without leaving BigQuery. The workflow centers on the CREATE MODEL (opens in a new tab) statement for training and inference functions for scoring. The stored model depends on BigQuery tables, so governance stays with the warehouse data.
A typical model development workflow follows this order:
CREATE MODEL statement to train a model on a BigQuery query result.TRANSFORM clause when needed.Regression and classification models in BigQuery ML predict a label from similar labeled examples. Regression predicts a continuous number, while classification (opens in a new tab) assigns each row to one of a set of categories. Both types are trained with CREATE MODEL and scored with prediction functions such as ML.PREDICT.
| Model | Task | Use when |
|---|---|---|
| Linear regression | Regression | The label is a real-valued number, such as price or revenue, and cannot be positive infinity, negative infinity, or NaN |
| Logistic regression | Classification | The label is one of two or more categories, such as low-value, medium-value, or high-value, with up to 50 unique values |
Several other BigQuery ML model types support both regression and classification on tabular data:
DNN_CLASSIFIER) and regression.AUTOML_CLASSIFIER) and regression models on tabular data at high speed and scale.Other built-in models in BigQuery ML handle segmentation, recommendations, forecasting, and diagnosis beyond simple prediction. These other models are also created with CREATE MODEL and stored in BigQuery datasets, but the functions available for each model vary by type. Choose the model type based on the shape of the question, not just the desire for a prediction.
ARIMA_PLUS and ARIMA_PLUS_XREG automatically handle anomalies, seasonality, and holidays, while the built-in TimesFM model can be called through the AI.FORECAST function without managing a model.Evaluation in BigQuery ML measures how a model is likely to perform on data outside the training set and supports comparison between models. Evaluation data flows from either the held-out portion of the training query or from a new table or query into an evaluation function, and metrics flow back as a query result. For linear and logistic regression, boosted tree, random forest, DNN, Wide & Deep, and matrix factorization models, the evaluation set is controlled by DATA_SPLIT_METHOD, DATA_SPLIT_EVAL_FRACTION, and DATA_SPLIT_COL options. For k-means, PCA, autoencoder, and ARIMA_PLUS models, BigQuery ML uses all input data as training data and calculates metrics against the entire input dataset.
Two SQL functions cover most evaluation needs. ML.EVALUATE evaluates a stored BigQuery ML model, while ML.METRICS computes metrics for any classification or regression result that already contains actual and predicted values.
| Function | Requires a stored model | Input |
|---|---|---|
| ML.EVALUATE | Yes | No input returns metrics calculated during creation; specified input evaluates the model on a new dataset |
| ML.METRICS | No | Any table or query with actual and predicted columns, for evaluating predictions without creating or referencing a model |
Exam tip: Use ML.EVALUATE with no input to see training-time metrics, use ML.EVALUATE with input data to test on new data, and use ML.METRICS when there is no stored model, only actual and predicted values.
Data locality in BigQuery ML means training and running machine learning models inside BigQuery (opens in a new tab), where the data already lives, instead of exporting that data to a separate environment. BigQuery ML brings machine learning to the data rather than bringing the data to the model. Models are stored in BigQuery datasets, similar to tables and views, so the data and the model that learns from it stay in the same place.
Keeping machine learning inside BigQuery removes the need to move data out of the data warehouse before training a model. Because no separate environment has to receive and reformat the data, fewer tools are involved, which reduces complexity. Development also reaches production faster, since moving and formatting large amounts of data for Python-based frameworks is not required. Avoiding that transfer also reduces latency and minimizes the costs associated with data transfer.
BigQuery's architecture with separated storage and compute layers lets each layer scale without slowing the other. The architecture has two parts: a storage layer that ingests, stores, and optimizes data, and a compute layer that provides analytics capabilities. These layers operate independently and communicate over Google's petabit-scale network. In legacy databases, read and write operations share resources with analytical operations, which can cause conflicts and slow queries, while BigQuery's separation lets each layer dynamically allocate resources without affecting performance or availability of the other.
This separation also makes BigQuery a fully managed serverless data warehouse. The BigQuery engineering team handles updates and maintenance, so users do not provision or manually scale resources. BigQuery storage is automatically replicated across multiple locations to provide high availability, and BigQuery ML is supported in the same regions as BigQuery, with locations defining where datasets are created and stored as regional or multi-region locations.
Building and running a model where the data lives means creating, evaluating, and using the model through SQL statements that run inside BigQuery. The typical order of operations uses three core statements, and training data never leaves the warehouse. A single model can use data from multiple BigQuery datasets for both training and prediction, so related tables do not have to be consolidated first.
CREATE MODEL creates and trains the model; the trained model is stored in a BigQuery dataset alongside tables and views.ML.EVALUATE evaluates the trained model's quality.ML.PREDICT uses the trained model to generate predictions.The order matters because evaluation and prediction both depend on the stored model produced by CREATE MODEL. BigQuery ML supports both online predictions on individual rows and batch predictions over large datasets, which lets one model (opens in a new tab) serve use cases with different latency needs.
Lowering the technical barrier means letting data analysts build and run models with SQL and familiar warehouse tools. Training models and accessing AI resources happens through SQL, a language familiar to data analysts, so no solution has to be programmed in Python or Java. This shifts machine learning work from a small group of specialists to the people who already understand the data.
Users can reach BigQuery ML capabilities through several interfaces:
bq command-line toolModels built in BigQuery can also be registered to Model Registry in Agent Platform (opens in a new tab) and deployed to endpoints for online prediction, so a model trained where the data lives can still serve predictions outside BigQuery.
Cost, regions, and limits for BigQuery ML depend on where training happens and what type of query runs. Queries against models always run in BigQuery and use BigQuery compute pricing, and storage for trained models is charged using BigQuery storage pricing. The type of model created affects where the model is trained and which pricing applies to that operation.
| Model type | Where training happens | What is charged |
|---|---|---|
| Internally trained models | Trained inside BigQuery | BigQuery compute for training and queries, plus BigQuery storage for the model |
| Remote models | The query calls Agent Platform models | BigQuery compute for the query, plus Agent Platform charges |
| Imported models | A previously trained model is loaded into BigQuery | The CREATE MODEL statement processes no bytes and incurs no BigQuery charges; storage for the model still applies |
BigQuery ML is not available in the Standard edition, and queries that use BigQuery ML functions and CREATE MODEL statements are subject to the quotas and limits on BigQuery query jobs.
Exam tip: Queries against models always run in BigQuery and use BigQuery compute pricing, but remote models add Agent Platform charges because those queries call Agent Platform models.
BigQuery ML (opens in a new tab) lets data analysts create, train, evaluate, and use machine learning models inside BigQuery by writing standard SQL queries instead of moving data to a separate environment. Models are stored in BigQuery datasets like tables and views, so SQL practitioners can use familiar tools and skills to build and run models. This approach democratizes machine learning by removing the need to program in Python or Java, reducing complexity because fewer tools are required, and increasing speed to production because large data does not need to be moved and formatted.
The core BigQuery ML SQL statements are CREATE MODEL, ML.EVALUATE, and ML.PREDICT, which together let users develop, check, and use a model without leaving BigQuery. CREATE MODEL creates and trains a model in a BigQuery dataset, or imports an already trained model such as a TensorFlow, TensorFlow Lite, or XGBoost model. ML.EVALUATE evaluates the model, and ML.PREDICT makes predictions with the model.
| SQL statement or function | Lifecycle stage | What it does |
|---|---|---|
| CREATE MODEL | Development and training | Creates and trains a model in BigQuery, or imports a model such as TensorFlow. Imported-model CREATE MODEL statements do not process bytes or incur BigQuery charges. |
| ML.EVALUATE | Evaluation | Evaluates a model's quality. |
| ML.PREDICT | Prediction | Uses a model to make predictions, including predictions with imported TensorFlow models. |
A model can use data from multiple BigQuery datasets for training and for prediction. BigQuery ML also supports many model types, including logistic regression, deep neural network (DNN), k-means, linear regression, matrix factorization, boosted tree, and AutoML models.
The ordered SQL workflow for BigQuery ML moves through dataset creation, model training, evaluation, prediction, and optional deployment. The order matters because each step depends on the model or data produced by the previous step. Training happens in BigQuery, so data does not need to be moved to a separate Python or Java framework.
A left-to-right process flow showing the BigQuery ML lifecycle: CREATE MODEL trains a model from BigQuery tables, ML.EVALUATE checks its quality, and ML.PREDICT generates predictions that flow back into BigQuery, all without leaving the warehouse.
To create a model and run inference, a user needs the BigQuery Data Editor (roles/bigquery.dataEditor) and BigQuery User (roles/bigquery.user) roles.
Deployment in BigQuery ML means making a trained model available for online prediction or using it through integrated Google Cloud tools. BigQuery ML integrates with Gemini Enterprise Agent Platform (opens in a new tab), the end-to-end platform for AI and ML in Google Cloud. You can register BigQuery ML models to Model Registry to deploy them to endpoints for online prediction. Exporting a model is not required for deployment on Agent Platform if you use the Model Registry; otherwise, you can export the model and deploy it on Agent Platform or a local machine.
BigQuery ML can also access Agent Platform models and Cloud AI APIs for AI tasks such as text generation or machine translation. These are remote models, and queries against them make calls to Agent Platform models. You can work with BigQuery ML through the Google Cloud console UI, the query editor, the bq command-line tool, the BigQuery REST API, integrated Colab Enterprise notebooks, or external tools such as a Jupyter notebook or business intelligence platform.
Costs, quotas, and limitations for BigQuery ML control what you pay and where you can run. You are charged for compute resources used to train models and run queries against models. Queries against models always run in BigQuery and use BigQuery compute pricing. You are charged for storage used by trained models using BigQuery storage pricing. Because remote models make calls to Agent Platform models, queries against remote models (opens in a new tab) also incur Agent Platform charges.
CREATE MODEL statements are subject to BigQuery query job quotas and limits, in addition to BigQuery ML-specific limits.Exam tip: Imported models use CREATE MODEL but do not process bytes or incur BigQuery charges; remote models make calls to Agent Platform and do incur Agent Platform charges.
CREATE MODEL and predictions flow back into BigQuery without leaving the warehouse.CREATE MODEL trains the model, ML.EVALUATE checks its quality, and ML.PREDICT generates predictions from new data.CREATE MODEL and scored with ML.PREDICT.ML.EVALUATE requires a stored BigQuery ML model, while ML.METRICS evaluates any table or query that already contains actual and predicted values without a stored model.Users run CREATE MODEL to train a model that is stored in a BigQuery dataset like a table or view, then use ML.EVALUATE to check its quality and ML.PREDICT to generate predictions from new data. Training data flows from BigQuery tables into the training statement and prediction results flow back into BigQuery tables, so training data never has to leave the warehouse.
Linear regression predicts a continuous number such as price or revenue, while logistic regression assigns each row to one of a set of categories such as low-value, medium-value, or high-value with up to 50 unique values. Both types are trained with CREATE MODEL and scored with prediction functions such as ML.PREDICT, and the linear regression label cannot be positive infinity, negative infinity, or NaN.
ML.EVALUATE requires a stored BigQuery ML model and returns training-time metrics with no input or evaluates the model on a new dataset when input is specified. ML.METRICS does not require a stored model and computes metrics for any table or query that already contains actual and predicted values.
Queries against models always run in BigQuery and use BigQuery compute pricing, while storage for trained models uses BigQuery storage pricing. Queries against remote models also incur Agent Platform charges because those queries call Agent Platform models, whereas a CREATE MODEL statement for an imported model processes no bytes and incurs no BigQuery charges though storage for the model still applies.
A business intelligence team stores mobile gaming user activity within BigQuery. The team wants to build a machine learning solution entirely with SQL in BigQuery ML to achieve two specific goals:
Which machine learning model type and built-in SQL function should the team use to meet these requirements?
A classification model evaluated using the ML.FEATURE_INFO function
A classification model evaluated using the ML.EVALUATE function
A clustering model evaluated using the ML.PREDICT function
A regression model evaluated using the ML.PREDICT function
Last updated