AccessMRF

Data dictionary

The tables behind Universal Search and Price Viewer, as the month's files describe them.
2026-11

Universal Search

utoc.results

1,244,966 rows, stored in the file

Plans and the files that hold their rates, for one month and every payer: one row per plan (payer, plan id, name and market) in the payer's Transparency in Coverage table of contents files. A table of contents lists plans and, for each group of plans, the in-network (negotiated rates) and allowed amount (out-of-network) files that apply to them; a plan listed in several of the month's files has the files of all of them, and its row is its first. The files of a row are file_groups rows with fgroup (in-network) or aa_fgroup (allowed amounts). Plans of one payer with the same in-network files form a file set (file_set), named by the products their plan names share (set_name).

ColumnTypeDefinition
sourceUSMALLINTThe payer source the file was crawled from: a number local to this file, sources.source.
source_nameVARCHARName of the payer source.
source_keyVARCHARThe payer source's id without dashes, as in https://www.accessmrf.com/sources .
tocUINTEGERThe table of contents file: a number local to this file, tocs.toc. The first of the month's files that list the plan.
toc_filenameVARCHARFile name of the table of contents file.
reporting_entity_nameVARCHARLegal name of the organization that published the table of contents.
positionINTEGERPosition of the row's first reporting structure in its table of contents file.
structuresUINTEGERHow many reporting structures of that table of contents list this plan.
plan_market_typeVARCHARThe plan's market: "group" or "individual".
plan_nameVARCHARName of the plan.
plan_sponsor_nameVARCHARBusiness name of the plan sponsor (the employer), given when the plan id is an EIN.
issuer_nameVARCHARName of the plan's insurer.
plan_ext_idVARCHARThe plan's id (CMS plan_id): the plan sponsor's EIN when plan_ext_type is "ein", the 10-digit or 5-digit HIOS id when it is "hios".
plan_ext_typeVARCHARWhat kind of id plan_ext_id is (CMS plan_id_type): "ein" (the employer's tax id) or "hios" (the federal Health Insurance Oversight System id).
in_network_filesUINTEGERNumber of in-network (negotiated rates) files listed for the plan.
allowed_amount_filesUINTEGERNumber of allowed amount (out-of-network) files listed for the plan.
fgroupUINTEGERThe plan's set of in-network files: file_groups.fgroup. NULL when it has none.
aa_fgroupUINTEGERThe plan's set of allowed amount files: file_groups.fgroup. NULL when it has none.
plan_identVARCHARplan_ext_type and plan_ext_id as one text, TYPE:ID; the id alone when it has no type; NULL without an id.
mrf_filesUINTEGERin_network_files plus allowed_amount_files.
n_tocsUINTEGERHow many of the month's table of contents files list the plan.
file_setUINTEGERThe plan's file set: the plans of this payer with the same in-network files. A plan with no in-network file is a set of its own.
set_plansUINTEGERNumber of plans in the file set.
set_idsUINTEGERNumber of distinct plan ids (plan_ident) in the file set; 0 when none of its plans has one.
set_nameVARCHARName of the file set: the products its plan names share, as a short label. NULL when no product name is found.
set_productsVARCHARThe product names of set_name, one per line, the first 20 of them. NULL when set_name is NULL.
set_products_nUINTEGERHow many product names set_name stands for (set_products keeps the first 20).
set_label_fromVARCHARWhere set_name comes from: the plan names, a payer's own rule, or the file names.
plan_rankUTINYINTWhere the plan sorts in its file set, lowest first: 0 its name is a product of set_name, 1 another product (and every plan alone in its set), 2 an add-on.

utoc.tocs

144,827 rows, stored in the file

The table of contents files of the month: one row per file a payer published.

ColumnTypeDefinition
tocUINTEGERThe table of contents file's number in this file. Referenced by results.toc.
sourceUSMALLINTThe payer source the file was crawled from: a number local to this file, sources.source.
toc_filenameVARCHARFile name of the table of contents file.
toc_dateDATEThe day the table of contents is published for.
toc_last_updated_onDATEThe date the payer last updated the file (CMS last_updated_on).
reporting_entity_nameVARCHARLegal name of the organization that published the file.
reporting_entity_typeVARCHARWhat kind of organization published the file, for example a health insurance issuer, a group health plan or a third-party administrator.
versionVARCHARVersion of the CMS schema the file follows.
toc_uuidUUIDThe table of contents file's id on accessmrf.com (https://www.accessmrf.com/tocs).
toc_urlVARCHARWhere the payer publishes the file.
toc_sizeBIGINTSize of the file as the payer serves it, in bytes. NULL when unknown.
toc_ingest_statusVARCHARWhether the file's contents were read into this dataset, for example ingested or failed.
toc_structuresUINTEGERHow many reporting structures the table of contents file lists, counted from those that list a plan (at least the last one's position).

utoc.files

172,944 rows, stored in the file

The in-network and allowed amount files the month's tables of contents point to: one row per file.

ColumnTypeDefinition
fileUINTEGERThe file's number in this file. Referenced by file_groups.file and parts.file.
schemaVARCHARWhat the file holds: IN_NETWORK (negotiated rates) or ALLOWED_AMOUNT (out-of-network allowed amounts).
nameVARCHARFile name, from the location the table of contents gives (CMS location: the URL where the file can be downloaded).
num_partsINTEGERHow many parts the file is published in.
last_updated_onDATEThe date the payer last updated the file, when known.
file_uuidUUIDThe file's id on accessmrf.com (https://www.accessmrf.com/files).

utoc.file_groups

658,175 rows, stored in the file

Which files belong to a set of files: one row per set and file. results.fgroup and results.aa_fgroup name a set.

ColumnTypeDefinition
fgroupUINTEGERThe set of files: a number local to this file.
fileUINTEGERThe file: a number local to this file, files.file.

utoc.parts

200,857 rows, stored in the file

The parts of each file: one row per part. A part whose rates were parsed is readable as parquet, and is file_part_id in the DuckLake catalog of the same month.

ColumnTypeDefinition
fileUINTEGERThe file: a number local to this file, files.file.
part_idxINTEGERThe part's position in its file, from 1.
part_uuidUUIDThe part's id: file_part_id in the DuckLake tables in_network, provider_references and metadata.
sizeBIGINTSize of the part as the payer serves it (compressed), in bytes. NULL when unknown.
parsedBOOLEANTrue when the part's negotiated rates were parsed and published as parquet.

utoc.result_columns

29 rows, stored in the file

The columns of results, one row each, with how many distinct values a text column has. Used by the Universal Search page to choose a column's filter.

ColumnTypeDefinition
column_nameVARCHARA column of results.
column_typeVARCHARIts DuckDB type.
distinct_countBIGINTIts number of distinct values. NULL when the column is not text.

utoc.sources

209 rows, stored in the file

The payer sources: one row per insurer, third-party administrator or portal whose files are crawled.

ColumnTypeDefinition
sourceUSMALLINTThe source's number in this file. Referenced by results.source and tocs.source.
source_uuidUUIDThe source's id on accessmrf.com.
display_nameVARCHARName of the payer source.

Price Viewer

Read from the catalog of 2026-11, built 2026-11-17 19:37 UTC (DuckLake 1.1, written by DuckDB v2.0.0, 17.3 MB to download). Every definition below is a comment stored in that catalog.

lake.in_network

53,734 parquet files, 3,181,956,335,102 rows, 13.22 TB

Negotiated rates. One row is one price a payer agreed with a group of providers for one billing code. Flattened from the in_network array of the payer's Transparency in Coverage in-network file: each row repeats its item's code and name. To get the providers, unnest provider_references and join table provider_references on (file_part_id, provider_group_id). To get the payer and the plan, join table metadata on file_part_id. A month is trillions of rows: always filter on source_id or file_part_id, and on billing_code_type and billing_code. data.accessmrf.com allows 100 requests per 10 seconds per address. Not kept from the payer's file: severity_of_illness, bundled_codes, covered_services, and provider groups written inline in a rate (schema 1.x).

ColumnTypeDefinition
additional_informationVARCHARFree text from the payer about an arrangement that does not fit the other fields. NULL when the payer gave none.
billing_classVARCHARThe kind of claim: "professional", "institutional" or "both".
billing_codeVARCHARThe billing code of the item or service. CSTM-00 means every code of the billing_code_type. Text: leading zeros are kept.
billing_code_modifierVARCHARBilling code modifiers this price depends on (for example CPT modifiers 26 or TC): a JSON array, as text.
billing_code_typeVARCHARThe code system of billing_code, for example CPT, HCPCS, MS-DRG, RC (revenue code), NDC, ICD, APC, CDT or LOCAL. CSTM-ALL means every code system. As the payer wrote it: case varies between payers.
billing_code_type_versionVARCHARVersion of the code system (for example the MS-DRG version), or the plan year when the code system has none.
descriptionVARCHARShort description of the item or service.
expiration_dateVARCHARThe date the agreement behind this price ends (YYYY-MM-DD, as text). 9999-12-31 means no end date.
nameVARCHARName of the item or service.
negotiated_rateDOUBLEThe price: a percentage when negotiated_type is "percentage" (40.5 means 40.5%), otherwise dollars.
negotiated_typeVARCHARWhat kind of amount negotiated_rate is: "negotiated" (the agreed dollar amount), "derived" (a price the payer assigns for its own accounting), "fee schedule" (the rate used to work out the member's cost sharing, when it differs from the negotiated one), "percentage" (a percent of billed charges) or "per diem" (dollars per day).
negotiation_arrangementVARCHARHow the service is paid for: "ffs" (fee for service), "bundle" or "capitation".
provider_referencesVARCHARThe provider groups this price applies to: a JSON array of provider_group_id values, as text, for example [1,2,3]. Join: UNNEST(provider_references::JSON::VARCHAR[]) = provider_references.provider_group_id, within one file_part_id. NULL when the payer wrote the providers inline in the rate or wrote an empty list, and in some files parsed before 2026-10-21.
service_codeVARCHARWhere the service is delivered: a JSON array, as text, of two-digit CMS place of service codes (for example ["11"] for an office, ["21"] for an inpatient hospital). Given for professional claims. CSTM-00 means every place of service.
settingVARCHARWhere care takes place: "inpatient", "outpatient" or "both".
dateDATEThe month the payer's file belongs to, as its first day (YYYY-MM-01). One catalog holds one month.
file_part_idVARCHARThe parsed file (a uuid). One file_part_id is one rates file, one provider file and one row of metadata.
source_idVARCHARThe payer source the file was crawled from (a uuid). Filter on it: files are grouped by source.
versionVARCHARLayout version of these parquet files (accessmrf-0.1). Not the payer's schema version, which is metadata.net_version.

lake.provider_references

53,647 parquet files, 118,209,499,675 rows, 808.72 GB

Provider groups: who a negotiated rate applies to. A group is a set of tax identifiers (TIN), each with the NPIs of its providers. From the provider_references array of the payer's in-network file. A provider_group_id is the payer's own number and repeats across files: always join on (file_part_id, provider_group_id). One row per TIN of a group (column provider_group); a group that lists no TIN has one row with provider_group NULL.

ColumnTypeDefinition
provider_group_idVARCHARThe payer's id of the provider group, unique only within one file_part_id. Text. Matches an element of in_network.provider_references.
network_nameVARCHARNames of the provider networks the group belongs to: a JSON array, as text.
locationVARCHARURL of a separate file that holds this group's providers, when the payer publishes them that way (schema 1.x; not in the linked schema version). That file is not read.
provider_groupVARCHAROne TIN of the group with its NPIs, as JSON text: {"npi": [...], "tin": {"type": "ein" or "npi", "value": the number, "business_name": the name}}. npi is [0] when the agreement names no individual provider.
dateDATEThe month the payer's file belongs to, as its first day (YYYY-MM-01). One catalog holds one month.
file_part_idVARCHARThe parsed file (a uuid). One file_part_id is one rates file, one provider file and one row of metadata.
source_idVARCHARThe payer source the file was crawled from (a uuid). Filter on it: files are grouped by source.
versionVARCHARLayout version of these parquet files (accessmrf-0.1). Not the payer's schema version, which is metadata.net_version.

lake.metadata

53,752 rows, stored in the file

One row per parsed file: what the payer's in-network file says about itself (who published it, for which plan, when) and the parser's counts. The net_ columns are the top-level fields of the payer's file under their own names. Join to the other tables on file_part_id. A file that covers several plans names none here: its plans are in the payer's table of contents file.

ColumnTypeDefinition
versionVARCHARLayout version of these parquet files (accessmrf-0.1). Not the payer's schema version, which is metadata.net_version.
dateDATEThe month the payer's file belongs to, as its first day (YYYY-MM-01). One catalog holds one month.
source_idVARCHARThe payer source the file was crawled from (a uuid). Filter on it: files are grouped by source.
file_part_idVARCHARThe parsed file (a uuid). One file_part_id is one rates file, one provider file and one row of metadata.
net_reporting_entity_nameVARCHARLegal name of the organization that published the file.
net_reporting_entity_typeVARCHARWhat kind of organization published the file, for example a health insurance issuer, a group health plan or a third-party administrator.
net_last_updated_onVARCHARThe date the payer last updated the file (YYYY-MM-DD, as text).
net_versionVARCHARVersion of the CMS schema the payer's file follows.
uncompressed_bytesUBIGINTSize of the payer's file once decompressed, in bytes.
in_network_recordsUBIGINTItems (billing codes with their rates) read from the payer's in_network array.
in_network_rowsUBIGINTRows this file has in table in_network (negotiated prices).
record_errorsUBIGINTItems of the payer's in_network array that could not be read to their end. Their rows before the error are kept.
provider_references_rowsUBIGINTProvider groups read from the payer's provider_references array.
provider_references_droppedUBIGINTEntries of the payer's provider_references array that could not be read and were left out.
service_code_canonBOOLEANTrue when the codes inside each in_network.service_code array were put in sorted order.
in_network_bytesUBIGINTSize of this file's rates parquet (in_network.zstd.parquet), in bytes. Recorded since 2026-10-31.
net_issuer_nameVARCHARName of the plan's insurer.
net_plan_nameVARCHARName of the plan.
net_plan_id_typeVARCHARWhat kind of id net_plan_id is: "ein" (the employer's tax id) or "hios" (the federal Health Insurance Oversight System id).
net_plan_idJSONThe plan's id: the plan sponsor's EIN when net_plan_id_type is "ein", the 10-digit or 5-digit HIOS id when it is "hios".
net_plan_sponsor_nameVARCHARBusiness name of the plan sponsor (the employer), given when the plan id is an EIN.
net_plan_market_typeVARCHARThe plan's market: "group" or "individual".
net_reporting_structureSTRUCT(reporting_plans STRUCT(plan_id VARCHAR, plan_id_type VARCHAR, issuer_name VARCHAR, plan_sponsor_name VARCHAR, plan_market_type VARCHAR, plan_name VARCHAR, toc_file_group_id VARCHAR)[], in_network_files STRUCT(description VARCHAR, "location" VARCHAR)[])[]Top-level field reporting_structure of the payer's file, as published. Not defined in the CMS schema this dictionary was written from.
net_reporting_entity_namenVARCHARTop-level field reporting_entity_namen of the payer's file, as published. Not defined in the CMS schema this dictionary was written from.
net_ProvidersJSONTop-level field Providers of the payer's file, as published. Not defined in the CMS schema this dictionary was written from.
net_out_of_networkSTRUCT("name" VARCHAR, billing_code_type VARCHAR, billing_code_type_version VARCHAR, billing_code VARCHAR, description VARCHAR, allowed_amounts STRUCT(tin STRUCT("type" VARCHAR, "value" VARCHAR), service_code VARCHAR[], billing_class VARCHAR, payments STRUCT(allowed_amount DOUBLE, billing_code_modifier VARCHAR[], providers STRUCT(billed_charge DOUBLE, npi UBIGINT[])[])[])[])[]Top-level field out_of_network of the payer's file, as published. Not defined in the CMS schema this dictionary was written from.
net_reporting_plansSTRUCT(plan_name VARCHAR, plan_id_type VARCHAR, plan_id "NULL", plan_market_type "NULL")[]Top-level field reporting_plans of the payer's file, as published. Not defined in the CMS schema this dictionary was written from.

lake.rates_with_providers

a view stored in the file

Negotiated rates joined to their provider groups: every column of in_network (without provider_references) and of provider_references, one row per rate and matching provider row. Always filter on file_part_id, and on billing_code_type and billing_code: the join is written so that a filter on file_part_id limits both tables to that file. A rate with no provider reference has no row here.

CREATE VIEW rates_with_providers AS SELECT r.* EXCLUDE (provider_references), p.* EXCLUDE ("version", date, source_id, file_part_id) FROM (in_network AS r INNER JOIN provider_references AS p ON (((p.file_part_id = r.file_part_id) AND list_contains(CAST(CAST(r.provider_references AS "JSON") AS VARCHAR[]), p.provider_group_id))));
ColumnTypeDefinition
additional_informationVARCHARFree text from the payer about an arrangement that does not fit the other fields. NULL when the payer gave none.
billing_classVARCHARThe kind of claim: "professional", "institutional" or "both".
billing_codeVARCHARThe billing code of the item or service. CSTM-00 means every code of the billing_code_type. Text: leading zeros are kept.
billing_code_modifierVARCHARBilling code modifiers this price depends on (for example CPT modifiers 26 or TC): a JSON array, as text.
billing_code_typeVARCHARThe code system of billing_code, for example CPT, HCPCS, MS-DRG, RC (revenue code), NDC, ICD, APC, CDT or LOCAL. CSTM-ALL means every code system. As the payer wrote it: case varies between payers.
billing_code_type_versionVARCHARVersion of the code system (for example the MS-DRG version), or the plan year when the code system has none.
descriptionVARCHARShort description of the item or service.
expiration_dateVARCHARThe date the agreement behind this price ends (YYYY-MM-DD, as text). 9999-12-31 means no end date.
nameVARCHARName of the item or service.
negotiated_rateDOUBLEThe price: a percentage when negotiated_type is "percentage" (40.5 means 40.5%), otherwise dollars.
negotiated_typeVARCHARWhat kind of amount negotiated_rate is: "negotiated" (the agreed dollar amount), "derived" (a price the payer assigns for its own accounting), "fee schedule" (the rate used to work out the member's cost sharing, when it differs from the negotiated one), "percentage" (a percent of billed charges) or "per diem" (dollars per day).
negotiation_arrangementVARCHARHow the service is paid for: "ffs" (fee for service), "bundle" or "capitation".
service_codeVARCHARWhere the service is delivered: a JSON array, as text, of two-digit CMS place of service codes (for example ["11"] for an office, ["21"] for an inpatient hospital). Given for professional claims. CSTM-00 means every place of service.
settingVARCHARWhere care takes place: "inpatient", "outpatient" or "both".
dateDATEThe month the payer's file belongs to, as its first day (YYYY-MM-01). One catalog holds one month.
file_part_idVARCHARThe parsed file (a uuid). One file_part_id is one rates file, one provider file and one row of metadata.
source_idVARCHARThe payer source the file was crawled from (a uuid). Filter on it: files are grouped by source.
versionVARCHARLayout version of these parquet files (accessmrf-0.1). Not the payer's schema version, which is metadata.net_version.
provider_group_idVARCHARThe payer's id of the provider group, unique only within one file_part_id. Text. Matches an element of in_network.provider_references.
network_nameVARCHARNames of the provider networks the group belongs to: a JSON array, as text.
locationVARCHARURL of a separate file that holds this group's providers, when the payer publishes them that way (schema 1.x; not in the linked schema version). That file is not read.
provider_groupVARCHAROne TIN of the group with its NPIs, as JSON text: {"npi": [...], "tin": {"type": "ein" or "npi", "value": the number, "business_name": the name}}. npi is [0] when the agreement names no individual provider.

Read it yourself

The queries behind this page, for a DuckDB 2.0 client.

INSTALL ducklake;
LOAD ducklake;
ATTACH 'ducklake:https://data.accessmrf.com/dev/frozen-ducklake/v3/2026-11/accessmrf.3ef8d3ce672992207dc704da3c4a3407.ducklake' AS lake (READ_ONLY);
SELECT table_name, comment
FROM duckdb_tables()
WHERE database_name = 'lake';
SELECT table_name, column_name, data_type, comment
FROM duckdb_columns()
WHERE database_name = 'lake'
ORDER BY table_name, column_index;
SELECT view_name, comment, sql
FROM duckdb_views()
WHERE database_name = 'lake' AND NOT internal;