
BigQuery ML includes built-in feature engineering functions and a TRANSFORM clause that keep preprocessing attached to a model from training through prediction. For the Professional Machine Learning Engineer exam, the practical distinctions center on how BigQuery ML persists preprocessing statistics, how preprocessing can be separated from training for reuse, and how default categorical encoding differs across data types and model families.
The TRANSFORM clause specifies preprocessing transformations during model creation and automatically applies those same transformations during evaluation and prediction. When BigQuery calculates statistics during training, such as the minimum and maximum values of a numeric column, it stores those values inside the model. During prediction, you do not need to reapply or recompute scaling logic manually because the model carries those statistics forward and processes new rows the same way it processed the training data.
Setting TRANSFORM_ONLY as the model type creates a model that performs only preprocessing with no training at all. Separating preprocessing from training lets you define one set of transformation rules once and reuse those rules across multiple models rather than duplicating the same logic in each model definition.
In a standard model creation query, transformations sit inside the TRANSFORM block above the training query:
CREATE MODEL sales.price_model
TRANSFORM(
ML.MIN_MAX_SCALER(square_feet) OVER() AS square_feet_scaled,
price
)
AS
SELECT
square_feet,
price
FROM sales.housesHere, BigQuery ML creates sales.price_model using training rows from sales.houses. Inside TRANSFORM, ML.MIN_MAX_SCALER rescales square_feet into a range between 0 and 1 based on the smallest and largest values in the column, aliasing the output as square_feet_scaled, while price passes through unchanged. The OVER() clause is required because preprocessing functions like ML.MIN_MAX_SCALER are analytic functions that compute statistics across the full set of rows rather than row by row.
Because the transformations are stored inside the model, you can run them on their own and inspect the output with ML.TRANSFORM. The function accepts two arguments, the existing model that holds the TRANSFORM clause and the table you want to transform:
SELECT *
FROM ML.TRANSFORM(
MODEL sales.price_model,
TABLE sales.houses
)This query points back to sales.price_model and returns the columns defined by its TRANSFORM clause, namely square_feet_scaled and price. The raw square_feet values are replaced by their scaled counterparts. ML.TRANSFORM does not recalculate the minimum and maximum on the incoming table. Instead, it reuses the minimum and maximum recorded when the model was trained, keeping the transformed output on the same scale the model learned on and allowing other models to reuse the same stored transformations.
ML.ONE_HOT_ENCODER converts single-value categorical columns into a compact index-and-value representation. Suppose a table has a food column with three rows containing pizza, sushi, and burger. Calling ML.ONE_HOT_ENCODER on that column returns an index and a value for each row. In this setup, pizza maps to index 1, sushi maps to index 2, and burger maps to index 3, with a value of 1 in each case.
Visually, the wide binary equivalent would create one column per category, where pizza is 1, 0, 0, sushi is 0, 1, 0, and burger is 0, 0, 1. Both forms hold the same information, but BigQuery ML returns only the index position that is switched on. That sparse representation stays compact and efficient at scale even when a feature contains thousands of distinct categories.
By default, BigQuery ML one-hot encodes all non-numeric, non-array, non-timestamp features for models other than tree-based models. When you train a linear or logistic regression model, this encoding happens automatically without adding an explicit function call.
ML.MULTI_HOT_ENCODER follows the same index-and-value pattern for array columns where a single row can contain multiple categories at once. Consider a food column where row 1 holds an array of pizza, sushi, and burger, and row 2 holds an array of pizza and burger. Calling ML.MULTI_HOT_ENCODER emits one entry per category present in each row:
pizza, index 2 for sushi, and index 3 for burger, each with a value of 1.pizza and index 3 for burger, each with a value of 1, while the absent category sushi does not appear in the output.In a wide layout, row 1 would read 1, 1, 1 and row 2 would read 1, 0, 1. Having multiple positions switched on for a single row is what distinguishes multi-hot encoding from one-hot encoding, while returning only the active index positions preserves the same storage efficiency. By default, BigQuery ML multi-hot encodes all non-numeric array features, such as columns containing arrays of strings.
ML.LABEL_ENCODER maps each categorical value to a single integer rather than returning index-and-value pairs. On the same three rows of pizza, sushi, and burger, BigQuery sorts the categories alphabetically and assigns 1 to burger, 2 to pizza, and 3 to sushi.
Integer codes imply an ordering that unordered categories like food do not have. For linear and logistic regression, that ordering creates a problem because the model treats 3 as larger than 1 and fits a coefficient accordingly. Tree-based models behave differently because they split on feature values instead of multiplying them by weights. A single integer code works cleanly for tree splits and keeps the representation compact at one number per row rather than tracking a separate position for every category.
Keeping BigQuery ML's default encoding rules straight helps when evaluating preprocessing scenarios on the Professional Machine Learning Engineer exam:
Our Professional Machine Learning Engineer course covers BigQuery ML feature engineering alongside model creation and evaluation in BigQuery ML, with practice questions that drill these distinctions.