Associate Data Practitioner

Identify ML use cases for developing models by using BigQuery ML and AutoML

Evaluate Use Cases for AutoML and Unstructured Data

The Administrative Jobs Explorer in BigQuery lets administrators view and filter job activity without writing complex queries. You can see slot usage, duration, and job owners at the project or organization level, making it easier to find problematic jobs. The Workload Manager helps you create evaluations that check whether your workloads follow best practices. You can define the scope by selecting projects and applying filters, then schedule evaluations and set notifications for results. For specialized AI, Anti Money Laundering (AML) AI uses engine configurations to manage model training. These configurations can include automatic tuning to optimize parameters without manual work, and they require specific permissions to create. The system uses asynchronous operations to handle long-running requests for creating and managing configurations.

Analyze Use Cases Suitable for BigQuery ML

BigQuery ML lets you build and run machine learning models directly in Google Cloud using SQL queries. This works well for data analysts who already know SQL, and it's especially useful for tasks like customer segmentation and predictive analytics. To develop a model, you first define the model type, then train it by writing a SQL query, and finally evaluate its accuracy and use it to generate predictions. During training, AutoML can automatically tune hyperparameters to improve performance with little manual effort. BigQuery ML is scalable, integrates with existing SQL workflows, and reduces costs because everything happens inside the data warehouse.

Assess BigQuery ML and AutoML Capabilities

BigQuery ML supports modeling tasks such as regression (using ARIMA for forecasting), classification (using XGBoost or logistic regression), and anomaly detection for fraud. You can assess model quality with metrics like MAE, MAPE, MSE, RMSE, and AUC directly in SQL. Alternatively, AutoML Tabular provides a guided interface or Vertex AI SDK that automates model development. After you load data, it creates a Vertex dataset, trains the model, obtains evaluation metrics, and deploys it for online prediction or batch prediction. Choose BigQuery ML when you want direct control with SQL and custom modeling. Choose AutoML when you need an end-to-end automated workflow with minimal code. Both options integrate with Vertex AI Workbench and handle large, real-world datasets.

Use pretrained Google large language models (LLMs) using remote connection in BigQuery

Utilize Google LLM Functions in BigQuery

BigQuery can call remote functions that run pretrained Google large language models (LLMs) directly inside SQL queries. These functions use remote connections to Vertex AI endpoints, so you can analyze text data without moving it out of BigQuery. You can perform tasks like text generation, summarization, and embeddings. To set up the function, you bring your text data into BigQuery from sources such as Conversational Insights, Dialogflow CX exports, or Google Analytics 4. Then you create an external connection to your Vertex AI model endpoint and use CREATE EXTERNAL FUNCTION in BigQuery SQL, specifying the connection, model name, and input/output schemas. Once defined, the function behaves like any other SQL function. You can run queries that mix data operations with AI calls—for example, passing conversation texts to generate summaries or computing sentiment. To evaluate performance, review response quality, latency, and cost on sample sets.

Configure Remote Model Definitions

BigQuery ML allows you to create a remote model that points to a Vertex AI foundation model (like Gemini) using standard SQL. You use a CREATE MODEL statement with the model type set to REMOTE and define the correct connection options. The endpoint configuration is critical: the location of your BigQuery dataset must align with the region of the Vertex AI model. For single-region datasets, the endpoint must be in the same region. For multi-region datasets like US or EU, you can use endpoints within that broader area. A global endpoint offers higher availability but does not guarantee a specific data processing location. Once configured, you interact with the remote model using standard SQL queries. The request is sent to Vertex AI for processing, and the inference results are returned to BigQuery for analysis.

Establish a Remote Connection to BigQuery

To integrate LLMs with BigQuery, you must set up a secure remote connection. This requires configuring a Google Cloud service account. You can create a new service account or use an existing one. While a service account key in JSON format is typically used for authentication, workload identity emails are recommended because they avoid the security risk of managing JSON key files. The integration needs parameters such as the project ID, private key ID, and private key. Two main authentication methods are available: the service account key JSON (which provides detailed information including type, project_id, and keys) and workload identity (the recommended approach). Using pretrained Google LLMs with BigQuery allows you to perform advanced data processing and analysis directly in your queries, improving security and performance.

Plan a standard ML project (e.g., data collection, model training, model evaluation, prediction)

Utilize GCP for Data Collection and Preparation

First, set up a GCP project and ensure billing is enabled to use paid services. You need the right permissions, like the Storage Admin role to create and manage Cloud Storage buckets. For handling data, use Cloud Storage to store raw files like CSVs, BigQuery as a data warehouse for fast SQL queries, and Dataflow to process, clean, and transform data. A typical workflow starts with raw data in Cloud Storage, uses Dataflow for cleaning, and then loads the prepared data into BigQuery for analysis and model training.

Configure and Execute Data Collection

BigQuery is central because you can create ML models using SQL. You use the CREATE MODEL statement, handle feature preprocessing, and refine the model with hyperparameter tuning. Vertex AI manages the full ML lifecycle, offering two paths: AutoML for training models on tabular or image data with minimal code, and Custom Training for full control using your own frameworks. You can also build automated ML pipelines with Vertex AI Pipelines, which structure tasks in a workflow graph. Integrating models from BigQuery ML into Vertex AI provides benefits like online model serving for real-time predictions and MLOps capabilities for continuous training and monitoring.

Implement Model Training, Evaluation, and Prediction

Feature preprocessing cleans data and creates features for training. In BigQuery ML, you can do this automatically or manually by defining custom logic in the CREATE MODEL statement using a TRANSFORM clause. You train the model using CREATE MODEL on your prepared dataset. During model optimization, you can use a validation set to try different model types, like DecisionTreeRegressor, to find the best fit. After training, model evaluation tests the model on a separate dataset using metrics like accuracy or mean squared error. Once validated, you use model inference to generate predictions on new data, providing actionable insights.

Execute SQL to create, train, and evaluate models using BigQuery ML

Create Machine Learning Models Using SQL

BigQuery ML (BQML) lets you build models using standard SQL without moving data. The core is the CREATE MODEL SQL statement, where you specify the model type and training data. For supervised learning, use LINEAR_REG for predicting continuous numbers (like prices) and LOGISTIC_REG for categories (like spam or not spam). Both require the input_label_cols option to identify the column to predict. For unsupervised learning, use KMEANS to group data into clusters, and set the num_clusters option. After creating a model, use ML.EVALUATE to check performance and ML.PREDICT to make predictions on new data.

Evaluate Model Performance Using SQL Functions

The ML.EVALUATE function generates metrics that tell you how well the model works. For classification models, examine accuracy, precision, and recall to see how often the model categorizes correctly. For regression models, examine mean squared error to measure how far predictions are from actual values. After evaluation, you can register the model in the Vertex AI Model Registry for advanced deployment and monitoring, which enables online serving and ongoing performance tracking.

SQL Syntax for Model Creation

Writing the CREATE MODEL statement correctly is key. You must specify the model type, training data table, and appropriate options. Model optimization involves comparing different approaches, like linear regression versus random forest, to see which performs better. Model evaluation uses a separate test dataset, and you can review results in formats like pandas DataFrames. Model inference uses ML.PREDICT to apply the model to new data. The Data Science Agent in BigQuery can assist by generating Python code or SQL queries for BQML when you use keywords like "SQL" in your prompts.

Perform inference using BigQuery ML models

Implement Prediction Mechanisms within BigQuery

BigQuery ML allows you to perform inference—making predictions on new data—using standard SQL queries. After generating predictions, you must validate their accuracy by comparing results against actual data using tools like a confusion matrix to identify errors. Once validated, you can integrate the predictions into broader data analysis workflows to enhance reports and drive data-driven decisions.

Execute Inference with Pre-trained BigQuery ML Models

To generate predictions, run SQL queries that call the ML.PREDICT function. This function takes a trained model and a new dataset and returns a structured table of predictions, including original features, the predicted label, and a confidence score. You can customize queries with options for batch size, confidence thresholds, and output destinations. To evaluate a pre-trained model's performance on labeled data, use ML.EVALUATE. This function returns metrics such as accuracy, mean_squared_error, or AUC depending on the model type. Reviewing these metrics helps decide whether to retrain the model, adjust features, or deploy the predictions.

Perform Time-Series Forecasting and Anomaly Detection

For temporal data, BigQuery ML offers ARIMA_PLUS and ARIMA_PLUS_XREG models for time-series forecasting and anomaly detection. These models analyze historical trends to predict future values and identify outliers without requiring labeled training data for every possible outcome (unsupervised learning). When working with large datasets, you can run batch inference through the Google Cloud console or API. This processes extensive data sources like BigQuery tables or CSV files in Cloud Storage. Batch inference is ideal when real-time predictions are not needed, as it prioritizes processing volume. The output tables store predicted values, confidence scores, and feature attributions for further analysis.

Organize Models in Model Registry

Vertex AI Model Registry is a central place to manage the lifecycle of your machine learning models. You can assign mutable aliases to model versions to mark stages like staging or production. When you promote a model, you update the alias to point to the newer version without changing application code. This supports MLOps practices such as managing model versions, orchestrating workflows, and monitoring for inference drift (which signals when a model might need retraining). Model versioning is essential: the registry keeps track of training data sources, version numbers, performance metrics, and schema details. Using consistent naming conventions and regular updates ensures clarity.

Model metadata management in the registry uses a MetadataStore as the top-level container, which is regional and tied to a specific GCP project. Within a MetadataStore, an artifact is a discrete entity (like a dataset or model binary), an execution records a workflow step (like training), a context groups related artifacts and executions (like a pipeline run), and an event links artifacts to executions to show provenance. MetadataSchema defines the structure of metadata using YAML definitions, ensuring consistency. After registering a BigQuery ML model in the registry, it appears alongside custom models, creating a unified view that simplifies governance and tracks versioning, evaluation histories, and deployment statuses.