# Form 5500 — service provider codes (Schedule C)

`drug_silver.fact_form5500_service_provider_code` · Drugs and formularies · status live · 10,564,656 rows

The EFAST2 Form 5500 Schedule C service-provider service codes per filing for plan years 2009-2025. The pre-EFAST2 Schedule C archive is byte-secured but is not merged into this certified EFAST2 sidecar.

**Grain.** One row per service-code position on a service-provider line within a single Form 5500 filing — i.e. one row per (filing, provider line, code slot). Verified minimal unique key is (ack_id, row_order, code_order) = exactly 3,419,239 distinct = the full table row_count (3,419,239). ack_id picks the filing (420,651 of them), row_order picks the provider line within that filing (1-908), code_order picks the position of one declared service code within that provider's list (1-23, dense). A given provider line carries ~2.5 service codes on average.

**How to use it.** Classify 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 ... ORDER BY p.direct_comp_usd DESC. This 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.**
- service_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.
- ack_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.
- is_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.
- The 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.
- This 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'.

| column | type | meaning |
|---|---|---|
| ack_id | string | DOL EFAST2 acknowledgement ID of the Form 5500 filing this row came from (string, e.g. '20240905083006NAL0043218194001'). 420,651 distinct filings. First leg of the grain. Functionally determines plan_year (each ack_id = exactly one plan year). Join key to the sibling parent table fact_form5500_service_provider on (ack_id, row_order). |
| plan_year | int | ERISA plan year the filing reports on. int, 5 values 2020-2024 (~76K-89K filings each). Functionally dependent on ack_id (NOT needed in the grain). Drives all the period columns. NOTE: this is the reported plan year; the table treats it as a calendar year regardless of off-calendar plan periods. |
| row_order | int | Positional index (1-based) of the service-PROVIDER line within the filing's Schedule C Part 1 Item 2 list. int, range 1-908 (avg ~3.3 provider lines per filing). Second leg of the grain. Joins to the same row_order in fact_form5500_service_provider, which holds that provider's name/EIN/state and compensation (direct_comp_usd). |
| code_order | int | Positional index (1-based) of the service-CODE within a single provider line's list of declared service codes. int, range 1-23, DENSE (always 1..n, no gaps), avg ~2.5 codes per provider line. Third and final leg of the grain. THIS, not service_code, is what makes a row unique within a provider line (a provider can list the same service_code twice). |
| service_code | string | DOL EFAST2 Form 5500 Schedule C service-code category the provider self-declared (2-char string '10'-'99', 55 distinct values, 0 nulls). Identifies WHAT TYPE of service the provider rendered to the plan (e.g. '50' recordkeeping-related is most common at 438K, '64','15','27','37'... ; '99' = 'Other services'). NOT unique within a provider line. This is a category code, not a dollar amount. |
| observed_period | date | date. Derived 100% from plan_year = Jan 1 of the plan year (2020-01-01 ... 2024-01-01). 1 distinct value per plan_year. A snapshot/as-of proxy, not a real observation date. |
| asserted_at | date | date the row was asserted/loaded by the Vlada pipeline. Single constant value 2026-05-15 for ALL 3.4M rows (this whole table was built in one batch). Bitemporal-lineage column; carries no per-row variation. |
| effective_period_start | date | date. Derived 100% from plan_year = Jan 1 of plan year. The profiler picks this as the temporal column (2020-01-01 -> 2024-01-01, annual). It is a calendar-year proxy, NOT the filing's actual ERISA plan-period start. |
| effective_period_end | date | date. Derived 100% from plan_year = Dec 31 of plan year. Calendar-year proxy, NOT the actual plan-period end. Zero rows deviate from Jan1-Dec31 of plan_year. |
| cms_vintage_sequence | int | int. Lineage/vintage sequence number. Constant value 1 across all rows (the 'CMS' naming is a generic pipeline lineage field reused for this DOL source; only one vintage loaded). |
| parse_revision | int | int. Parser revision number for the extraction. Constant value 1 across all rows (single parse revision). |
| parser_artifact_hash | string | string sha256 of the parser code artifact that produced the row. Single constant value across all rows. Provenance/reproducibility field. |
| source_url | string | string. URL of the DOL EFAST2 source ZIP this row was parsed from (e.g. https://askebsa.dol.gov/FOIA Files/2023/Latest/F_SCH_C_PART1_ITEM2_CODES_2023_Latest.zip). 5 distinct values = one annual 'Latest' file per plan_year 2020-2024. |
| sha256_content_hash | string | string. sha256 of the raw source file content. 5 distinct (one per plan_year file). Content-addressed provenance of the bronze object. |
| source_id | string | string. Logical source identifier. Single constant value 'dol_efast2_schedule_c_p1i2_codes' for the entire table (DOL EFAST2 Schedule C, Part 1, Item 2 service codes). |
| is_current | boolean | boolean SCD/current-version flag. TRUE for 100% of rows (3,419,239 / 3,419,239); zero non-current rows exist. No version-dedup filtering is needed today, but query with is_current=true to stay future-proof. |
| bronze_object_key | string | string. S3 key of the bronze-layer object this row was derived from (drug/bronze/dol_efast2_schedule_c_p1i2_codes/<year>/<hash>-...zip). 5 distinct (one per plan_year). Data-lake lineage pointer. |
| s3_version_id | string | string. S3 object version-id of the bronze file. 5 distinct (one per plan_year). Pins the exact immutable S3 object version for reproducibility. |
| vintage_tag | string | string vintage label. Single constant value 'none' across all rows (no special vintage tagging applied to this source). |
| drug_source_version_id | string | string. Cross-source drug version linkage id. 100% NULL across all 3.4M rows (0 distinct). Not populated for this table; do not rely on it. |

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