VladaOmega
Sign inStart free
Curated datasets

Billing Code Vocabulary (HCPCS/CPT/etc.)

vlada_curated.dim_code·live·dim·static·Billing Code Intelligence
Rows
20,196
Columns
9
History
2022 (source_year; effective_start 2022-10-01) — but only as descriptor vintage, not a true history to 2026 (source_year; effective_start 2026-01-01; single loaded_at snapshot 2026-04-17)
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A reference dimension (lookup) table of US healthcare procedure/service billing codes (HCPCS, which includes CPT) with their CMS short text descriptions.
  2. 2It is the code-vocabulary spine used to attach human-readable labels and effective dates to coded line items (e.g. to enrich fact_rate / chargemaster / fee-schedule rows that are keyed by a bare procedure code).

Grain

KeyGrain
code
  1. 1One row per distinct procedure/service code.
  2. 2Verified: 19,766 rows = 19,766 distinct `code` values (also distinct case-insensitively), so `code` alone is the full, minimal primary key.
  3. 3`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 read it

  1. 1Use it as a code->label lookup to enrich coded cost/rate facts.
  2. 2Example: 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

  1. 1Column 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.
  2. 2`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.
  3. 3Dirty 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.
  4. 4Not 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.
  5. 5code_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.

Columns (9)

ColumnTypeMeaning
codestringThe 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_systemstringConstant '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.
descriptionstringThe 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_descriptionstring100% NULL across the entire table. Despite the name, the actual short descriptor lives in `description`. Treat this column as unused/reserved.
source_yeardoubleVintage 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_startdateDate 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_endstringDeclared 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.
sourcestringProvenance / 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_attimestampETL 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

describe_data_source(source_fqn="vlada_curated.dim_code")     # the card, live
query_substrate(sql="SELECT * FROM vlada_curated.dim_code WHERE code = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keycode
Joinhow_to_join(source_fqn="vlada_curated.dim_code")
Raw doorAthena (read-only via query_substrate): vlada_curated.dim_code
NoteRebuilt + proven 2026-07-10: 20,196 rows = exact canonical union (anti-join 0 both ways), 0 malformed, semantic idempotency checksum A9DC27EFB14A92C9. source_year is descriptor provenance (2005-2026), not a publication series.

This page as markdown: /docs-md/data/vlada_curated_dim_code.md · every page in one file: /llms-full.txt