# ICD-10-PCS Inpatient Procedure Code Dimension

`drug_silver.dim_icd10pcs` · Curated datasets · status live · 1,221,444 rows

The full ICD-10-PCS inpatient procedure code set (code + description) by federal fiscal-year vintage — the inpatient procedure vocabulary that feeds MS-DRG grouping logic and inpatient claim interpretation.

**Grain.** One row per ICD-10-PCS code per ingestion run. On is_current=true there are exactly 79,115 rows = 79,115 distinct codes (one row per code). The full table is 158,230 rows = that same 79,115-code FY2026 file loaded TWICE (an is_current=false copy dated 2026-05-16 and an is_current=true copy dated 2026-05-18). So the real, usable grain is one row per code after filtering is_current=true.

**How to use it.** Use it to decode or validate ICD-10-PCS inpatient procedure codes. Example: SELECT code, description FROM drug_silver.dim_icd10pcs WHERE is_current = true AND code = '0016070' returns 'Bypass Cerebral Ventricle to Nasopharynx with Autologous Tissue Substitute, Open Approach'. ALWAYS include WHERE is_current = true (otherwise every code returns 2 rows). To filter to a PCS section, use substr(code,1,1) (e.g. ='0' for Medical & Surgical, ='B' for Imaging, ='X' for New Technology).

**Traps.**
- DOUBLE-COUNT TRAP: the table is 158,230 rows but only 79,115 distinct codes. Every code appears exactly twice — the SAME FY2026 file (identical sha256_content_hash) was ingested on 2026-05-16 (is_current=false) and 2026-05-18 (is_current=true). ALWAYS filter WHERE is_current = true; then 'code' alone is a unique key.
- FAKE TIME DIMENSION: the profiler reports earliest 2026-05-16 -> latest 2026-05-18, 2 periods, 'daily' periodicity. That is NOT code history — it is just the two re-ingestion dates of one file. effective_period_start/observed_period/asserted_at all carry the ingestion date, not the CMS fiscal-year effective date (~2025-10-01). Treat this table as STATIC FY2026, single vintage (cms_vintage_sequence=1, source_url has one FY2026 file).
- MEANINGFUL NULL: s3_version_id is NULL for the entire older (is_current=false) copy and populated for the current copy. A null s3_version_id reliably flags the superseded run; it is not random missing data.
- WRONG CODE SYSTEM TRAP: this is ICD-10-PCS = inpatient PROCEDURE codes (7-char alphanumeric, used for hospital inpatient procedures and MS-DRG assignment). The sibling dim_icd10cm (98,186 codes) is ICD-10-CM = DIAGNOSIS codes. Do not join PCS codes to diagnosis fields or vice-versa. Also note dim_msdrg is the related DRG grouping table.
- PURE DECODE TABLE, NO COST: this dimension contains NO prices, rates, or payment amounts — only code + description. Its role in a cost catalog is to translate/validate ICD-10-PCS codes that appear in inpatient claims/MS-DRG cost data; pricing lives in fact_* tables (e.g. drug spending, reimbursement) and in the Medicare/commercial rate marts elsewhere.
- PROVENANCE COLUMNS DON'T VARY BY CODE: parser_artifact_hash (2 values) and vintage_tag (2 values) differ only by ingestion run, and every other provenance column (source_url, sha256_content_hash, bronze_object_key, source_id, drug_source_version_id, cms_vintage_sequence) is single-valued. Don't mistake parser_artifact_hash/vintage_tag for per-code attributes.

| column | type | meaning |
|---|---|---|
| code | string | The 7-character ICD-10-PCS procedure code. THE business key. Verified: all 79,115 codes are exactly 7 chars, no nulls, all match the PCS alphanumeric alphabet (0-9 plus A-H,J-N,P-Z; letters I and O are excluded to avoid 1/0 confusion). First character = PCS section: '0'=Medical & Surgical (68,419 codes, the bulk), '1'=Obstetrics, '2'=Placement, '3'=Administration, '4'=Measurement/Monitoring, '5'-'9'=other procedure sections, 'B'=Imaging (2,978), 'C'=Nuclear Medicine, 'D'=Radiation Therapy, 'F'=Phys Rehab/Audiology, 'G'=Mental Health, 'H'=Substance Abuse Treatment, 'X'=New Technology (476). Range '0016070' to 'XY0YX37'. |
| description | string | Official CMS long description of the procedure (e.g. 'Bypass Cerebral Ventricle to Nasopharynx with Autologous Tissue Substitute, Open Approach'). 8-198 chars, never null, never empty. Identical across both ingestion runs for every code (0 codes drift). |
| observed_period | date | Date the source file was observed/ingested. Only two values: 2026-05-16 (the is_current=false copy) and 2026-05-18 (the is_current=true copy). NOT a real code-validity date — it is the ingestion timestamp, identical for all codes in a run. |
| asserted_at | date | Bitemporal 'asserted' date; equals observed_period in every row (2026-05-16 or 2026-05-18). Ingestion-run marker, not code semantics. |
| effective_period_start | date | Start of the row's effective window; equals observed_period (2026-05-16 or 2026-05-18). The profiler picks this as the time column, but it reflects the ingestion date, NOT the CMS fiscal-year effective date (which would be 2025-10-01 for FY2026). Do not read code-history into it. |
| effective_period_end | date | End of the effective window. Uniformly the sentinel '2099-12-31' for all current rows = open-ended / still in effect. No code is retired in this snapshot. |
| cms_vintage_sequence | int | Sequence number of the CMS vintage. Constant = 1 (single FY2026 vintage present). Would increment if multiple fiscal-year files were loaded. |
| parse_revision | int | Parser revision number. Constant = 1. |
| parser_artifact_hash | string | SHA256 of the parser artifact/run. Has 2 distinct values — one per ingestion run (the 2026-05-16 run vs the 2026-05-18 run). This is one of the columns that distinguishes the duplicate copies; it does NOT vary by code. |
| source_url | string | CMS download URL. Single value: 'https://www.cms.gov/files/zip/2026-icd-10-pcs-codes-file.zip'. Confirms exactly one fiscal-year source (FY2026). |
| sha256_content_hash | string | SHA256 of the raw source file content. SINGLE value across the whole table ('d37ad06e...') — proving both the is_current=true and is_current=false copies are byte-identical content (the same file re-ingested), not a content change. |
| source_id | string | Logical source identifier. Constant = 'icd10pcs'. |
| is_current | boolean | Snapshot/dedup flag. THE column you must filter on. Exactly half the rows are true and half false; each code has one true and one false row. Filter is_current=true to get the clean one-row-per-code FY2026 set (79,115). Without it you double-count every code. |
| bronze_object_key | string | S3 key of the bronze landing object: 'drug/bronze/icd10pcs/fy2026/d37ad06e37275baf-icd10pcs_fy2026_codes_file.zip'. Single value across the table. |
| s3_version_id | string | S3 object version id of the bronze file. MEANINGFUL NULL: it is NULL for all 79,115 is_current=false rows (2026-05-16 run) and populated ('owTFqIErx6...') for all is_current=true rows (2026-05-18 run). So a null here marks the older superseded ingestion. |
| vintage_tag | string | Human-readable vintage label. 2 distinct values: 'icd10pcs_fy2026_2026-05-16' (non-current) and 'icd10pcs_fy2026_2026-05-18' (current). Encodes fiscal year + ingestion date; the date part is what differs between the two copies. |
| drug_source_version_id | string | Drug-platform source version id. Constant = '2026' (the fiscal year). One version present. |

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