Data dictionary
The tables behind Universal Search and Price Viewer, as the month's files describe them.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).
utoc.tocs
144,827 rows, stored in the file
The table of contents files of the month: one row per file a payer published.
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.
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.
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.
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.
utoc.sources
209 rows, stored in the file
The payer sources: one row per insurer, third-party administrator or portal whose files are crawled.
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).
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.
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.
- Field definitions (CMS)
- The same plan fields in the table of contents schema
- The Transparency in Coverage rule
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))));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;