Task-based reference for querying the All of Us Researcher Workbench CDR. Each page answers one "how do I do X" question with exact code, parameters, and inline Pitfall warnings for silent-wrong-answer traps.
Audience: fluent in SQL and pandas. No language basics here.
Looking up a specific task? Find it in the Reference index below. Building a full pipeline? See the Guides below — they chain Reference pages into end-to-end workflows with the connective reasoning between steps.
Callout key:
- Pitfall — something that produces a wrong answer silently. Inline, at the point of danger. The most important callout in the doc.
- Note — clarifying aside, not critical to correctness.
- Version note — behavior that differs by CDR release.
- Choose the right compute environment — Jupyter vs Dataproc/Hail, CDR config, Dataproc file persistence, sklearn alternatives.
- Define a case cohort by condition codes — Identify participants with specific conditions using SNOMED concept hierarchies.
- Build matched controls — Age/sex-matched controls with anti-join exclusion of case-condition participants.
- Exclude participants by condition history — Remove participants with prior diagnoses using date-aware anti-joins.
- Discover OMOP concepts by name or hierarchy — Find concept IDs by searching names, expanding via concept_ancestor.
- Extract demographic features — Age, sex, race/ethnicity from the person table with concept joins.
- Compute genetic ancestry principal components — Access pre-computed PCs for population stratification adjustment.
- Query condition occurrence with date constraints — Pull conditions within temporal windows, with provenance filtering.
- Build a phenotype from multiple condition codes — Combine SNOMED/ICD codes into binary phenotype columns.
- Query lab measurements by LOINC code — Pull from measurement with unit normalization and plausibility bounds.
- Query drug exposure history — Ingredient-level queries via concept_ancestor with duration handling.
- Apply pre/post-index-date windowing — Filter clinical events to windows relative to per-participant index dates.
- Audit a feature matrix for temporal leakage — Per-participant validation that no post-index data leaked into features.
- Query carrier status for a gene panel — Variant-to-person carrier lookup with non-carrier zero-fill.
- Filter variants to ClinVar P/LP — ClinVar significance filtering with compound-string handling.
- Discover genomics table schemas — INFORMATION_SCHEMA queries to verify table/column names across CDR versions.
- Integrate AlphaMissense scores — Match AoU variants to AlphaMissense pathogenicity predictions.
- Dry-run a query to estimate cost — Estimate bytes processed without execution.
- Cap query cost before execution — Set maximum_bytes_billed to abort expensive queries.
- Query survey responses — Pull and decode responses from AoU survey modules (The Basics, COPE, SDOH, etc.).
- Query procedure occurrence — Procedure lookups by CPT4/SNOMED with concept_ancestor rollup.
- Query visit occurrence — Visit counting, utilization metrics, and encounter-level event anchoring.
- Filter participants by observation period — Require minimum EHR coverage before including a participant.
- Distinguish EHR from survey data sources — Use type_concept_id to separate EHR, survey, and physical measurement provenance.
- Build a SHAP-ready feature matrix — Wide-format matrix assembly with leakage and encoding guardrails.
- Run PC-adjusted logistic regression — Logistic regression with ancestry PCs as covariates.
- Run propensity score matching — 1:N PSM with statsmodels and cKDTree (no sklearn needed).
Glossary — CDR, Registered/Controlled Tier, index date, carrier, P/LP, FDR, dry run, OMOP CDM, concept_id, and more. Linked from every page that uses these terms.
Pitfall Index — every Pitfall callout from all 23 Reference pages, grouped by failure mode (missing denominator, temporal leakage, ancestry confounding, vocabulary errors, unit/encoding errors, provenance mixing, cost traps). Use as a pre-submission checklist.
Reference page template — the fixed structure every Reference page follows.
Technical contributions must be battle tested in the All of Us Researcher Workbench before submission. See the contributing guide for validation evidence, privacy requirements, content placement, and the pull request checklist.
End-to-end workflows that chain Reference pages into complete pipelines. Each Guide shows the pipeline shape and connective reasoning; full code lives on the linked Reference pages.
- Build a genomic case-control cohort from scratch — 12-step pipeline from condition definition through analysis-ready dataframe with carrier status, demographics, and ancestry PCs.
- Add a properly-windowed lab feature to an existing cohort — 7-step pipeline: concept lookup, temporal query, unit normalization, plausibility bounds, aggregation, merge, and leakage audit.
- Estimate and cap the cost of a large query — Dry-run, evaluate, optimize, cap, execute. The shortest Guide but the one that saves the most money.
- Take case/control pairs to FDR-corrected results — Per-gene regression, p-value collection, Benjamini-Hochberg correction, results table with carrier counts.
27 Reference pages · 4 Guides · Validated against Pan-Cancer Germline Predisposition Project cookbook (CDR v8, August 2026).