# Billing Code Vocabulary (HCPCS/CPT/etc.)

`vlada_curated.dim_code` · Curated datasets · status live · 20,196 rows

The canonical billing-code dimension: code + code_system + descriptions + effective window + source. The vocabulary tools join to for a human label on any HCPCS code. Derived CURRENT vocabulary: exact union of valid HCPCS codes across the five canonical Medicare/ASP Iceberg tables (PFS/OPPS/ASC/CLFS/ASP is_current) at pinned snapshots, fail-closed validated.

**Grain.** One row per distinct procedure/service code. Verified: 19,766 rows = 19,766 distinct `code` values (also distinct case-insensitively), so `code` alone is the full, minimal primary key. `code_system` adds nothing to the key (it is the constant 'HCPCS' on every row), and the profiler's reported (code, code_system) composite is a superset — code by itself is sufficient.

**How to use it.** Use it as a code->label lookup to enrich coded cost/rate facts. Example: SELECT f.code, c.description FROM vlada_curated.fact_rate f LEFT JOIN vlada_curated.dim_code c ON f.code = c.code WHERE regexp_like(c.code,'^[0-9A-Z]{5}$') — answers 'what procedure does this billed code mean?' (returns the CMS short descriptor; expect NULL descriptions for the ~14% of codes flagged source='medicare_other' that aren't in the Medicare fee schedule).

**Traps.**
- Column names are inverted/misleading: the human-readable label is in `description` (and it is only the CMS ~28-char SHORT descriptor, max len exactly 28 — not a full long description), while `short_description` is 100% NULL. Never select short_description expecting text.
- `source` partitions the table into two NON-overlapping halves. Only medicare_pfs (17,040) rows have description/source_year/effective_start; all 2,726 medicare_other rows have those three columns NULL. So a NULL description here means 'code not in the Medicare fee schedule', not 'missing data'. If you LEFT JOIN this table to label codes, ~14% of codes resolve to no description.
- Dirty rows leaked into the primary key: 7 medicare_other rows are footnote text fragments (code='NOTE 1: Pa', etc.) and 1 medicare_pfs row is an Excel control-char artifact (code='_x001A_'). They are length>5 (or '_x...') — exclude with regexp_like(code,'^[0-9A-Z]{5}$') to get the ~19,758 legitimate codes.
- Not a time-series / SCD table despite having source_year, effective_start, effective_end and loaded_at. loaded_at is one constant value (single 2026-04-17 snapshot), effective_end is 100% NULL, there is no is_current/version column, and source_year skips 2025 with 2026 holding 84% of rows. Treat it as a static current-as-of-2026 code dictionary, not a history of code validity. Do NOT filter on is_current — no such column exists.
- code_system is the constant 'HCPCS' everywhere and is therefore useless for distinguishing CPT (Category I numeric), HCPCS Level II (alpha-prefixed), or Category II/III / PLA codes. Derive the code family from the shape of `code` itself, not from code_system.

| column | type | meaning |
|---|---|---|
| code | string | The procedure/service billing code and the sole primary key. 5 chars for nearly all real codes. Three families observed: pure-numeric 5-digit CPT Category I (9,810 e.g. '99213','70551'); alpha+4-digit HCPCS Level II (8,209 e.g. 'J9299','G0008','S-codes'); 4-digit+trailing-letter CPT Category II/III & PLA/MAAA (1,739, e.g. '0001A','0001U' PLA lab,'0001M' MAAA). WARNING: 8 rows are dirty non-codes — 7 footnote text fragments like 'NOTE 1: Pa' and one Excel artifact '_x001A_' — filter with regexp_like(code,'^[0-9A-Z]{5}$') if you need clean codes. |
| code_system | string | Constant 'HCPCS' on all 19,766 rows (HCPCS is the CMS umbrella that contains CPT). Carries zero discriminating information today; do NOT rely on it to distinguish CPT vs HCPCS-Level-II — derive that from the code's own shape instead. |
| description | string | The code's text label. MISLEADINGLY NAMED: it holds the CMS ~28-character SHORT descriptor, not a long description (max length observed is exactly 28; '99213'->'Office o/p est low 20 min', '70551'->'Mri brain stem w/o dye'). Populated only for medicare_pfs rows (17,037 of 17,040; 3 PFS rows null). NULL for ALL 2,726 medicare_other rows. |
| short_description | string | 100% NULL across the entire table. Despite the name, the actual short descriptor lives in `description`. Treat this column as unused/reserved. |
| source_year | double | Vintage year of the source file the code/description came from, as a double. Only populated for medicare_pfs (NULL for all medicare_other). Distinct values: 2026 (16,648 rows = the bulk current annual load), and sparse residuals 2024 (133), 2023 (140), 2022 (119). NO 2025. This is NOT a clean year-by-year history — older years are just codes whose latest sourced descriptor predates the 2026 file; do not treat as an as-of-year snapshot dimension. |
| effective_start | date | Date the code/description became effective, perfectly correlated 1:1 with source_year (2026->2026-01-01, 2024->2024-01-01, 2023->2023-10-01, 2022->2022-10-01). Populated only for medicare_pfs; NULL for all medicare_other. Useful as a coarse 'when this code entered the fee schedule' signal, not an exact code-introduction date. |
| effective_end | string | Declared as date in the profiler but actually varchar, and 100% NULL on every row. No code-retirement / end-dating is captured. Do not use for point-in-time validity logic. |
| source | string | Provenance / coverage flag with exactly 2 disjoint values. 'medicare_pfs' (17,040) = code is priced in the Medicare Physician Fee Schedule and carries description+source_year+effective_start. 'medicare_other' (2,726) = code exists but is NOT in the PFS, so ALL of description/source_year/effective_start are NULL; dominated by PLA lab codes (0xxxU, 499) and HCPCS Level II (2,205). The two sources share ZERO codes (intersection = 0). |
| loaded_at | timestamp | ETL load timestamp, identical single value '2026-04-17 18:47:42 UTC' on every row. Confirms this is a one-shot static snapshot with no incremental/versioned loading. Not a per-row event time. |

Query it: `query_substrate` with `SELECT ... FROM vlada_curated.dim_code WHERE ... LIMIT 100`; read `describe_data_source("vlada_curated.dim_code")` first.