Insurer negotiated rates

Commercial negotiated rates (TiC)

vlada_curated.fact_rate·partial·curated·Commercial Negotiated Rates (Transparency in Coverage)

Rows

N/A

Columns

History

N/A

Card

nocolumns only

Typed tools

What it is

1Cleaned commercial-insurer negotiated rates extracted from payer Transparency-in-Coverage machine-readable files.
2The substrate of "what does this payer actually pay this provider for this code."

Grain

Key	Grain
N/A	1One row per (payer, plan, NPI, code, modifier, billing_class, POS, negotiated_type) at a snapshot.

Columns (54)

Column	Type	Meaning
`rate_id`	string
`payer_id`	string
`entity`	string
`state`	string
`state_source`	string
`code`	string
`code_system`	string
`code_version`	string
`code_prefix`	string
`modifiers`	array<string>
`description`	string
`billing_class`	string
`negotiation_arrangement`	string
`negotiated_type`	string
`negotiated_rate`	double
`setting`	string
`service_codes`	array<string>
`bundled_codes`	array<string>
`expiration_date`	string
`provider_group_hash`	string
`raw_rate_identity`	string
`raw_provider_group_ids`	array<bigint>
`schema_version`	string
`version_id`	string
`canonical_source_file`	string
`source_hash`	string
`loaded_at`	timestamp
`commercial_mrf_version_id`	string	FK to dim_commercial_mrf_version (RDS, migration 033). UUID as string. NULL for pre-Phase-C rows; populated by C.J re-extraction. Audit chain: row → dim → raw_s3_key → archived bytes → replay tool.
`observed_period`	date	M-2 universal: period the data describes. For commercial TiC MRFs, the YYYY-MM-01 of the manifest version. Substrate-wide name; distinct from version_id (payer's filename convention).
`asserted_at`	date	M-2 universal: date the source published this bytes-version. For commercial MRFs, derived from MRF's last_updated_on field (preferred) or HTTP Last-Modified header (fallback). Quality in asserted_at_source.
`effective_period_start`	date	M-2 universal: start of period this data is effective for reasoning. For commercial rates, equals observed_period (point-in-time pricing snapshot). M-3 catalogs per-table convention.
`effective_period_end`	date	M-2 universal: end of effective period. Equals start for point-in-time snapshots (typical for commercial TiC). Range for spans.
`asserted_at_source`	string	M-2 universal ENUM: mrf_last_updated_on \| http_last_modified \| filename_inferred \| unknown_default. Quality marker for asserted_at.
`calculation_epoch`	date	M-2 universal NULLABLE: for sources with mid-cycle recalculation. Expected NULL for commercial TiC (no mid-cycle rerun semantics — payers republish full monthly manifests).
`vintage`	string	M-2 universal ENUM: as_published \| as_restated. Binary display category. Commercial payers rarely restate but the column is required substrate-wide.
`cms_vintage_sequence`	int	M-2 universal: 1 for first publication, 2 for first restatement, etc. Advances ONLY on bronze sha256 change (payer-side). For commercial, advances when a payer republishes the same source file.
`parse_revision`	int	M-2 universal: substrate-side parser-bug-fix sequence. Advances on parser_artifact_hash bump for the same bronze object. Initial 1. Distinct from cms_vintage_sequence (payer-side changes).
`restatement_of`	string	M-2 universal NULLABLE (UUID-as-string): FK to prior vintage's commercial_mrf_version_id when a payer restates. NULL for cms_vintage_sequence=1 rows.
`is_current`	boolean	M-2 universal: TRUE for the most recent (cms_vintage_sequence, parse_revision) of this (natural_key, observed_period) tuple. Invariant: exactly one TRUE per natural key.
`snapshot_set`	string	M-2 universal: JSON-encoded MAP<table_fqn, iceberg_snapshot_id> covering every silver table read to produce this row. Bronze-anchored: single-entry self-reference. Derived: multi-entry upstream map. Phase 1 JSON; native MAP later.
`s3_version_id`	string	M-2 universal NULLABLE: exact S3 VersionId of the bronze object this row was parsed from. NULL for derived rows. Detects silent payer republishes that don't bump filename.
`bronze_object_key`	string	M-2 universal: full S3 key of bronze object (substrate-wide name). ALIAS of raw_s3_key (dim_commercial_mrf_version.raw_s3_key) during transition. Future migration drops the legacy name.
`sha256_content_hash`	string	M-2 universal: SHA-256 of bronze content (substrate-wide name). Populated by Rust extractor FU-9 streaming primitive. Same value as dim_commercial_mrf_version.content_sha256.
`source_lineage`	string	M-2 universal NULLABLE: JSON-encoded ARRAY<STRUCT<table_fqn, snapshot_id, row_set_fingerprint>>. Derived-row lineage. NULL on bronze-anchored fact_rate; populated on mart_unified_pricing joins. Phase 1 JSON; native ARRAY<STRUCT> later.
`parent_vintage_fingerprint`	string	M-2 universal NULLABLE: SHA-256 of sorted-concatenated upstream (s3_version_id \|\| snapshot_id) tuples. Used by query helper to emit cascade_stale caveat when upstream vintages advance.
`source_id`	string	M-2 universal: stable source-family identifier from M-1's source registry. For commercial fact_rate, value like 'commercial_in_network_tic' or 'commercial_allowed_amount_tic'.
`source_url`	string	M-2 universal: original download URL for this row's bronze object. For commercial, the MRF URL from payer_toc_files.file_url at fetch time. Useful for replay-tool re-fetch if archived bytes corrupt.
`parser_artifact_hash`	string	M-2 universal: SHA-256 of parser code artifact (Rust extractor binary). Stamped at compile time via VLADA_PARSER_ARTIFACT_HASH env var. Reparse decisions key on this hash. Same value as dim_commercial_mrf_version.parser_artifact_hash.
`ingested_at`	timestamp	M-2 universal: UTC timestamp of silver write. Audit-only; never used for query semantics. Distinct from existing loaded_at if present (ALIAS during transition).
`methodology_version`	string
`substrate_schema_version`	string
`builder_sha`	string
`nppes_vintage_at`	timestamp
`lookup_snapshot_id`	string

Query it

describe_data_source(source_fqn="vlada_curated.fact_rate")     # the card, live
query_substrate(sql="SELECT * FROM vlada_curated.fact_rate WHERE <filter> LIMIT 100")

Item	Value
Typed tools that read it	get_negotiated_rate, lookup_rates_for_npi, lookup_rates_for_tin, plan_commercial_price_proof, prove_commercial_price, replay_commercial_price_proof
Identity key	`npi`
Join	`how_to_join(source_fqn="vlada_curated.fact_rate")`
Raw door	Athena workgroup vlada-analytics: vlada_curated.fact_rate (always filter ghost_rate_score < 0.5)
Note	Current commercial truth packet v5 has 30/46 configured payer/network profiles indexed/live: amerihealth_caritas, amerihealth_nj, bcbs_id, bcbs_sc, carefirst, centene, cigna, emblemhealth, excellus, fallon, geisinger, health_new_england, health_partners, highmark, johns_hopkins_hc, kaiser, la_care, mclaren_health_plan, medical_mutual, molina, mvp, premera, priority_health, priority_health_via_cigna, quartz, sentara_health_plans, summacare, the_health_plan, tufts, and wellmark. Excellus/MVP/Quartz ledger backfill is closed; Highmark required G4.5 hot indexes. NEVER aggregate across modifier groups (rate-component splitter).

This page as markdown: /docs-md/data/vlada_curated_fact_rate.md · every page in one file: /llms-full.txt