Sources
Every section below is generated at build time from the ingest module’s own docstring (ast.get_docstring) — the licence quote, version axis, geography vintage, and deliberate omissions are written once, in the code that does the ingest, and reproduced here rather than retyped. Nothing on this page is hand-copied licence text.
cancer site
uv run cancerbasemap cancer-site (src/cancerbasemap/cancer_site.py)
Tables written: raw.seer__site_recode, raw.seer__cod_recode, measure.cancer_site
SEER Site Recode ICD-O-3/WHO 2008 <-> SEER Cause of Death Recode 1969+ (ICD-10 mortality) -> measure.cancer_site, plus a small curated NCIt/MONDO bridge for the cancer sites State Cancer Profiles (SCP) publishes (SPEC.md § measure.cancer_site; issue #31).
Licence. Both tables are National Cancer Institute (SEER) publications. NCI’s own reuse policy: “Unless otherwise indicated, all text within National Cancer Institute (NCI) products is free of copyright and may be reused without our permission. Credit the National Cancer Institute as the source.” (https://www.cancer.gov/policies/copyright-reuse, checked 2026-09-18). SEER content is additionally a U.S. Government work, public domain under 17 U.S.C. Sec 105 (resources.data.gov/open-licenses/, the same basis the other federal sources in this repo cite).
Which recode, and why. SCP’s own FAQ names exactly the two upstream tables behind its statistics: “How do you determine what is included in a cancer site? Incidence Data - We use the Site Recode ICD-O-3/WHO 2008 Definition, which can be found at https://seer.cancer.gov/siterecode/icdo3_dwhoheme/index.html . Mortality Data - We use a combination of ICD-8, ICD-9, ICD-10. Definitions of the codes can be found at https://seer.cancer.gov/codrecode/1969_d03012018/ .” (https://statecancerprofiles.cancer.gov/faq.html, checked 2026-09-18). Those are exactly the two editions this module lands – not the 2023 revision SEER also publishes (a different, incompatible 2-digit code space); SCP’s FAQ doesn’t point to it, so landing it would be enumerating an edition SCP doesn’t use, against the issue’s “start with the sites SCP actually publishes.”
Format: the plain-text form, not the HTML table. Both pages publish the same table as HTML and as a ;-delimited ASCII file (“The information provided in this table is also available in an ASCII text file”, both pages, checked 2026-09-18). The HTML renders site groups nested up to four levels deep via rowspan/colspan; the text form needs none of that – hierarchy is plain leading-space indentation (4 spaces/level) and every record is one line, so it’s the simpler of the two real layouts and is used here (stdlib string splitting; no HTML parser, no new dependency).
**raw.seer__site_recode** lands the site-recode text file verbatim: site_group keeps the source’s own leading-space indentation (its hierarchy marker) rather than trimming it, icdo3_site/icdo3_histology are the published range expressions unparsed (that column holds both exclusion ranges, e.g. “excluding 9050-9055, 9140, 9590-9993”, and, for a few codes, an inclusion range instead, e.g. Melanoma’s “8720-8790” or Myeloma’s “9731-9732, 9734” – landed as published either way), and recode is the numeric code where the source gives one, blank for every row that is a group heading rather than a site (e.g. “Colon and Rectum”, “Digestive System”) – never invented. A handful of codes are defined by more than one physical row (e.g. 31040 “Cranial Nerves Other Nervous System” has one row for site C710-C719/histology 9530-9539 and a second for site C700-C709,C720-C729/histology “excluding …”); both land as separate raw rows exactly as published – transform concatenates a code’s distinct clause texts into one measure.cancer_site row.
**raw.seer__cod_recode** lands only the text file’s first table (“Neoplasm Causes of Death”); the “Non-Neoplasm Causes of Death” and “Codes Not Indicating Causes of Death” tables that follow it in the same file are administrative/non-cancer causes, out of scope for a cancer-site bridge. recode is blank, or the literal sentinel “–” (“All Malignant Cancers”), for rows with no site-recode equivalent.
measure.cancer_site derives one row per numeric recode in raw.seer__site_recode (cancer_site_code), never per group heading – SEER’s table assigns no code to any heading at any depth, so parent_code is NULL throughout this edition (declared per SPEC.md/the issue, ready for an edition that does publish one – see ponytail below). icd10_mortality is filled by joining raw.seer__cod_recode on an EXACT label match (“where the site names align exactly”, per the issue) – deliberately not fuzzy or case-folded, so a near-miss caused by COD’s own footnote markers baked into a label (e.g. “Soft Tissue including Heart$”) or a real wording difference (COD’s mortality-only “Mesothelioma (ICD-10 only)+” vs incidence’s plain “Mesothelioma”) is left NULL rather than guessed. 56 of 81 site-recode codes align this way; the other 25 are individually finer-grained than COD’s own grouping (the 9 colon subsites 21041-21049 all roll up to COD’s single “Colon excluding Rectum” = a different code, 21040) and get no ICD-10 range here.
“All Sites”/“All Cancer Sites” has no cancer_site_code. SCP’s FAQ: “All Cancers refers to all invasive cancers combined. This includes the cancer sites that we list on our Web site … as well as all other invasive cancers.” (statecancerprofiles.cancer.gov/faq.html, checked 2026-09-18) – it is the absence of a site filter, not a discrete SEER recode value; the COD table’s own “All Malignant Cancers” row carries the literal sentinel “–” instead of a code, confirming the same on the mortality side. Left out of the curated CSV; the issue’s “every cancer_site_code used by #27 resolves” is unaffected since there is no code to resolve for it.
Curated NCIt/MONDO bridge (src/cancerbasemap/data/cancer_site_ontology.csv). Researched first, per the issue: neither SEER, NCIt nor MONDO publishes a site-recode-to-ontology crosswalk (checked NCI EVS/OLS4 and MONDO’s own mapping docs, 2026-09-18) – this file is this project’s own curation, mapping_basis = 'curated' wherever it applies. One row per constituent cancer_site_code, so a reviewer can check every id against the exact SEER code it covers. Every id was looked up and confirmed live against the EBI OLS4 API (https://www.ebi.ac.uk/ols4/api/ontologies/{ncit,mondo}/terms?obo_id=...) on 2026-09-18: not obsolete, label matches the CSV’s own ncit_label/ mondo_label. Gaps, reported honestly rather than guessed: - All Sites – no cancer_site_code exists to attach it to (see above). - Oral Cavity and Pharynx – no NCIt term combining exactly this SEER group (C000-C149) was found with confidence; candidates were either narrower (a single subsite) or broader (all of head and neck). Left unmapped. - Liver and Intrahepatic Bile Duct – MONDO has an exact-label match (MONDO:0024477); NCIt does not (its nearest term, “Malignant Hepatobiliary Neoplasm” C8609, also covers gallbladder and other biliary sites SEER keeps separate) – ncit_id NULL, mapping_basis still ‘curated’ since mondo_id is filled. - Kidney and Renal Pelvis – mapped to the general “Malignant Kidney Neoplasm”/“kidney cancer” terms (a broader match: NCIt separately defines a narrower “… Except Renal Pelvis” sibling term, implying the plain term includes it, but this was not independently confirmed) – noted in the CSV’s own note column. For every SCP category that is not a single SEER leaf code (Colon & Rectum, NHL, Leukemia, Liver & Bile Duct, Uterus/Corpus, Brain & ONS), the CSV lists each constituent cancer_site_code. SCP’s FAQ spells out Brain & ONS’s two constituents explicitly: “We combine brain and other nervous system together as one cancer site grouping - Brain and ONS. This includes two groupings: Brain, Cranial Nerves Other Nervous System.” The FAQ names no others, so the rest are this project’s own reading of the SEER Site Recode table’s matching SECTION HEADERS (its “Colon and Rectum”, “Non-Hodgkin Lymphoma”, “Leukemia”, “Corpus and Uterus, NOS”, “Liver and Intrahepatic Bile Duct” groupings use the identical wording SCP’s site list uses) – a note on each such row says so.
mapping_relation (#90). The CSV’s free-text note said whether a mapping was exact or broader only in prose; #90 asks for that as its own column so a cross-lake join to biocOnIce’s ontology namespace (#60, #61) can filter to exact matches only, the same way a stratum pooling is made visible via measure.stratum_map.relation (SPEC.md) – the same four-value vocabulary (exact | broader | narrower | overlap) that column declares, not a cancer_site-only pair.
Most of the 40 curated rows already documented above as a “SCP combined category” (Colon & Rectum’s 11 subsites, NHL’s 2, Leukemia’s 9, Liver & Bile Duct’s 2, Brain & ONS’s 2 – 26 rows) are broader: the ontology term names the combined disease, and the row’s own cancer_site_code is one narrower constituent of it. Kidney and Renal Pelvis (29020) is also broader, for the different reason already in its note (no NCIt/MONDO term splits kidney parenchyma from renal pelvis at SEER’s granularity) – 27 broader rows in total. The Corpus/Uterus pair splits instead of matching that pattern: Corpus Uteri (27020, C540-C549) is exactly the anatomic site “Malignant Uterine Corpus Neoplasm”/“uterine corpus cancer” names, so it is exact; Uterus, NOS (27030, C559) is unclassified by subsite – some cases are corpus, some are not – so it is neither a subset nor a superset of the ontology term and is overlap rather than broader (which would wrongly claim every NOS case is a corpus case). The remaining 11 single-site rows (Breast, Cervix, Lung, Prostate, Melanoma, Bladder, Pancreas, Thyroid, Ovary, Stomach, Esophagus) are exact – a 1:1 match between the SEER code and the ontology term, for 12 exact rows total with Corpus Uteri. Final split: 12 exact, 27 broader, 1 overlap. mapping_relation is NULL exactly where mapping_basis is NULL (no ontology id at all).
ponytail: parent_code is declared but always NULL for this edition (see above) – add real values if a future SEER edition (e.g. the 2023 revision, which does assign single codes to some of these same combined groups) turns out to be what a downstream consumer needs, rather than inventing a cross-edition mapping here.
ponytail: only the “Neoplasm Causes of Death” section of the COD recode text file is landed; “Non-Neoplasm Causes of Death” and “Codes Not Indicating Causes of Death” are unrelated to cancer sites.
ponytail: the SEER 2023 revision definitions (icdo3_2023, icdo3_d2023) are not landed – SCP’s FAQ does not name them as what SCP uses; add them alongside these two if SCP (or another consumer) migrates.
cdc svi
uv run cancerbasemap svi (src/cancerbasemap/cdc_svi.py)
Tables written: raw.svi__county, raw.svi__tract, measure.definition, measure.stratum, measure.observation
CDC/ATSDR Social Vulnerability Index (SVI) -> Iceberg.
Upstream: https://www.atsdr.cdc.gov/place-health/php/svi/ – county and tract CSVs for US-wide editions 2000, 2010, 2014, 2016, 2018, 2020, 2022 (SPEC.md § Sources: “area-level context index… land every published edition”). The download page (svi-data-documentation-download.html) is a form; the underlying files were found by reading its js/loadXML.js and live at https://svi.cdc.gov/Documents/Data/<edition>/csv/states_counties/SVI_<edition>_US_county.csv (county) and .../csv/states/SVI_<edition>_US.csv (tract), verified 2026-09-18 by downloading all 14 real files.
Licence. CDC/ATSDR’s own materials-use page states: “Most of the information on the CDC and ATSDR websites is not subject to copyright, is in the public domain, and may be freely used or reproduced without obtaining copyright permission.” (https://www.cdc.gov/other/agencymaterials.html, checked 2026-09-18). SVI is produced by ATSDR’s Geospatial Research, Analysis & Services Program, a federal agency, in the course of employees’ official duties – also a U.S. Government work under 17 U.S.C. § 105.
Version axis: the edition (2000, 2010, 2014, 2016, 2018, 2020, 2022), landed as svi_edition. “Methods and themes changed between editions, so an edition is a different measure definition, not just a new period” (#40) – verified directly: theme membership (which raw variables feed RPL_THEME1-4) differs between the two landed layout families (see below), so derived measure_ids embed the family, not just the edition.
Editions landed: 2000, 2010, 2014, 2016, 2018, 2020, 2022 (county); same for tract (#92 lands 2000 and 2010’s tract files too – both are real, keyless bulk files at the same URL pattern as the rest, verified 2026-09-19).
**2000 and 2010 land into their own per-layout tables, not the shared raw.svi__county / raw.svi__tract union (#92, following on from #40’s deferral).** Neither fits safely into the “2014”/“2020” layouts unioned below: - 2010’s real header names a column “STATE” holding the 2-digit state FIPS code (e.g. ‘01’), while every 2014+ layout names its full state NAME column “STATE” (e.g. ‘Alabama’) – the same column name means a different kind of value depending on edition. Worse, 2000’s OWN “STATE” column holds the full name (like 2014+, unlike 2010) while its “ST” column holds the 2-digit FIPS (unlike 2014+, where ST is the USPS abbreviation) – three editions, three different (ST, STATE) meanings, verified directly against all three real files. Landing any two of them under one shared column name would be a silent semantic trap for anyone querying raw directly, so 2000 and 2010 each get a dedicated table (raw.svi__county_2000, raw.svi__tract_2000, raw.svi__county_2010, raw.svi__tract_2010) with their own real column names, matching the issue’s suggested resolution. - 2010 has no E_/M_ ACS estimates or MOEs at all for several concepts this issue derives (structural Census 2010 SF1 100%-count only, no sampling error: AGE65, AGE17, SNGPRNT, MINORITY, GROUPQ have no M_ column in 2010 – confirmed against the real file and against SVI-2010-Documentation-H.pdf’s own “Based on 100% counts - no sampling error” notes). 2010 also has no disability variable at all – the Census Bureau collected no tract-level disability data for either the 2010 Census or the 2006-2010 ACS (same PDF), so 2010’s theme 2 is Household Composition only (age 65+, age 17-, single-parent), one fewer component than every other edition’s Household Composition/Disability theme. - The 2000 county/tract CSVs ship with two header lines, verified by downloading the real files: line 1 (STATE_FIPS,CNTY_FIPS,...,G1V1R,...,USG1V1P,...) does not match the data that follows it at all (its columns don’t correspond to the values in any row); line 2 (ST,COU,STCNTY,...,P_POV,...,RPL_THEME1,...,F_POV,...) does – e.g. Autauga County, AL’s row 3 gives P_POV=0.1092, a plausible real 2000 poverty rate, lining up with line 2’s column names, not line 1’s. Line 1 appears to be a stale, vestigial header CDC never removed (its columns match the SVI 2000 Data Dictionary in SVI2000Documentation-H.pdf almost exactly – an older naming CDC superseded without updating the file). land_raw skips line 1 (HEADER_SKIP) and verifies the real line 2 against the declared layout. 2000 has no E_/M_ raw ACS variables at all (understandable – SVI 2000 draws on Census 2000, not a 5-year survey), so no MOE is ever published, matching 2010’s MOE-less structural concepts in spirit but for every variable.
Real documentation, fetched and read directly 2026-09-19 (neither is linked from the current download page, but both are still live at their historical map25 URLs, found via a general web search rather than the page’s own links): https://svi.cdc.gov/map25/data/docs/SVI-2010-Documentation-H.pdf (“SVI 2010 Documentation - 3/31/2015”) and https://svi.cdc.gov/map25/data/docs/SVI2000Documentation-H.pdf (“About the Data / Social Vulnerability Index 2000”). Also fetched: the CDC/ATSDR-published Comparison-of-Source-Data-and-Variables.csv (https://www.atsdr.cdc.gov/place-health/media/pdfs/2024/Comparison-of-Source-Data-and-Variables.csv), which tabulates data source (Census vs. ACS) and theme membership per variable per edition and confirms every composition claim below.
Three real column contracts (LAYOUT/COUNTY_COLUMNS), two methodology families (FAMILY). A layout is what header a real file has; a family is which measure_ids and theme composition an edition asserts (module code keeps these separate on purpose – collapsing them loses the fact that 2016/2018 share 2014’s methodology but not its exact header): - layout "2014" – county 2014 only (127 columns, includes AFFGEOID and the trailing Shape/Shape.STArea()/Shape.STLength(), excluded from the union below). - layout "2016" – county 2016, 2018 (verified byte-identical to each other; 2014’s 127 columns minus those same 4). - layout "2020" – county 2020, 2022, and tract 2022 (verified: the 2022 tract file’s header is byte-identical to the 2020/2022 county header – same layout, different geography grain – so tract lands via the same code path with no new layout to declare; this is why tract landing extends past 2022 trivially but isn’t wired up for other editions here). - family "2014" – editions 2014, 2016, 2018 (layouts “2014” and “2016”: identical concept columns and theme composition, verified from each real header’s column order, despite the “2014”-only geometry/AFFGEOID columns). - family "2020" – editions 2020, 2022 (layout “2020”). raw.svi__county unions all three layouts’ columns (the “2020” layout plus the 13 columns unique to “2014”: AFFGEOID and the POV/PCI group, since 2020 renamed poverty to POV150 and dropped PCI from the methodology – ArcGIS geometry columns excluded, per SPEC.md’s geometry non-goal); raw.svi__tract is exactly the “2020” layout.
Two more layouts, "2000" and "2010", land into their own dedicated tables instead (module docstring above) and so double as their own families – FAMILY["2000"] == "2000", FAMILY["2010"] == "2010", each edition its own family, matching #92’s instruction that theme composition differs again from both the 2014 and 2020 families.
Geography vintage is the edition’s own year, not a landed Gazetteer vintage. Each edition’s own data dictionary states its AREA_SQMI column is computed from “Census Cartographic Boundary File - U.S. Tracts <edition> 500K” (SVI2014Documentation, SVI2016Documentation, SVI2018Documentation, SVI2020Documentation, SVI2022Documentation, all checked 2026-09-18) – i.e. SVI documents each edition against its own year’s Census geography, not against the nearest 2010/2020 decennial vintage. Collapsing that into two buckets is wrong at the edges, confirmed empirically against the real files: - Alaska: pre-2019 Valdez-Cordova (02261) appears through the 2018 edition; the 2019 split into Chugach (02063) and Copper River (02066) appears starting with the 2020 edition (county count rises 3,142 -> 3,143, +1 net) – this alone rules out treating 2020 as 2010-vintage, since a 2010-vintage file cannot contain a 2019 split. - South Dakota: the 2015 Shannon -> Oglala Lakota recode (46113 -> 46102) appears starting with the 2016 edition; 2014 still carries 46113. - Connecticut: the legacy 8 counties (09001-09015) persist through the 2020 edition; the 9 planning regions (09110-09190) appear starting with 2022 (county count rises 3,143 -> 3,144, net +1) – same pattern as places.py and census_gazetteer.py, just landing at a different edition. These three changes account for the entire county-count sequence (3,142, 3,142, 3,142, 3,143, 3,144) and each shows up at a distinct edition, confirming the vintage moves edition-by-edition rather than in two 2010/2020 steps. GEO_VINTAGE is therefore each edition’s own year – {2014: 2014, 2016: 2016, 2018: 2018, 2020: 2020, 2022: 2022} – for both county and tract (tract 2022 confirmed on the same file: STCNTY carries the planning regions there too). GEO_VINTAGE["2000"] = 2000 and GEO_VINTAGE["2010"] = 2010 follow the same rule (each edition its own year) – 2000 is not one of geography.unit’s landed vintages (2010/14/16/18/20/22/26), so a 2000 SVI row’s geo_id/geo_vintage currently has no matching row to join against there. This module does not join to geography.unit for any edition (geo_id/geo_vintage are recorded as asserted by the source, per SPEC.md’s model, not FK-checked at ingest time), so nothing here is broken by 2000’s absence – but it is worth flagging rather than silently forcing 2000 onto the nearest landed vintage (2010), which would misrepresent which boundaries its FIPS codes actually describe.
Period: the ACS 5-year window each edition’s own documentation states under “Methods > Variables Used” (svi.cdc.gov/map25/data/docs/SVIPERIOD_OVERRIDE corrects two of 2010’s concepts away from that family default: age_65_plus and minority are 2010 Census SF1 100%-count variables in the 2010 edition specifically (unlike every other landed edition, where both are ACS 5-year estimates), so their true period is the single year 2010, not 2006-2010 – stated explicitly in the same PDF (“Based on 100% counts - no sampling error”; “AGE65 … 2010 SF1”). The RPL_THEME/RPL_THEMES percentile rows for the 2010 family keep the family’s ACS window (2006-2010) since the published index itself blends both sources into one score without attributing a period to the blend.
Suppression. -999 is SVI’s numeric missing sentinel, published in both raw estimate (E_/M_) and percent/percentile (EP_/MP_/EPL_/RPL_/SPL_) columns, confirmed present in every landed edition’s real file -> not_available, never a number (SPEC.md Acceptance C). A margin of error can be -999 while its paired estimate is valid (or vice versa) – each is checked independently; a suppressed EP_ value never derives a lower/upper from a present MP_, and a present EP_ with a suppressed MP_ still derives value with no interval.
Derived: RPL_THEMES/RPL_THEME1-4 (rate_basis=‘index’, method=‘derived’) as measure_id = 'SVI:RPL_THEMES:<family>' / 'SVI:RPL_THEME<n>:<family>', family ‘2000’, ‘2010’, ‘2014’ or ‘2020’ (FAMILY[edition]) since theme composition genuinely differs between all four (see THEME_DESC) – plus the simple E_/EP_/P_ component variables named in #40: poverty (100% threshold through 2018 (and 2000, 2010) as SVI:poverty, 150% threshold from 2020 as SVI:poverty150 – CDC’s own renamed variable, confirmed EP_POV -> EP_POV150 in the real files), no HS diploma, no vehicle, age 65+, minority, limited English – all five of the latter keep one stable measure_id across all four families (verified: identical concept meaning in every layout, per Comparison-of-Source-Data-and-Variables.csv). SVI:uninsured is not asserted by the 2000 or 2010 families at all – neither publishes an uninsured variable (confirmed absent from both real headers and from the comparison CSV). interval_level=0.90 (ACS MOE) where both a percent estimate and its MOE column are present and unsuppressed; 2000 publishes no MOE for anything, and 2010 publishes none for age_65_plus/minority (100%-count, module docstring above), so those rows always land with interval_level NULL, lower/upper NULL, and just a bare value.
ponytail: age_65_plus and minority are true 100%-count Census variables (no sampling error) in the 2000 and 2010 editions specifically, versus ACS 5-year sample estimates (method='survey_direct') in every other landed edition – measure.definition.method still reads 'survey_direct' for both regardless of which edition wrote it last, since splitting these two concepts into their own family-suffixed measure_ids just to carry a more precise method felt like more machinery than the nuance is worth (a 100%-count value is if anything more certain than a sampled one, so nothing here overstates precision the way a wrong period or a suppressed value read as zero would). Upgrade path if a consumer needs a single method value per source_release: mint SVI:age_65_plus:2000/ :2010 and SVI:minority:2000/:2010 alongside the stable ids.
measure.definition/measure.stratum writes are scoped to the measure_ids this edition’s family actually asserts (In("measure_id", ids)), never wholesale per source – SPEC.md’s “never replace wholesale per source” rule (issue #76): ingesting the 2020-family after the 2014-family must not delete the 2014-family’s own theme definitions, since a 2014 measure.observation row’s FK still points at them.
ponytail: PCI (per capita income, all editions through 2018 including 2000/2010) and the structural variables this issue doesn’t ask for (age17, disability, single-parent, group quarters, housing type, crowding, race breakdowns added 2020, no-internet) land in raw but are not derived – “simple and cancer-relevant” per #40 names the six concepts above; add more alongside CONCEPT_COLUMN/CONCEPT_LABEL if a later measure needs them.
census acs
uv run cancerbasemap acs (src/cancerbasemap/census_acs.py)
Tables written: measure.definition, measure.stratum, measure.observation
US Census ACS 5-year Summary File (detailed tables) -> Iceberg (SPEC.md § Sources: “ACS 5-year (curated table subset) – demographics, insurance, poverty, vehicle access | public domain; MOE -> interval_level = 0.90”).
Indicator-set decision (#29 was held on “which table subset?”). The subset is the indicator set Cancer InFocus (CIF) publishes, since CIF parity is the adoption path SPEC.md’s Landscape section names (“an export recipe that writes CIFTools-shaped files … is a better adoption path than a rival dashboard”): age (under 18, 18-64, 65+), race/ethnicity (Hispanic, NH White, NH Black, NH Asian, other), educational attainment (no HS diploma; bachelor’s+), median household income, poverty, unemployment and labor-force participation, vehicle access (no vehicle households), vacant housing, rent burden (>=30% and >=50% of income), the Gini index, limited English, plus total population as a universe. Health insurance and Medicaid are NOT landed in this PR – see “Dropped: health insurance / Medicaid” below.
Upstream, and why this module does not call the Census Data API. The issue’s brief assumed “the Census Data API … works without a key for low volume.” That is no longer true, verified 2026-09-18: every endpoint tried (https://api.census.gov/data/2023/acs/acs5[/profile|/subject]?get=...), including a single-variable, single-state request, HTTP 302-redirects to https://api.census.gov/data/missing_key.html with header X-DataWebAPI-KeyError: 1. Only the metadata endpoints (/variables.json, /groups/<table>.json) are still keyless – used here at authoring time to look up variable labels, never at ingest time. Per this issue’s instructions (“if a key is demanded, stop and report (never commit a key)”), this module uses the brief’s documented fallback instead: the ACS table-based Summary File, a bulk, keyless, per-table download –
https://www2.census.gov/programs-surveys/acs/summary_file/<year>/table-based-SF/data/5YRData/acsdt5y<year>-<table>.dat– one pipe-delimited .dat file per detailed table, nationwide (every summary level in one file), verified 2026-09-18 by downloading the tables below for the 2023 and 2021 releases. Because no Census Data API endpoint is ever called, the API Terms of Service attribution sentence the issue asked to quote does not currently apply; quoted here anyway for the record, and for whoever adds a keyed API lander for the Data Profile/Subject tables later: “This product uses the Census Bureau Data API but is not endorsed or certified by the Census Bureau.” (https://www.census.gov/data/developers/about/terms-of-service.html).
Why detailed (B/C) tables, not Data Profile (DP) or Subject (S) tables. CIF’s own indicators map most directly onto DP02/DP03/DP04/DP05 (education, income, poverty, employment, insurance, housing) and S0101/S2701/S2704 (age, insurance) – confirmed by inspecting their variable labels via the metadata endpoints. But the table-based Summary File only ships detailed tables (confirmed: its 5YRData/ directory lists ~1,193 acsdt5y<year>-b*.dat / -c*.dat files and zero dp*/s* files) – Data Profile and Subject tables are a data.census.gov/API-only convenience layer computed from the detailed tables, unreachable without a key. This module uses the closest detailed-table equivalent of each CIF indicator instead (table -> concept, verified against each table’s real header and variable labels, 2026-09-18):
B01003 Total population (universe)
B09001 Population under 18 years by age (age: under 18)
B09020 Population 65 years and over ... (age: 65 and over)
B03002 Hispanic or Latino origin by race (race/ethnicity)
B15003 Educational attainment for the population
25 years and over (no HS diploma; bachelor's+)
B19013 Median household income in the past 12
months (median household income)
C17002 Ratio of income to poverty level in the
past 12 months (poverty)
B23025 Employment status for the population
16 years and over (labor force participation; unemployment)
B25044 Tenure by vehicles available (no vehicle households)
B25002 Occupancy status (vacant housing)
B25070 Gross rent as a percentage of household
income (GRAPI) (rent burden >=30%, >=50%)
C16002 Household language by household limited
English speaking status (limited English)
B19083 Gini Index of Income Inequality (Gini index)
B27001 Health Insurance Coverage Status
by Sex by Age (uninsured)
C27007 Medicaid/Means-Tested Public
Coverage by Sex by Age (Medicaid coverage)Age 18-64 is not published anywhere in ACS as a single variable (every age table bins in 5- or 10-year spans that straddle 18); it is derived as the residual B01003_E001 - B09001_E001 - B09020_E001 (see “Margins of error” below). Race/ethnicity “other” sums NH American Indian/Alaska Native + NH Native Hawaiian/Pacific Islander + NH Some Other Race + NH Two-or-more. Education’s “no HS diploma” sums B15003’s 15 sub-diploma categories (rows 002-016: no schooling through “12th grade, no diploma”); “bachelor’s or higher” sums bachelor’s + master’s + professional + doctorate (022-025). Rent burden’s denominator is B25070_E001 - B25070_E011 (excluding “Not computed”), matching DP04’s own GRAPI universe definition.
Health insurance / Medicaid (#125). CIF’s insurance indicators (uninsured; Medicaid) map cleanly onto Subject tables S2701/S2704 (a single “Percent Uninsured” / “Percent Public Coverage, Medicaid row” variable each) – but those are API-only (see above). #29/#124 dropped this rather than land either of the two detailed-table equivalents whole: the Census API’s /groups/<table>.json metadata endpoint reports B27001 as “230 variables” and B27010 as “266 variables”, which is what that decision was made on – but that count includes the four E/M/EA/MA suffix variants the API serves per cell; the table-based Summary File this module actually reads ships only E/M, verified directly against the real 2023/2021 downloads’ headers: B27001 is 57 variable-pairs (114 + GEO_ID columns) and C27007 – “Medicaid/ Means-Tested Public Coverage by Sex by Age”, the smaller table that already carries the with/without-Medicaid split CIF needs, so B27010’s 66 variable-pairs (with/without/by type of coverage – employer, Medicare, Medicaid, TRICARE, … – strictly more than CIF’s single Medicaid rate needs) is not landed – is 21. At ~4MB/variable-pair (see the size-scaling note this replaces), that is ~228MB (B27001) + ~84MB (C27007) per release, not the 250-580MB the #29/#124 estimate assumed. Landed whole, this module’s own convention: every “No health insurance coverage” cell (9 age brackets x 2 sexes = 18 leaves) summed over B27001’s own total universe for ACS:uninsured; every “With Medicaid/means-tested public coverage” cell (3 age brackets x 2 sexes = 6 leaves) summed over C27007’s own total universe for ACS:medicaid_coverage. Both tables share B27001’s “civilian noninstitutionalized population” universe (verified against both tables’ real /groups/<table>.json universe field, 2026-09-19).
Licence. U.S. Census Bureau content is a federal government work. Per resources.data.gov/open-licenses/: “Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection under 17 U.S.C. Sec 105. Government works are by default in the U.S. Public Domain.” (checked 2026-09-18, same citation as census_gazetteer.py and ers_rucc.py.)
Version axis and geo_vintage. acs_year is the 5-year release’s END year (e.g. 2023 for “the 2019-2023 release”); source_release downstream is f"{year-4}-{year}". Two releases are landed: 2023 (2019-2023, the latest reachable) and 2021 (2017-2021) – NOT 2018 (2014-2018): the table-based Summary File format only exists from the “2021” vintage onward (https://www2.census.gov/programs-surveys/acs/summary_file/<year>/table-based-SF/ 404s for 2020 and earlier, 200 from 2021, checked 2026-09-18); 2014-2018 is only published in the older sequence-based format (per-state fixed-layout files joined against a sequence-to-variable crosswalk), a different enough parser that supporting it is out of scope here. 2019-2023 and 2017-2021 overlap by three years – the closest “non-overlapping-ish” pair this format reaches, exactly as the issue brief anticipated might be necessary. #125 part 2 (pre-2021 releases) stays out of scope in this PR for the same reason: the sequence-based format is a genuinely different parser (per- state fixed-layout files against a sequence-to-variable crosswalk, not one national pipe-delimited file per table), and #125’s own text says CIF parity does not need it – there is no keyless blocker here to work around, just a second format not worth building speculatively. geo_vintage is verified directly against each release’s own Connecticut county rows, not assumed from a landed Gazetteer vintage (SPEC.md): the 2019-2023 file’s county rows for state 09 are the nine 2022 planning regions (09110-09190) -> GEO_VINTAGE[2023] = 2020; the 2017-2021 file’s are the eight legacy counties (09001-09015) -> GEO_VINTAGE[2021] = 2010 – the same two vintage labels PLACES and the Gazetteer use for the pre/post-2022 Census county definitions.
Geography scope: county and tract, filtered mechanically, not landed whole. The table-based Summary File interleaves every summary level (nation, state, county, place, tract, block group, …) in one national file per table – unlike census_gazetteer.py’s or cdc_svi.py’s upstream, which ship a separate file per level. land_raw filters each downloaded file to the GEO_ID prefix for the requested --level (0500000US = county, 1400000US = tract) before landing – a mechanical geography-level split applied uniformly to every column, not a curated row subset, and the same two levels SPEC.md’s Sources table declares for ACS.
Margins of error. Per U.S. Census Bureau, Understanding and Using American Community Survey Data: What All Data Users Need to Know (2020), Chapter 8, “Calculating Measures of Error for Derived Estimates” (https://www.census.gov/content/dam/Census/library/publications/2020/acs/acs_general_handbook_2020_ch08.pdf): - Sums (formula 1): MOE = sqrt(sum of each component’s MOE squared). Chapter 8 also notes that a difference of two estimates uses the same formula as a sum “ignoring covariance” (the sum/difference formulas differ only in the sign of a covariance term this approximation already drops) – so the age 18-64 residual and the rent-burden GRAPI-computable denominator (both differences) reuse the sum formula rather than a separate one. - Proportions/percentages (formula 6, numerator a subset of the denominator): MOE(P) = (1/Y) * sqrt(MOE(X)^2 - P^2 * MOE(Y)^2), and “if the value under the square root is negative, … substitute a ‘plus’ for the ‘minus’ sign” – both implemented in _pct below. All of this module’s percentages (age, race/ethnicity, education, poverty, labor force, vehicle, vacancy, rent burden, limited English) are nested proportions in this sense; the Gini index and median household income are landed as single cells with their own published MOE, no combination needed.
Sentinels (ACS “jam values”). Per the Census Bureau’s official “Jam Value Specifications for the 2023 American Community Survey” (https://www2.census.gov/programs-surveys/acs/tech_docs/jam_values/2023_Jam_Values.xlsx, downloaded and parsed 2026-09-18 – the same values are also documented, without the full table, at https://www.census.gov/content/dam/Census/library/publications/2023/acs/acs_table_based_summary_file_handbook_ch03.pdf):
Estimate jam values (all present in the real 2023/2021 downloads):
-666666666 "Estimate not computed due to insufficient number of
sample cases." -> suppressed_small_count
-999999999 "Estimate not displayed due to insufficient number of
sample cases for selected geography."
-> suppressed_small_count
-888888888 "Estimate not applicable or available." -> not_applicable
MOE jam values (all reachable via the same source; -222222222 and
-333333333 confirmed present in the real B19013/B19083 downloads):
-222222222 "MOE not computed due to insufficient number of sample
cases." -> value stays reported,
lower/upper = NULL
-333333333 "MOE not computed for medians in lower or upper interval"
-- i.e. the estimate itself is an artificial open-interval
boundary, not a real median -> not_available,
value = NULL too
-555555555 "MOE not appropriate - estimate is controlled ... MOE may
be treated as zero" -> value stays reported,
lower/upper = NULL
(MOE also reuses -888888888 / -999999999 for "MOE not applicable" /
"MOE not displayed" -- same NULL-the-interval-only treatment.)Per this issue’s instructions, an unmapped negative-jam-looking cell (-\d{6,} matching none of the six values above) raises SystemExit in land_raw rather than landing silently – see _check_sentinels. median_household_income is the one measure with a real median (a $-value that ACS artificially pins to $2,500 or $250,001 when the true median falls in an unbounded bracket): it gets the extra -333333333 -> not_available rule; every other measure treats a jammed MOE as “value reported, interval unavailable” per the brief.
Composed-measure suppression, resolved (#125 part 3). Measures built by summing or ratio-ing several raw cells (race “other”, education, poverty, vehicle access, rent burden, limited English, uninsured, Medicaid) collapse any contributing jam value to suppressed_small_count, rather than distinguishing not_applicable per-leaf. #29/#124 left this as a ponytail note pending real volumes; #125 checked directly against every landed row so far (2019-2023 county + tract, 2017-2021 county – 91,823 rows, all 216 estimate columns across all 15 tables, every level and release this module lands): -666666666 (“insufficient sample cases”) is the ONLY estimate jam value that occurs anywhere, 2,749 times; -888888888 (“not applicable”) and -999999999 never occur once, in any table, at either geography level. So the collapse is not losing information in practice, not just “acceptable” – every real suppressed_small_count in a composed measure genuinely is a small-sample suppression, matching the status’s own documented meaning exactly. No further work item here; re-run this same check (_check_sentinels already hard-stops on an unmapped jam shape, so a future edition introducing -888888888 for the first time would be caught, not silently mis-classified) if a future release changes that.
census gazetteer
uv run cancerbasemap gazetteer (src/cancerbasemap/census_gazetteer.py)
Tables written: raw.census__gazetteer_counties, raw.census__gazetteer_tracts, raw.census__state_fips, raw.census__gazetteer_cd, raw.census__gazetteer_sldu, raw.census__gazetteer_sldl, geography.unit
US Census Bureau Gazetteer Files -> geography.unit, the non-geometry half of the geography spine (SPEC.md § Geography).
Upstream: https://www.census.gov/geographies/reference-files/time-series/geo/gazetteer-files.html lists one national county file and one national tract file per vintage year, zipped tab- (or, from 2025, pipe-) delimited text under https://www2.census.gov/geo/docs/maps-data/data/gazetteer/, e.g. 2024_Gazetteer/2024_Gaz_counties_national.zip. The 2010 files live at the same host without a year folder or prefix (Gaz_counties_national.zip) and carry two extra 2010-Census columns, POP10/HU10, that later vintages drop.
Licence. U.S. Census Bureau content is a federal government work. Per resources.data.gov/open-licenses/ (the definition data.gov uses for every federal agency’s “Public Domain” listing): “Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection under 17 U.S.C. § 105. Government works are by default in the U.S. Public Domain.” No registration or attribution is required to redistribute.
Version axis: release. Each file is one dated annual (or decennial) edition, e.g. “2024_Gazetteer” — a citable label the source itself publishes, not a retrieval date, so merge.manifest is called with method="release_number" and version=<the gazetteer year> (SPEC.md’s vintage rule for sources with no other version label).
Geography vintage. The gazetteer year is the boundary vintage: Census’s own TIGER/Line documentation states legal boundaries and names in a given year’s shapefiles are “as of January 1” of that year (confirmed against the 2020, 2022 and 2023 TIGER/Line pages), and the gazetteer files are released under the same year label as extracts of the matching TIGER/Line vintage — the switch from Connecticut’s eight counties (present through the 2020 files) to its nine planning regions, FIPS 09110-09190 (present from at least 2024), lines up with Connecticut’s real-world 2022 change and confirms the two follow the same clock.
Header quirk. The 2010 and “modern” (2011-2024) layouts pad their last header column, INTPTLONG, with trailing spaces before the newline; the 2025+ layout does not, and also gained a fully-qualified GEOIDFQ column and switched delimiter from tab to pipe. Three real layouts, two file kinds -> COUNTY_COLUMNS/TRACT_COLUMNS hold one COLUMNS contract per layout instead of a single module-level COLUMNS (the source-common convention assumes one layout per source; this source has three, verified 2026-09-18 against the real 2010, 2020 and 2026 files). A header matching none of them raises SystemExit before anything is read, same as a single-layout source.
Encoding quirk. The 2010 county file has one stray Latin-1 byte instead of UTF-8 (NM 35013, “Doña Ana County”); every other vintage checked, and the 2010 tract file (which has no NAME column to carry the accent), are clean UTF-8. _ensure_utf8 decodes the whole file as Latin-1 on a UTF-8 failure — safe because the rest of the file is plain ASCII, where Latin-1 and UTF-8 agree — recovering the intended character exactly rather than raising or replacing it with U+FFFD.
ponytail: only counties and tracts are landed (SPEC.md M1 asks for county + tract, 2010 + 2020 + latest). Places, county subdivisions, ZCTAs, PUMAs and every other gazetteer file are not — add one alongside these two if a later milestone needs it.
ponytail: state names come from raw.census__state_fips (https://www2.census.gov/geo/docs/reference/state.txt, landed once per release, not per vintage — FIPS-to-state assignments don’t move on the gazetteer’s cadence). If that file can’t be reached, land_state_fips warns and returns 0 rather than failing the whole ingest; transform then falls back to the state’s USPS code as its name, and says so in the same way.
ponytail: layout choice is decided purely by matching the file’s own header against the three known contracts, not by branching on the year — so a year outside 2010/2011-2024/2025+ that happens to match a known layout still lands correctly, and one that doesn’t fails loudly rather than guessing.
ponytail: no geography.crosswalk row for the Connecticut county -> planning region transition. Each vintage’s units stand on their own; a weighted crosswalk (SPEC.md Acceptance B) is a separate, still-open piece of work.
Districts (#104). The same Gazetteer host also carries one national congressional-district (CD) file and one national state-legislative file per chamber (SLDU/SLDL upper/lower) per vintage year, e.g. 2024_Gazetteer/2024_Gaz_119CDs_national.zip, 2024_Gazetteer/2024_Gaz_sldu_national.zip — confirmed 2026-09-19 against the 2024 and 2026 gazetteer directory listings. Same public-domain licence as above (same host, same federal-agency doctrine); checked again 2026-09-19.
CD version axis: Congress number, not the gazetteer year. The issue’s own framing states it: “Congress number / redistricting cycle = the boundary vintage.” Census re-labels the CD file for whichever Congress its maps currently serve (2024_Gazetteer → 119CDs, 2026_Gazetteer → 120CDs, even before the 120th is sworn in) and updates it mid-cycle for court-ordered redraws — confirmed real: North Carolina’s district 3701 carries a materially different ALAND/AWATER between the 2024 (119CDs) and 2026 (120CDs) files, the same boundary vintage window the issue names (NC, AL, LA, NY, GA). land_districts resolves the Congress number for a gazetteer year from CD_CONGRESS (verified against the real directory listings, 2017-2026) rather than guessing a formula, and skips CD (warn, continue) for a year outside it — the same graceful-degradation shape land_state_fips/transform already use for state names, so a district-less year never fails the whole ingest. geography.unit.vintage stays the gazetteer year for cd/sldu/sldl too (not the Congress number) — consistent with every other level, and sufficient: landing two gazetteer years both lands and keeps both boundaries queryable, Congress number or not, exactly like Connecticut’s county -> planning-region switch above.
GEOID, not a NAME column, for CD. Unlike SLDU/SLDL (which publish NAME, e.g. “State Senate District 1”), the CD file has no NAME column (like tracts) — transform synthesizes “Congressional District N” from the GEOID’s district digits, “Congressional District At Large” for the single-district states (WY, VT, … — GEOID’s last two digits ‘00’, verified against the real file).
No 2010 layout for districts. ponytail: CD/SLDU/SLDL are only landed in the “modern” (2011-2024) and “2025+” layouts — the same two the 2024/2026 files above are in. The 2010-era files exist (Gaz_cd111_national.zip, Gaz_sldu_national.zip, Gaz_sldl_national.zip, confirmed 2026-09-19) but add a third naming scheme (cd111, the Congress number before “CDs”) and a third column layout (POP10/HU10, like 2010 counties) for a vintage this milestone doesn’t need — add CD_COLUMNS["2010"]/a _cd_zip_url 2010 case alongside COUNTY_COLUMNS["2010"]’s if a later milestone lands district history back to 2010.
Tract -> district weights: deferred to #25, not landed here. An earlier version of this module landed the 2020 redistricting-cycle Block Assignment Files (raw.census__baf, one zip per state, https://www2.census.gov/geo/docs/maps-data/data/baf2020/) and a tract_district_weights recipe over them. Caught in review (2026-09-19): that BAF product ties blocks to the 116th-Congress CD lines (the map in effect for the 2020 Census), not the 2024/2020-Gazetteer/2026-Gazetteer vintages this module actually lands (119th/120th) — joining it against geography.unit’s cd/sldu/sldl rows here would silently emit district ids for the wrong vintage. Removed rather than shipped wrong. #25 (geography.crosswalk) should instead use the Census Block Equivalency Files for the 118th/119th Congress cycle (correct vintage; not yet verified against a live download) when it lands the weighted crosswalk — this module stops at landing the district units themselves.
epa sdwis
uv run cancerbasemap sdwis (src/cancerbasemap/epa_sdwis.py)
Tables written: raw.sdwis__violations_enforcement, raw.sdwis__pub_water_systems, raw.sdwis__geographic_areas, raw.sdwis__ref_ansi_areas, measure.definition, measure.stratum, measure.observation
EPA SDWIS Federal (drinking-water violations) -> Iceberg.
Upstream: EPA ECHO’s SDWA bulk download (https://echo.epa.gov/tools/data-downloads -> “Safe Drinking Water Act (SDWA)”), a single zip refreshed quarterly: https://echo.epa.gov/files/echodownloads/SDWA_latest_downloads.zip (verified 2026-09-18: HTTP header content-length: 423774232, last-modified: Thu, 09 Jul 2026). Column definitions are documented at https://echo.epa.gov/tools/data-downloads/sdwa-download-summary (“SDWA Data Download Summary and Data Element Dictionary”), which also states the files are “refreshed quarterly.” The real downloaded header is the contract checked here, not that page’s own listing – the page’s column list differs slightly from the actual 2026Q2 file for two of the four files (missing COMPL_PER_BEGIN_DATE/COMPL_PER_END_DATE/PWS_DEACTIVATION_DATE in the violations file; page order and real order both verified by downloading the real 424 MB zip, not by trusting the page).
Chosen over Envirofacts’ efservice REST tables (the paged alternative SPEC.md’s issue #53 raised): the ECHO zip is one dated, complete extract: SUBMISSIONYEARQUARTER = '2026Q2' on every row of every file in the real download, a real citable version label the source itself publishes. Paging efservice over 15M+ violation rows has no documented per-call limit and a 15-minute request timeout, so a completeness guarantee would have to be built by hand; the bulk zip already is one.
Licence. Public domain. The SDWIS dataset’s own catalog.data.gov record (https://catalog.data.gov/dataset/safe-drinking-water-information-system-sdwis, checked 2026-09-18) names its License as https://edg.epa.gov/EPA_Data_License.html, which states verbatim: “Unless otherwise specified, all data produced by the U.S EPA is by default in the public domain and is not subject to domestic copyright protection under 17 U.S.C. § 105.” No registration or attribution is required to redistribute.
Version axis. SUBMISSIONYEARQUARTER (‘2026Q2’ in the checked download) is a column in every one of the four files, not a per-source constant this module invents – unlike RUCC or the Gazetteer, whose editions live only in the file name/URL. It is used directly as the raw overwrite-scope column (no redundant edition column added) and as source_release/merge.manifest’s version (method="release_number").
The four files landed. SDWA_PUB_WATER_SYSTEMS.csv, SDWA_VIOLATIONS_ENFORCEMENT.csv and SDWA_GEOGRAPHIC_AREAS.csv are the three the issue named. A fourth, SDWA_REF_ANSI_AREAS.csv, is landed too (raw.sdwis__ref_ansi_areas) – it is EPA’s own small (~86 KB, ~3,000-row) ANSI/FIPS county reference table, bundled in the very same zip, and it is what makes ANSI_ENTITY_CODE (a real FIPS/ANSI code column, per SPEC’s preference over name-matching) resolvable to a full county FIPS: see “Geography” below for why it is necessary rather than a hardcoded lookup. SDWA_EVENTS_MILESTONES.csv, SDWA_FACILITIES.csv, SDWA_LCR_SAMPLES.csv, SDWA_PN_VIOLATION_ASSOC.csv, SDWA_REF_CODE_VALUES.csv, SDWA_SERVICE_AREAS.csv and SDWA_SITE_VISITS.csv are also in the zip and are NOT landed – out of scope for this issue’s violations-by-county ask.
Individual columns excluded. SDWA_PUB_WATER_SYSTEMS.csv’s real header carries an administrative-contact block that is personal data for a large share of the ~434,000 systems in the real download, not just its obviously-named contact fields. Nine columns are excluded from raw.sdwis__pub_water_systems entirely (SPEC.md/AGENTS.md: public aggregates and organisations only, never individuals): - ADMIN_NAME, EMAIL_ADDR, PHONE_NUMBER, PHONE_EXT_NUMBER, FAX_NUMBER, ALT_PHONE_NUMBER – verified to hold individual people’s names, emails and phone numbers directly (e.g. ADMIN_NAME “KLINGMAN, KEN” with a personal-looking email). - ORG_NAME – verified NOT to be reliably an organisation name: of the 276,273 systems that are both privately owned (OWNER_TYPE_CODE = 'P') and small (POPULATION_SERVED_COUNT <= 500), 84,140 (30%) have an ORG_NAME matching a "LASTNAME, FIRSTNAME" pattern rather than a business name; across the whole file, 123,992 rows (29%) match that pattern, and of those, 116,976 (94%) have ORG_NAME identical to ADMIN_NAME – the same individual, not a coincidentally name-shaped company. This reverses an earlier version of this module, which kept ORG_NAME reasoning it was purely an organisational field; the real prevalence here says otherwise. - ADDRESS_LINE1, ADDRESS_LINE2 – of those same personal-ORG_NAME rows, 116,370 (94%) carry a populated ADDRESS_LINE1, which for a small, individually-owned system (a private well, a mobile-home park) is very often that person’s own mailing address, not a facility address distinct from a residence. PWS_NAME (the system’s own name), CITY_NAME, STATE_CODE, ZIP_CODE and every geography/derivation field are kept – the system itself is an organisation and a place, and nothing derived here reads any of the nine excluded columns.
Geography: PWSIDs are not counties, and this module counts violations against counties served. SDWA_GEOGRAPHIC_AREAS.csv gives each PWSID’s served areas, AREA_TYPE_CODE = 'CN' marking a county-served row. That row carries ANSI_ENTITY_CODE – a real FIPS/ANSI county entity code, preferred per SPEC over a county-name match – but it is a bare 3-digit code with no state attached, and STATE_SERVED (“state that the facility is serving”, per EPA’s own field description) is empty on every CN row in the real 2026Q2 download (verified: 0 of 411,651 county-served rows have it populated). The state instead comes from PWSID‘s own two-letter prefix (EPA’s documented PWSID format: “a two-letter state or region code followed by seven digits”), joined against raw.sdwis__ref_ansi_areas.STATE_CODE to recover the matching ANSI_STATE_CODE, which concatenates with ANSI_ENTITY_CODE into a 5-digit county FIPS. raw.sdwis__ref_ansi_areas also catches invalid codes – verified against the real file: ~5,000 ANSI_ENTITY_CODE values (mostly a block of New Jersey rows carrying even codes like ’002’, ‘004’, … where every real NJ county FIPS is odd) do not correspond to any real county for their state, and PWSID prefixes that are not a real postal state (numeric EPA-region codes, ‘NN’ for Navajo Nation, etc. – 1,124 of 411,651 rows) cannot be resolved this way at all. Both kinds are counted, reported (transform prints the unmatched rate) and excluded – never fuzzy-matched, per SPEC.md. A system serving several counties counts once in each (verified against real PWSIDs with multiple CN rows); this over-counts a single violation across a multi-county system’s service area rather than apportioning it, and is called out explicitly here per the issue’s own instruction.
Why a fourth raw table instead of a hardcoded state-FIPS constant. A static {"CT": "09", ...} dict would resolve the state half cheaply, but it cannot also validate ANSI_ENTITY_CODE against a real county list – doing that would need either landing this same reference data by another name, or silently trusting whatever 3-digit code the row carries (which is exactly how the ~5,000 bad New Jersey codes above would turn into wrong FIPS values read as real ones). Landing EPA’s own reference file, from the same zip under the same licence and version, catches that instead of trusting it.
Geography vintage: 2010. raw.sdwis__ref_ansi_areas carries Alaska’s pre-2019 Valdez-Cordova Census Area (02261) rather than the Chugach (02063)/Copper River (02066) census areas that replaced it in 2019, and Connecticut’s eight legacy counties (09001-09015) rather than the nine planning regions (09110-09190) that replaced them in 2022 – both verified directly against the real downloaded file. census_gazetteer.py documents the same two markers for its own 2010-vintage file (2020-vintage carries the post-2019 Alaska areas; CT’s planning regions arrive with a 2022-vintage source), so GEO_VINTAGE = 2010 here matches an already-landed geography.unit vintage rather than inventing a new one.
No zero-count rows. Only (county, year) pairs with at least one matched health-based violation get a row; a county with none in a given year is absent, not present with value = 0. This is a plain count of what the source reports, not a survey needing an explicit non-response marker, and there is no complete county-by-vintage universe available here to fill in against (unlike RUCC, which has one row per FIPS in its own file).
What is NOT derived, and why. The issue also asked, “if defensible,” for a count of active community water systems and the population served by systems with a health-based violation, per county-year. Neither is: SDWIS’s system-level fields (PWS_ACTIVITY_CODE, POPULATION_SERVED_COUNT) are a current snapshot, not a per-year history – SDWA_PUB_WATER_SYSTEMS.csv carries no historical activity or population column at all. Attaching today’s activity/population to a 2016 violation would misrepresent a present-day fact as a historical one, the opposite of the derivation this measure earns by reading violation dates directly. Left out; only the violation-count measure below is derived.
ponytail: violations before the earliest year Cancer InFocus expects (2016) or in quarter’s own (necessarily incomplete) calendar year are landed in raw like everything else but excluded from the derived measure – verified against the real file that the extract quarter’s own year is a fraction of every prior year’s row count (4,967 health-based violations begun in 2026 vs. 32,346-70,695 per prior year), confirming it is a partial year, not that violations stopped.
ponytail: SDWA_VIOLATIONS_ENFORCEMENT.csv (15,432,737 rows / ~4.1 GB / ~5.1 GB as Arrow in the real 2026Q2 download) fits in memory whole (measured locally: 1.5s, ~20 GB peak RSS against the local sqlite warehouse), but is landed in batches anyway – ported from bioc-on-ice’s ncbi.py::_land – because the production target is R2 Data Catalog, which rate-limits commits per table and, per that module’s own experience, does not want one multi-GB single-commit overwrite.
ers food access
uv run cancerbasemap food-access (src/cancerbasemap/ers_food_access.py)
Tables written: raw.ers__food_access_sram, raw.ers__food_access, measure.definition, measure.stratum, measure.observation
USDA ERS Food Access Research Atlas -> Iceberg, in the same two phases as the other sources.
Upstream: https://www.ers.usda.gov/data-products/food-access-research-atlas/ download-the-data – census-tract low-income/low-access (“food desert”) flags and the population counts behind them (SPEC.md § Sources – first tranche). As of the site’s 2026-07-27 update, ERS has renamed the 2019 product “Large Retailer Access Map (LRAM) (formerly known as the Food Access Research Atlas (FARA))” and added a 2025 “SNAP-authorized Retailer Access Map (SRAM)” on 2020 tracts; both still link the original 2019/2015/2010/2006 FARA files under “Archived Versions” (checked 2026-09-18). This module lands 2019, 2015 and the 2025 SRAM (issue #112, see below); 2010 and the archived 2006 file stay out (ponytail note below). source stays ‘FARA’ downstream since that is the name issue #42 and every fixture/measure_id here use.
Licence. USDA ERS is a federal agency; its work product is a U.S. Government work. Per resources.data.gov/open-licenses/ (the definition data.gov uses for every federal agency’s “Public Domain” listing, already the basis for ers_rucc.py’s licence in this repo): “Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection under 17 U.S.C. Sec 105. Government works are by default in the U.S. Public Domain.” (checked 2026-09-18). No registration or attribution is required to redistribute.
Version axis is the edition. Verified by downloading each file (2026-09-18) from the Download-the-Data page’s real links: - 2019: https://www.ers.usda.gov/media/5627/2019-large-retailer-access-map- lram-formerly-known-as-the-food-access-research-atlas-fara-data.zip – a zip of a 147-column, comma-delimited, UTF-8 CSV (Food Access Research Atlas.csv) plus a ReadMe and a VariableLookup sheet, 72,531 data rows. - 2015: https://www.ers.usda.gov/media/5623/2015-food-access-research- atlas-fara-data-and-documentation.zip – a zip of a 147-column .xlsx (FoodAccessResearchAtlasData2015.xlsx, sheet “Food Access Research Atlas”) plus its PDF documentation, 72,864 data rows. Read with DuckDB’s excel extension (INSTALL excel; LOAD excel;) via read_xlsx – .xls-only sources (2006, below) stay out rather than adding a dependency, same call as ers_rucc.py’s 2013 omission.
Both editions publish the exact same 147 columns in the exact same order – verified by diffing the two real headers column-for-column – differing only in the case of one column: 2019 spells it Pop2010, 2015 spells it POP2010. COLUMNS below carries the two real (verified) headers keyed by edition; the landed raw.ers__food_access table uses 2019’s spelling as the canonical column name for that field, so PROVENANCE needs no separate union-schema handling the way census_gazetteer.py’s three real layouts do.
ponytail: 2010 and the archived 2006 “Food Desert Locator” are not landed. 2010 (https://www.ers.usda.gov/media/5624/… .zip, verified 2026-09-18) is a real third layout – 65 columns in a different order, no race/ethnicity breakdown, no PovertyRate/MedianFamilyIncome, plus Rural and UATYP10 columns the later editions drop – tractable in principle but a third COLUMNS layout is more than this module needs for #42’s ask (2019 “and 2015 if its layout is tractable”, which it is). 2006 (https://www.ers.usda.gov/media/5625/archived-2006-food-desert-locator.zip) ships only as a legacy .xls, unreadable by DuckDB’s excel extension (.xlsx-only), same constraint as ers_rucc.py’s 2013. Revisit either if a later milestone needs pre-2015 food-access history.
Geography vintage: 2010 census tracts for 2019 and 2015 (2025 SRAM moves to 2020 tracts – see below). ERS’s own Documentation page states the 2019 (LRAM) estimates are “based on … the 2010 Decennial Census” and the 2015 estimates likewise “based on … the 2010 Decennial Census” – checked against https://www.ers.usda.gov/data-products/food-access- research-atlas/documentation, 2026-09-18. The downloaded files confirm it directly: Connecticut’s tracts in both editions keep the legacy county-based 11-digit FIPS prefix 09001-09015 (e.g. tract 09001010101, Fairfield County), not the 2022 planning-region prefixes (09110-09190) that show up in county-level sources landed elsewhere in this repo from their 2022+ releases (ers_rucc.py, places.py) – 2010-vintage Census tract geography is unaffected by that later county-level administrative reorganization.
Missing-value sentinel differs by edition – enumerated, not guessed. 2019’s CSV uses the literal text NULL for every missing count/share cell (e.g. 71,025 of 72,531 rows for lapop20); there are zero genuinely blank cells anywhere in the file (checked with DuckDB’s nullstr='', i.e. an empty field would already read as SQL NULL and none do). 2015’s xlsx has no missing-value sentinel at all for any column this module derives – every cell holds a real number, 0 where a distance threshold has no low-access population, confirmed by reading the raw sheet XML directly (e.g. lapop20 cells are literal <v>0</v>, not blank). _check_sentinels fails loudly on any non-numeric, non-‘NULL’ text in a derived column, rather than silently mis-parsing a third convention this module hasn’t seen.
Share columns are on different scales per edition – a real landmine. Recomputing from the published counts: 2015’s ...share columns are 0-1 fractions (tract 01001020100’s lapop1share = 0.70998, and lapop1 / Pop2010 = 1357.48 / 1912 = 0.70998 exactly); 2019’s are 0-100 percentages (the same tract’s lapop1 / Pop2010 * 100 = 1896 / 1912 * 100 = 99.16, rounding to the published 99.19 – the small residual is the published integer counts themselves being rounded from the same underlying floats 2015 keeps unrounded). SHARE_SCALE normalizes both onto the 0-100 percent scale for measure.observation.value (rate_basis='percent') so the two editions are comparable there; raw.ers__food_access keeps each edition’s own published scale verbatim, unmodified (SPEC.md: raw lands whole).
ponytail: only 10 of the 147 columns are derived into measure.observation – the 4 LILA flags plus the half-mile and 1-mile population / low-income / no-vehicle share triples, the distance thresholds a catchment access-gap recipe (SPEC.md § Recipes #3) actually reaches for. The 10-mile and 20-mile thresholds, every race/ethnicity breakdown (la*1, la*10, la*20, la*half per race), SNAP, poverty rate, median family income, and the group-quarters columns all land in raw.ers__food_access and stay queryable there via SQL; add a measure.definition row alongside these if a later recipe needs one.
2025 edition: the SNAP-authorized Retailer Access Map (SRAM) (issue #112). CIF’s food-desert layer cites “USDA ERS, 2025” on 2020 census tracts; #112 asked whether ERS had published a post-2019 FARA edition #87 missed. It has. The Download-the-Data page (https://www.ers.usda.gov/data-products/food-access-research-atlas/ download-the-data, checked 2026-09-19) lists the 2019 file under “Current Versions” alongside a new “2025 SNAP-authorized Retailer Access Map (SRAM) Data”, last updated 7/27/2026; its own ReadMe recommends the citation “U.S. Department of Agriculture, Economic Research Service. Food Access Research Atlas, SNAP-authorized Retailer Access Map” – the same product family CIF is citing. The Documentation page states: “Estimates in the Large Retailer Access Map (LRAM) for 2019 are based on … the 2010 Decennial Census …”; “Estimates in the … SNAP-authorized Retailer Access Map (SRAM) for 2025 are based on … the 2020 Decennial Census; the LandScan USA 2020 nighttime dataset; and the 2020-24 American Community Survey (ACS)” (both checked 2026-09-19). Verified directly against the downloaded file too: 84,119 rows (vs. 72,531 for 2019), and CensusTract20 11060xxxxxxx-style values that only exist in geography.unit’s 2020 vintage – resolving #112’s open question: CIF’s 2020-tract food-desert layer is this SRAM release, not a crosswalk of 2019 data (that would have been #25’s territory; it is not needed here).
SRAM is not a header change to the same 147-column layout – it is a genuinely different upstream product, same house rule as ers_ruca.py’s 2010-vs-2020 layouts: SRAM measures access to any SNAP-authorized retailer (“all store types (e.g., smaller grocery stores, specialty food stores, convenience stores, neighborhood markets, dollar stores),” per the Documentation page), not the 2019 LRAM’s supermarkets/supercenters/large grocery stores only, and it ships as three separate CSVs inside one zip (https://www.ers.usda.gov/media/29395/2025-snap-authorized-retailer-access- map-sram-data.zip, verified 2026-09-19) rather than one: “SRAM General Tract Characteristics Data.csv” (27 columns, GENERAL_COLUMNS_2025), “SRAM Straight Line Distance Data.csv” (135 columns incl. 4 join keys, SD_COLUMNS_2025) and “SRAM Driving Distance Data.csv”. This module lands the first two, joined on CensusTract20, into their own table – raw.ers__food_access_sram, USDA’s own column names verbatim, no aliasing onto the 147-column layout’s names (their meaning genuinely differs: 2020 Census/2020-24 ACS inputs, 2020 tracts, SNAP retailers, not 2010/2019 supermarkets). transform maps the FARA:* measure ids this module already derives onto SRAM’s SD_SRAM_-prefixed straight-line-distance columns and POP2020/OHU2020 denominators, so the same measure_id is comparable across all three editions via source_release.
ponytail: the Driving (Network-Based) Distance file is not landed. It is a wholly new distance methodology (road-network travel time) with no analog in any earlier edition and nothing in this module’s existing derivation reaches for it – same reasoning as the 2010/2006 omissions above. Revisit if a later recipe wants network-distance food access.
ponytail: County24 (the SRAM general file’s 2024-vintage county name) is not landed. Nothing here needs a second county name alongside County20; add it if a later join wants 2024 county boundaries specifically.
Missing-value sentinel, third convention. SRAM’s own Read Me: “Cells are intentionally left blank when data are unavailable or not applicable” – neither 2019’s literal 'NULL' text nor 2015’s always-populated cells. Verified directly: e.g. tract 01015981903 (a zero-population Calhoun County, AL tract) has SD_SRAM_lapop1 = '0' but a genuinely blank SD_SRAM_lapop1share. nullstr='' on the CSV read turns a blank cell into SQL NULL directly, so _check_sentinels/TRY_CAST’s existing “anything that isn’t a number is a hard stop unless it’s the enumerated sentinel” logic already covers this convention with no code change – a real NULL never reaches the “unmapped non-numeric” check at all.
Encoding. Both SRAM files have the same stray-Latin-1-byte issue as census_gazetteer.py’s 2010 county file and ers_ruca.py’s 2020 CSV: “Doña Ana County” (tract 35013001103) is not valid UTF-8. _ensure_utf8 recovers it the same way – decode the whole file as Latin-1 on a UTF-8 failure, safe because the rest of each file is plain ASCII.
Share scale. SRAM’s share columns are already 0-100 percentages, like 2019’s (recomputed: tract 01001020100’s SD_SRAM_lapop1 / POP2020 * 100 = 1080 / 1775 * 100 = 60.85, matching the published SD_SRAM_lapop1share exactly) – SHARE_SCALE["2025"] = 1, no rescaling.
ers ruca
uv run cancerbasemap ruca (src/cancerbasemap/ers_ruca.py)
Tables written: raw.ers__ruca_tract, measure.definition, measure.stratum, measure.observation
USDA ERS Rural-Urban Commuting Area codes -> Iceberg, tract level.
Upstream: https://www.ers.usda.gov/data-products/rural-urban-commuting-area-codes Sub-county rurality: primary and secondary RUCA codes at census-tract grain (SPEC.md § Sources — first tranche: “rurality, food access”). Third writer in the ERS family after RUCC (ers_rucc.py, #13) – same publisher, same licence basis, same Latin-1 caveat on the CSV edition.
Licence. Neither the product page, the documentation page nor the users’ guide (https://www.ers.usda.gov/data-products/rural-urban-commuting-area-codes, its /documentation and /users-guide pages, all checked 2026-09-18) states a licence – same as RUCC. USDA ERS is a federal agency; the RUCA documentation page’s own byline credits federal staff (“Elizabeth A. Dobis or Austin Sanders”, division RRED). “Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection under 17 U.S.C. Sec 105. Government works are by default in the U.S. Public Domain.” (https://resources.data.gov/open-licenses/, checked 2026-09-18).
Version axis is the edition. ERS publishes tract-level RUCA for 1990, 2000, 2010 (revised 2019) and 2020 (verified 2026-09-18 by fetching the product page and listing every /media/*.{csv,xlsx,xls} link on it). Only 1990 and 2000 have no CSV/XLSX form – .xls only, unreadable with DuckDB’s excel extension (.xlsx-only) and no new dependency is allowed, mirroring ers_rucc.py’s 2013 omission.
ponytail: 1990/2000 not landed – legacy .xls only, same reasoning as ers_rucc.py’s 2013 omission. Revisit if ERS ever republishes either as CSV or XLSX.
ponytail: the ZIP-code RUCA approximation (also on this product page) is OUT OF SCOPE. It is keyed by ZIP code, and issue #64 has not yet settled what a ZIP-keyed measure means in this lake (crosswalk to ZCTA? land ZIP as its own geography level?) – landing it here would prejudge that. Only the census -tract files are landed.
The two landed editions are genuinely different upstream layouts, not a header that merely drifted – house rule “one COLUMNS layout per real upstream layout, detected by header match” (AGENTS.md) applies literally:
- 2010 (revised 3 Jul 2019, per the workbook’s own errata note) is an
.xlsxwith a 9-columnDatasheet:COLUMNS_2010, read via DuckDB’sexcelextension (INSTALL excel; LOAD excel;). Row 1 of that sheet is the errata note, not data – the real header is row 2 (range='A2:...',stop_at_empty=trueso the read stops at the sheet’s actual last row rather than padding to Excel’s 1,048,576-row ceiling). Reading it typed (notall_varchar) and then formatting numeric cells to VARCHAR ourselves avoids a DuckDBexcel-extension quirk:all_varchar=trueconverts an Excel float to text at full double precision (10.199999999999999for a cell displaying10.2); casting a typed DOUBLE straight to VARCHAR does not have that problem (verified against the real file, 2026-09-18). - 2020 is a plain CSV,
COLUMNS_2020, encoded Latin-1 like RUCC’s CSV (verified 2026-09-18: “Cañon City, CO” inPrimaryDestinationName) despite the server’sContent-Typeclaiming UTF-8 – decoding the header line as Latin-1 is safe regardless, since the header itself is plain ASCII.
Both editions land into the one raw.ers__ruca_tract table, discriminated by ruca_edition; each edition’s own columns are NULL on the other edition’s rows (schemas.py’s “a column exists here only once something populates it” already anticipates exactly this: both editions are landed by this same PR’s end-to-end run, so every declared column is in fact populated by some row).
Geography vintage. GEO_VINTAGE maps each edition to the census-tract boundary year it classifies (2010 tracts for the 2010 edition, 2020 tracts for the 2020 edition) – verified directly against each downloaded file, not inferred from the edition label alone:
- 2010 edition: Connecticut carries its eight legacy counties (09001-09015, e.g. “09001,CT,Fairfield County”) and Alaska carries the pre-2019 “02261,AK,Valdez-Cordova Census Area” rather than its current Chugach (02063) / Copper River (02066) split – both markers of 2010-vintage Census geography, confirmed in the downloaded workbook.
- 2020 edition: this file carries TWO tract-FIPS columns,
TractFIPS20(built on each tract’s 2020-vintage county code: Connecticut’s eight legacy counties, Alaska’s post-2019 areas) andTractFIPS23(the same tracts under the county-equivalent codes ERS uses as of 2023 – Connecticut’s nine planning regions, 09110-09190). Both are genuinely present in the real file (verified 2026-09-18): e.g. tract 09001010101 (Fairfield County, legacy) appears asTractFIPS20, the same physical tract appearing asTractFIPS23 = 09190010101(Western Connecticut Planning Region); four zero-population Connecticut water tracts haveTractFIPS23 = 'N/A'(no defined 2023 county assignment) whileTractFIPS20is always populated.geo_idis derived fromTractFIPS20, notTractFIPS23: it is always present, and it is what lines up with the 2020-vintage geography this Cancer Basemap release already carries elsewhere (census_gazetteer.py’s vintage-2020 landing also uses Connecticut’s legacy counties, “present through the 2020 files” per its own docstring) – consistent by verification, not forced to match (AGENTS.md: “Do not force it to a landed Gazetteer vintage”).TractFIPS23and itsCountyFIPS23/CountyCode23/CountyName23siblings still land in raw, verbatim, for whoever needs to join this file to a 2023-planning-region -keyed product (e.g.ers_rucc.py’s 2023 edition) later.
Suppression. Both editions use code 99 on both PrimaryRUCA and SecondaryRUCA for the same thing: a zero-population tract with no rural-urban identifier (UrbanCoreType = 'Water' on every 99 row checked) – value_status = 'not_applicable', value NULL, never landed as the number 99. No other sentinel (blank, -999, *) appears in either edition’s RUCA columns (checked directly, both files).
ponytail: Population/LandArea/PopDensity (2020) and the _2010 equivalents land in raw (they are part of the file) but are not derived into measure.observation – population denominators come from SEER/ACS elsewhere in the lake, matching ers_rucc.py’s identical choice.
ers rucc
uv run cancerbasemap rucc (src/cancerbasemap/ers_rucc.py)
Tables written: raw.ers__rucc, measure.definition, measure.stratum, measure.observation
USDA ERS Rural-Urban Continuum Codes -> Iceberg, in the same two phases as the other sources.
Upstream: https://www.ers.usda.gov/data-products/rural-urban-continuum-codes County-level rurality classification (codes 1-9: metro by population size, nonmetro by urban-population size and adjacency to a metro area), published per edition. ERS’s own product and documentation pages carry no licence statement (checked 2026-09-18). RUCC is produced by USDA ERS, a federal agency, as part of employees’ official duties, so it is a U.S. Government work: “Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection under 17 U.S.C. Sec 105. Government works are by default in the U.S. Public Domain.” (https://resources.data.gov/open-licenses/, checked 2026-09-18).
Version axis is the edition (ERS republishes RUCC roughly once a decade: 1974, 1983, 1993, 2003, 2013, 2023), not a retrieval date – the edition is in the file name and in the published attribute name (RUCC_2023), so it is a real citable label, landed as rucc_edition.
Only the 2023 edition is landed. It is published as a long-format CSV, verified 2026-09-18 by downloading it: header FIPS,State,County_Name, Attribute,Value, comma-delimited, iso-8859-1 encoded (not UTF-8 – e.g. “Doña Ana County”), one row per (county, attribute) with Attribute in Population_2020 | RUCC_2023 | Description. 2013 is published only as a legacy .xls (verified 2026-09-18: ERS lists no CSV or .xlsx form for it, only for 2023 and the rolling current file – /media/5767/...xlsx exists for 2023 alongside the CSV, but 2013’s only sibling is /media/5769/ 2013-rural-urban-continuum-codes.xls). DuckDB cannot read legacy .xls (excel extension is .xlsx-only) and no new dependency is allowed, so 2013 is left out.
ponytail: 2013 not landed – no CSV/XLSX form exists upstream and adding an .xls reader is a new dependency. Revisit if ERS ever republishes it, or if a later source needs pre-2020 rurality and a hand conversion becomes worthwhile.
Geography vintage: 2020, for the 2023 edition. ERS’s documentation says the codes classify counties “based on the 2023 Office of Management and Budget (OMB) delineation of metro areas,” itself built on “2020 Census and the 2016-20 American Community Survey (ACS)” geography, and that in 2022 “planning regions in Connecticut replaced counties in the Census Bureau’s county-level geography.” The downloaded file confirms this directly: it carries Connecticut’s nine 2022 planning regions (FIPS 09110-09190, e.g. “09110,CT,Capitol Planning Region”) rather than the six legacy counties (09001-09015), and Alaska’s current census areas – Chugach (02063) and Copper River (02066) – rather than the pre-2019 Valdez-Cordova (02261) they replaced. Both are 2020-vintage Census geography markers.
Two American Samoa entities (Rose Island 60030, Swains Island 60040) report zero 2020 population and carry no RUCC_2023 attribute row at all – verified in the downloaded file. That is a missing code, not a suppressed one; it lands as value_status = 'not_available', never as a number and never silently dropped.
ponytail: Population_2020 is landed in raw (it is part of the file) but not derived into measure.observation – population denominators come from SEER/ACS elsewhere in the lake, not from RUCC.
fda mqsa
uv run cancerbasemap mqsa (src/cancerbasemap/fda_mqsa.py)
Tables written: raw.fda__mqsa_facilities, facility.site
FDA MQSA certified mammography facility list -> Iceberg, landed weekly.
Upstream: FDA’s “Search for a Certified Facility” page – https://www.fda.gov/radiation-emitting-products/consumer-information-mqsa/ search-certified-facility (redirects to a renamed slug on fda.gov, checked 2026-09-18) – links “download the mammography facilities zip file … This file is replaced weekly” (fda.gov, same page, checked 2026-09-18) to the historical, still-live URL http://www.accessdata.fda.gov/premarket/ ftparea/public.zip. The zip’s own Last-Modified header read 2026-09-14 when checked 2026-09-18, consistent with a weekly refresh.
Licence. FDA is a federal agency; its site-wide policy (https://www.fda.gov/about-fda/about-website/website-policies, checked 2026-09-18) states: “Unless otherwise noted, the contents of the FDA website (www.fda.gov) – both text and graphics – are not copyrighted. They are in the public domain and may be republished, reprinted and otherwise used freely by anyone without the need to obtain permission from FDA.” There is no dataset-specific terms page for this listing (unlike HRSA’s per-dataset Data Usage Terms, hrsa_sites.py) – this site-wide statement, plus 17 U.S.C. Sec 105 (federal-employee works are not subject to domestic copyright), is the licence basis, same as every other federal source landed here.
No individual records. Every field is Facility Name / Address 1-3 / City / State / Zip / Phone / Fax (verified against the live file 2026-09-18, and matches the field list FDA’s own page publishes) – no clinician or patient data. FDA’s page also notes Veterans Health Administration facilities are excluded (MQSA does not apply to VHA).
Version axis: retrieval date. FDA replaces the whole file weekly with no edition label – the same shape as HRSA’s two live snapshots (hrsa_sites.py): retrieved_on is raw’s version column, and merge.manifest is called with method=“retrieval_date”.
File format, verified 2026-09-18 by downloading the live zip. One member, public.txt: 9 pipe-delimited fields, in the order FDA’s own page documents (Facility Name(75) / Address 1(50) / Address 2(50) / Address 3(50) / City(50) / State(2) / Zip Code(15) / Phone(50) / Fax(50)) – COLUMNS below. There is NO header row (confirmed against all 9,131 live rows: none is the field-name list). CRLF line endings; every byte checked across all 9,131 rows is plain ASCII (no Latin-1 gotcha here, unlike two other sources in this repo); no quote characters or embedded delimiters anywhere in the file, so quote='' (no quoting at all) is the honest dialect rather than DuckDB’s " default. _check_layout verifies the first line still splits into exactly 9 pipe-delimited fields before landing; a field added or removed upstream raises SystemExit rather than silently misaligning every column that follows it.
No facility id. The file carries no id column of any kind. facility_id is therefore ‘FDA_MQSA:’ + an md5 of the normalised (uppercased, internal whitespace collapsed to one space) Facility Name + Address 1/2/3 + City + State + the zip’s first 5 digits – phone/fax are excluded from the key deliberately, see the next paragraph.
Collision, seen in the real file (2026-09-18). “Invision Diagnostics - Mobile”, Charlotte NC 28277, is listed three times: two rows share the exact name/address text (differing only in phone number and one having “MOBILE” capitalised) and collapse to the SAME key here; the third differs in the address itself (“Elm Ln.” vs “Elm Lane”) and keeps its own key. That is real untidiness in FDA’s own list, not a bug in this key – deduplication keeps one row per key (QUALIFY row_number() ... = 1, deterministic tie-break on name then address) and the ingest report says how many raw rows collapsed.
ponytail: a facility that renames, or has its address re-typed, between two snapshots looks exactly like the old one closing and a new one opening (merge.merge’s ‘retired’ + ‘new’, never ‘changed’), because the key IS the name+address text – there is no id underneath to prove continuity. That is the ceiling of any name+address key, not something this module can compute around; the fix is FDA publishing a real id. A direct consequence: facility.site’s only non-key attributes for this source are the always-‘mammography’ kind and the always-NULL lat/lon/geo_id/geo_vintage, so a ‘changed’ row (same facility_id, different attributes) structurally never happens for FDA_MQSA – every real edit surfaces as retire+new instead.
Geography. No coordinates, no FIPS anywhere in the file – lat, lon, geo_id and geo_vintage all land NULL; zip, city, state go into attributes_json instead. Deriving geo_id needs a ZIP -> county step (a ZCTA/ZIP crosswalk) that this module deliberately does not perform: no geocoder dependency and no external geocoding API (AGENTS.md: no new dependencies), and SPEC.md’s own Open Questions flags ZIP/ZCTA crosswalks as “the usual licence trap” – issue #64 tracks that decision and has not made it yet.
facility.site.source_release is NULL for FDA_MQSA, for the same reason as HRSA_HC (hrsa_sites.py, issue #19): source_release is not part of facility.site’s business key, so an unchanged facility merges as ‘unchanged’ across retrieval dates instead of opening a fresh version every single week purely because the snapshot date moved. The retrieval date itself is recorded in provenance.release and in raw’s own retrieved_on, per SPEC.md’s versioning model.
geography alias
uv run cancerbasemap geo-alias (src/cancerbasemap/geography_alias.py)
Tables written: raw.geography__county_recodes, geography.alias
geography.alias – FIPS renames and re-codings that are not boundary changes (SPEC.md § geography.alias, #26), so a join on an old FIPS code resolves to the new one rather than silently dropping (SPEC.md Acceptance B).
Upstream: the Census Bureau’s “Substantial Changes to Counties and County Equivalent Entities: 1970-Present” landing page (https://www.census.gov/programs-surveys/geography/technical-documentation/ county-changes.html) and its decade sub-pages (…/county-changes.1990.html, …/county-changes.2000.html, …/county-changes.2010.html, …/county-changes.January_2020.html). Checked 2026-09-18: none of these pages, nor the index page, link to a machine-readable (CSV/XLS) file of name/code changes – the only linked data files are Connecticut’s county-to-subdivision crosswalk (a boundary crosswalk, not a rename/recode list; that belongs to geography.crosswalk, #25). The page content itself is prose tables, so this source is a small curated CSV committed in the package (data/county_recodes.csv), one row per recode, each row’s note column quoting the exact Census sentence it was transcribed from with the decade page it came from as source_url.
Licence. census.gov carries no per-page copyright statement (checked 2026-09-18 against the index and every decade page used here), but it is a federal agency site and the Bureau is a federal agency: “Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection under 17 U.S.C. Sec 105. Government works are by default in the U.S. Public Domain.” (https://resources.data.gov/open-licenses/, checked 2026-09-18) – the same authority the other Census/USDA sources in this package cite.
Version axis. Census publishes no edition label for this page (it is a living document, amended as changes occur), so it is versioned like raw.census__state_fips: merge.manifest records version_method ="retrieval_date" with the date this transcription was checked (2026-09-18), and the raw table is replaced wholesale (AlwaysTrue()) each time the curated CSV is revised, rather than accumulating “editions” that do not exist upstream.
Geography. old_geo_id/new_geo_id carry no vintage: unlike geography.unit (keyed on geo_id + vintage, since boundaries move), an alias is a pure code mapping that a join resolves regardless of which vintage the observation’s geo_id came from.
Included – pure recodes/renames since 2000, plus one 1997 recode still carried by SCP-era data (per issue #26): - Shannon County, SD 46113 -> Oglala Lakota County, SD 46102 (2015) - Wade Hampton Census Area, AK 02270 -> Kusilvak Census Area, AK 02158 (2015) - Dade County, FL 12025 -> Miami-Dade County, FL 12086 (1997) All three are Census’s own “Name and/or Code Changes” category: the area is untouched, only the FIPS code and name change.
Excluded – real boundary changes, verified against the same decade pages, left for geography.crosswalk (#25) because a weighted crosswalk, not a 1:1 alias, is the honest representation: - Connecticut’s eight counties -> nine planning regions (2022): the planning regions do not share the counties’ boundaries. - Valdez-Cordova Census Area, AK 02261 -> split into Chugach 02063 and Copper River 02066 (2019): one code becomes two areas, not a rename. - Bedford (independent) city, VA 51515 -> merged into Bedford County 51019 (2013): a real annexation (~6,222 people), not a pure recode. - Broomfield County, CO 08014 (2001): a new entity assembled from parts of four other counties (Adams, Boulder, Jefferson, Weld), not a rename of any one of them. - Prince of Wales-Outer Ketchikan Census Area, AK 02201 -> Prince of Wales-Hyder 02198 (2008): the “rename” happened only because Ketchikan Gateway Borough annexed most of the area first – a boundary change with a new name attached, not a pure recode. - Skagway-Hoonah-Angoon (02232, 2007) and Wrangell-Petersburg (02280, 2008) census areas: each split into two new entities. - LaSalle Parish, LA (22059); LaSalle County, IL (17099); Doña Ana County, NM (35013): spelling corrections with NO FIPS code change – a join on the “old” code already resolves, so there is nothing for this table to fix.
ponytail: only decade pages back to 1990 were checked (far enough to cover every SCP-era recode named in #26); 1970/1980 are not read. Add a row – citing the matching decade page – if an older pure recode turns out to matter to a landed source.
Verified against the real Gazetteer (2026-09-18, cancerbasemap gazetteer --year 2010 and --year 2026, ad hoc – not a pytest test, since it hits the network): every old_geo_id here that postdates the 2010 vintage (Shannon/Oglala Lakota, Wade Hampton/Kusilvak, both 2015) is present in the landed 2010 geography.unit and absent from 2026’s, with new_geo_id the reverse. Dade/Miami-Dade (1997) predates 2010, so its new_geo_id is present in BOTH landed vintages and old_geo_id in neither – the Gazetteer has never carried the retired code, which is the correct outcome for a recode that happened before Cancer Basemap’s earliest landed vintage, not a gap in this table.
A join that resolves an old code before it ever reaches geography.unit (SPEC.md Acceptance B):
SELECT coalesce(a.new_geo_id, o.geo_id) AS geo_id, o.value
FROM measure.observation o
LEFT JOIN geography.alias a ON a.old_geo_id = o.geo_id
-- now join geo_id (not o.geo_id) to geography.unit; a row keyed to
-- Shannon County's old code (county:46113) resolves to Oglala Lakota's
-- current one (county:46102) instead of dropping.hrsa ahrf
uv run cancerbasemap ahrf (src/cancerbasemap/hrsa_ahrf.py)
Tables written: raw.hrsa__ahrf, measure.definition, measure.stratum, measure.observation
HRSA Area Health Resources Files (AHRF), county level -> Iceberg.
County-level workforce and facility supply data – the supply-side context for a catchment (SPEC.md § Sources – first tranche). Upstream: https://data.hrsa.gov/topics/health-workforce/ahrf (topic page) and https://data.hrsa.gov/data/download?data=AHRF (downloads), annual releases.
Distribution format, checked 2026-09-18. The download page’s real hrefs (fetched directly, not summarized) show county-level AHRF as ASCII fixed-width for 1999-2018, and CSV + SAS + ASCII from the 2022-2023 release on (AHRF_CSV_2022-2023.zip, AHRF%202023-2024%20CSV.zip, AHRF_2024-2025_CSV.zip). Only those three CSV-format releases are ever in scope for this module; earlier fixed-width-only editions are left out.
ponytail: only the current 2024-2025 release is landed. 2022-2023 and 2023-2024 are also CSV-format and could be added the same way RUCC’s editions would be, by declaring their own header/COLUMNS constants first – not done here since nothing yet needs the history.
The CSV zip is one enormous county-row-per-file plus redundant subset extracts, not several independent thematic files. Downloaded and inspected directly: AHRF_2024-2025_CSV.zip contains AHRF2025.csv (3,235 rows x 4,352 columns – one row per county/county-equivalent, every field) plus seven “thematic” files (env, exp, geo, hf, hp, pop, utl) that are column subsets of the same master file, for people who only want one category – verified column-for-column identical to the master file (a handful of last-column mismatches turned out to be a CRLF artifact of the comparison, not real data). Landing the one master file is landing the whole release; the thematic files add nothing.
Licence – this is the load-bearing finding of this module
The topic page states, verbatim: “Usage limitations: None” (checked 2026-09-18, https://data.hrsa.gov/topics/health-workforce/ahrf, rendered page). That is the generic, page-level claim the issue’s license:cleared label was presumably set from.
The release’s OWN bundled technical documentation (AHRF_USER_TECH_2024-2025.zip -> Technical Documentation/AHRF 2024-2025 Technical Documentation.xlsx, downloaded and opened directly, 2026-09-18) says something narrower and, for two entire categories of field, contrary:
“The Area Health Resources Files are made available by the Bureau of Health Workforce. Reproduction for re-use or sale is not authorized without the expressed permission of the Bureau. Further, data from the American Dental Association, the American Hospital Association, and the American Medical Association are subject to copyright restrictions; these data may not be copied or reproduced in whole or in part without the prior consent of the copyright owner.”
and its own source-abbreviation glossary, in the same file:
“AHA Survey Database = American Hospital Association Hospital Facilities Database (Copyright)” “AMA Phys Master File = American Medical Association Physician Master File (Copyright)”
The per-field data dictionary in the same document (columns FIELD/CAT/YEAR OF DATA/VARIABLE NAME/CHARACTERISTICS/SOURCE/DATE ON) names, for every one of AHRF’s 4,352 county columns, which of 45 distinct sources it came from (6 more columns list no source at all). The gate below is an ALLOWLIST, not a blocklist: a column lands only if its source is on ALLOWED_SOURCES (or is one of the 6 no-source structural fields, NO_SOURCE_LISTED_ALLOWED) – anything else is excluded by default, whether this module has a documented reason for it (EXCLUDED_SOURCES) or has simply never seen that source before (reported by land_raw, never landed silently). Every one of the 45 distinct sources in the real 2024-2025 file, checked 2026-09-18:
| Source (as the documentation spells it) | Cols | Verdict | Why |
|---|---|---|---|
| AMA Phys Master File | 1815 | EXCLUDED | American Medical Association Physician Masterfile – glossary-flagged (Copyright); named in the cover-page restriction quoted above. Every physician-specialty count (oncology, radiation oncology, gastroenterology, radiology, dermatology, total M.D.s) is this source. |
| AHA Survey Database 23 | 493 | EXCLUDED | American Hospital Association survey – (Copyright), same restriction. Every hospital/bed count is this source. |
| AHA Survey Database 22 | 295 | EXCLUDED | as above. |
| ADA Masterfile | 50 | EXCLUDED | American Dental Association Masterfile – named directly in the cover-page restriction (its glossary entry omits the (Copyright) tag the other two carry, but the prose names it all the same). |
| County Health Rankings File | 2 | EXCLUDED | Third party (County Health Rankings & Roadmaps, U Wisconsin PHI / RWJF) – SPEC.md’s Licence gate already lists this source as “Needs review before ingest … confirm the record’s licence”, repo-wide and unresolved. |
| System Sciences Study | 1 | EXCLUDED | One 1976-77 elevation field from a private contractor’s environmental-mortality study – no redistribution terms found. |
| DDH,9-33 | 1 | EXCLUDED | One field (cnty_name) citing an internal document code not resolved in the documentation’s own glossary – excluded for lack of a verifiable source, not suspected proprietary (the county name is also carried, allowed, as part of cnty_name_st_abbrev, “Derived From GSA”). |
| NCHS Mortality File | 88 | EXCLUDED (pending review) | National Center for Health Statistics (CDC/HHS) – federal settles copyright but NOT NCHS’s own vital-statistics data-use agreement: the AHRF User Guide confirms “NCHS restrictions prohibit release of any subnational data with fewer than 10 occurrences”. SPEC.md’s Licence gate already treats CDC WONDER as “Needs review before ingest” for exactly this restriction. This is a review hold, not a finding that the data is actually restricted – revisit once that agreement’s redistribution terms (as opposed to its publication-threshold rule) are checked. |
| NCHS Natality File | 42 | EXCLUDED (pending review) | same NCHS vital-statistics data-use agreement as NCHS Mortality File above. |
| NCHS Mort/Nat File | 2 | EXCLUDED (pending review) | same, a combined mortality/natality-file citation. |
| 2019-23 Census ACS | 338 | allowed | U.S. Census Bureau, federal. |
| 2018-22 Census ACS | 338 | allowed | ditto. |
| Census SAHIE | 152 | allowed | ditto (Small Area Health Insurance Estimates). |
| 2010 Census SF1 | 129 | allowed | ditto. |
| 2020 Census DHC | 124 | allowed | ditto. |
| CMS NPI File | 60 | allowed | CMS NPPES provider registry, federal. |
| Census County Char File | 54 | allowed | U.S. Census Bureau, federal. |
| 2020 Census Redistrict | 48 | allowed | ditto (PL 94-171). |
| CMS Marketplace | 44 | allowed | CMS, federal. |
| 2010 Census Redistrict | 42 | allowed | ditto. |
| Medicare Geographic Var | 40 | allowed | CMS, federal. |
| CMS Provider of Services | 24 | allowed | CMS, federal (already used for FQHC/RHC/hospice/ASC counts). |
| HRSA DNHSC | 18 | allowed | HRSA, federal. |
| ERS Dept of Agriculture | 16 | allowed | USDA, federal (same source ers_rucc.py already treats as public domain). |
| Census Map | 14 | allowed | U.S. Census Bureau, federal. |
| Census SAIPE | 14 | allowed | ditto. |
| Dept of Veterans Affairs | 10 | allowed | federal. |
| OMB-23-01 | 8 | allowed | Office of Management and Budget, federal. |
| HRSA Data Warehouse | 8 | allowed | HRSA, federal (already used for HPSA codes). |
| Census County Pop Est | 8 | allowed | U.S. Census Bureau, federal. |
| Bureau of Labor Stats | 8 | allowed | federal. |
| CMS | 7 | allowed | federal. |
| LAPI | 6 | allowed | U.S. Bureau of Economic Analysis, federal (Local Area Personal Income). |
| CMS Monthly Enroll Dashbrd | 6 | allowed | CMS, federal. |
| CMS MA Penetration | 6 | allowed | CMS, federal. |
| EPA | 6 | allowed | federal. |
| EPA Air Qual Index Rep | 6 | allowed | federal. |
| Derived From GSA | 5 | allowed | General Services Administration, federal – includes fips_st_cnty itself, which becomes raw’s fips key column rather than a column_name/value cell. |
| Census Bureau | 4 | allowed | federal. |
| CMS PDP Penetration | 4 | allowed | CMS, federal. |
| CDC EPH Tracking Network | 4 | allowed | federal. |
| Census SNAP File | 2 | allowed | U.S. Census Bureau, federal. |
| Census Housing Unit File | 2 | allowed | ditto. |
| U.S. Post Office | 1 | allowed | USPS state abbreviations – standard postal codes, the same ones census_gazetteer.py/ers_rucc.py already carry unremarked. |
| DHHS | 1 | allowed | federal. |
| (no source listed) | 6 | allowed | AHRF’s own file-structural fields (blank, date_cretn, date_file, entity_file, file_length, st_name) – not content from any dataset. |
2,789 excluded + 1,563 allowed = 4,352, the whole header. raw.hrsa__ahrf lands 1,562 of the 1,563 allowed columns as (column_name, value) cells – the 1,563rd, fips_st_cnty, becomes the fips key column instead (see the Derived From GSA row). Checked against the real 2024-2025 file programmatically (not just this table by hand): zero columns fell into the “unrecognised source” bucket – every one of the 45 distinct sources above is accounted for by ALLOWED_SOURCES or EXCLUDED_SOURCES. Three of those 45 (the NCHS rows above, 132 columns) are a deliberate REVIEW HOLD, not a finding of restriction – see their row for why.
Consequence for this module, and for the issue that asked for it (#39): the issue’s requested curated list – “active MDs, primary-care physicians, oncologists, gastroenterologists, radiologists, dermatologists, hospitals, hospital beds” – is drawn entirely from the AMA/AHA rows above and is NOT landed here, in raw or derived, under any circumstance (AGENTS.md hard gate: “License before ingest… license:unknown is a hard stop”; here the finding is stronger than “unknown” – it is affirmatively not cleared). What ships instead is the curated set built from AHRF’s genuinely allowed columns (see MEASURE_DEFINITIONS below and the PR description).
The allowed sources above are U.S. government work, public domain per 17 U.S.C. Sec 105 (“Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection… Government works are by default in the U.S. Public Domain.”, https://resources.data.gov/open-licenses/) – the same standing quote the other sources in this repo cite – except U.S. Post Office, treated as allowed for the same reason the repo’s other sources already carry USPS state codes unremarked (a standardized, non-creative 2-letter code, not the Postal Service’s copyrightable content). land_raw computes the eligible column set itself, at ingest time, straight from the release’s own technical documentation (see _classify_columns), rather than trusting a hand-copied list to stay in sync with it.
Landing WHOLE, in LONG form
AHRF is very wide even after the licence gate: 1,562 allowed columns x 3,235 counties. Measured locally against the real file: declaring one NestedField (with a doc, per house rule) for each of ~1,700 columns is not a schema anyone could review or maintain, and the vast majority would never be read by anything this repo derives. So raw lands LONG instead – (ahrf_release, file, fips, column_name, value), one row per (county, field) cell, verbatim strings – which is still landing every eligible cell of the release whole (SPEC.md ADR-0002’s “land raw whole”, just at cell rather than wide-row granularity; ADR-0002 doesn’t mandate one physical column per source field, only that nothing is thrown away or pre-filtered by guessing what will matter later). Measured: unpivoting the real release takes ~5s and produces 5,053,070 rows locally (1,562 columns x 3,235 counties).
ponytail: because raw is long and every cell is addressed by name rather than position, this module’s header check does NOT reproduce AHRF’s 4,352-column header verbatim the way the other sources’ COLUMNS contracts do (that would be ~100KB of literal column names for no real safety gain here: a reordered or additional column is harmless to a name-addressed unpivot). Instead land_raw checks that fips_st_cnty exists and that every column this module actually reads (CURATED_FIELDS, below) is present and allowed, and fails loudly naming exactly which ones are not – the part of “drift” that would actually break something downstream.
Version axis and the three time axes (SPEC.md § Versioning)
source_release is the release label itself, "2024-2025" – AHRF’s own citable edition name (method="release_number"), the same for every row regardless of which data year a given column describes. period_start / period_end come from each column’s OWN year suffix instead (e.g. hospcs_24 describes 2024, pers_noins_lt65_pct_21 describes 2021) – exactly the SPEC.md distinction the issue calls out (“One release carries many data years: the period comes from each field’s year suffix, not the release”). Getting this backwards (using 2024-2025 as the period) would silently claim every measure describes the same year, which is false – np_npi_24 and np_npi_23, both in the SAME 2024-2025 release, describe different data years AND (see below) different geography.
Geography vintage: inferred per ROW from the real file, not per column family
AHRF’s HPSA fields state no vintage of their own, so this is inferred directly, as SPEC.md requires when a source doesn’t document one. The real 2024-2025 file carries BOTH Connecticut’s 8 pre-2022 counties (09001-09015) AND its 9 post-2022 planning regions (09110-09190) as separate rows in the SAME file – and likewise both Alaska’s pre-2019 Valdez-Cordova (02261) and its current Chugach/Copper River census areas (02063/02066) – verified by downloading and querying the real CSV directly. Different source families populate different halves of that split: CMS Provider of Services and HRSA Data Warehouse fields (FQHC/RHC/hospice/ambulatory-surgery-center counts, HPSA codes) are non-NULL on the legacy CT counties and NULL on the planning regions; Census SAHIE and CMS NPI File fields are the reverse. More tellingly, the split isn’t even fixed per source: np_npi_24 (2024 data) populates only the 9 planning regions, while np_npi_23 (2023 data, same column FAMILY, same release) populates only the 8 legacy counties – confirming Connecticut’s real 2022 transition actually reached this particular CMS feed between the 2023 and 2024 data years. So geo_vintage here is a property of which FIPS code a ROW carries, not of which measure or year produced it: LEGACY_FIPS (the pre-2022 CT counties plus Valdez-Cordova) get 2010; everything else gets 2020. This one rule correctly reproduces every case above without touching source family or year at all.
Missing data
Every curated column, checked directly against the real file: a source cell is either a plain non-negative integer/decimal string, or blank. Blank is AHRF’s only missing marker for these columns (no ., -999, * sentinel seen) and is mapped to value_status = 'not_available', never a number and never silently dropped – a blank cell for a county still gets a raw row (value = NULL) and a measure.observation row, because AHRF publishes a column for every county row; it just leaves some cells empty rather than omitting the row (unlike RUCC’s Rose Island, which omits the row entirely). A 0 (e.g. Chugach’s fedly_qualfd_hlth_ctr_24 = 0) is a real reported zero, never read as missing. value_status is derived from the same TRY_CAST that produces value (not from the raw cell’s presence), so a hypothetical present-but-non-numeric cell – never seen in the real curated columns, but not something the source format rules out – lands as not_available rather than a numberless 'reported' row (issue #148).
hrsa sites
uv run cancerbasemap hrsa-sites (src/cancerbasemap/hrsa_sites.py)
Tables written: raw.hrsa__health_center_sites, raw.hrsa__hpsa_primary_care, facility.site, measure.definition, measure.stratum, measure.observation
HRSA health-center sites + primary-care HPSA designations -> Iceberg.
Upstream: https://data.hrsa.gov/data/download lists both as CSV downloads. Direct URLs (stable, verified 2026-09-18 via HTTP HEAD – both respond Last-Modified as of the same day, confirming they are refreshed in place daily rather than published as dated editions): - Health Center Service Delivery and Look-Alike Sites (FQHC sites): https://data.hrsa.gov/DataDownload/DD_Files/Health_Center_Service_Delivery_and_LookAlike_Sites.csv - Primary-care HPSA designations (dental/mental-health siblings exist as BCD_HPSA_FCT_DET_DH.csv / _MH.csv but are not landed here – issue #38 asks for primary care “at minimum”): https://data.hrsa.gov/DataDownload/DD_Files/BCD_HPSA_FCT_DET_PC.csv
Licence. HRSA’s own Data Usage Terms & Conditions, shown on the download page for each dataset (https://data.hrsa.gov/data/download, checked 2026-09-18): “Health Center Service Delivery Sites (HCSD)” (Data Published by: HRSA, Bureau of Primary Health Care (BPHC)) and “Health Professional Shortage Areas (HPSA)” (Data Published by: HRSA, Bureau of Health Workforce (BHW), Division of Policy and Shortage Designation (DPSD)) both state “Usage limitations: None”. HRSA is also a federal agency, so both are independently a U.S. Government work: “Data and content created by government employees within the scope of their employment are not subject to domestic copyright protection under 17 U.S.C. Sec 105. Government works are by default in the U.S. Public Domain.” (https://resources.data.gov/ open-licenses/, checked 2026-09-18).
No individual-provider records. Every column of both files was read 2026-09-18: site/grantee/organization names and addresses only (e.g. “Site Name”, “Health Center Name”, “HPSA Name”, “HPSA Component Name”); no column names or holds an individual clinician.
Version axis: retrieval date. Neither file publishes an edition label – each is a live snapshot HRSA overwrites in place – so retrieved_on (this ingest’s UTC date) is the version column raw is scoped and replaced by, and merge.manifest is called with method="retrieval_date" (SPEC.md’s versioning model already reserves this for exactly this case).
Header quirk, both files. The real header line ends in a stray trailing comma, producing a spurious empty final column name, while every data row has one fewer field than the header (verified 2026-09-18 against the live downloads and reproduced byte-for-byte in the fixtures). COLUMNS/ HPSA_COLUMNS hold the real columns only; _check_header tolerates exactly that one trailing empty field (mirrors census_gazetteer.py’s trailing- whitespace quirk) and null_padding=true in the read_csv call pads it with NULL rather than shifting every real column over.
facility_id. HRSA’s “BPHC Assigned Number” (e.g. ‘BPS-H80-000078’) is the per-site id: verified unique across all 19,246 rows of the live file (2026-09-18). “Health Center Location Identification Number” looks similar but is a lookup code (only 3 distinct values file-wide) – not a site id.
Geography. SPEC.md’s facility.site calls for a tract-level geo_id; HRSA’s health-center file publishes only county FIPS, so geo_id here is ‘county:’+FIPS – a documented gap, not a tract lookup this module performs. GEO_VINTAGE = 2020: the live file’s Connecticut rows carry only the nine 2022 planning regions (09110-09190, never the eight legacy counties 09001-09015) and its Alaska rows carry only current census areas (e.g. 02063 Chugach, 02066 Copper River, never the retired 02261 Valdez-Cordova), both markers of 2020-vintage Census geography (2026-09-18).
attributes_json, not a map. SPEC.md’s facility.site declared attributes map<string,string>. A DuckDB MAP value cast through Arrow into that Iceberg schema aborts the process outright – Arrow’s C++ validator fails a check (“Map array keys array should have no nulls”) that is not a catchable Python exception, confirmed with a minimal repro 2026-09-18 – so facility.site.attributes_json is a JSON string column instead (this PR edits SPEC.md’s facility.site declaration to match).
HPSA: geographic designations only. Only ‘Geographic HPSA’ and ‘High Needs Geographic HPSA’ rows carry a usable county FIPS on every row; ‘HPSA Population’, ‘Federally Qualified Health Center’, its Look-Alike sibling, ‘Rural Health Clinic’, ‘Correctional Facility’, ‘Other Facility’ and the IHS/Tribal designation type are population-group or facility designations that don’t fit geo_id (SPEC.md’s own note on issue #38) – they land in raw but are not derived. A HPSA with sub-county components (Census Tract / County Subdivision) repeats one row per component, all sharing the same county FIPS; transform deduplicates on (HPSA ID, FIPS) before counting so a multi-component HPSA counts once per county, not once per row.
ponytail: only two measures are derived from HPSA – a per-county count of currently-designated (‘HPSA Status’ = ‘Designated’) primary-care geographic HPSAs and their max HPSA Score – rather than reconstructing HRSA’s own shortage-index math (SPEC.md non-goal: republish, don’t compute). period is the retrieval date, since HRSA HPSA designations are a live roster with no published period of their own.
ponytail: a currently-Designated geographic HPSA with a masked/placeholder FIPS (‘XXXXX’/‘XXX’, seen only on Withdrawn / Proposed For Withdrawal rows as of 2026-09-18) is silently excluded from the county aggregation rather than raising – that combination doesn’t exist in the real file today, and would be a HRSA data-quality anomaly if it appeared, not something this ingest should hard-stop on.
facility.site.source_release is NULL for HRSA_HC. facility.site’s business key is (facility_id, source) – source_release is deliberately excluded from it (issue #19). An earlier version of this module set source_release to the retrieval date, which – since merge.merge diffs every non-key column – made every unchanged site open a fresh Type-2 version on every single ingest: 19k rows of churn per run for zero real change. HRSA_HC writes NULL there instead: the snapshot date is already recorded in provenance.release and in raw.hrsa__health_center_sites.retrieved_on, so an unchanged site now merges as ‘unchanged’ across retrieval dates the way RUCC/PLACES rows do, and a site’s actual content change still opens a new version. This is an interim fix under issue #19, not a resolution of it – see merge.py’s and schemas.py’s own notes on that.
places
uv run cancerbasemap places (src/cancerbasemap/places.py)
Tables written: measure.definition, measure.stratum, measure.observation
CDC PLACES: Local Data for Better Health, COUNTY data -> Iceberg.
Model-based small-area estimates for chronic disease, screening and behavioral risk factors, at (county, measure, stratification-type) grain (SPEC.md § Sources — first tranche: “model-based tract/county screening & behaviors”).
Licence. Public domain. The dataset’s own Socrata metadata states "license": {"name": "Public Domain"} (checked against https://data.cdc.gov/api/views/swc5-untb.json, 2026-09-18), attributed to “Centers for Disease Control and Prevention, National Center for Chronic Disease Prevention and Health Promotion, Division of Population Health”; see also https://www.cdc.gov/places.
Version axis: the annual release is a separate Socrata dataset. PLACES does not version one URL — each year’s county file is its own dataset id on data.cdc.gov, discovered via the Socrata catalog API (https://api.us.socrata.com/api/catalog/v1?domains=data.cdc.gov&q=PLACES county data release, checked 2026-09-18). RELEASES records every long-format “…County Data source_release downstream is the release year label itself (PLACES publishes no separate release-number scheme).
The header is not stable across editions. COLUMNS states the CURRENT shape — verified identical on the 2024 and 2025 releases, the two this module actually lands (see docstring below) — and lands only those; an older release fails the header check by design rather than silently mis-landing (mirrors hgnc.py’s dated-archive handling): - the 2020 export (dv4u-3x3q) has no LocationID at all — a Latitude column holding real latitude text sits in its place, and its point column is misspelled Geolocatioin — upstream’s own defect, confirmed against https://data.cdc.gov/api/views/dv4u-3x3q.json’s column list, not a scrape artifact. - 2021-2023 lack TotalPop18plus (added starting with the 2024 release). ponytail: only the 2024/2025 header is supported. Landing an older release means declaring its own COLUMNS first, same as hgnc.py’s dated archives.
Geography vintage moves mid-series. Connecticut’s LocationIDs are the legacy 8 counties (09001-09015) through the 2023 release and the 9 planning regions (09110-09190) from the 2024 release on — verified directly against each release’s real file (PLACES’ own methodology notes the 2022 Census change but not which release picks it up). GEO_VINTAGE records 2010 for the legacy-county releases and 2020 for the planning-region ones, the same two vintage labels SPEC.md’s Milestones use for the pre/post-2022 Census county definitions.
Suppression. The only footnote either landed release carries is “Estimates suppressed for population less than 50” (0 rows in the 2024 release, 66 in 2025) -> value_status = 'suppressed_small_count'. FOOTNOTE_STATUS enumerates it explicitly; any other footnote text raises SystemExit in transform rather than guessing. value_status is derived from the same TRY_CAST that produces value (a genuinely empty cell looks up its footnote; a present-but-non-numeric one – never seen in the real files, verified 2026-09-19 – falls back to not_available rather than guessing “suppressed”), not from Data_Value‘s raw presence: the two used to disagree, landing a present-but-malformed cell as a numberless ’reported’ row (#148; merge.check_observations’s reverse guard now catches any regression).
No cervix/lung screening measure in county data. The county file’s 40 measures include COLON_SCREEN (colorectal) and MAMMOUSE (mammography) but no cervical-cancer-screening measure — BRFSS dropped that module from the data years this dataset draws on. All 40 measures land and derive regardless of cancer relevance, per instructions.
ponytail: TotalPopulation/TotalPop18plus are NOT this measure’s denominator — PLACES publishes model-based prevalence with no published numerator/denominator pair, only the population the estimate covers. They land in raw and stay there; measure.observation.denominator is NULL.
ponytail: measure.definition has no valid_from/valid_to — it is a lookup table, not Type-2 versioned (SPEC.md). Interim fix for #76: this module overwrites only the measure_ids it asserts this release (And(EqualTo("source", "PLACES"), In("measure_id", ids))), not the whole source = 'PLACES' scope, so a later release that drops a measure (ISOLATION -> LONELINESS in 2025 — same slot, different MeasureId, real data, checked directly) no longer deletes the earlier definition its still- live 2024 measure.observation rows reference. It does NOT give measure.definition history: if a measure_id that persists across releases changes its label/universe text, the newer release’s text still silently overwrites the older one in place. The real fix (Type-2 measure.definition, or another option) is part of #19’s versioning decision.
Tract data (#30), the same house rules extended, not copied. TRACT_RELEASES records every “…Census Tract Data RELEASES (Socrata catalog API, q=PLACES Census Tract Data release, plus a follow-up query for 2024 specifically since one catalog page missed it; checked 2026-09-18): 2020 4ai3-zynv, 2021 373s-ayzu, 2022 nw2y-v4gm, 2023 em5e-5hvn, 2024 ai6z-tcin, 2025 cwsq-ngmh. Same licence (each dataset’s own Socrata metadata: "license": {"name": "Public Domain"}, checked directly on cwsq-ngmh and ai6z-tcin, 2026-09-18).
The tract header is its own contract, verified against the real files, not guessed from the county shape. TRACT_COLUMNS is the 2024/2025 tract header — only these two are landed, same policy as county and for the same reason (2020-2023 lack TotalPop18plus, confirmed against each real file’s first line). It is NOT COLUMNS plus a TractFIPS column: the real tract file has no such column. CountyFIPS/CountyName are new (the county file has neither, since it is the county); LocationID holds the 11-digit tract FIPS, and — an upstream quirk, verified against real rows — LocationName holds that same 11-digit tract FIPS again, not a human name, unlike the county file where LocationName is the county name.
Tract publishes crude prevalence only. Verified via the Socrata SODA API against every landed release ($select=distinct(data_value_type) on cwsq-ngmh returns only “Crude prevalence”; count(distinct(measureid)) is 40, matching county’s measure count). transform therefore reuses the same PLACES:<MeasureId>:crude ids county’s crude rows already assert — tract mints no measure_ids of its own. To make that safe when both levels feed one defs query, transform raises SystemExit if county and tract ever disagree on a measure’s label/category/unit text for the same id, rather than silently picking one (they agree on every id checked: e.g. CSMOKING’s Measure text is verbatim identical in both files).
No suppression in the tract editions actually landed. Checked directly via the SODA API (count(*) where data_value_footnote is not null and where data_value is null, both 0) against cwsq-ngmh (2025) and ai6z-tcin (2024) — every one of ~3M rows in each is reported. (2020-2022 do have exactly one unfootnoted-but-null row each — a genuine upstream anomaly — but those releases aren’t landed here anyway.) FOOTNOTE_STATUS is still enforced on tract rows via the same shared raise SystemExit path as county, in case a future release introduces one.
Geography vintage moves at the same release as county, verified independently for tract. Tract count jumps from 72,337 (2023, em5e-5hvn) to 83,522 (2024, ai6z-tcin) — consistent with the 2010-vintage (~73k) to 2020-vintage (~85k) tract count change, not a partial update. Connecticut’s CountyFIPS values confirm it directly: 09001-09015 (legacy counties) in the 2023 file, 09110-09190 (planning regions) in the 2024 file — the identical switch point GEO_VINTAGE already records for county, so tract reuses that same dict rather than a second one.
Tract files are large: ~2.9M-3.2M rows per release (2,555,113 in 2023; 3,183,048 in 2024; 3,047,284 in 2025 — SODA count(*)), 40 measures x ~72-84k tracts x crude-only, versus county’s few hundred thousand. land_raw streams a remote tract download to a temp file in fixed-size chunks (_download) before DuckDB parses it from disk, rather than handing DuckDB the URL directly the way the much smaller county file does: the two don’t need to hold the whole response in memory as bytes and as an Arrow table at once. Measured against the real 2025 files end to end (land_raw + transform, county then tract, county’s 229,298 raw rows + tract’s 3,047,284): peak RSS 15.4 GB, wall clock 15.8s (/usr/bin/time -v on the ingest host, 2026-09-18) — tract’s own land_raw+transform alone peaks at 10.1 GB in 3s. Comfortably one process’s job at that size; not the 71.5M-row scale (25x this) where bioc-on-ice’s ncbi.py::_land batches reads and commits — that’s the upgrade path if a future tract file’s row count grows into that range, not needed here.
ponytail: tract shares land_raw’s county code path via a level parameter (mirrors cdc_svi.py’s single land_raw(..., level=...)) rather than a second function, since the only real differences are the dataset-id table, the column contract, and (tract only) the temp-file download. transform similarly derives from a raw view that unions whichever of raw.places__county / raw.places__tract are landed for a release (cdc_svi.py’s same fix for the same problem: measure.observation’s merge scope is (source, source_release), not per-level, so deriving from one level alone would retire the other level’s rows landed earlier under the same release).
scp
uv run cancerbasemap scp (src/cancerbasemap/scp.py)
Tables written: measure.definition, measure.stratum, measure.observation
State Cancer Profiles (SCP) -> Iceberg, all vintages (#27, the flagship).
Upstream has no API, bulk download or archive. statecancerprofiles.cancer.gov serves only its current numbers; seandavi/state-cancer-profile-scraper scrapes it and republishes each captured edition as a versioned Zenodo deposit – a vintage (~/Documents/git/state-cancer-profile-scraper/CLAUDE.md/docs/releases.md: “A vintage is one edition of the upstream estimates … Several dated scrapes that captured identical values belong to one vintage.”). This module lands from those public Zenodo deposits directly (anonymous HTTPS, no credentials, no tailnet) – never from cdsci-lake, even though cdsci-lake already curates SCP under its own ADR-0012 (~/Documents/git/cdsci-lake/src/cdsci/lake/sources/scp/, confirmed to exist by filename only, not read, per this issue’s explicit instruction to source the public deposits for this PR).
Licence. SCP’s own site states only a re-identification restriction (42 U.S.C. Sec 242m(d), statecancerprofiles.cancer.gov/dataUseRestrictions.html, checked 2026-09-18) – irrelevant here since it publishes aggregates only. SCP is produced by NCI/CDC as part of employees’ official duties: a U.S. Government work, public domain under 17 U.S.C. Sec 105 (resources.data.gov/open-licenses/, the same basis already used for RUCC/RUCA/SEER in this repo), plus NCI’s own reuse policy: “all text within National Cancer Institute (NCI) products is free of copyright and may be reused without our permission” (cancer.gov/policies/copyright-reuse). This is a republished scrape, not the original archive (#65): the scraper’s own MIT licence covers its code; its Zenodo deposits (the bytes this module reads) are CC-BY-4.0; the underlying content they carry is the public-domain SCP data above. That sentence is repeated verbatim in each raw table’s own comment (#65’s ask), since this module cannot edit the shared provenance.release declaration.
Vintages found (data/vintages.json in the scraper repo, checked 2026-09-18): V1 (2024-08-02-1 capture), V2 (2025-02-10 .. 2026-02-01, 13 captures), V3 (2026-04-01 .. 2026-08-24, 6 captures). Each Zenodo record’s own metadata was fetched live (https://zenodo.org/api/records/<id>) to get its DOI and file list – not read from a doc, which docs/schema-drift.md in that repo turned out to be one day stale about V3’s own column set (see below).
Topics differ by vintage. V1/V2 ship incidence+mortality only, as .csv.gz; V3 adds demographics+risk and also ships Parquet. This module reads the .csv.gz form for every vintage/topic it lands (all_varchar=true, same as every other CSV source here) rather than V3’s Parquet, deliberately: the real V3 Parquet’s own column types are inconsistent within one topic (verified directly – age_adjusted_rate_per_100_000 is a native DOUBLE, but lower_ci_rate in the same file is VARCHAR because it must hold the literal sentinel "*" while the main rate column represents the same suppression as a plain NULL) – reading the CSV keeps one column type (VARCHAR) throughout, verbatim, with no per-column type inconsistency to reconcile.
Only incidence, mortality and risk are landed here; demographics is not. ponytail: demographics is ACS/Census population-composition data (poverty, education, crowding, income), not a cancer measure, its real column set is very wide (dozens of topic-specific value columns keyed by a long/sparse topic/demo pair) and unexplored in the depth the other topics got, and this repo already has a dedicated, better-scoped home for ACS data (#29). Landing it here would duplicate that source under a worse contract. Revisit as its own issue if a downstream consumer specifically wants SCP’s own ACS extract rather than a directly-landed ACS release.
Three real column layouts per topic, one raw table per topic (house rule: “one COLUMNS layout per real upstream layout, detected by header match”). Verified against real bytes for every vintage/topic landed (not from docs/schema-drift.md, whose “V3 = 29 cols, unchanged from V2” claim is stale – written the day before the 2026-08-24 scrape that this V3 deposit actually holds, which added suppression_reason and, for mortality, dropped stage entirely). raw.scp__incidence/raw.scp__mortality are the union of every vintage’s real columns; a column absent from a given vintage’s real layout lands NULL, same technique as cdc_svi.py. raw.scp__risk has one layout (V3 only).
Suppression – differs completely by vintage, this is the hard part (#27). V1 and V2 carry no suppression marker of any kind – verified: every row’s age_adjusted_rate_per_100_000 is non-NULL, and no CI/trend cell is the sentinel "*", in the complete real V1 and V2 files (both topics). Per the scraper’s own docs/schema-drift.md: “Suppressed cells are absent rows, not marked cells … There is no suppression-reason column and no reason information survives anywhere” for those two vintages – every landed V1/V2 row is honestly value_status='reported'; there is nothing to enumerate for them, not an oversight. V3 adds a real suppression_reason column with exactly two non-NULL values (verified by a full GROUP BY over the real V3 incidence, mortality and risk files – zero unmapped, zero counter-examples of a non-NULL value alongside either): - suppressed_small_count -> value_status='suppressed_small_count'. Per notes_incidence.txt (fetched from the V3 deposit, 2026-09-18): “Data has been suppressed to ensure confidentiality and stability of rate estimates. Counts are suppressed if fewer than 16 records were reported in a specific area-sex-race category.” - withheld_state_law (incidence only) -> value_status='not_available'. Same file’s [P1 note]: “Data not available because of state legislation and regulations which prohibit the release of county level data to outside entities.” – the state declined to submit, which is exactly SPEC.md’s own example for not_available. No "3 or fewer", "¶" or "§§" sentinel forms exist anywhere in the real V1/V2/V3 bytes checked – the brief’s list of examples doesn’t match this source; only the two strings above are coded, and an unmapped third would raise SystemExit rather than guess. value_status is derived from the same TRY_CAST that produces value, not from suppression_reason’s presence alone (#148): a present rate cell with no known suppression_reason that still fails to parse – never observed in the real files, but not ruled out by the schema – lands not_available rather than a numberless reported row.
A real duplicate-row artifact, found only at full scale (Acceptance A). SCP’s “By County” and “By State” comparison-report modes BOTH capture the national aggregate row and DC’s/Puerto Rico’s rows (each is simultaneously a state and a county-equivalent), so the real V3 file lands two raw rows for the same (fips, sex, age, race, stage, cancer) – verified exhaustively: every such duplicate key has exactly one row of each areatype, never more. Values are identical in the overwhelming majority (all 3,978 mortality duplicate groups; 7,293 of 7,683 incidence groups) but differ in a small remainder (390 incidence groups) – SCP’s live data evidently drifted between the two capture times, which are hours apart within one scrape run. Both raw rows are landed verbatim regardless (raw.scp__* keeps both, unmodified); _topic_frames deterministically prefers the ‘By County’ capture before deriving, so measure.observation’s business key stays unique. This means the row COUNT that reproduces the vintage exactly is raw’s count minus these 11,661 real duplicate rows (7,683 incidence + 3,978 mortality), not raw’s own count – reported precisely in the PR’s real-ingest numbers, not hidden in an average.
No not_applicable case, contrary to the brief’s assumption. cancer='Cervix' AND sex='Male' and cancer='Prostate' AND sex='Female' are NOT suppressed or flagged specially – verified directly in the real V3 file: both combinations have thousands of ordinary reported and suppressed_small_count rows, like any other combination. SCP evidently still serves (or suppresses on count, same as any county) these cells rather than marking them not-applicable. Reported honestly rather than inventing a mapping with no evidence behind it; not_applicable is simply never produced by this module.
recent_trend carries the source’s trend call (stable/rising/falling) verbatim, or two non-trend markers landed as trend=NULL: "*" (trend itself could not be computed/is suppressed – co-occurs with a suppressed rate in most but not all rows: 198,574 V3 incidence rows have recent_trend='*' while the rate is present and reported, i.e. trend suppression is independent of value suppression) and "[P1 note]" (the same state-law withholding as above, on the trend field). No reliability_flag exists anywhere in the real files – SCP publishes no separate reliability signal on the rate itself; always NULL here.
period_start/period_end – a genuine gap, not guessed around. No row in any vintage carries a real year: the year column is the literal constant "Latest 5-year average" in every row of every vintage (verified). The actual five-year window is stated only in notes_incidence.txt/notes_mortality.txt, which exist only in the V3 deposit (added by the same 2026-08-24 scrape that added suppression_reason); V1 and V2 ship no notes file and no period text anywhere in their bytes. Quoting the real V3 notes (fetched 2026-09-18): incidence "All Cancer Sites (All Stages^), 2018-2022", mortality "All Cancer Sites, 2019-2023" – one constant window per topic, since SCP’s own report generation uses one current submission’s window for every cancer site queried in the same run (confirmed: the scraper always passes year=0, “latest”, to every query). This module therefore only derives measure.observation for V3. V1 and V2 land verbatim in raw (preserving them is the entire point of a vintage lake) but are never turned into observation rows here – inventing a V1/V2 period with no evidence would violate AGENTS.md’s “report honestly” gate. Revisit if a real V1/V2 period surfaces (e.g. from the Internet Archive’s own capture metadata, per the scraper’s docs/wayback-assessment.md, which found none either).
Geography. areatype and locale_type are both unreliable for level (verified: areatype='By County' is constant even on the national aggregate row in V1; locale_type misclassifies many real counties as 'other' – Louisiana parishes, Alaska boroughs, DC, Puerto Rico – per the scraper’s own docs/coverage-drift.md). Level is derived from the fips code’s own shape instead, which is unambiguous in the real data: '00000' -> nation; a 5-digit code ending '000' -> state (its first two digits); anything else -> county. No real county FIPS ends in '000' by construction, so this never misclassifies a county as a state.
geo_vintage=2010, uniformly, for every vintage (#22). Verified directly in both V1 (2024-08-02) and V3 (2026-08-24) real files: Connecticut carries only the 8 legacy counties (09001-09015) – confirmed explicitly by notes_incidence.txt, “This website still uses Connecticut counties instead of planning regions … If/ when all data sources have new planning regions, then this website will switch to using them” – never the 9 planning regions (09110-09190); Alaska carries the pre-2019 02261 Valdez-Cordova, not the post-split 02063/02066; South Dakota already carries the 2015 rename, 46102 Oglala Lakota, not 46113 Shannon County, from the earliest vintage on. All three checks land in the same place (between the 2015 SD rename and the 2019 AK / 2022 CT changes) in every vintage checked – no drift observed between V1 and V3, so one constant applies to all three.
Cancer sites use SCP’s own numeric codes (select_options.json in each Zenodo deposit, verified byte-identical between V1 and V3) for measure_id, and are bridged to measure.cancer_site (#31) by hand-translating SCP’s combined-category label to the matching SEER site-recode label already curated there (e.g. SCP’s “Colon & Rectum” -> SEER’s “Colon and Rectum”) and taking the lowest-numbered SEER leaf code sharing that label as the representative FK – several SEER leaf codes share one SCP category, and any one of them names the same site. "All Cancer Sites", "Breast (Female in situ)", the two "Childhood ..." age-based aggregates, and "Oral Cavity & Pharynx" (verified: SEER’s own “Oral Cavity and Pharynx” text is a group heading with no numeric recode of its own, same as cancer_site.py already found for other headings) resolve to no single leaf code and stay NULL, per the issue’s own “leave NULL otherwise”. The lookup reads cancer_site.py’s curated CSV directly (not a live measure.cancer_site scan – its own label column holds each numeric code’s individual SEER site_group text, e.g. “Ascending Colon”, not the rolled-up category name the CSV groups by), so it resolves whether or not #31 has actually been ingested into this warehouse.
Strata. SCP crosses sex x age x race/ethnicity x stage (stage only where the topic/vintage publishes one); select_options.json gives one closed vocabulary, identical between V1 and V3, so one scheme='SCP_RACE_2024' covers every vintage landed here (the “2024” reflects the vintages’ own “based on the 2024 submission” footnote, the newest hard evidence found, not a claim the categories themselves will keep this name forever). An unmapped sex/age/race/stage value raises SystemExit rather than silently landing as a NULL-keyed stratum.
risk (screening & risk factors, V3 only) is landed to raw but not derived here. ponytail: its real geography split is messier than incidence/mortality – locale_type='county' rows are DC/Puerto Rico only (BRFSS is state-level, not county-level), and 41% of its rows carry locale_type='other' with unclear real geography; its own race vocabulary also differs from (and contains a verbatim upstream typo in) incidence/mortality’s (“Asian /Pacifice Islander”). Deriving it correctly needs its own investigation; land-only here so the vintage is at least preserved. Follow-up: derive raw.scp__risk into measure.observation.
ponytail: ci_rank/lower_ci_rank/upper_ci_rank and percent_of_cases_with_late_stage land in raw only – distinct published statistics from the main rate, out of this issue’s scope to derive.