CareerByteCode
ByteBlogby CareerByteCode

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.

BigQuery ML: Train a Model with Just SQL

✍️ 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.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

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.

How BigQuery ML trains a model
From a table to predictions: CREATE MODEL, ML.EVALUATE and ML.PREDICT

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

  1. 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.
  2. Open Cloud Shell (the >_ icon at the top right of the Google Cloud console). It already has gcloud and bq.
  3. Save your project id in a variable. Replace the text in quotes with your own project id:
export PROJECT_ID="your-project-id"
  1. 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 ls
Updated property [core/project].
Dataset 'your-project-id:cbc_ml' successfully created.
  datasetId  
 ----------- 
  cbc_ml

Step 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 |
+---------+-----------+
Dataset and practice data
Steps 1 and 2: the dataset is created and the table has 5000 customers

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 |
+-------------+---------------+--------------+---------------+---------+---------+
The customers table
Step 3: five rows of features and the churned label

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 SELECT lists the features and the label. Every other column (here customer_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_ml
Created your-project-id.cbc_ml.churn_model

      Id            Model Type        Labels    Creation Time   
 ------------- --------------------- -------- ----------------- 
  churn_model   LOGISTIC_REGRESSION            18 Nov 06:51:00
CREATE MODEL in the terminal
Step 4: the model is trained in about one minute and listed in the dataset

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"
  }
]
ML.EVALUATE
Step 5: accuracy 0.88 and ROC AUC 0.963 on the held-back rows

What the numbers mean, in plain words:

NumberOur valueMeaning
accuracy0.8888 out of 100 guesses were right
precision0.877When the model says "will churn", it is right 88 percent of the time
recall0.908Of the customers who really churned, the model caught 91 percent
f1_score0.892One number that balances precision and recall
roc_auc0.9631.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 |
+-----------------+--------+
ML.WEIGHTS
Step 6: support calls push towards churn, long tenure pulls away

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 |
+------------------------+-------------------+-------------------+
Predictions for three new customers
Step 7: a loyal customer is safe, a new customer with five calls is almost sure to leave
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →

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 |
+-------------------+
Break it and fix it
A missing column fails with a clear message; ML.FEATURE_INFO lists the four inputs

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 |
+-------------+---------------+---------------+-------------------+
Top churn risks
POC: every customer is scored; the riskiest have few months and 5 support calls

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
Clean up
The dataset and the model are gone: 0 datasets left

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 seeWhyFix
BigQuery API has not been used in projectThe API is offgcloud services enable bigquery.googleapis.com
Column ... is not found in the input data to the PREDICT functionA feature is missing in the rows you sendRun ML.FEATURE_INFO and send every listed input with the same name
Label column churned ... not foundThe label is not in the training SELECTAdd churned to the SELECT of CREATE MODEL
Not found: Model ...:cbc_ml.churn_modelWrong project, or the model was deletedgcloud config set project $PROJECT_ID, then run Step 4 again
Every prediction is the same classThe label is almost always one value, or the features carry no signalCheck the label balance (Step 2 query) and add better features
Access Denied: ... bigquery.jobs.createYour account cannot run jobs in this projectAsk the project owner for the BigQuery User role

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →BigQuery for Beginners: Query Billions of Rows for Free⏱ 11 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Contributor writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get 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.

💬 Comments (0)

Top · Newest

Sign in to comment

More from CareerByteCode Team

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →