Abridged

SchemaMethodology

---

The abridged table contains the essential columns of Clear Rates. This is the table that is loaded in the API and powers our Clear Rates based products, such as Analyze.

There are a few key concepts you should know before using Clear Rates:

Key Concepts

1. The ROID
2. Canonicalization
3. Traceability

---

1. The ROID

The roid uniquely identifies a row in Clear Rates and it's defined in the code here:

from_big_endian_64(xxhash64(CAST(
    payer_id || 
    CAST(network_id AS VARCHAR) ||
    provider_id ||
    bill_type ||
    c.billing_code ||
    billing_code_type ||
    COALESCE(CAST(facility AS VARCHAR), '')
    AS varbinary
))) AS roid

As you can see, it represents a distinct combination of:

• payer_id
• network_id
• provider_id
• bill_type (Inpatient, Outpatient, Professional)
• billing_code
• billing_code_type
• facility (True or False when bill_type = 'Professional' else NULL)

Note that roid does not change across subversion runs, ensuring stable identification of the same rate record over time.

2. Canonicalization

We may have multiple rates in MRF data for the same roid. The Clear Rates pipeline standardizes the various representations and selects a "best" rate. We call this "best" rate the "canonical rate".

prod_combined_abridged has a canonical_rate field and various other canonical columns that tell you more about this rate.

Field	Description
canonical_rate	The "best" rate identified for this roid.
canonical_rate_source	Source of the rate: payer, hospital, or imputed.
canonical_method_formula	Traceability formula used to compute the rate.
canonical_method_params	Parameters used in the canonical_method_formula.
canonical_rate_category	posted, enhanced, or real world (see explanation here).
canonical_rate_score	1-5 score used in Analyze (see explanation here).
canonical_rate_validation_method	How the rate was validated.
canonical_rate_subversion	Each Clear Rates run selects the best rate from multiple months of data (e.g. 4 months of Core Rates and historic hospital rates); the subversion indicates the month, e.g. 2025_06.
rate type
canonical_rate_class	Indicates rate type: raw, transform, or impute.
canonical_imputation_method	Imputation approach: MRF-Reported Base Rate, Inferred Base Rate, Inferred RC Family Rate, Inferred RC Global Rate, Inferred Outpatient Procedure Grouper, or MRF-CSTM.
canonical_transformation_method	How rate was transformed: Per Diem to Dollar or Percentage to Dollar.
canonical_rate_type	A unique identifier for the rate derivation method (primarily used in code).
canonical_contract_methodology	Indicates methodology based on contract_methodology in hospital MRF and negotiated_type in payer MRF. Value may be one of: Case Rate, Fee Schedule, Per Diem, Percent of Total Billed Charges. "negotiated" and "derived" are classified as Case Rate if is_surg_code=True and Fee Schedule otherwise.
crosswalked billing_code_type
canonical_crosswalk_method	Whether billing code type was converted from one type to another (e.g. APR-DRG to MS-DRG or vice versa).
canonical_rate_original_billing_code_type	Original billing code type before any crosswalk.
canonical_rate_original_billing_codes	Original billing codes before any crosswalk.
gross charges
canonical_gross_charge	Gross charge amount.
canonical_gross_charge_type	Type of gross charge.
canonical_rate_gross_charge_original_billing_code_type	Original billing code type for gross charge in raw data.
canonical_rate_gross_charge_original_billing_codes	Original billing codes for gross charge in raw data.
percent of medicare/list
canonical_rate_percent_of_state_avg_medicare	Rate expressed as a percent of state average Medicare.
canonical_rate_percent_of_medicare	Rate expressed as a percent of Medicare.
canonical_rate_percent_of_list	Rate expressed as a percent of list charges.

The best_payer and best_hospital fields contain the best payer and best hospital rates. In general, one of these will be the "canonical rate". There are rare exeptions, such as when a hospital MRF posts a base rate.

Field	Description
best_payer_rate	The "best" rate identified for this roid.
best_payer_rate_score	1-5 score used in Analyze (see explanation here).
best_payer_rate_validation_score	Validation score (see explanation here).
best_payer_rate_type	A unique identifier for the rate derivation method (primarily used in code).
best_hospital_rate	The "best" rate identified for this roid.
best_hospital_rate_score	1-5 score used in Analyze (see explanation here).
best_hospital_rate_validation_score	Validation score (see explanation here).
best_hospital_rate_type	A unique identifier for the rate derivation method (primarily used in code).

3. Traceability

Given a roid, canonical_method_params and canonical_method_formlua tell you how we got the rate.

Here are a couple examples:

canonical_rate	canonical_method_params	canonical_method_formula
3719.68	{"percentage":18.40,"ccr_adjustment":1.4499954231,"gross_charge":13941.86}	{percentage} * {gross_charge} * {ccr_adjustment}
6166.1	{"percentage":77.60,"gross_charge":7946.00}	{percentage} * {gross_charge}

Additional traceability columns:

column	description
canonical_rate_id	only in prod_combined_all, this corresponds to the id field in core_rates and hospital_rates; it only applies to "raw" and "transform" rates
payer_location_data_source_name	payer data source name
hospital_filename	hospital file name