Project 2: BigQuery Part Deux

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.

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.

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.

Q: What is the capstone?

Build a governed text-to-SQL agent on the warehouse you shipped in Tiers 80 to 93, then measure how much your curation lifts a bare model. It is the same three-file recipe you learned in Module 1B, on your own dataset: a definitions.sql (6 to 8 business terms, each pinned to one SQL query), a reference.md (tables, join paths, every NULL-prone column), and an eval set (about 10 questions with known answers). The agent must consult the definitions first, generate SQL, run it, and answer.

Q: How do I define the vocabulary in BigQuery, concretely?

BigQuery supports exactly this: a CREATE TABLE FUNCTION is a persistent, parameterized, table-valued function, the same idea as the Postgres example in Module 1B. You define each term once and call it by name:

CREATE TABLE FUNCTION myds.listener_of(artist STRING) AS (
  SELECT DISTINCT l.user_id
  FROM myds.Listens l JOIN myds.Songs s USING (song_id)
  WHERE s.artist = artist
);
-- compose by name, no re-derivation:
SELECT user_id FROM myds.Users
EXCEPT DISTINCT
SELECT user_id FROM myds.listener_of('Taylor Swift');

Then the agent does not discover these on its own. You supply a manifest in the model's context: the function names, their parameters, and a one-line description each, alongside the schema and join policy, with the instruction to compose the vocabulary first and write raw SQL only when nothing fits. The table functions are where the vocabulary lives and runs; the manifest is how the model learns it exists. (Prefer not to create database objects? The alternative is a manifest-only approach: keep the definitions as a YAML or markdown of name to SQL template and have the agent inline them as CTEs. That is the dbt / Cube style, and it is fine too.)

Q: What does the text-to-SQL agent actually do? Am I building a model?

No, you are not training anything. The agent is a small loop (about 100 to 170 lines) around a commodity model. Per question it: (1) assembles context, loading your manifest (the vocabulary names and descriptions), the schema, and the join policy into the prompt; (2) asks the model to write BigQuery SQL for the question, composing your table functions where they fit; (3) runs that SQL on BigQuery; (4) returns the answer, and for an eval, compares it to the confirmed answer. The model's job is the open-ended part: map fuzzy English to the right composition of your vocabulary and parameters, and write raw SQL for anything the vocabulary does not cover. Your job is the harness plus the grounding, the manifest and policy that make that mapping reliable. For a small domain you paste the whole manifest into the prompt; with hundreds of terms you would retrieve just the relevant ones, which is the same idea at scale.

Q: What is the deliverable, exactly?

The bare-vs-grounded measurement. Run your agent with no knowledge model, then with it, on the same question set, and report both accuracies. Grounded must beat bare, or your writeup explains the failure mode (a valid outcome; it is how Anthropic learned that raw query history was worthless). Then run the gap-closing loop: every failed question becomes required curation, and you re-measure.

Q: How is the eval set built so it is not gameable?

Generate the questions from your domain description, not your definitions, so they surface gaps you did not plan for (this is how Anthropic builds "long-tail" evals). Author about 10, spanning your definitions and including at least 2 gotcha questions (NULLs, no-activity rows, dedup). Ground truth for each is a verified SQL query you trace by hand, the Module 1 debug-table skill. You are graded on coverage (fraction answered correctly, target ≥ 85%), lift, breadth, and reference-doc completeness, not on the model you called.

Q: Which model do I use? Do I need my own API key?

The generator is a commodity and is not graded, so use whatever is easiest. The zero-setup default is BigQuery ML ML.GENERATE_TEXT (or AI.GENERATE_TABLE), which calls Gemini from inside BigQuery with no external key and bills against your $50 credit; the template ships the remote-model connection set up for you. If you already have a Claude, OpenAI, or Gemini API key, use it. Running an open model in Colab is allowed but not recommended: a small open model is weaker at text-to-SQL and muddies the lift you are trying to measure.

Q: My dataset is time-series / geographic / text-heavy. Does the capstone still fit?

Yes, and its shape lives in your eval questions. A time-series dataset should include forecast-style questions, a geographic one ST_DISTANCE-style questions, a text-heavy one extraction and semantic questions, a large one scale questions. Same capstone, your flavor. Your agent may even call a BQML model, and a prediction can be one of your definitions.