Running Claim Checks in Snowflake Alongside a Mainframe

Last updated: October 2026

Claims systems built decades ago tend to share a shape: an overnight batch that adjudicates everything at once, a small group of people who can read its data, and rejections that surface days or weeks after a claim arrives. Replacing that system is usually a multi-year programme. Speeding up the work around it is not.

This article describes the parallel validation approach we built for a mid-size health insurer, covered in our healthcare claims case study. The insurer processed 180,000 claims a month, average adjudication took 14 business days, and during open enrollment the backlog passed 30 days. Project details come from that engagement. The SQL and the NPI function are illustrations written for this article.

Validate claims before the mainframe sees them

Replacing the insurer's adjudication engine would have been a three-year project, and nobody had budget for it. We left it in place and reimplemented its 47 core adjudication rules as Snowflake stored procedures and dbt models. Every claim is scored by that pipeline before the mainframe processes it overnight. A claim that is likely to fail, because the provider NPI is missing, the diagnosis code is invalid or the claim is a duplicate, goes to a human reviewer straight away instead of waiting in the mainframe queue for two weeks before being rejected.

The approach changed no COBOL, and the mainframe remains the system of record. It also made the case for eventually replacing the mainframe easier to put to leadership, because the modern pipeline was producing measurable results next to the legacy one.

Extracting from DB2 with Azure Data Factory

The mainframe sat behind a firewall with no public endpoint, so a self-hosted integration runtime was required. We deployed it on an on-premises VM with network access to both DB2 and Azure. ADF's DB2 connector supports DB2 for z/OS 12.1 and 11.1, DB2 for i and DB2 for LUW, and the integration runtime includes its own DB2 driver. The connector works as a Copy activity source and in Lookup activities; it cannot write to DB2.

{
  "name": "Db2Mainframe",
  "properties": {
    "type": "Db2",
    "typeProperties": {
      "connectionString": "server=<host>:<port>;database=<location>;authenticationType=Basic;username=<user>;packageCollection=<collection>;certificateCommonName=<cert-cn>;",
      "password": {
        "type": "AzureKeyVaultSecret",
        "store": { "referenceName": "KeyVaultLS", "type": "LinkedServiceReference" },
        "secretName": "db2-extract-password"
      }
    },
    "connectVia": { "referenceName": "OnPremSHIR", "type": "IntegrationRuntimeReference" }
  }
}

Two details from Microsoft's documentation save time. The default port is 50000, or 446 for DB2 for i (448 with TLS), and certificateCommonName is required when the connection uses TLS. If the first query fails with SQLCODE -805 because a package cannot be found, the documented fix is to set packageCollection=NULLID.

In this project, connectivity through the self-hosted integration runtime was the trickiest part of the work, and the connection settings needed tuning before the 15-minute extracts ran reliably against the mainframe. We recommend running a long, realistic extract through the integration runtime in the first week, before any downstream work depends on it.

ADF pulls new and changed claims every 15 minutes and writes them to ADLS Gen2 as Parquet, partitioned by claim_date and claim_type. Our guide to incremental loading in ADF covers the watermark pattern used for this kind of extract, and the self-hosted integration runtime guide covers installation and high availability.

Loading the Parquet files into Snowflake

Snowflake reads ADLS Gen2 through a storage integration. One detail is easy to get wrong: Snowflake's documentation says to use the blob.core.windows.net endpoint for all Azure blob storage, including Data Lake Storage Gen2, so stage URLs use azure://<account>.blob.core.windows.net/... even though the files were written through the dfs endpoint.

Snowpipe can load each file as it lands. On Azure, that uses an Event Grid subscription that sends BlobCreated events to a storage queue, which Snowflake reads through a notification integration. The queue's storage account must be a StorageV2 account used only by Snowflake.

CREATE STORAGE INTEGRATION claims_adls_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'AZURE'
  ENABLED = TRUE
  AZURE_TENANT_ID = '<tenant-id>'
  STORAGE_ALLOWED_LOCATIONS = ('azure://<account>.blob.core.windows.net/claims/');

-- Returns AZURE_CONSENT_URL; grant the app Storage Blob Data Reader.
DESC STORAGE INTEGRATION claims_adls_int;

CREATE STAGE raw.claims_stage
  STORAGE_INTEGRATION = claims_adls_int
  URL = 'azure://<account>.blob.core.windows.net/claims/'
  FILE_FORMAT = (TYPE = PARQUET);

CREATE NOTIFICATION INTEGRATION claims_queue_int
  ENABLED = TRUE
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE
  AZURE_STORAGE_QUEUE_PRIMARY_URI = 'https://<queue-account>.queue.core.windows.net/claims-files'
  AZURE_TENANT_ID = '<tenant-id>';

CREATE PIPE raw.claims_pipe
  AUTO_INGEST = TRUE
  INTEGRATION = 'CLAIMS_QUEUE_INT'
AS
  COPY INTO raw.claims
  FROM @raw.claims_stage
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Snowpipe on Azure does not load renamed files, and it only picks up files that arrive after the Event Grid subscription exists. ALTER PIPE ... REFRESH can load files from the previous seven days, which helps after an outage.

Checking the provider NPI

A missing or malformed National Provider Identifier is one of the simplest reasons for a claim to fail, and it can be checked exactly. CMS defines the NPI as nine digits followed by a check digit calculated with the Luhn formula. On cards the NPI is preceded by the prefix 80840, and CMS specifies that the check digit is always calculated as if the prefix were present, which is done by adding the constant 24 when the NPI is used on its own.

CMS's procedure is to double alternate digits starting with the rightmost digit of the nine-digit base, add the digits of those products to the unaffected digits, and subtract the total from the next number ending in zero. CMS's own example, 123456789, totals 67 with the constant included, so its check digit is 3 and the full NPI is 1234567893.

CREATE OR REPLACE FUNCTION ref.is_valid_npi(npi VARCHAR)
RETURNS BOOLEAN
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'check'
AS
$$
def check(npi):
    if npi is None or len(npi) != 10 or not npi.isdigit():
        return False
    total = 24                      # CMS constant for the 80840 prefix
    for i, ch in enumerate(reversed(npi[:9])):
        d = int(ch)
        if i % 2 == 0:              # double alternate digits from the right
            d *= 2
            if d > 9:
                d -= 9              # same as adding the two digits
        total += d
    return (10 - total % 10) % 10 == int(npi[9])
$$;

SELECT ref.is_valid_npi('1234567893');   -- TRUE, the CMS example
SELECT ref.is_valid_npi('1234567890');   -- FALSE

A valid check digit proves the number is well formed. It does not prove the provider exists or is in network, so the next rule in the same pass should join to the insurer's provider table.

Validating diagnosis codes against the right code set

ICD-10-CM codes are three to seven characters long, and the official guidelines state that a code with an applicable seventh character is invalid without it. A format check catches typing errors, but the only reliable test is a lookup against the official code set for the date of service, because codes are added and retired every year.

The code set changes on 1 October. The FY 2027 release applies to services from 1 October 2026 to 30 September 2027, and FY 2026 also had an update on 1 April 2026, so a reference table needs validity dates. CDC's National Center for Health Statistics publishes the files each year. Normalise codes the same way on both sides of the join, for example by removing any decimal point, so that formatting differences do not show up as invalid codes.

INSERT INTO claims.rule_flags (claim_id, rule_id, detail, flagged_at)
SELECT d.claim_id,
       'DX_INVALID',
       d.dx_code,
       CURRENT_TIMESTAMP()
FROM staging.claim_diagnoses d
LEFT JOIN ref.icd10cm_codes r
       ON r.code = REPLACE(UPPER(TRIM(d.dx_code)), '.', '')
      AND d.service_date BETWEEN r.valid_from AND r.valid_to
WHERE r.code IS NULL;

Catching duplicate submissions

Duplicates are usually resubmissions of the same service by the same provider for the same member. A window function finds them in one pass. Which fields define a duplicate is a business decision, so we agreed the key with the claims operations team before writing the rule.

INSERT INTO claims.rule_flags (claim_id, rule_id, detail, flagged_at)
SELECT claim_id, 'DUPLICATE', first_claim_id, CURRENT_TIMESTAMP()
FROM (
    SELECT claim_id,
           FIRST_VALUE(claim_id) OVER (
               PARTITION BY member_id, provider_npi, service_date,
                            procedure_code, billed_amount
               ORDER BY received_at) AS first_claim_id
    FROM staging.claims
    WHERE received_at >= DATEADD(day, -90, CURRENT_DATE())
)
WHERE claim_id <> first_claim_id;

Each rule writes to the same rule_flags table with its own rule identifier. That keeps the 47 rules independent, makes it easy to report which rules fire most often, and gives reviewers one queue to work from.

Status codes the documentation did not mention

One problem took us by surprise. The internal wiki listed 14 claim status codes, but the DB2 table contained 23 distinct values. We spent two days mapping the undocumented ones by cross-referencing the COBOL source. On systems that predate version control, we recommend profiling every coded column at the start, with a simple SELECT status, COUNT(*) ... GROUP BY status, and add a dbt accepted_values test so that any new code fails the build instead of being silently miscounted.

The project also gave operations managers a Power BI dashboard showing queue depth, average processing time by claim type, top rejection reasons and bottleneck stages, refreshed as often as they liked instead of a weekly spreadsheet that was three to five days old. Average processing time fell from 14 days to 3.8, a 73% improvement, and $1.6 million was saved in provider network retention, at a point when providers had been threatening to leave.