Form 5500 — service provider codes (Schedule C)

What it is

1. 1A normalized child fact table holding the DOL Form 5500 Schedule C Part 1 Item 2 service-code selections: for each service provider a benefit plan reported, the one-or-more 2-digit DOL codes describing the type(s) of service rendered.
2. 2It exists to classify the providers (and therefore the plan spend) recorded in its parent table fact_form5500_service_provider by service category.

Grain

How to read it

1. 1Classify benefit-plan vendors and their spend by service type: e.g., to find the top PBMs/recordkeepers paid by plans in 2023, join codes to the parent provider table — SELECT p.provider_name, array_join(array_agg(c.service_code ORDER BY c.code_order),',') AS codes, p.direct_comp_usd FROM fact_form5500_service_provider_code c JOIN fact_form5500_service_provider p ON c.ack_id=p.ack_id AND c.row_order=p.row_order WHERE c.plan_year=2023 GROUP BY ...
2. 2ORDER BY p.direct_comp_usd DESC.
3. 3This returns real rows like OPTUMRX, INC. with codes '12,50,62,99' and ~$221M direct comp, telling you both who was paid and what services they declared.

Traps

1. 1service_code is NOT part of the grain and is NOT unique within a provider line — 1,198 provider lines list the same service_code twice, so (ack_id, row_order, service_code) yields 3,418,014 distinct vs 3,419,239 rows and silently drops 1,225 rows. Always use code_order (the positional slot) as the third key, never service_code.
2. 2ack_id alone is only 420,651 distinct and a provider line spans multiple code rows, so any per-filing or per-provider aggregation must DISTINCT/GROUP appropriately. plan_year is redundant in the key (functionally determined by ack_id) and code_order resets to 1 on every new provider line, so neither plan_year nor code_order is unique on its own.
3. 3is_current is TRUE for 100% of rows and asserted_at is a single constant (2026-05-15) — there is currently no version history or SCD churn to dedup, but filter is_current=true to stay correct if future loads add superseded rows.
4. 4The period columns (observed_period, effective_period_start, effective_period_end) are 100% derived from plan_year as calendar Jan 1 - Dec 31 — they are NOT the filing's true ERISA plan-period dates and will be wrong for off-calendar plan years. Use plan_year as the real time dimension.
5. 5This table holds NO dollar amounts and NO provider identity — only category codes. To get the actual cost/compensation (direct_comp_usd, indirect comp) and provider_name/EIN/state, JOIN to the parent fact_form5500_service_provider on (ack_id, row_order) (100% FK integrity from this side; ~8,730 provider lines in the parent declared no code and are absent here). drug_source_version_id is 100% NULL and source_id is the single constant 'dol_efast2_schedule_c_p1i2_codes'.

Columns (20)

Query it

describe_data_source(source_fqn="drug_silver.fact_form5500_service_provider_code")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_form5500_service_provider_code WHERE ack_id = '<value>' AND row_order = '<value>' AND code_order = '<value>' LIMIT 100")