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

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. 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: