# ICD-10-CM Diagnosis Code Dimension

`drug_silver.dim_icd10cm` · Curated datasets · status live · 1,490,532 rows

The full ICD-10-CM diagnosis code set (code + description) by federal fiscal-year vintage — the diagnosis vocabulary that grounds claim-line diagnosis fields, the ICD-10→HCC crosswalk, and clinical interpretation. Distinct from the medicare_dim_icd10_hcc_crosswalk (which only maps the HCC-relevant subset); this is the complete code dimension.

**Grain.** One row per ICD-10-CM code per ingest snapshot. The full table has 196,372 rows = the same 98,186 codes ingested TWICE (an 05-16 parse and an 05-18 parse of the identical FY2026 file). On the current snapshot (is_current = true) it is exactly one row per code: 98,186 rows = 98,186 distinct codes. To get a clean code-level dimension, always filter is_current = true (or pick one vintage_tag); code alone is NOT unique across the full table.

**How to use it.** Look up the human-readable label and billability of a diagnosis code, filtering to the current snapshot: SELECT code_dotted, long_description, is_header FROM drug_silver.dim_icd10cm WHERE is_current = true AND code = 'E1165'; — returns the dotted form 'E11.65', the full description (Type 2 diabetes mellitus with hyperglycemia), and is_header=false (billable). More generally, join a fact table's ICD-10 column to this dimension on code (or code_dotted) WHERE is_current = true to enrich diagnosis codes with descriptions, and filter is_header = false when you need only billable/codable diagnoses.

**Traps.**
- code is NOT unique on the full table — each of the 98,186 codes appears twice (one per ingest snapshot), so the full table is 196,372 rows. You MUST filter is_current = true (or one vintage_tag) before treating `code` as a primary key, or every code-level join/count will double.
- The two 'history' snapshots are the SAME data, not two code years: sha256_content_hash and the code set are identical; drug_source_version_id is '2026' and cms_vintage_sequence is 1 for both. The 05-16 vs 05-18 difference is just a re-parse 2 days apart (only parser_artifact_hash and the null-vs-populated bronze fields differ). Do not interpret it as a year-over-year code change.
- is_header is a billable flag, and its sense is inverted/non-obvious: is_header=True means NON-billable (a header/category needing a more specific child), is_header=False means billable. You cannot infer billability from code length — many 4/5/6-char codes are billable leaves while some are headers (only 7-char codes are uniformly billable). Filter is_header=False for codes you can actually report.
- The date columns are ingest/provenance dates, not clinical effective dates. effective_period_start = the snapshot load date (2026-05-16/18), NOT the CMS FY2026 effective date (2025-10-01); effective_period_end is a constant '2099-12-31' sentinel meaning 'open', not a real retirement date; observed_period = asserted_at = effective_period_start in every row.
- bronze_object_key and s3_version_id are NULL on the non-current (2026-05-16) snapshot. A null there does not mean missing provenance for a code — it just marks the superseded ingest. The current snapshot has them populated.
- order_number is a dense 1..98,186 CMS tabular-order sequence (clinical chapter order), unique per snapshot. It is stable across these two identical snapshots but is release-specific — do NOT use it as a durable code key across future fiscal years, since CMS re-sequences when codes are added or removed.
- This table is a label/reference dimension only — it contains NO prices, NPIs, or rates. It exists to attach diagnosis descriptions (prefer long_description) to codes; join via `code` (undotted) or `code_dotted` depending on how the fact table stores ICD-10 codes.

| column | type | meaning |
|---|---|---|
| code | string | ICD-10-CM diagnosis code WITHOUT the decimal point (e.g. 'A000', 'C441021'). Lengths 3-7 chars. Unique within a snapshot (98,186 distinct on is_current). This is the natural join key. NOT unique across the full table (each code appears once per snapshot). |
| code_dotted | string | Same code in standard dotted display form (e.g. 'A00.0', 'C44.1021'). The dot is inserted after the 3rd character; for 3-char codes it equals `code` exactly (no dot). 1:1 with `code` (also 98,186 distinct). Use this for display; use `code` for matching against systems that store undotted codes. |
| is_header | boolean | Billable flag, inverted: True = a non-billable header/category (a more specific child code exists and must be used), False = a billable terminal/leaf code you can actually report. On the current snapshot: 23,467 headers (True) vs 74,719 billable (False). ALL 7-char codes are billable (is_header=False); shorter codes are mixed. Example: 'A00' Cholera is a header (True) while its children 'A00.0/.1/.9' are billable (False). Do NOT infer billability from code length alone — use this flag. |
| order_number | int | The code's 1-based position in CMS's official tabular-order listing (1 to 98,186, a dense gapless sequence). Reflects clinical chapter/category ordering, not numeric sort of the code. Unique within a snapshot and byte-identical across the two snapshots here, but it is presentation order tied to a fiscal-year release — do not treat it as a stable cross-year identifier; CMS re-sequences when codes are added/removed. |
| short_description | string | CMS abbreviated description, max ~60 chars, using clinical shorthand ('d/t' = due to, 'NEC' = not elsewhere classified, 'oth' = other, 'unspec'). Never null. Differs from long_description for 55,443 of 98,186 codes; identical for the other 42,743. |
| long_description | string | CMS full-text description, up to 228 chars, fully spelled out. Never null. This is the human-readable label to prefer in reports. |
| observed_period | date | Date the source file was observed/ingested for this snapshot (2026-05-16 or 2026-05-18). Within each snapshot it is identical to asserted_at and effective_period_start — a bitemporal triple that carries no extra signal in this single-vintage data. |
| asserted_at | date | Bitemporal 'as-asserted' date for the snapshot; equals observed_period and effective_period_start in every row (2026-05-16 or 2026-05-18). Provenance scaffolding for future multi-vintage history. |
| effective_period_start | date | Start of the validity window for the row's assertion; equals the snapshot ingest date (2026-05-16 / 2026-05-18). NOTE: this is the ingest/assertion date, not the CMS fiscal-year effective date (FY2026 codes are effective 2025-10-01) — do not read clinical effective dating from it. It is the profiler's chosen 'time column'. |
| effective_period_end | date | End of the validity window. Always the sentinel '2099-12-31' for every row (100%) — meaning open/not-yet-superseded. No code is retired in this data; this is a placeholder, not a real expiry. |
| cms_vintage_sequence | int | Ordinal of the CMS vintage. Constant = 1 across the whole table (only the first/FY2026 vintage has been loaded). Would increment as additional fiscal-year code sets are added. |
| parse_revision | int | Parser revision counter. Constant = 1 for all rows. Reserved for re-parses of the same source under a changed parser. |
| parser_artifact_hash | string | SHA-256 of the parser artifact/run that produced the row. Has TWO distinct values — one per snapshot (the 05-16 parse vs the 05-18 parse) — so it distinguishes the two ingests even though the underlying CMS content is identical. |
| source_url | string | CMS download URL of the source ZIP. Constant: 'https://www.cms.gov/files/zip/2026-code-descriptions-tabular-order.zip' for all rows. |
| sha256_content_hash | string | SHA-256 of the source file content. IDENTICAL ('55a9124a...') for BOTH snapshots — proving the two ingests are the same bytes re-parsed, not new data. The 'history' is a re-ingest, not a code-set change. |
| source_id | string | Logical source identifier. Constant = 'icd10cm' for all rows. |
| is_current | boolean | Snapshot-dedup flag: True for the latest ingest (2026-05-18, 98,186 rows), False for the superseded one (2026-05-16, 98,186 rows). ALWAYS filter is_current = true to get a one-row-per-code dimension; omitting it doubles every code. |
| bronze_object_key | string | S3 key of the bronze object this row was built from. Populated ONLY on the current snapshot; NULL on the superseded 2026-05-16 snapshot. A null here therefore means 'this is the older/non-current ingest', not 'no source'. |
| s3_version_id | string | S3 object version id of the bronze file. Same pattern as bronze_object_key: present on the current snapshot, NULL on the superseded 05-16 snapshot. |
| vintage_tag | string | Human-readable snapshot label combining source, fiscal year and ingest date: 'icd10cm_fy2026_2026-05-16' (non-current) and 'icd10cm_fy2026_2026-05-18' (current). This is the cleanest single-column snapshot discriminator; (code, vintage_tag) is unique across the full table. |
| drug_source_version_id | string | Version id of the source within the drug substrate. Constant = '2026' (the only code year loaded). Would carry the fiscal-year/version when more vintages are added. |

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