Skip to main content

Data Integrity Tests

Table of Contents:

1. Uniqueness Tests​

Rates Table Duplicate ID Check​

File: sql/qa/rates_no_duplicate_ids.sql

Verifies that the id field in the rates table contains no duplicate values.

SELECT count(*) as n
FROM (
    SELECT id, count(*) as cnt
    FROM {{ table }}
    GROUP BY id
    HAVING count(*) > 1
) duplicates

Rates Table Unique Combination Check​

Function: test_rates_unique_combination()
Location: qa/final_table_qa.py:495

Validates that the combination of key columns uniquely identifies each record in the rates table.

SELECT COUNT(*) as duplicate_count
FROM (
    SELECT 
        ssp_id, 
        sub_package_id, 
        payer_id, 
        network_id, 
        provider_id, 
        fee_type, 
        billing_code,
        COUNT(*) as cnt
    FROM pricing_service_rates
    GROUP BY 
        ssp_id, 
        sub_package_id, 
        payer_id, 
        network_id, 
        provider_id, 
        fee_type, 
        billing_code
    HAVING COUNT(*) > 1
) duplicates

Business Rule: The combination of ssp_id, sub_package_id, payer_id, network_id, provider_id, fee_type, and billing_code should uniquely identify each record
Purpose: Ensures data integrity and prevents duplicate rates that could cause incorrect pricing calculations
Expected Result: 0 duplicate combinations found

SSP Network Provider ID Uniqueness​

File: sql/qa/ssp_no_duplicate_network_ssp_ids.sql

Ensures no duplicate network_provider_ssp_id values exist for non-cash payers in the SSP table.

SELECT count(*) as n
FROM (
    SELECT network_provider_ssp_id, count(*) as cnt
    FROM {{ table }}
    WHERE payer_id != '0'
    GROUP BY network_provider_ssp_id
    HAVING count(*) > 1
) duplicates

SSP Network Sub Package ID Uniqueness​

File: sql/qa/ssp_no_duplicate_network_sub_package_ids.sql

Similar to the above test but for network_sub_package_id field.

2. Data Completeness Tests​

Non-Null Column Validation​

File: sql/qa/rates_no_all_null_columns.sql, sql/qa/ssp_no_all_null_columns.sql

Checks that no columns in the rates or SSP tables contain only NULL values.

Final Rates Output Validation​

File: sql/qa/final_rates_not_null.sql

Verifies that the final rates processing step produces output data.

SELECT count(*) as n
FROM {{ table }}

Final SSP Output Validation​

File: sql/qa/final_ssp_not_null.sql

Verifies that the final SSP processing step produces output data.

3. Data Validation Tests​

Source Field Validation​

Function: test_ssp_source_not_null()
File: sql/qa/rates_source_not_null.sql

Ensures that the source field is populated in SSP data.

Fee Type Validation​

File: sql/qa/rates_valid_fee_types.sql

Validates that fee_type contains only expected values.

SELECT count(*) as n
FROM {{ table }}
WHERE fee_type NOT IN ('professional', 'facility')

Provider Type Validation​

File: sql/qa/rates_valid_provider_types.sql

Validates that provider types conform to expected values.

4. Data Type Tests​

Rates Table Decimal Type Validation​

Function: test_rates_decimal_types()

Validates that rate-related columns in the rates table have the correct Decimal(21,3) data type.

Columns Validated:

  • average_units
  • negotiated_rate
  • gross_charge
  • discounted_cash_rate
  • state_avg_medicare_rate
  • lower_bound
  • upper_bound
  • anesthesia_unit_conversion
  • price
  • allocation
  • allocated_price

SSP Table Decimal Type Validation​

Function: test_ssp_decimal_types()

Validates that price-related columns in the SSP table have the correct Decimal(21,3) data type.

Columns Validated:

  • facility_fee
  • professional_fee
  • max_price

5. Count Validation Tests​

Expected SSP IDs​

File: sql/qa/rates_expected_ssp_ids.sql

Validates that rates data contains expected SSP ID references.

Network Count Validation​

File: sql/qa/rates_network_count.sql

Validates expected number of networks in rates data.

Payer Count Validation​

File: sql/qa/rates_payer_count.sql

Validates expected number of payers in rates data.

Provider Count Validation​

File: sql/qa/rates_provider_count.sql

Validates expected number of providers in rates data.

Cash Provider Count​

File: sql/qa/ssp_provider_count_cash.sql

Validates provider count specifically for cash (payer_id = '0') transactions.

On this page: