Project 2: BigQuery Part Deux

Tier	What you ship	Dataset and BQ cost	Effort
Tier 80	The base project: Spark ELT for a 1 GB Wikipedia slice, BQ embeddings, 6 queries, BQML model, scale math, writeup. "The notebook you'd send to a hiring manager."	1 GB Wikipedia + your dataset. ~$2 to $8 of your $50 BQ credit.	29 to 38 hours. ~200-300 Python LOC + ~150-310 SQL lines.
Tier 87 warehouse layout	+ Section 5: scale fact layer to ≥ 5 GB, design partition and cluster keys, build a materialized view, before/after benchmark. "The stack you'd ship before lunch."	5+ GB combined fact layer. + ~$1.	34 to 43 hours. + ~50-80 Python LOC.
Tier 93 production retrieval	+ Section 6: scale embed pass to 1 GB of text, build a real BQ vector index, run a hybrid keyword + semantic query, compare indexed vs. brute-force latency. "The real-scale retrieval layer."	~5 GB of vector data. + ~$8 to $15 for the embed pass.	39 to 49 hours. + ~30-60 Python LOC.
Tier 100 capstone	+ Section 7: pick one of six career-narrative capstones (Wikipedia 22 GB / retrieval eval rigor / multi-model + calibrated / forecasting / geospatial / LLM extraction). "The portfolio piece you'd open the interview with."	Depends on capstone. + ~$1 to $15.	44 to 54 hours. + ~80-200 Python LOC.
+5 bonus	Awarded at Tier 93 or above for quality of storytelling: analysis depth, engineering judgment, and how clearly the project's insights come through.	n/a	Not LOC; the polish you put on the writeup.

Section	Title	Points
Tier 80: required for everyone
Phase 0	Spark + 1 GB Wikipedia slice + keyword join + proposal (Oct 30). Banked on time; forfeit if late.	10
Phase 1	Embed slice in BigQuery (≤ 100 MB text) + one semantic join query	10
1	6 non-simple queries (2+ using Wiki layer) + 3 visualizations	20
2	BQML model + evaluation	15
3	Scale math + query plans (10x and 100x I/O)	10
4	Writeup + AI takeaways + 5-min demo	15
Tier 80 total	80
Tier 87 unlock
5	Warehouse layout: ≥ 5 GB fact layer + partition + cluster + MV + before/after bench	7
Tier 87 total	87
Tier 93 unlock (requires Section 5)
6	Production retrieval at scale: 1 GB embed + BQ vector index + hybrid keyword + semantic query + latency comparison	6
Tier 93 total	93
Tier 100 unlock (requires Section 6)
7	Capstone: pick any one of six	7
Tier 100 total	100

Capstone	What you ship	Hours
(a) Wikipedia scale-up data platform / data eng	Scale your Spark ELT to ≥ 10 GB of Wikipedia (target 22 GB). Partition + cluster the loaded table. At least one analytic query that combines this layer with your dataset in a way that the 1 GB slice could not answer.	6-8
(b) Retrieval eval rigor AI / search	Take Section 6's hybrid retrieval to full eval rigor. Author 20+ eval queries with known ground truth. Report R@1, R@5, R@10 for keyword-only, vector-only, and the hybrid you built. Tune the hybrid weighting; report which signal wins on which query shape and why.	5-7
(c) Multi-model + calibrated decision product DS	Train 3+ model classes (e.g., logistic regression, boosted tree, deep). Slice analysis on a fairness or cohort axis. Threshold tied to a business metric (e.g., max expected revenue at cost C). Report what you'd ship.	5-7
(d) Forecasting + backtest quant / ops research	Train a BQML `ARIMA_PLUS` (or similar) on a time-series target. 4-week holdout. Compare against a naive baseline (last-value or seasonal-naive). Seasonality decomposition. Backtest report.	5-7
(e) Geospatial analytics spatial / urban DS	Join your dataset + Wikipedia on geography using BQ's `ST_*` functions (or H3). Spatial clustering (`ST_CLUSTERDBSCAN`), heatmap, distance-decay analysis. At least one visualization that renders on a map.	5-7
(f) LLM-augmented extraction modern data eng / AI	Use `ML.GENERATE_TEXT` (or `AI.GENERATE_TABLE`) at scale to extract structured fields from your Wikipedia slice. Join the extracted structure into your analysis. Sanity-check extraction quality on a 100-row sample.	5-8

Getting Started

Q: What exactly carries over from Project 1?

You can reuse: your project overview, table descriptions, and setup code. Your Project 1 fact tables stay in their BigQuery dataset and become one half of every join in Project 2.

Must be new: all 6 queries, all 3 visualizations, your Wikipedia layer, your embeddings, and the BQML model. If you partner, choose one of the two Project 1 notebooks as your starting point; you do not merge both. If you start fresh, your new dataset still needs 3+ tables and at least 500 MB.

Q: What does the Oct 30 proposal look like?

The proposal IS Phase 0, worth 10 points. Submit by Oct 30 with Phase 0 actually working and the 10 points are yours; miss the deadline and Phase 0 forfeits. One markdown cell at the top of your notebook (with Phase 0 code cells run above it), covering:

Proof of Phase 0. Paste your Spark filter recipe (the keyword set or category prefix), the output of your one keyword join query, and one viz.
Filter precision. Sample 30-100 articles from your slice. What fraction are actually in your domain? One short paragraph.
What surprised you. One paragraph on what the join surfaced.
Plans. Target tier (80 / 87 / 100). If 100, which capstone (a-f). If partnering: who owns which section.

Q: Where are the rubric details?

Inline in the template Colab notebook. Each section has its rubric, its expected output, and a "what good looks like" cell. The summary on this page is the macro picture; the per-section details live in the template.

Phase 0: Spark + Wikipedia (all tiers)

Q: How do I run Spark on Colab?

The template's first cell installs a pinned PySpark and starts a local SparkSession. Colab's free CPU runtime is enough for the 1 GB filtered slice. Do not try to set up a Dataproc cluster, you do not need one and it would cost you credits you don't have.

Q: How do I filter Wikipedia to my domain?

Three recipes the template ships, pick one and adapt:

Title-regex / keyword set. List 20 to 50 keywords for your domain. Spark filters articles whose title or first paragraph matches. Cheap and obvious.
Category prefix. Wikipedia articles ship with a categories array. Filter on prefixes like Category:Medicine, Category:Companies_of_*. Higher precision than keyword.
Link-graph hop. Start from a seed set of articles you know are in-domain, follow their outbound links one hop. Highest recall, fiddliest to debug.

The template includes four worked recipes (FHIR, NYC taxi, finance, music) you can copy and edit. Report your filter's hit rate (sampled) in 1-3 sentences.

Q: What does the Phase 0 keyword join look like for my domain?

Three examples to anchor on. Same shape, different domain:

FHIR / healthcare: for each of your top-20 patient conditions, count Wikipedia articles whose title or first paragraph mentions the condition name. LEFT JOIN wiki_domain w ON LOWER(w.title) LIKE CONCAT('%', LOWER(c.condition_name), '%'). The chart is condition coverage from highest to lowest.
NYC taxi / urban: for each NYC neighborhood you see in your trip data, join to Wikipedia articles about that neighborhood. Plot trip pickup count against article length as a proxy for cultural prominence.
Finance / public companies: for each ticker in your dataset, look up the article whose title matches the company name. Pull the infobox industry field. Aggregate analytics by inferred industry.

Q: Colab dies in the middle of my Wikipedia download. Help.

Two mitigations. First, checkpoint to Drive: write the filtered Parquet to a mounted Drive folder before doing the BQ load. If the session dies, you don't restart the whole pipeline. Second, filter early: apply your keyword filter inside the Spark read, not after. The full 22 GB doesn't need to materialize in RAM, Spark can stream-filter it.

Q: How do I parity-check my Phase 0 join?

Run the same title-keyword query against bigquery-public-data.samples.wikipedia (a public 314M-row Wikipedia revision sample with a title column, no upload required). Counts should agree within sampling tolerance once you align on title case and keyword set. If they disagree wildly, your filter is wrong before you spend a week on analysis. The template ships this parity helper.

Phase 1: Embeddings in BigQuery (all tiers)

Q: How much does Phase 1 actually cost?

It depends on how much text you embed. ML.GENERATE_EMBEDDING at text-embedding-005 charges roughly $0.025 per 1k characters. Honest numbers for a typical filtered slice:

30K articles × ~500 chars (first paragraph): ~$0.40
100K articles × ~500 chars: ~$1.25
100K articles × full text (~3k chars): ~$7.50

Default rule: embed first paragraphs only, cap at ~100 MB of text. Total typical cost: $1 to $3. If you want to embed more, the FAQ math is right there; you make the call.

Q: What's the minimum SQL to embed and query?

Four statements. Template ships them; this is the shape:

-- 1. Register the remote embedding model
CREATE OR REPLACE MODEL `myproj.cs145_p2.embed_model`
REMOTE WITH CONNECTION `myproj.us.vertex_ai_connection`
OPTIONS(ENDPOINT = 'text-embedding-005');

-- 2. Embed your filtered slice (input table must have a column named `content`)
CREATE OR REPLACE TABLE `myproj.cs145_p2.wiki_domain_embedded` AS
SELECT
  article_id, title, url, first_paragraph,
  ml_generate_embedding_result AS embedding
FROM ML.GENERATE_EMBEDDING(
  MODEL `myproj.cs145_p2.embed_model`,
  (SELECT article_id, title, url, first_paragraph,
          first_paragraph AS content
   FROM `myproj.cs145_p2.wiki_domain`)
)
WHERE ml_generate_embedding_status = '';

-- 3. Index it (optional for slices < 5 GB; brute-force search works at small scale)
CREATE OR REPLACE VECTOR INDEX wiki_idx
ON `myproj.cs145_p2.wiki_domain_embedded`(embedding)
OPTIONS(distance_type = 'COSINE', index_type = 'IVF');

-- 4. Query it semantically. VECTOR_SEARCH returns query.*, base.*, distance.
SELECT query.query_text, base.title, base.url, distance
FROM VECTOR_SEARCH(
  TABLE `myproj.cs145_p2.wiki_domain_embedded`, 'embedding',
  (SELECT
     'my query text' AS query_text,
     ml_generate_embedding_result AS embedding
   FROM ML.GENERATE_EMBEDDING(
     MODEL `myproj.cs145_p2.embed_model`,
     (SELECT 'my query text' AS content))),
  top_k => 5, distance_type => 'COSINE');

Q: What does the Phase 1 semantic join look like for my domain?

The shape is always: embed your query strings, run VECTOR_SEARCH against the embedded Wiki slice, join the top-K hits back to your dataset. Three concrete forms:

FHIR / healthcare: for each top-20 condition in your cohort, return the 3 Wikipedia articles closest in embedding space. Common surprise: the keyword join hits "diabetes mellitus" but the semantic join also surfaces "insulin resistance" and "metabolic syndrome," because the embedding model knows they cluster.
NYC taxi / urban: for each high-traffic neighborhood, return the 3 articles most semantically similar to its zone description. The semantic join catches articles that don't say the neighborhood name but describe the same vibe (transit hub, nightlife district, business corridor).
Finance / public companies: for each ticker's industry description, return the 3 closest Wikipedia articles. Useful for sector enrichment when the infobox-industry field is missing or stale.

Document what your semantic join found that the Phase 0 keyword join missed. That delta is the lesson.

Q: My semantic join returns nonsense. What's wrong?

Three things to check, in order. First: did your filter produce articles actually in your domain? Run the filter precision sample again. Garbage in, garbage out. Second: are you embedding the same field you're searching? If you embedded first_paragraph but your query text is a single keyword, cosine similarity won't reward you. Third: does your query text match the corpus register? Short keyword queries against long descriptive paragraphs is the classic mismatch.

Q: Should I build a CREATE VECTOR INDEX at Phase 1?

No. BQ vector indexes require ~5 GB of vector data minimum, which a 100 MB-text Phase 1 slice is well short of. Brute-force VECTOR_SEARCH is the right call at this scale. Tier 93 (Section 6) is where you scale the embed pass up enough to build a real index.

Section 1: SQL + visualizations (all tiers)

Q: What counts as a "non-simple" query?

Combines 2+ of {JOINs, CTEs, window functions, subqueries, complex CASE, VECTOR_SEARCH}. A VECTOR_SEARCH joined back to your dataset is non-simple by definition. Six queries total; at least 2 must use the Wiki layer (keyword join, semantic join, or both).

Q: What makes a good join query and a good visualization?

Joins must demonstrate a new insight, not just technical ability. Each chart should directly illustrate findings from a specific query. Include a caption like "This chart shows results from Query #3, revealing that..."

Good visualization

Clear trend or pattern
Directly answers your question
Labeled axes, title, legend

Bad visualization

Random scatter with no pattern
Unlabeled axes
Too cluttered or unrelated to the analysis

Section 2: BQML (all tiers)

Q: How do I create and evaluate a BigQuery ML model?

-- Train
CREATE OR REPLACE MODEL `your_dataset.model_name`
OPTIONS(
  model_type='linear_reg',
  input_label_cols=['target_column']
) AS
SELECT feature1, feature2, feature3, target_column
FROM `your_dataset.training_table`;

-- Evaluate
SELECT * FROM ML.EVALUATE(
  MODEL `your_dataset.model_name`,
  (SELECT feature1, feature2, target_column
   FROM `your_dataset.test_table`));

Model types: linear_reg (continuous), logistic_reg (binary classification), boosted_tree_regressor / boosted_tree_classifier (advanced), arima_plus (forecasting, capstone d).

Q: Can I use the embeddings as features in my BQML model?

Yes, and it's a great move. Use ML.GENERATE_EMBEDDING on the relevant text column of your training rows, then feed the embedding into a boosted_tree_* model. Saves your bullet point: "I used semantic features in my model."

Q: What makes a good ML prediction target?

Something product-relevant that you can't trivially calculate. "Predict tip amount from trip features" is good; "Predict trip_id" is not.

Red flag: if your model hits 99.9% accuracy, you probably have data leakage.

Section 3: Scale math (all tiers)

Q: How do I compute I/O costs at 10x and 100x scale?

The formulas (BigSort, BNLJ, SMJ, HPJ) are covered in the Big Sort lesson and Module 3 join lessons; use the equation sheet. The recipe: pick the operator that shows up in your query plan, substitute your table page counts and buffer size, then compute the same expression with P(R) replaced by 10 × P(R) and 100 × P(R). Report both numbers.

Tier 87: Warehouse layout (Section 5)

Q: My fact layer is 1 GB. Does that qualify for Section 5?

No. Section 5 requires the fact layer to be ≥ 5 GB. Below that, partitioning teaches almost nothing: the optimizer's small-table tricks dominate, $/query is rounding error, and your before/after benchmark won't show measurable improvement. Scale your Spark ELT to ≥ 5 GB first, then Section 5 starts paying out.

Q: How do I pick partition and cluster keys?

Partition on a date / time column most of your queries filter on. BigQuery physically separates rows into partitions, so a WHERE date BETWEEN ... skips most of the scan. Cluster on a categorical column most of your queries group or filter on (1 to 4 columns). Clustering re-sorts rows within a partition for faster range reads.

Run your chosen query before and after applying the layout. Report bytes scanned and $/query in both. If the layout didn't help, your keys don't match your queries; pick different ones.

Q: What does a materialized view buy me?

Precomputed, incrementally maintained query results. For an ML feature pipeline, CREATE MATERIALIZED VIEW feature_v AS SELECT ... FROM fact ... means your training query reads the MV instead of recomputing the aggregation. Cheaper, faster, and if your fact layer keeps growing, the MV updates incrementally instead of from scratch.

Tier 93: Production retrieval at scale (Section 6)

Q: What does Section 6 actually require?

Scale up the embed pass. Lift the Phase 1 cap from 100 MB of text to ~1 GB (a typical filter, embedding first paragraphs of ~1-2 M articles or full text of ~300 K articles). Expect $8 to $15 of BQ credit.
Build a real vector index. CREATE VECTOR INDEX on the scaled embedded table; BQ's index minimum is ~5 GB of vector data, which a 1 GB text embed pass clears.
Run one hybrid query. Combine a keyword score (BM25-style: title / first-paragraph keyword match) with vector similarity, in a single SQL statement.
Compare latency and bytes scanned between indexed VECTOR_SEARCH and brute-force VECTOR_SEARCH (force brute force with options => '{"use_brute_force": true}'). Report both.

Q: How do I write a hybrid query?

The standard shape: run vector search and a keyword filter as two CTEs, fuse the scores in a final SELECT. Reciprocal-rank fusion is the simplest defensible fuser; a weighted-sum on normalized scores is fine too. Document the fusion you chose in a comment.

WITH vec AS (
  SELECT base.article_id, base.title, distance,
         ROW_NUMBER() OVER (ORDER BY distance) AS vec_rank
  FROM VECTOR_SEARCH(
    TABLE wiki_domain_embedded, 'embedding',
    (SELECT ML.GENERATE_EMBEDDING(...)),
    top_k => 50)
), kw AS (
  SELECT article_id, title,
         ROW_NUMBER() OVER (ORDER BY LENGTH(title)) AS kw_rank
  FROM wiki_domain
  WHERE LOWER(title) LIKE '%diabetes%'
  LIMIT 50
)
SELECT COALESCE(vec.article_id, kw.article_id) AS id,
       1.0/(60+vec.vec_rank) + 1.0/(60+kw.kw_rank) AS rrf_score
FROM vec FULL JOIN kw USING (article_id)
ORDER BY rrf_score DESC LIMIT 10;

Q: My vector index is slower than brute force at 1 GB. Did I do something wrong?

Probably not. BQ's vector index has a fixed build cost and a query overhead; below a few million vectors, brute force can beat the index on small queries. That is itself the finding: document where the crossover is and why. Real-scale retrieval teams pick indexed vs. brute force based on exactly this kind of empirical measurement.

Q: $8 to $15 is a lot. Can I skip the scale-up and just use my Phase 1 slice?

No; the BQ vector index will not build below its data minimum, so the "production retrieval" story doesn't work at Phase 1 scale. If $15 is a budget problem, embed first-paragraphs of a broader filter (cheaper per article) rather than full text of a narrow filter. The cost table shows typical totals stay under your $50 credit.

Tier 100: Capstone choice (Section 7)

Q: Which capstone should I pick?

Pick by curiosity. Either the role you want to explore, or the shape of your dataset:

Your dataset has lots of text and you want AI/search: (b) retrieval eval rigor or (f) LLM extraction.
Your dataset has lots of geography: (e) geospatial.
Your dataset is time-series-shaped (transactions, prices, demand): (d) forecasting.
You want to prove you can scale a real Spark ELT: (a) Wikipedia 22 GB.
You want to prove you can ship a calibrated model: (c) multi-model.

Pick one. You may not earn capstone credit for partial work across multiple options.

Q: (a) Wikipedia 22 GB capstone, what's the bar?

You must scale your Spark ELT to at least 10 GB of Wikipedia (target the full 22 GB). The loaded BigQuery table must be partitioned and clustered. At least one analytic query in your notebook must combine this scaled layer with your dataset in a way that the 1 GB Phase 0 slice could not have answered. Expect 30 to 60 min of Spark wall time and at least one Colab session crash you have to recover from.

Q: (b) Retrieval eval rigor, what's the bar?

Section 6 already shipped one hybrid query. The capstone takes that to full eval discipline. Author 20+ eval queries in your domain with known-correct ground truth. Run all three retrievers (keyword-only, vector-only, your hybrid from Section 6) and report R@1, R@5, R@10 for each. Tune your hybrid weighting; report which signal wins on which query shape and why. The hybrid must beat the better single-signal baseline on at least one metric, OR your writeup explains the failure mode.

Q: (c) Multi-model + calibrated decision, what's the bar?

Train 3+ model classes on the same prediction target. Pick one based on a holdout metric. Do a slice analysis on a cohort or fairness axis you can defend. Tie a threshold to a business metric (max revenue at cost C, max recall subject to precision floor, etc.). The deliverable is "this is the model I'd ship and here's why," not "I trained three models."

Q: (d) Forecasting + backtest, what's the bar?

BQML ARIMA_PLUS (or equivalent) on a time-series target with clear seasonality. 4-week (or domain-appropriate) holdout window. Compare against a naive baseline (last-value or seasonal-naive). Decompose the seasonality and report it. Show the backtest table.

Q: (e) Geospatial capstone, what's the bar?

At least 3 queries using BQ's geo functions (ST_DISTANCE, ST_INTERSECTS, ST_CLUSTERDBSCAN, or H3 functions). Join your dataset and your Wikipedia layer on geography (e.g., neighborhood polygons, lat/lng buckets). At least one visualization renders on an actual map (geopandas, plotly, kepler.gl, your choice).

Q: (f) LLM extraction capstone, what's the bar?

Use ML.GENERATE_TEXT or AI.GENERATE_TABLE to extract structured fields from your Wikipedia article text at scale (1k+ articles). Examples: "for each drug article, extract mechanism of action," "for each company article, extract HQ city and founding year." Join the extracted structure into your analysis. Sanity-check extraction quality on a 100-row sample and report precision/recall on a defined schema.

AI Takeaways (all tiers)

Q: How much AI is too much?

Use AI freely. The real bar is whether you can talk through your filter design, your queries, your model, and your capstone in plain English. That skill is what data jobs actually lean on, even more than typing the SQL yourself. See the AI Policy for the full rule.

Q: What should the AI Takeaways section look like?

Short and informal. Half a page, not a report. 2 to 3 anecdotes (a few sentences each) plus one plain-English walkthrough of one piece of your pipeline. Good anecdotes capture a moment:

"AI suggested a Spark schema that silently dropped null titles. I caught it when my filter hit rate plummeted between dev and prod."
"AI's first cut at my VECTOR_SEARCH query embedded the same model for docs and query, but compared cosine across the wrong axis."
"AI suggested logistic regression for a continuous target. I caught it when ML.EVALUATE returned accuracy instead of R²."

Q: What should I double-check on AI-generated code?

Model type matches your target (regression vs classification).
Features make logical sense. No data leakage from features that "know" the target.
Embeddings and search use compatible models (same embedder for docs and queries).
Filter precision is sanity-checked, not assumed.
Query cost estimates match what you expected before you run anything > 1 GB scan.

Project 2: BigQuery Part Deux

Technical Memo: Build the Production Data Stack

Project Scope

Required resources

Estimated Work-Back Schedule

Week 1 · Land the production stack

Week 2 · Add a semantic layer

Week 3 · Tell the analytic story

Week 4 · Engineer for production

Week 5 · Ship a capstone and a story

Tiers at a glance

Why This Project Matters

Grading

Metrics you will report

Quality rubric (100 points)

Capstone menu (Section 7, pick one)

Library Policy and Honor Code

You CAN use

You CANNOT use

Honor Code

Frequently Asked Questions