Low-code AI: BigQuery ML, AutoML and prebuilt AI APIs

Section 1 of the exam: building models with SQL in BigQuery ML (model types, TRANSFORM, evaluation, forecasting, explanations, remote Gemini models and tuning), AutoML on Agent Platform, and the prebuilt Vision, Document AI, Translation and Speech APIs. Includes a hands-on BigQuery ML lab.

1. The low-code ladder

Exam section 1 (about 13%) rewards picking the least effort that meets the requirement. Climb only when the rung below can't do the job.

2. BigQuery ML: models in SQL

BigQuery ML (BQML) trains and runs models with SQL, inside BigQuery. The data never leaves the warehouse, analysts don't need Python, and prediction runs over whole tables. It's the default answer when "the data is in BigQuery" and "the team knows SQL".

-- Train: the TRANSFORM clause stores preprocessing with the model,
-- so ML.PREDICT applies exactly the same steps at prediction time.
CREATE OR REPLACE MODEL `shop.churn_model`
TRANSFORM(
  ML.STANDARD_SCALER(tenure_days) OVER () AS tenure_scaled,
  ML.QUANTILE_BUCKETIZE(monthly_spend, 10) OVER () AS spend_bucket,
  plan_type,
  churned
)
OPTIONS(
  model_type = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols = ['churned'],
  auto_class_weights = TRUE,
  data_split_method = 'AUTO_SPLIT',
  enable_global_explain = TRUE,
  model_registry = 'VERTEX_AI'            -- also register it in Model Registry
) AS
SELECT tenure_days, monthly_spend, plan_type, churned
FROM `shop.customers`
WHERE signup_date < '2026-01-01';

SELECT * FROM ML.EVALUATE(MODEL `shop.churn_model`);          -- precision, recall, roc_auc, log_loss...
SELECT * FROM ML.GLOBAL_EXPLAIN(MODEL `shop.churn_model`);    -- which features matter overall
SELECT * FROM ML.PREDICT(MODEL `shop.churn_model`,
                         (SELECT * FROM `shop.customers_today`));
SELECT * FROM ML.EXPLAIN_PREDICT(MODEL `shop.churn_model`,    -- per-row attributions
                         (SELECT * FROM `shop.customers_today`), STRUCT(3 AS top_k_features));

Splits, tuning and export

-- Built-in tuning: 20 trials, 4 at a time, optimizing ROC AUC
CREATE OR REPLACE MODEL `shop.churn_tuned`
OPTIONS(
  model_type = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols = ['churned'],
  num_trials = 20, max_parallel_trials = 4,
  hparam_tuning_objectives = ['roc_auc'],
  max_tree_depth = HPARAM_RANGE(3, 10),
  learn_rate = HPARAM_RANGE(0.01, 0.3)
) AS SELECT * FROM `shop.training_view`;

SELECT * FROM ML.TRIAL_INFO(MODEL `shop.churn_tuned`);

When BigQuery ML is the wrong tool

• Custom architectures, custom losses, or frameworks like PyTorch with bespoke training loops.
• Images, audio or video as raw input (use AutoML, Gemini or custom training).
• Strict millisecond online latency directly from BigQuery: register the model and deploy it to an endpoint, or export it.

3. Gemini and remote models from BigQuery

A remote model points BigQuery at a hosted model (Gemini, an embedding model, or your own endpoint) through a connection whose service account has Agent Platform access. Generation and embedding then run as SQL over tables.

BQML can supervised-tune a Gemini model on prompt/response pairs stored in a table, then serve it as a remote model, all from SQL:

CREATE OR REPLACE MODEL `support.ticket_tagger`
  REMOTE WITH CONNECTION `us.vertex_conn`
  OPTIONS (
    endpoint = 'gemini-2.5-flash',          -- base model to tune
    max_iterations = 300,
    prompt_col = 'prompt',
    input_label_cols = ['label']
  ) AS
SELECT prompt, label FROM `support.labelled_examples`;

SELECT * FROM ML.GENERATE_TEXT(MODEL `support.ticket_tagger`,
  (SELECT ticket_text AS prompt FROM `support.new_tickets`),
  STRUCT(0.0 AS temperature, TRUE AS flatten_json_output));

4. AutoML

AutoML trains a high-quality model from labelled data with no model code: it searches architectures and hyperparameters for you. You pay in node hours and give up control of the architecture and loss.

AutoML practicalities

5. Prebuilt AI APIs

Customizing without training a model

6. Lab: churn model end to end in BigQuery ML

Use a sandbox project with billing alerts. Placeholders: PROJECT_ID, dataset shop, region us-central1.

1. Load a public e-commerce or telco churn table into your dataset, or create shop.customers with a label column.
2. Train a LOGISTIC_REG baseline, then a BOOSTED_TREE_CLASSIFIER with a TRANSFORM clause and auto_class_weights=TRUE.
3. Compare with ML.EVALUATE. Run ML.CONFUSION_MATRIX at thresholds 0.3, 0.5 and 0.7 and pick one with the confusion-matrix calculator.
4. Run ML.EXPLAIN_PREDICT on five rows and ML.GLOBAL_EXPLAIN. Is any feature suspiciously dominant (possible leakage)?
5. Retrain with num_trials=10 and read ML.TRIAL_INFO.
6. Retrain with model_registry='VERTEX_AI' and find the model in Model Registry.
7. Create a remote Gemini model through a connection and use ML.GENERATE_TEXT to summarize the 20 most recent support tickets per churner.

You should be able to say: why TRANSFORM prevents training-serving skew, why PR AUC beats accuracy here, and how batch scoring in SQL avoids running an endpoint. Clean up: DROP MODEL for each model; delete the dataset and the connection.

7. "Which low-code option?" chooser

Pick an item to see what it means, why, and the tempting wrong choice.

8. Sample outputs

What the tools return. Expand an item to see a typical result and what to read in it.

precision  recall  accuracy  f1_score  log_loss  roc_auc
0.412      0.783   0.951     0.540     0.142     0.931

feature          attribution
tenure_scaled    0.318
support_tickets  0.244
spend_bucket     0.131
plan_type        0.087
last_refund_flag 0.512

store_id  forecast_timestamp       forecast_value  prediction_interval_lower_bound  prediction_interval_upper_bound
s-014     2026-11-15 00:00:00 UTC  1843.2          1611.7                           2074.7
s-014     2026-11-16 00:00:00 UTC  2210.9          1958.0                           2463.8
s-022     2026-11-15 00:00:00 UTC   932.4           801.3                           1063.5

9. Scenarios

Exam-style situations: how to diagnose each one, the likely cause and the fix.

10. Searchable reference

11. Flashcards (click to flip)

12. Common gotchas in one list

13. Practice quiz (22 questions)