BigQuery ML: Train a Model with Just SQL
What you build: a churn prediction model written in SQL with BigQuery ML, scored and used for predictions, in your own Google Cloud account. Every step tested.

✍️ Translate this blog into Hindi, Tamil or Telugu
In this lab you train a real machine learning model without Python, without servers and without moving data: only SQL in BigQuery. You build a table of 5000 practice customers, train a model that predicts who will leave (churn), check how good it is, read what it learned, and use it to score new customers. You finish by deleting everything.
Who it is for: learners who know basic SQL (SELECT, WHERE, GROUP BY) and want a first, honest step into machine learning. No maths background needed. Time: about 1 hour. Cost: free tier (BigQuery gives every account 1 TB of query reading and 10 GB of storage each month, and the first 10 GB of data used to train models each month is free; this lab uses well under 1 MB).
Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every output and screenshot is the real result we got. We ran the commands from a terminal with the Google Cloud CLI (gcloud and bq); Cloud Shell in your browser has the same tools ready, so you can copy and paste each command there.
Part A: BigQuery ML in plain English
Machine learning means: show a computer many past examples with the answer, and it finds a pattern it can use on new examples. BigQuery ML lets you do that with SQL statements inside BigQuery, next to your data.

Six words you will use in this lab:
- Churn: a customer who stops buying or cancels. Real-life example: a gym member who stops renewing.
- Label: the column the model must learn to guess. Here it is
churned(true or false). - Feature: a column the model uses to make its guess: months as a customer, monthly bill, support calls, plan type. Real-life example: the clues a doctor looks at before saying "flu or not".
- Logistic regression: a simple, well-tested model type for yes/no answers. It gives each feature a weight and turns the total into a probability between 0 and 1.
- Training and evaluation split: BigQuery keeps 20 percent of rows aside and never shows them to the model during training. It then tests the model on those rows. Real-life example: a teacher who keeps some questions back for the final exam.
- Prediction: the model's answer for a new row, with a probability. 0.9 means "90 percent likely to churn".
How you pay: CREATE MODEL is billed by the bytes it reads, like a query, and the first 10 GB each month is free. Our training table is about 200 KB.
Before you start
- A Google Cloud account (the free trial works) and a project with billing linked. Create one at https://console.cloud.google.com/projectcreate if you do not have one.
- Open Cloud Shell (the
>_icon at the top right of the Google Cloud console). It already hasgcloudandbq. - Save your project id in a variable. Replace the text in quotes with your own project id:
export PROJECT_ID="your-project-id"- The BigQuery API is on by default in new projects. If a command says it is disabled, run
gcloud services enable bigquery.googleapis.com.
Part B: the lab
Step 1: point the CLI at your project and make a dataset
Why: the dataset cbc_ml will hold the customer table and the model. A model in BigQuery ML is stored inside a dataset, just like a table.
gcloud config set project $PROJECT_ID
bq --location=US mk --dataset --description "CareerByteCode BigQuery ML lab" $PROJECT_ID:cbc_ml
bq lsUpdated property [core/project].
Dataset 'your-project-id:cbc_ml' successfully created.
datasetId
-----------
cbc_mlStep 2: make 5000 practice customers with SQL
Why: a model needs examples. We do not want to share real customer data, so we build a practice table with SQL alone. GENERATE_ARRAY(1, 5000) makes 5000 ids, and FARM_FINGERPRINT turns each id into a fixed "random" number, so you get exactly the same table we got. The churned column follows a hidden rule (many support calls, a high bill, few months and a monthly plan all push towards churn) plus some noise, like real life.
bq query --use_legacy_sql=false --quiet \
"CREATE OR REPLACE TABLE cbc_ml.customers AS
WITH base AS (
SELECT id,
MOD(ABS(FARM_FINGERPRINT(CONCAT(\"t\", CAST(id AS STRING)))), 60) + 1 AS tenure_months,
MOD(ABS(FARM_FINGERPRINT(CONCAT(\"m\", CAST(id AS STRING)))), 900) + 199 AS monthly_bill,
MOD(ABS(FARM_FINGERPRINT(CONCAT(\"s\", CAST(id AS STRING)))), 6) AS support_calls,
IF(MOD(ABS(FARM_FINGERPRINT(CONCAT(\"c\", CAST(id AS STRING)))), 3) = 0, \"yearly\", \"monthly\") AS plan,
MOD(ABS(FARM_FINGERPRINT(CONCAT(\"r\", CAST(id AS STRING)))), 100) AS noise
FROM UNNEST(GENERATE_ARRAY(1, 5000)) AS id)
SELECT id AS customer_id, tenure_months, monthly_bill, support_calls, plan,
(support_calls * 12 + monthly_bill / 40 - tenure_months * 0.8 + IF(plan = \"monthly\", 15, 0) + noise * 0.35 > 45) AS churned
FROM base"
bq query --use_legacy_sql=false "SELECT churned, COUNT(*) AS customers FROM cbc_ml.customers GROUP BY churned ORDER BY churned"Created your-project-id.cbc_ml.customers
+---------+-----------+
| churned | customers |
+---------+-----------+
| false | 2322 |
| true | 2678 |
+---------+-----------+
About half of the customers churned. That balance makes the lab easy to read; real data is often 5 to 20 percent churn.
Step 3: peek at the rows
Why: always look at your data before you train. bq head shows rows without running a query, so it reads nothing billable.
bq head -n 5 cbc_ml.customers+-------------+---------------+--------------+---------------+---------+---------+
| customer_id | tenure_months | monthly_bill | support_calls | plan | churned |
+-------------+---------------+--------------+---------------+---------+---------+
| 23 | 49 | 720 | 0 | yearly | false |
| 33 | 57 | 935 | 1 | monthly | false |
| 122 | 14 | 783 | 2 | monthly | true |
| 123 | 35 | 643 | 1 | monthly | true |
| 163 | 59 | 320 | 5 | yearly | true |
+-------------+---------------+--------------+---------------+---------+---------+
Step 4: train the model with CREATE MODEL
Why: this one statement trains the model. Read it like a sentence:
CREATE OR REPLACE MODEL cbc_ml.churn_model: the name of the model.model_type = "logistic_reg": a yes/no model.input_label_cols = ["churned"]: the column to guess.data_split_method = "RANDOM", data_split_eval_fraction = 0.2: keep 20 percent back for the exam.- The
SELECTlists the features and the label. Every other column (herecustomer_id) is left out on purpose: an id is not a clue.
bq query --use_legacy_sql=false --quiet \
"CREATE OR REPLACE MODEL cbc_ml.churn_model
OPTIONS (model_type = \"logistic_reg\", input_label_cols = [\"churned\"],
data_split_method = \"RANDOM\", data_split_eval_fraction = 0.2) AS
SELECT tenure_months, monthly_bill, support_calls, plan, churned
FROM cbc_ml.customers"
bq ls --models cbc_mlCreated your-project-id.cbc_ml.churn_model
Id Model Type Labels Creation Time
------------- --------------------- -------- -----------------
churn_model LOGISTIC_REGRESSION 18 Nov 06:51:00
Training took about one minute. BigQuery did the maths; you wrote one SQL statement. The text column plan was turned into numbers for you (this is called one-hot encoding).
Step 5: how good is it? ML.EVALUATE
Why: never trust a model before you test it. ML.EVALUATE runs the model on the 20 percent it never saw and compares its guesses with the real answers.
bq query --use_legacy_sql=false --format=prettyjson \
"SELECT ROUND(precision, 3) AS precision, ROUND(recall, 3) AS recall,
ROUND(accuracy, 3) AS accuracy, ROUND(f1_score, 3) AS f1_score, ROUND(roc_auc, 3) AS roc_auc
FROM ML.EVALUATE(MODEL cbc_ml.churn_model)"[
{
"accuracy": "0.88",
"f1_score": "0.892",
"precision": "0.877",
"recall": "0.908",
"roc_auc": "0.963"
}
]
What the numbers mean, in plain words:
| Number | Our value | Meaning |
|---|---|---|
| accuracy | 0.88 | 88 out of 100 guesses were right |
| precision | 0.877 | When the model says "will churn", it is right 88 percent of the time |
| recall | 0.908 | Of the customers who really churned, the model caught 91 percent |
| f1_score | 0.892 | One number that balances precision and recall |
| roc_auc | 0.963 | 1.0 is perfect, 0.5 is a coin toss; above 0.9 is very good |
Our data follows a simple rule, so the model scores well. Real data is messier; 0.75 to 0.85 ROC AUC is common and still useful.
Step 6: what did it learn? ML.WEIGHTS
Why: a logistic regression is easy to explain. Each feature has a weight: positive pushes towards churn, negative pushes away.
bq query --use_legacy_sql=false \
"SELECT processed_input, ROUND(weight, 3) AS weight
FROM ML.WEIGHTS(MODEL cbc_ml.churn_model)
WHERE processed_input != \"plan\" ORDER BY processed_input"+-----------------+--------+
| processed_input | weight |
+-----------------+--------+
| __INTERCEPT__ | -2.508 |
| monthly_bill | 0.003 |
| support_calls | 1.764 |
| tenure_months | -0.124 |
+-----------------+--------+
Read it: each extra support call adds 1.764 to the churn score (a strong push), each extra month as a customer takes 0.124 away (loyalty protects), and the monthly bill adds a small 0.003 per rupee. That is the same story as the hidden rule we used in Step 2: the model found it from the data alone. (The plan weights are per category, so we left that row out to keep the table short.)
Step 7: predict for three new customers
Why: this is what the model is for. ML.PREDICT takes the model and any query with the same feature columns, and adds predicted_churned and the probabilities.
bq query --use_legacy_sql=false \
"SELECT name, predicted_churned,
ROUND((SELECT prob FROM UNNEST(predicted_churned_probs) WHERE label = TRUE), 3) AS churn_probability
FROM ML.PREDICT(MODEL cbc_ml.churn_model, (
SELECT \"Asha (loyal, yearly)\" AS name, 48 AS tenure_months, 299 AS monthly_bill, 0 AS support_calls, \"yearly\" AS plan
UNION ALL SELECT \"Ravi (new, many calls)\", 2, 999, 5, \"monthly\"
UNION ALL SELECT \"Meena (middle)\", 20, 650, 2, \"monthly\"))"+------------------------+-------------------+-------------------+
| name | predicted_churned | churn_probability |
+------------------------+-------------------+-------------------+
| Asha (loyal, yearly) | false | 0.0 |
| Ravi (new, many calls) | true | 1.0 |
| Meena (middle) | true | 0.866 |
+------------------------+-------------------+-------------------+
The probability is rounded to 3 decimals, so 1.0 means "above 0.9995". Meena is the interesting case: 0.866, a customer a team could call this week with a better offer.
Break it and fix it
A common mistake: you send the model a row that is missing one of the features it was trained on. Here we forget support_calls.
bq query --use_legacy_sql=false \
"SELECT predicted_churned FROM ML.PREDICT(MODEL cbc_ml.churn_model,
(SELECT 2 AS tenure_months, 999 AS monthly_bill, \"monthly\" AS plan))"Error in query string: Error processing job 'your-project-id:bqjob_r2b1d111df15719c2_000001a1ed3605aa_1': Invalid table-valued
function ML.PREDICT
Column support_calls is not found in the input data to the PREDICT function. at
[1:31]The error says exactly which column is missing. Fix: ask the model which inputs it expects with ML.FEATURE_INFO, then send all of them.
bq query --use_legacy_sql=false \
"SELECT input, min, max, ROUND(mean, 1) AS mean, category_count FROM ML.FEATURE_INFO(MODEL cbc_ml.churn_model)"
bq query --use_legacy_sql=false \
"SELECT predicted_churned FROM ML.PREDICT(MODEL cbc_ml.churn_model,
(SELECT 2 AS tenure_months, 999 AS monthly_bill, 5 AS support_calls, \"monthly\" AS plan))"+---------------+-------+--------+-------+----------------+
| input | min | max | mean | category_count |
+---------------+-------+--------+-------+----------------+
| tenure_months | 1.0 | 60.0 | 30.7 | NULL |
| monthly_bill | 199.0 | 1098.0 | 650.6 | NULL |
| support_calls | 0.0 | 5.0 | 2.5 | NULL |
| plan | NULL | NULL | NULL | 2 |
+---------------+-------+--------+-------+----------------+
+-------------------+
| predicted_churned |
+-------------------+
| true |
+-------------------+
ML.FEATURE_INFO also shows the range of each input the model saw during training (for example tenure 1 to 60 months). Predictions for values far outside that range are guesses, so keep new data in a similar range.
POC: score every customer and find the top risks
The proof that it works end to end: score all 5000 customers in one query and list the five most likely to leave. A team would call these customers first.
bq query --use_legacy_sql=false \
"SELECT customer_id, tenure_months, support_calls,
ROUND((SELECT prob FROM UNNEST(predicted_churned_probs) WHERE label = TRUE), 3) AS churn_probability
FROM ML.PREDICT(MODEL cbc_ml.churn_model, TABLE cbc_ml.customers)
ORDER BY churn_probability DESC LIMIT 5"+-------------+---------------+---------------+-------------------+
| customer_id | tenure_months | support_calls | churn_probability |
+-------------+---------------+---------------+-------------------+
| 4053 | 1 | 5 | 1.0 |
| 1389 | 3 | 5 | 1.0 |
| 4900 | 10 | 5 | 1.0 |
| 3214 | 1 | 5 | 1.0 |
| 294 | 7 | 5 | 1.0 |
+-------------+---------------+---------------+-------------------+
Proof: the riskiest customers all have 5 support calls and only 1 to 10 months as customers, which matches what the weights told us in Step 6. You trained, tested, explained and used a model with SQL alone.
Clean up (delete everything)
Why: a model and a table are small, but stored data adds up over time and an empty project is easier to read. bq rm -r -f -d removes the dataset with every table and model inside it, without asking.
bq rm -r -f -d $PROJECT_ID:cbc_ml
echo "datasets left: $(bq ls --format=json | grep -c datasetId)"datasets left: 0
datasets left: 0 is your proof.
FAQ
Do I need to know Python or maths? No. You need SQL. BigQuery ML does the training maths for you; it helps to understand the evaluation numbers, and the table in Step 5 explains them.
Which model types does BigQuery ML have? Many: linear regression (numbers), logistic regression (yes/no or classes), k-means (groups), time-series forecasting (ARIMA_PLUS), boosted trees, deep neural networks and matrix factorisation, plus calls to Vertex AI models. Start with logistic or linear regression: they are fast and easy to explain.
Is my data copied somewhere else? No. Training runs inside BigQuery and the model is stored in your dataset.
Why did my numbers differ a little from yours? The random 80/20 split can differ between runs, so accuracy may move by a point or two. The table itself is the same for everyone because it is built from fixed hashes.
Can I retrain when new data arrives? Yes. Run the same CREATE OR REPLACE MODEL statement again; it replaces the old model. Many teams do this on a schedule with BigQuery scheduled queries.
Can I use the model outside BigQuery? Yes. You can export many BigQuery ML models to Cloud Storage and serve them, for example on Vertex AI.
Is 0.88 accuracy good enough? It depends on the cost of a wrong guess. For a "call this customer" list, a good recall (catching most churners) usually matters more than perfect precision.
Troubleshooting
| You see | Why | Fix |
|---|---|---|
BigQuery API has not been used in project | The API is off | gcloud services enable bigquery.googleapis.com |
Column ... is not found in the input data to the PREDICT function | A feature is missing in the rows you send | Run ML.FEATURE_INFO and send every listed input with the same name |
Label column churned ... not found | The label is not in the training SELECT | Add churned to the SELECT of CREATE MODEL |
Not found: Model ...:cbc_ml.churn_model | Wrong project, or the model was deleted | gcloud config set project $PROJECT_ID, then run Step 4 again |
| Every prediction is the same class | The label is almost always one value, or the features carry no signal | Check the label balance (Step 2 query) and add better features |
Access Denied: ... bigquery.jobs.create | Your account cannot run jobs in this project | Ask the project owner for the BigQuery User role |
What next
- New to BigQuery itself? Look for our tested lab "BigQuery for Beginners: Query Billions of Rows for Free" on ByteBlog.
- Read our tested Kubernetes lab: Zero to Kubernetes in 2.5 hours.
- More hands-on posts from the community: ByteBlog.
- Practice with guided projects in ByteLabs and see the full courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet CareerByteCode Team's next blog by email
One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.
We email you once to confirm. By subscribing you agree to the privacy policy.
More from CareerByteCode Team
CareerByteCode TeamBigQuery for Beginners: Query Billions of Rows for Free
What you build: SQL on a public dataset with millions of rows, then your own sales table with a view and a daily partition, in your own Google Cloud account. Every step tested.
#beginners
CareerByteCode TeamCloud Storage Deep Dive: Classes, Lifecycle and Signed URLs
What you build: a private bucket with storage classes, a lifecycle policy, object versions and expiring signed URLs, in your own Google Cloud account. Every step tested.
#beginners
CareerByteCode TeamFirestore Hands-On: A NoSQL Database for Apps
What you build: a to-do app on Firestore in the free tier, in your own Google Cloud account. Every step, command and screenshot explained for beginners.
#google-cloud
CareerByteCode TeamLogs to BigQuery: Analyse a Month of Logs with SQL
What you build: a log sink from Cloud Logging to BigQuery and four SQL reports on a month of web traffic, errors and slow pages, in your own Google Cloud account. Every step tested.
💬 Comments (0)
Top · NewestSign in to comment