Build NanoQuery, a small agentic analytics stack for your own dataset. Connect tables to domain documents and answer analytical questions. Higher tiers test whether reusable SQL definitions help an LLM answer questions correctly.
Proposal: Oct 30 (10 points; no late days)Final: Nov 20 (up to 90 PTS; late-day policy)
Embeddings and LLM runs are not required for the proposal.
In Colab, choose File → Upload notebook. Local Jupyter also works.
Runtime: CPU works; use a GPU if available to speed up embeddings. The notebook selects an available GPU automatically; otherwise it uses CPU.
HTML preview — quick browsing Examples — worked report and topic ideas BigQuery setup and troubleshooting AI policy and Honor CodeTeams: Two students recommended; solo allowed. One submission per team. Late days are deducted from both partners and limited by the smaller remaining balance. See the partnership and late-day policy.
We grade completed work, with partial credit in each category. Your proposed tier is a planning target, not a score cap.
In the notebook: Phases 0–1 and Sections 1–4.
Each analytical query must combine at least two of joins, CTEs, window functions, subqueries, CASE-based analysis, and semantic retrieval. CASE-based analysis applies a classification or calculation rule used in your answer. Semantic retrieval followed by a join counts as two techniques. Each chart must show a specific query result with a title, labeled axes, and a short caption.
| Graded work | PTS |
|---|---|
| Prepare documents, check their links to tables, and submit the proposal (Phase 0) | 10 |
| Create passages and embeddings, load BigQuery, and run semantic search (Phase 1) | 10 |
| Write five analytical queries and create three visualizations | 20 |
| Check document evidence, table mappings, and answers for two of those queries | 15 |
| Analyze query cost and estimate I/O at 10× and 100× scale | 10 |
| Explain findings (5 PTS), AI use and checks (6 PTS), and the pipeline (4 PTS) | 15 |
| Core total | 80 |
Section 4 writeup: Half a page to one page. Code, figures, tables, and Section 7 evaluation are outside this limit. Include 2–3 examples of AI assistance and how you checked its suggestions. If you did not use AI assistance, describe 2–3 independent checks, debugging decisions, or revisions instead. Tier 100 still requires the LLM experiment.
Reuse two of your five analytical queries. For each, show the question, retrieved excerpts and source links or paths, mapping to table IDs, SQL, and checked result.
In the notebook: Section 5 — Governed SQL library.
Reuse your five analytical queries. Turn each into a named, parameterized SQL definition with clear domain rules and a small correctness test. At T100, these definitions provide context for the LLM.
Tests (3 PTS): give each definition a small input with a hand-checked expected result. Run the SQL and compare actual rows with expected rows using assertions. Cover NULLs, duplicates, and missing matches across the library.
For example, give one taxi zone three trips and another zone one trip. Write the expected counts, then check that your query returns those counts.
Reference notes (1 PT): reuse your existing schema and document-to-table mappings; add allowed joins and supported question types.
In the notebook: Section 6 — Ground truth.
Save your T87 SQL library, then choose five new questions that reuse or combine its definitions. Change parameters, filters, or aggregations rather than repeating the original questions. Reuse your existing tables and documents.
Build a verified answer key for T100; no LLM run is required for T93. Setup and test queries do not count toward the five analytical or five reference queries.
In the notebook: Section 7 — Text-to-SQL comparison and evaluation.
For the same five questions, generate SQL with two prompts:
Keep the saved T87 SQL library fixed across all five comparisons. Keep the Tier 93 reference SQL and results out of both prompts. For each question, keep the model, settings, data, and question-specific instructions identical. For document-based questions, give both prompts the same entity IDs used in the T93 reference.
Use the notebook helper to build the prompts. Paste each into a fresh chat, then paste the original SQL response back into the notebook.
List the allowed project.dataset.table IDs, including document tables. Check every physical table in joins, CTE bodies, and subqueries. CTE names and aliases are not additional physical tables. Reject unapproved, unsupported, or unresolved references; do not silently extend the list.
Show one allowed query and one rejected query. On rejection, save the original SQL and reason, skip execution, and continue. These checks belong to the 2 PTS for safe execution.
Different SQL can produce the same correct answer. Full credit is possible at any model accuracy when your references, checks, and analysis are correct. Preserve original responses; label later fixes and reruns separately. Five questions support a case study, not a general claim about model accuracy.
Bonus: Staff select the top five Science projects for overall quality and compelling work; each receives 0–5 bonus points.
Vector indexes and hybrid retrieval are optional experiments.
Why Wikipedia? Wikipedia provides background facts that your tables may not contain.
TITLES and run the download cells. For custom documents, supply an extract with id, url (source link or stable path), title, and text. Run the Spark preparation cells and inspect a random sample of 30 documents for relevance (all if fewer than 30). Replace missing or unrelated documents.Example: waterfront-park article → park location → taxi-zone ID → compare weekend and weekday drop-offs.
For categories, write a labeling rule and check the assigned labels. Documents must affect the records selected or the information returned in your two document/table analyses. See T80 for scoring and evidence requirements.
“My dataset contains [tables and columns]. I want to answer [example questions]. Suggest Wikipedia articles that provide useful facts or background. Group them by topic and explain the connections.”
Review the suggestions and download the original articles; do not treat generated summaries as source documents.
Report document count, passage count, text bytes, and embedding dimensions.
Only the proposal and final submission are deadlines; adjust this suggested pace to your target tier.
Use AI as a consultant. Draft your analytical queries, SQL definitions, and implementation yourself first. Then use AI to brainstorm, explain, or debug. Compare its suggestions with your draft, test the changes, and write your final code yourself. Explain each query and design choice with the chat window closed.
Source selection: AI may suggest article titles. Review the suggestions and use the original source documents, not generated summaries. Record this assistance.
LLM experiments: Your chosen LLM may generate SQL inside the workflow. Keep those outputs in your experiment records and evaluate them against reference SQL and answers that you wrote and checked.
Use the supplied setup code and model libraries. Generate your own embeddings from the selected text. Precomputed Wikipedia embeddings and pre-built project solutions are prohibited.
We’ve aimed to keep the project page and notebook consistent. The project page gives the full requirements and grading; the notebook provides compact guidance, function contracts, and starter code.
The supplied helpers support setup and retrieval; you write your dataset-specific queries, mappings, and tests through T87. For T93–T100, extend your work with verified reference answers and the LLM comparison. You may need to adapt the helpers to your implementation.
You’re welcome to adapt the starter code within the project requirements. If something seems inconsistent or buggy, check the project page and ask privately on Ed; include the error and a small reproducible example for bugs. Note important assumptions in your notebook so we can take them into account when reviewing your work.
The starter supplies setup, chunking, BigQuery helpers, A/B prompts, read-only SQL checks, query-byte limits, and response capture. You write filters, mappings, analytical SQL, and the table allowlist check, plus tests and evaluation for your tier. No frontend or model training is required.
Use your own Google Cloud project ID, choose a dataset for your project outputs, and match its location to your source data. Enable RUN_BIGQUERY and run the connection check. It checks SELECT 1, reads one row from your chosen source table, and loads and reads a tiny setup table in your output dataset. For Wikipedia, fill in the article titles and contact field, then enable RUN_COLLECTION. For custom documents, follow the document setup instructions. Keep the large source tables in BigQuery.
The starter’s 100 MB default limits each query’s estimated scan, not your total dataset size. Call preview_query(sql) to see the estimate and limit before execution. If the estimate exceeds the limit, narrow the scan or deliberately change the limit within your available quota. The notebook never raises it automatically. A LIMIT clause restricts returned rows and generally does not reduce the bytes scanned.
Tables stay in BigQuery; Spark and MiniLM run in Colab or locally. For local work, use Python 3.11+, Java 17, Google Application Default Credentials, and preferably at least 8 GB RAM. In Colab, uncomment the notebook’s optional Drive checkpoint cell before collecting documents; rerun it after reconnecting to reuse saved files. Saving the notebook alone does not preserve runtime files; runtime availability varies.
The starter loads MiniLM for embeddings. T100 also uses an LLM chat of your choice; follow the T100 comparison instructions.
Billing is not required. We verified imports, embedding uploads, vector search, and SQL with billing disabled. Stay within BigQuery sandbox limits. Billing for additional experiments is optional and does not change grading.
Use Spark to load, filter, deduplicate, and prepare the extract. Practice building a reproducible preparation step on a small collection. Save Parquet and stop the Spark session before computing embeddings. The starter also releases its local Spark gateway; a kernel restart after checkpointing is another option.
Use the supplied chunker on your documents or selected relevant sections. If you select sections, record your selection rule. Preserve document IDs, source links or paths, and passage IDs. The chunker keeps passages within MiniLM's input limit. Use the same MiniLM model for passages and search questions.
Compute them in Colab or local Jupyter with sentence-transformers/all-MiniLM-L6-v2. Save the text,
source identifiers, and embedding arrays as Parquet, then batch-load the records into
BigQuery. Query embeddings are computed with the same model and passed into SQL.
No remote embedding endpoint or vector index is required.
Inspect the text and the document-to-table mapping. Record the mismatch and refine your question or document selection. Preserve the evidence for your final queries so another reader can check the answers.
For example, join trips to zones, then use a window function to rank zones by trip count. This combines two required techniques. See the Tier 80 rubric for the full rule.
Inspect one analytical query's measured bytes and available execution details. Choose an appropriate operator from the Module 3 lessons, state page size, buffer size, and input cardinalities, and compute I/O at the current size and hypothetical 10× and 100× sizes. State which inputs grow and when the algorithm needs more passes. Apply the textbook algorithm to your stated assumptions and label the results as estimates. Keep this analysis computational; use your existing data.
Proposal due Oct 30. Final notebook due Nov 20. Submit through Gradescope.
Submit executed Phase 0 cells: the Spark load/filter, document-to-table mapping, one keyword join and chart, and a random sample of 30 documents (all if fewer than 30) labeled for domain relevance, with the fraction relevant reported. Add your target tier, example questions, source details, and partner work split. No late days are allowed for the proposal. Embeddings and LLM runs are not required for the proposal. Your proposal query and chart count toward the final totals if they meet the final requirements.
.ipynb and PDF files, with both partners' names and work split where applicable. Open it in Colab and verify that all saved outputs are visible before submitting.These examples suggest a direction. Choose questions your own data and documents can answer.
Open the Global Fishing Watch example. A worked flow with queries, code, and saved results.
Optional background: the Spark and Distributed SQL colab walkthrough from Module 5.