CPT Codes: Complete Guide to Procedure Classification & Billing

What are CPT Codes?

CPT (Current Procedural Terminology) codes are a standardized set of medical codes maintained by the American Medical Association (AMA) used to describe medical, surgical, and diagnostic services. CPT codes are essential for billing, reimbursement, and healthcare data analytics.

CPT Code Categories

Category I - Permanent Codes

Most commonly used CPT codes for established procedures:

Category II - Performance Measurement Codes

Optional tracking codes for quality measurement (alphanumeric, ending in 'F'):

• 3006F - Chest X-ray results documented
• 4000F - Tobacco use assessed

Category III - Temporary Codes

Temporary codes for emerging procedures (alphanumeric, ending in 'T'):

• 0075T - Transcatheter placement of extracranial vertebral artery stent

Database Schema for CPT Codes

CPT Master Table

CREATE TABLE cpt_codes (
  cpt_cd VARCHAR(10) PRIMARY KEY,
  
  -- Code Details
  cpt_desc VARCHAR(1000) NOT NULL,   -- Full description
  short_desc VARCHAR(100),            -- Abbreviated description
  
  -- Code Classification
  category VARCHAR(20),               -- Category I, II, III
  section VARCHAR(100),               -- E&M, Surgery, Radiology, etc.
  subsection VARCHAR(100),
  
  -- Code Type
  code_typ VARCHAR(20),               -- Primary, Add-on, Modifier 51 Exempt
  is_add_on BOOLEAN DEFAULT FALSE,   -- Add-on procedure
  is_bilateral BOOLEAN DEFAULT FALSE, -- Can be bilateral
  
  -- Reimbursement
  rvu_work DECIMAL(8,2),              -- Work RVU
  rvu_pe_nf DECIMAL(8,2),             -- Practice Expense (non-facility)
  rvu_pe_f DECIMAL(8,2),              -- Practice Expense (facility)
  rvu_mp DECIMAL(8,2),                -- Malpractice RVU
  rvu_total DECIMAL(8,2),             -- Total RVU
  
  -- Global Period
  global_days INT,                    -- Global period (0, 10, 90, etc.)
  
  -- Modifiers
  accepts_modifiers BOOLEAN DEFAULT TRUE,
  mod_51_exempt BOOLEAN DEFAULT FALSE, -- Multiple procedure modifier exempt
  
  -- Version Control
  eff_dt DATE NOT NULL,
  exp_dt DATE,
  version VARCHAR(10),                -- Annual version (e.g., '2024')
  
  -- Metadata
  last_upd_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_cpt_section ON cpt_codes(section);
CREATE INDEX idx_cpt_desc ON cpt_codes USING gin(to_tsvector('english', cpt_desc));
CREATE INDEX idx_cpt_category ON cpt_codes(category);
CREATE INDEX idx_cpt_version ON cpt_codes(version, eff_dt);

Procedure Services Table

CREATE TABLE procedure_services (
  proc_svc_id VARCHAR(50) PRIMARY KEY,
  
  -- Patient & Encounter
  pt_id VARCHAR(50) NOT NULL,
  enc_id VARCHAR(50),
  clm_id VARCHAR(50),                 -- Associated claim
  
  -- Procedure
  cpt_cd VARCHAR(10) NOT NULL,
  proc_desc VARCHAR(1000),
  
  -- Modifiers (up to 4)
  mod_1 VARCHAR(2),                   -- Primary modifier
  mod_2 VARCHAR(2),                   -- Secondary modifier
  mod_3 VARCHAR(2),
  mod_4 VARCHAR(2),
  
  -- Service Details
  svc_dt DATE NOT NULL,               -- Date of service
  svc_qty INT DEFAULT 1,              -- Units/quantity
  svc_loc VARCHAR(2),                 -- Place of service
  
  -- Provider
  rendering_npi VARCHAR(10),          -- Performing provider
  facility_npi VARCHAR(10),           -- Facility
  
  -- Charges & Payments
  chrg_amt DECIMAL(10,2),             -- Billed amount
  alwd_amt DECIMAL(10,2),             -- Allowed amount
  pd_amt DECIMAL(10,2),               -- Paid amount
  
  -- Metadata
  crtd_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (pt_id) REFERENCES patients(pt_id),
  FOREIGN KEY (cpt_cd) REFERENCES cpt_codes(cpt_cd)
);

-- Indexes
CREATE INDEX idx_proc_svc_pt ON procedure_services(pt_id);
CREATE INDEX idx_proc_svc_enc ON procedure_services(enc_id);
CREATE INDEX idx_proc_svc_cpt ON procedure_services(cpt_cd);
CREATE INDEX idx_proc_svc_dt ON procedure_services(svc_dt);
CREATE INDEX idx_proc_svc_npi ON procedure_services(rendering_npi);

CPT Modifiers

Modifiers are 2-digit codes appended to CPT codes to provide additional information:

Common CPT Modifiers

Common CPT Code Abbreviations

Relative Value Units (RVUs)

RVUs are used to calculate Medicare reimbursement. Each CPT code has associated RVU values:

RVU Components

• Work RVU - Physician work, time, skill, intensity
• Practice Expense RVU - Overhead costs (staff, supplies, equipment)
• Malpractice RVU - Professional liability insurance

Reimbursement Calculation

-- Medicare payment formula
Payment = (Work RVU + PE RVU + MP RVU) × Conversion Factor × Geographic Adjustment

-- SQL calculation example
SELECT 
  cpt_cd,
  cpt_desc,
  rvu_total,
  (rvu_total * 33.8872) as medicare_payment_2024
FROM cpt_codes
WHERE cpt_cd = '99213';  -- Office visit, established patient

-- Result: ~$100 for typical office visit

Common E&M Codes (Evaluation & Management)

CPT Lookup Queries

-- Search by keyword
SELECT cpt_cd, cpt_desc, rvu_total
FROM cpt_codes
WHERE to_tsvector('english', cpt_desc) @@ to_tsquery('english', 'knee & arthroscopy')
ORDER BY cpt_cd;

-- Find all E&M office visit codes
SELECT cpt_cd, cpt_desc, rvu_total
FROM cpt_codes
WHERE cpt_cd BETWEEN '99202' AND '99215'
ORDER BY cpt_cd;

-- Most frequently billed procedures
SELECT 
  ps.cpt_cd,
  c.cpt_desc,
  COUNT(*) as proc_cnt,
  SUM(ps.chrg_amt) as total_charges,
  AVG(ps.pd_amt) as avg_payment
FROM procedure_services ps
JOIN cpt_codes c ON ps.cpt_cd = c.cpt_cd
WHERE ps.svc_dt >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY ps.cpt_cd, c.cpt_desc
ORDER BY proc_cnt DESC
LIMIT 100;

-- Provider productivity by RVU
SELECT 
  rendering_npi,
  COUNT(DISTINCT pt_id) as patient_cnt,
  COUNT(*) as proc_cnt,
  SUM(c.rvu_total) as total_rvus
FROM procedure_services ps
JOIN cpt_codes c ON ps.cpt_cd = c.cpt_cd
WHERE svc_dt BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY rendering_npi
ORDER BY total_rvus DESC;

Integration with Claims

EDI 837 Professional Claims

CPT codes appear in the SV1 segment:

• SV1*HC:99213*100.00*UN*1***1
• HC = CPT/HCPCS qualifier
• 99213 = CPT code
• 100.00 = Charge amount
• UN = Unit
• 1 = Quantity

Modifiers in Claims

-- Claims with modifiers
CREATE TABLE claim_procedures (
  clm_proc_id VARCHAR(50) PRIMARY KEY,
  
  clm_id VARCHAR(50) NOT NULL,
  line_nbr INT NOT NULL,
  
  cpt_cd VARCHAR(10) NOT NULL,
  mod_1 VARCHAR(2),
  mod_2 VARCHAR(2),
  mod_3 VARCHAR(2),
  mod_4 VARCHAR(2),
  
  svc_dt DATE NOT NULL,
  svc_qty INT DEFAULT 1,
  
  chrg_amt DECIMAL(10,2),
  alwd_amt DECIMAL(10,2),
  pd_amt DECIMAL(10,2),
  
  FOREIGN KEY (clm_id) REFERENCES claims(clm_id),
  FOREIGN KEY (cpt_cd) REFERENCES cpt_codes(cpt_cd)
);

Best Practices

Coding Accuracy

• ✅ Use most specific CPT code available
• ✅ Append appropriate modifiers
• ✅ Verify code is active for date of service
• ✅ Check for bundled procedures (CCI edits)
• ✅ Document medical necessity

Data Quality

• ✅ Validate CPT codes against master table
• ✅ Ensure modifier combinations are valid
• ✅ Verify units/quantity are appropriate
• ✅ Check place of service codes

Annual Updates

• ✅ Download updated CPT files every January 1st
• ✅ Update RVU values from CMS fee schedule
• ✅ Test new/deleted/revised codes
• ✅ Communicate changes to providers and billers

CPT vs HCPCS

HCPCS (Healthcare Common Procedure Coding System) includes CPT plus additional codes:

• Level I HCPCS = CPT codes (maintained by AMA)
• Level II HCPCS = Non-physician services (maintained by CMS)
  • A codes - Supplies, DME
  • J codes - Drugs
  • Q codes - Temporary codes

Resources

CPT Code Database Design for Snowflake and BigQuery

The core cpt_codes schema works across all SQL platforms, but Snowflake and BigQuery have different optimization knobs for CPT lookup queries at scale.

Snowflake DDL — Clustered by Section and Date

-- Snowflake: cluster on section + eff_dt for range scans on E&M or Surgery codes
CREATE TABLE cpt_codes_sf (
  cpt_cd       VARCHAR(10)   NOT NULL PRIMARY KEY,
  cpt_desc     VARCHAR(1000) NOT NULL,
  short_desc   VARCHAR(100),
  section      VARCHAR(100),
  category     VARCHAR(20),
  rvu_work     NUMBER(8,2),
  rvu_pe_nf    NUMBER(8,2),   -- non-facility
  rvu_pe_f     NUMBER(8,2),   -- facility
  rvu_mp       NUMBER(8,2),
  rvu_total    NUMBER(8,2),
  global_days  NUMBER(3,0),
  is_add_on    BOOLEAN DEFAULT FALSE,
  eff_dt       DATE NOT NULL,
  exp_dt       DATE,
  version      VARCHAR(10)
)
CLUSTER BY (section, eff_dt);

-- BigQuery: partition by effective date for annual version scans
CREATE TABLE `project.dataset.cpt_codes_bq`
(
  cpt_cd       STRING NOT NULL,
  cpt_desc     STRING,
  short_desc   STRING,
  section      STRING,
  category     STRING,
  rvu_work     NUMERIC,
  rvu_pe_nf    NUMERIC,
  rvu_pe_f     NUMERIC,
  rvu_mp       NUMERIC,
  rvu_total    NUMERIC,
  global_days  INT64,
  is_add_on    BOOL,
  eff_dt       DATE NOT NULL,
  exp_dt       DATE,
  version      STRING
)
PARTITION BY eff_dt
CLUSTER BY section, cpt_cd;

Key Type Differences

RVU to Payment Calculation SQL Examples

Medicare payment uses a three-component RVU formula adjusted for geography via the Geographic Practice Cost Index (GPCI). Store GPCI values in a separate reference table so you can join at query time for any locality.

GPCI Reference Table

CREATE TABLE geographic_practice_cost_index (
  locality_cd  VARCHAR(10) PRIMARY KEY,  -- CMS locality code
  locality_nm  VARCHAR(100),
  state_cd     CHAR(2),
  work_gpci    DECIMAL(6,4),              -- Work GPCI (usually near 1.0)
  pe_gpci      DECIMAL(6,4),              -- Practice Expense GPCI
  mp_gpci      DECIMAL(6,4),              -- Malpractice GPCI
  eff_dt       DATE NOT NULL,
  exp_dt       DATE
);

CREATE INDEX idx_gpci_state ON geographic_practice_cost_index(state_cd, eff_dt);

Complete Payment Formula SQL

-- Medicare payment = (Work×WorkGPCI + PE×PEGPCI + MP×MPGPCI) × Conversion Factor
-- 2026 conversion factor: 33.8872
SELECT
  c.cpt_cd,
  c.cpt_desc,
  g.locality_nm,
  c.rvu_work,
  c.rvu_pe_nf                                          AS rvu_pe,
  c.rvu_mp,
  ROUND(
    (c.rvu_work  * g.work_gpci
   + c.rvu_pe_nf * g.pe_gpci
   + c.rvu_mp    * g.mp_gpci)
   * 33.8872,
  2)                                                    AS medicare_payment,
  ROUND(
    (c.rvu_work  * g.work_gpci
   + c.rvu_pe_nf * g.pe_gpci
   + c.rvu_mp    * g.mp_gpci)
   * 33.8872 * 0.80,
  2)                                                    AS medicare_payment_after_20pct_coinsurance
FROM cpt_codes c
CROSS JOIN geographic_practice_cost_index g
WHERE c.cpt_cd IN ('99213','99214','99215')
  AND g.state_cd = 'CA'
  AND g.eff_dt  <= CURRENT_DATE
  AND (g.exp_dt IS NULL OR g.exp_dt > CURRENT_DATE)
ORDER BY c.cpt_cd, g.locality_nm;

Provider Productivity Dashboard Query

-- Monthly RVU productivity per provider with payment estimate
SELECT
  ps.rendering_npi,
  DATE_TRUNC('month', ps.svc_dt)       AS svc_month,
  COUNT(DISTINCT ps.pt_id)              AS unique_patients,
  COUNT(*)                              AS procedure_count,
  SUM(c.rvu_work)                       AS total_work_rvus,
  SUM(c.rvu_total)                      AS total_rvus,
  ROUND(SUM(c.rvu_total) * 33.8872, 2) AS est_medicare_payment,
  ROUND(SUM(ps.pd_amt), 2)              AS actual_paid_amt,
  ROUND(SUM(ps.pd_amt) /
    NULLIF(SUM(c.rvu_total) * 33.8872, 0) * 100,
  1)                                    AS payment_vs_medicare_pct
FROM procedure_services ps
JOIN cpt_codes c ON ps.cpt_cd = c.cpt_cd
WHERE ps.svc_dt >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY ps.rendering_npi, svc_month
ORDER BY svc_month DESC, total_rvus DESC;

Frequently Asked Questions