Loading...
Validates that the coverage effective date is before or equal to the coverage termination date. A termination date earlier than the effective date indicates a data integrity issue that can lead to incorrect eligibility determination and claims processing errors.
Flags potential duplicate claims by identifying records with the same member_id, service_date, provider_npi, procedure_code, and billed_amount within a configurable time window (default 30 days). Duplicate claims can result in overpayments and compliance violations, and should be reviewed before payment processing.
Asserts that when column A has a specific value, column B has the expected corresponding value. Enforces business rules such as 'if status is shipped, shipped_date must not be null' or 'if country is US, currency must be USD'.
Asserts that all values in a column belong to a predefined set of allowed values. Catches typos, unexpected category values, or upstream system changes that introduce new enum variants without coordination.
Validates that the date of service is on or after the patient's date of birth. A service date before the patient's birth date indicates a data entry error, patient mismatch, or system integration issue. This is a fundamental integrity check for all healthcare claims.
Validates that diagnosis codes are appropriate for the patient's age. Maternity/obstetric codes (O00-O9A) should only appear for patients aged 12-55, newborn codes (P00-P96) for age 0, and pediatric-specific codes (certain Z00 preventive codes) for ages 0-17. Age-incompatible diagnoses indicate coding errors, patient mismatch, or data quality issues.
Validates that W-2 Box 2 (Federal income tax withheld) does not exceed Box 1 (Wages, tips, other compensation). Federal tax withheld cannot logically be greater than the total wages from which it was deducted. A violation indicates a payroll calculation error or data entry mistake that would trigger IRS scrutiny.
Validates the temporal ordering of claim dates: the loss date must be on or before the report date, and the report date must be on or before the current date. Catches backdated claims, data entry errors, and ETL issues that break the expected chronological sequence of insurance claim events.
Asserts that a start date column is always before or equal to an end date column for every row. Catches data entry errors, timezone conversion bugs, or ETL transformation issues that invert temporal ordering.
Validates consent requirements for minors under CCPA/CPRA. Children under 13 require verifiable parental consent before any sale or sharing of personal information. Minors aged 13-15 must provide affirmative opt-in consent themselves. No sale or sharing is permitted without the appropriate consent in place.
Validates monetary amount relationships on healthcare claims: billed amount must be >= 0, allowed amount must be >= 0, and the allowed amount should not exceed the billed amount. These checks prevent negative charges, overpayments, and data entry errors in claims processing.
Validates that the same person did not both create and approve a transaction or journal entry. The creator and approver must be different individuals to prevent fraud and ensure proper internal controls.
Detects gaps greater than 1 day between consecutive enrollment periods for the same member. For each member, enrollment periods are ordered by effective date, and the termination date of period N is compared to the effective date of period N+1. A gap greater than 1 day may indicate a lapse in coverage that affects eligibility determination and claims processing.
Validates that diagnosis codes are appropriate for the patient's reported gender. Female-only diagnoses such as obstetric codes (O00-O9A), cervical/uterine conditions, and ovarian disorders should not appear on male patient claims. Male-only diagnoses such as prostate conditions (N40-N42), testicular disorders, and male-specific genital codes should not appear on female patient claims. Gender mismatches indicate coding errors or patient data issues.
Asserts that the sum of detail/line-item rows matches the corresponding header or total row value. Validates financial reconciliation, order totals, and any parent-child numeric relationships where parts must equal the whole.
Validates that prescribed drugs are age-appropriate for the patient. Flags pediatric-only formulations (liquid suspensions, chewable tablets) dispensed to adults over the configurable adult age threshold, and identifies drugs contraindicated for elderly patients (over 65) based on the Beers Criteria. This rule uses drug name pattern matching as a first-pass screen and should be supplemented with a clinical formulary reference for production use.
Validates that when a consumer has opted out of the sale or sharing of their personal information, no records for that consumer appear in sale or sharing transaction tables after the opt-out date. This ensures businesses honor the consumer's right to say no to data monetization.
Validates that loss ratio values fall within the expected range of 0% to 200%. The loss ratio (incurred losses divided by earned premium, expressed as a percentage) is a key actuarial metric. Values outside this range typically indicate data errors, misallocated premiums, or catastrophic event data that should be flagged for review.