Build a Recommendation System with BigQuery ML
What you build: a movie recommender in BigQuery ML with SQL only: a popularity baseline, matrix factorization refused on free pricing, K-means taste clusters, and a real 59.8 vs 25.5 percent hit rate.

✍️ Translate this blog into Hindi, Tamil or Telugu
"Because you watched..." rows drive a large share of what people watch on streaming services. In this lab you build a movie recommendation system with BigQuery ML, using only SQL, and you measure whether it is any good. You start with the simplest recommender (the most popular movies), try the textbook BigQuery ML recommender and hit a real limit of the free pricing model, then build a recommender that works on the free tier: a K-means model that groups users by taste. Finally you test both on what users really watched next: the taste model finds a right movie for 59.8 percent of users, against 25.5 percent for "most popular".
Who it is for: data and ML beginners who know basic SQL. Time: about 1 hour. Cost: free. The data is under 1 MB, so every query and model stays far inside BigQuery's free tier (1 TB of queries and 10 GB of storage a month).
Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every output is the real result we got.
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโน499Build the projects →Part A: recommendations in plain English

- Recommender: picks items a user is likely to want next. Real-life example: a librarian who knows which books readers like you borrowed.
- Popularity baseline: recommend the most watched items to everyone. Simple, often surprisingly hard to beat, and always the first thing to compare against.
- Collaborative filtering: learn from many users' behaviour ("people like you watched..."). BigQuery ML's built-in version is matrix factorization.
- Taste profile: each user described as the share of their views in each genre (for example 40 percent Drama, 37 percent Action).
- K-means clustering: an unsupervised model that puts similar rows (here users with similar taste) into clusters. We then recommend each cluster's most watched movies that the user has not seen.
- Offline evaluation: hide each user's last 3 views, recommend 5 movies from the earlier history, and count how often at least one hidden movie is in the 5. That share is the hit rate.
The data is made by a small script: 60 movies in 6 genres and 600 users who each love 2 genres and watch about 18 movies, mostly from those genres, with a few "hits" in each genre watched far more often (like real life).
Before you start
- A Google Cloud account (the free trial works) and a project.
- Open Cloud Shell (the
>_icon at the top right of the console). It hasbqand Python. Set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.com- Make a folder
~/lab254and save the data script there:
# make_views.py: a small streaming service. 60 movies in 6 genres, 600 users. Each user loves 2 genres and
# picks about 30 movies (repeats are skipped, so about 18 different ones), mostly from those genres (like real taste). Writes movies.csv and views.csv.
import csv, random
random.seed(21)
genres = ["Action", "Comedy", "Drama", "Horror", "SciFi", "Romance"]
words = ["Night", "City", "Storm", "River", "Echo", "Signal", "Garden", "Mirror", "Rocket", "Letter"]
movies = []
for g_i, g in enumerate(genres):
for k in range(10):
movies.append((len(movies) + 1, f"{random.choice(words)} {g} {k + 1}", g))
by_genre = {g: [m for m in movies if m[2] == g] for g in genres}
with open("movies.csv", "w", newline="") as f:
w = csv.writer(f); w.writerow(["movie_id", "title", "genre"]); w.writerows(movies)
rows, day = [], 0
for u in range(1, 601):
fav = random.sample(genres, 2)
seen = set()
for n in range(random.randint(22, 38)):
pool = by_genre[random.choice(fav)] if random.random() < 0.8 else movies
# within a genre, lower ids are the "hits": they get picked more often
m = random.choices(pool, weights=[1 / (i + 1) ** 0.6 for i in range(len(pool))])[0]
if m[0] in seen: continue
seen.add(m[0])
rows.append((u, m[0], n + 1, random.choice([3, 4, 4, 5, 5]) if m[2] in fav else random.choice([1, 2, 3, 3, 4])))
with open("views.csv", "w", newline="") as f:
w = csv.writer(f); w.writerow(["user_id", "movie_id", "seq", "rating"]); w.writerows(rows)
print(f"movies.csv: {len(movies)} movies | views.csv: {len(rows)} views by 600 users")Part B: the lab
Step 1: load the viewing data
# Load the viewing data: 60 movies and about 17,000 views by 600 users
python3 make_views.py
bq --location=US mk --dataset --description "Recommendation lab" recs 2>&1 | tail -1
bq load --quiet --skip_leading_rows=1 recs.movies movies.csv movie_id:INT64,title:STRING,genre:STRING
bq load --quiet --skip_leading_rows=1 recs.views views.csv user_id:INT64,movie_id:INT64,seq:INT64,rating:INT64
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT COUNT(*) AS views, COUNT(DISTINCT user_id) AS users, COUNT(DISTINCT movie_id) AS movies, ROUND(AVG(rating), 2) AS avg_rating FROM recs.views"movies.csv: 60 movies | views.csv: 10625 views by 600 users
Dataset 'your-project-id:recs' successfully created.
Upload complete.
Upload complete.
+-------+-------+--------+------------+
| views | users | movies | avg_rating |
+-------+-------+--------+------------+
| 10625 | 600 | 60 | 3.87 |
+-------+-------+--------+------------+10,625 views by 600 users of 60 movies, average rating 3.87 out of 5.
Step 2: split into training and test data
Why: to judge a recommender honestly, it must not see what it is asked to predict. For every user we keep the last 3 views apart as the test, and train only on the views before them (seq is the order in which each user watched).
# Hold out each user's LAST 3 views as a test: we train on the past and check if we can predict what they watched next
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE TABLE recs.train AS SELECT v.* FROM recs.views v JOIN (SELECT user_id, MAX(seq) AS last FROM recs.views GROUP BY user_id) m USING (user_id) WHERE v.seq <= m.last - 3" && echo "train table created"
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE TABLE recs.test AS SELECT v.* FROM recs.views v JOIN (SELECT user_id, MAX(seq) AS last FROM recs.views GROUP BY user_id) m USING (user_id) WHERE v.seq > m.last - 3" && echo "test table created"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT (SELECT COUNT(*) FROM recs.train) AS train_views, (SELECT COUNT(*) FROM recs.test) AS test_views"Created your-project-id.recs.train
train table created
Created your-project-id.recs.test
test table created
+-------------+------------+
| train_views | test_views |
+-------------+------------+
| 9553 | 1072 |
+-------------+------------+Step 3: the baseline, most popular movies
Why: any smarter method must beat this. It gives everybody the same list.
# Baseline: recommend the 5 most popular movies the user has not seen yet (the same list for everybody)
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT m.title, m.genre, COUNT(*) AS views FROM recs.train t JOIN recs.movies m USING (movie_id) GROUP BY m.title, m.genre ORDER BY views DESC LIMIT 5"+-----------------+--------+-------+
| title | genre | views |
+-----------------+--------+-------+
| Storm Action 1 | Action | 332 |
| Garden Action 2 | Action | 287 |
| Rocket Comedy 1 | Comedy | 222 |
| Garden Action 3 | Action | 219 |
| City Horror 1 | Horror | 213 |
+-----------------+--------+-------+
Three of the top five are Action movies. A user who only likes Romance and Comedy would get almost nothing useful from this list.
Break it: the textbook recommender on free pricing
BigQuery ML has a model made for exactly this job: MATRIX_FACTORIZATION. Let us train it.
# Break it: the textbook BigQuery ML recommender is MATRIX_FACTORIZATION. Try it on the normal on-demand pricing
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE MODEL recs.mf_model OPTIONS (model_type = 'matrix_factorization', user_col = 'user_id', item_col = 'movie_id', rating_col = 'rating', num_factors = 8) AS SELECT user_id, movie_id, rating FROM recs.train" 2>&1 | tr -s '\n' ' ' | sed 's/.*_1.: //' | cut -c1-300; echoTraining Matrix
Factorization models is not available for on-demand usage. To train, please set
up a reservation (flex or regular) based on instructions in BigQuery public
docs.The model is refused: matrix factorization is not available for on-demand usage. On-demand is the default "pay per byte scanned" pricing that the free tier uses. Matrix factorization needs a reservation (dedicated BigQuery capacity, paid by time). Reservations are a good choice for a company running recommendations every day, but not for a free learning project. So we build a recommender that works with on-demand pricing.
Fix it: a recommender that works on the free tier
Step 4: a taste profile for every user
Why: describe each user by what they watch: the share of their training views in each of the 6 genres. These 6 numbers are the user's features.
# Fix it (a model that works on-demand): describe every user's taste as the share of their views in each genre
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE TABLE recs.taste AS SELECT user_id, ROUND(COUNTIF(genre = 'Action') / COUNT(*), 2) AS action, ROUND(COUNTIF(genre = 'Comedy') / COUNT(*), 2) AS comedy, ROUND(COUNTIF(genre = 'Drama') / COUNT(*), 2) AS drama, ROUND(COUNTIF(genre = 'Horror') / COUNT(*), 2) AS horror, ROUND(COUNTIF(genre = 'SciFi') / COUNT(*), 2) AS scifi, ROUND(COUNTIF(genre = 'Romance') / COUNT(*), 2) AS romance FROM recs.train JOIN recs.movies USING (movie_id) GROUP BY user_id" && echo "taste table created"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT * FROM recs.taste WHERE user_id IN (7, 8) ORDER BY user_id"Created your-project-id.recs.taste
taste table created
+---------+--------+--------+-------+--------+-------+---------+
| user_id | action | comedy | drama | horror | scifi | romance |
+---------+--------+--------+-------+--------+-------+---------+
| 7 | 0.37 | 0.05 | 0.42 | 0.05 | 0.05 | 0.05 |
| 8 | 0.16 | 0.47 | 0.05 | 0.32 | 0.0 | 0.0 |
+---------+--------+--------+-------+--------+-------+---------+User 7 watches mostly Drama (0.42) and Action (0.37). User 8 likes Comedy (0.47) and Horror (0.32). Two very different people.
Step 5: train a K-means model in BigQuery ML
Why: model_type = 'kmeans' groups the 600 taste profiles into 10 clusters of similar users. K-means is allowed on on-demand pricing. ML.EVALUATE returns quality numbers: a lower Davies-Bouldin index means clusters that are tighter and further apart.
# Train a K-means model in BigQuery ML: it groups users with similar taste into 10 clusters
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE MODEL recs.taste_model OPTIONS (model_type = 'kmeans', num_clusters = 10, standardize_features = FALSE) AS SELECT * EXCEPT (user_id) FROM recs.taste" && echo "model trained"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT ROUND(davies_bouldin_index, 3) AS davies_bouldin, ROUND(mean_squared_distance, 3) AS mean_squared_distance FROM ML.EVALUATE(MODEL recs.taste_model)"Created your-project-id.recs.taste_model
model trained
+----------------+-----------------------+
| davies_bouldin | mean_squared_distance |
+----------------+-----------------------+
| 1.145 | 0.062 |
+----------------+-----------------------+Step 6: recommend 5 movies per user
Why: ML.PREDICT tells us each user's cluster (CENTROID_ID). Then, in plain SQL: count how often each movie was watched inside each cluster, remove movies the user already watched (NOT EXISTS), and keep the top 5 per user (ROW_NUMBER() OVER (PARTITION BY user_id ...)).
# Recommend: for each user, the most watched movies in THEIR cluster that they have not seen yet
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE TABLE recs.cluster_of AS SELECT user_id, CENTROID_ID AS cluster FROM ML.PREDICT(MODEL recs.taste_model, (SELECT * FROM recs.taste))" && echo "every user has a cluster"
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE TABLE recs.top5 AS
WITH pop AS (SELECT c.cluster, t.movie_id, COUNT(*) AS n FROM recs.train t JOIN recs.cluster_of c USING (user_id) GROUP BY c.cluster, t.movie_id),
cand AS (SELECT u.user_id, p.movie_id, p.n FROM recs.cluster_of u JOIN pop p USING (cluster) WHERE NOT EXISTS (SELECT 1 FROM recs.train t WHERE t.user_id = u.user_id AND t.movie_id = p.movie_id))
SELECT * FROM (SELECT user_id, movie_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY n DESC, movie_id) AS rank FROM cand) WHERE rank <= 5" && echo "top 5 per user saved"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT r.rank, m.title, m.genre FROM recs.top5 r JOIN recs.movies m USING (movie_id) WHERE r.user_id = 7 ORDER BY r.rank"📝⭐ Recommended by CareerByteCode · Certification examGoogle Analytics Certification (GA4) PracticeTake the examTake the exam →Created your-project-id.recs.cluster_of
every user has a cluster
Created your-project-id.recs.top5
top 5 per user saved
+------+-----------------+--------+
| rank | title | genre |
+------+-----------------+--------+
| 1 | Mirror Action 7 | Action |
| 2 | River Action 6 | Action |
| 3 | Storm Action 9 | Action |
| 4 | Garden Drama 4 | Drama |
| 5 | Letter Drama 5 | Drama |
+------+-----------------+--------+
User 7 (a Drama and Action fan) gets three Action and two Drama movies: personal recommendations, not the global top list.
Step 7: evaluate, did it work?
Why: this is the step most tutorials skip. We compare both methods on the hidden last 3 views of every user: a hit means at least one of the 5 recommendations is a movie the user really watched next.
# Did it work? Hit rate: the share of users whose top 5 contains at least one of the 3 movies they really watched next
bq query --quiet --use_legacy_sql=false --format=pretty "WITH pop AS (SELECT movie_id, COUNT(*) AS n FROM recs.train GROUP BY movie_id),
popcand AS (SELECT u.user_id, p.movie_id, p.n FROM (SELECT DISTINCT user_id FROM recs.train) u CROSS JOIN pop p WHERE NOT EXISTS (SELECT 1 FROM recs.train t WHERE t.user_id = u.user_id AND t.movie_id = p.movie_id)),
poptop AS (SELECT user_id, movie_id FROM popcand WHERE TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY n DESC, movie_id) <= 5),
hits AS (SELECT 'popular (same for all)' AS method, user_id, MAX(IF(t.movie_id IS NOT NULL, 1, 0)) AS hit FROM poptop p LEFT JOIN recs.test t USING (user_id, movie_id) GROUP BY user_id
UNION ALL SELECT 'taste clusters (BQML)', user_id, MAX(IF(t.movie_id IS NOT NULL, 1, 0)) FROM recs.top5 r LEFT JOIN recs.test t USING (user_id, movie_id) GROUP BY user_id)
SELECT method, COUNT(*) AS users, ROUND(100 * AVG(hit), 1) AS hit_rate_pct FROM hits GROUP BY method ORDER BY hit_rate_pct"+------------------------+-------+--------------+
| method | users | hit_rate_pct |
+------------------------+-------+--------------+
| popular (same for all) | 600 | 25.5 |
| taste clusters (BQML) | 600 | 59.8 |
+------------------------+-------+--------------+
The taste clusters hit for 59.8 percent of users, more than twice the 25.5 percent of the popularity list. That is the number you would show your team, and the number you try to beat next time.
POC: prove it works
- 10,625 views loaded and split by time into training (9,553) and test (1,072) views.
MATRIX_FACTORIZATIONwas refused on on-demand pricing: a real limit, with the exact error.- A K-means model in BigQuery ML grouped users into 10 taste clusters (Davies-Bouldin 1.145).
- Each user got 5 personal recommendations of movies they had not watched.
- On real held-out views, the hit rate was 59.8 percent vs 25.5 percent for the popularity baseline.
Quiz task: check what you learned
- Why hide each user's last views instead of random ones? (Answer: in real life we predict the future from the past; random hiding lets the model peek at later behaviour.)
- Why must recommendations exclude movies the user already watched? (Answer: recommending what they saw is useless, and it inflates the hit rate unfairly.)
- What would you need to use
MATRIX_FACTORIZATION? (Answer: a BigQuery reservation (capacity-based pricing), not on-demand.) - How could you improve the taste model? (Answer: more features (ratings, recent views weighted higher), a different number of clusters, or mixing in popularity.)
- What does a hit rate of 59.8 percent mean? (Answer: for 59.8 percent of users, at least one of the 5 suggestions was a movie they really watched next.)
Clean up (delete everything)
# Clean up: delete the dataset with its tables and models
bq rm -r -f -d recs && echo "dataset recs deleted"
rm -f movies.csv views.csv
echo "datasets left: $(bq ls --format=json 2>/dev/null | python3 -c 'import json,sys; print(len(json.load(sys.stdin) or []))' 2>/dev/null || echo 0)"dataset recs deleted
datasets left: 0
FAQ
Is K-means a "real" recommender? It is a simple form of collaborative filtering: users with similar behaviour share recommendations. Matrix factorization and deep learning models learn finer patterns, but a clustered or popularity-based recommender is a strong, explainable first version.
How do I serve recommendations in an app? Store the top5 table and read it by user_id (BigQuery for reports, or copy it nightly to a low-latency store such as Firestore or Memorystore for the app).
How often should I retrain? As often as tastes and the catalogue change: nightly or weekly for most services. A scheduled query can rebuild the taste table, the model and the top 5.
What about new users with no history (cold start)? Show the popularity list (or ask them to pick genres) until they have watched a few items.
Is hit rate the only metric? No. Teams also use precision and recall at K, coverage (how many different items get recommended) and, most importantly, online A/B tests with real users.
Can BigQuery ML do content-based recommendations? Yes: describe items by features (genre, cast, text embeddings) and recommend items similar to what the user liked, for example with the cosine distance between embeddings.
Troubleshooting
| You see | Why | Fix |
|---|---|---|
Training Matrix Factorization models is not available for on-demand usage | Matrix factorization needs a reservation | Use a reservation, or a K-means or popularity approach as here |
Not found: Dataset ...:recs | The dataset is missing or in another location | bq ls; keep everything in the US location |
| All users get the same 5 movies | The cluster join is missing or num_clusters is 1 | Check recs.cluster_of, use several clusters |
| Hit rate looks too good | Test views leaked into training | Split by time and train only on recs.train |
ML.EVALUATE returns nothing for a model | The model is still training or failed | bq show --model recs.taste_model |
What next
- Train a first model with SQL: BigQuery ML: Train a Model with Just SQL.
- The SQL behind it: BigQuery for Beginners: Query Billions of Rows for Free.
- More hands-on posts: ByteBlog. Practice in ByteLabs and see the 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.
Related blogs
CareerByteCode TeamCI/CD for Machine Learning with Cloud Build
What you build: a Cloud Build pipeline that retrains a churn model whenever new data lands in a bucket, checks it with a quality gate and redeploys on Cloud Run; broken data is stopped automatically.
#google-cloud
CareerByteCode TeamMLflow on Google Cloud: Track Every Experiment
What you build: an MLflow tracking server on Google Cloud with models in Cloud Storage, five tracked runs, a registered champion and the missing-URI mistake. Every step tested.
#feature
CareerByteCode TeamFeature Store Explained with a Real Example
What you build: Feast with real customer data: a leaky training set that scores 94 percent, the point-in-time truth, online serving and TTL. Every step tested.
#google-cloud
CareerByteCode TeamMLOps Project for Your Resume on Google Cloud
What you build: an MLOps loop on Google Cloud: quality gate, model registry, batch scoring to BigQuery, drift checks and a retrain that must earn its place. Every step tested.
💬 Comments (0)
Top · NewestSign in to comment