Table of Contents
CASE STUDY – MASTER DATA MANAGEMENT & AI
How Psiog replaced a week-long manual file-mapping process with a pipeline that resolves what it can deterministically, calls a model only for what is left, and holds every uncertain decision for a person to confirm.
| Client | A leading national RCM provider, Charlotte, NC |
| Scale | 500+ clients, 47 states |
| Domain | Bad Debt receivables onboarding into a Snowflake-native MDM layer |
Business Problem
Every hospital or physician practice the client represents sends its receivables data in its own shape: its own file format, its own column names, its own conventions for the codes it uses. Before that data can support bad debt collection, someone has to understand it, map it onto a common model, and load it into a master data layer that the downstream collection software reads.
That understanding work was manual. An engineer studied a new client’s file, worked out what each column meant, wrote the result up as a set of processing instructions, and ran every later file from that client against them. A single client took the better part of a week to move from file received to data usable downstream. Volume kept rising. The client represents more than 500 hospital and physician practices, each with a file shape to decode and each a fresh chance for a mapping error to reach the collection system undetected.
Automation was the obvious answer. The harder question was whether it could be trusted with the judgment calls a person used to make by hand, at the scale and under the regulatory weight healthcare data carries.
Context Setting
The client is one of the nation’s leading Revenue Cycle Management (RCM) companies, serving more than 500 hospital and physician practices across 47 states. This engagement covers the Bad Debt placement workflow specifically: receivables that have moved past active billing and need to be placed with collections, loaded into the MDM layer that feeds the client’s bad debt collection software.
Onboarding is organized into three sequential waves, moving the client’s book of business through the new pipeline over the course of the engagement.
Every file in scope carries protected health information (PHI), which shapes the design throughout. Data stays inside a single platform boundary from the moment it lands, and nothing patient-identifiable reaches the AI model.
Executive Overview
A client load that used to take about a week now takes a few hours. Psiog built an AI-assisted onboarding pipeline that reads each incoming client file, proposes how its columns map onto the client’s canonical data model, and routes anything it scores as uncertain to a person before the data reaches the golden record.
Engagement snapshot
| Customer | A leading national RCM service provider, 500+ hospital and physician practices across 47 states |
| Delivery team | Psiog’s Data Dev team: schemas, pipelines, and AI agents for the Bad Debt onboarding stream |
| Platform | A Snowflake-native pipeline: AI agents orchestrated inside the same platform boundary as the data |
| Domain focus | Bad Debt receivables, carrying protected health information (PHI) |
| Downstream consumer | The client’s bad debt collection platform |
Impact at a glance
| Client onboarding time | About a week per client, manual | A few hours, AI-assisted |
| Column mapping | Hand-mapped by an engineer, file by file | Inferred automatically, reviewed only when confidence is low |
| Consistency across clients | Re-derived per client, per engineer | Learned once and reused through a shared concept model |
| Onboarding effort per client | Ad hoc, dependent on the engineer | Benchmarked at a consistent, predictable effort |
System Overview
One pipeline runs natively inside Snowflake from end to end. Files land, the pipeline resolves the feed, maps the columns, applies business rules and lookups, then stages and masters the result into the golden MDM record. All of it happens inside a single platform boundary that PHI never leaves. AI agents sit alongside the pipeline, each sized for one task and grounded against a shared concept model that becomes more reusable with every client onboarded. Review and monitoring applications give data stewards a place to confirm AI proposals, author rules in plain language, and trace any value in the mastered record back to its source.
Technical Approach
One rule governs every stage. Resolve deterministically wherever a known pattern or a previously confirmed layout applies, call on AI only for what is left, and route anything the system scores as uncertain to a person before it writes to the golden record. That holds down cost and risk together. A model call carries a price, and a wrong guess in a regulated dataset costs more than a slower, certain answer.
A working example
Take a small, fixed-width client file carrying two fields, a client account number and a client Social Security number, each in a fixed position in every row. The client also sent two business rules as plain-text notes: the SSN must be exactly nine characters, and the account number cannot be blank. The onboarding step handles that file in four stages.
- The pipeline reads the shape of the file (field positions, lengths, a sample of values) and proposes a mapping for each field onto the client’s canonical data model, with a confidence score on each proposal. Before anything downstream sees it, the proposal is checked against a strict output schema: the expected fields, the expected types, and a confidence value that is a number between 0 and 1. A malformed or incomplete model response is rejected outright and never reaches the pipeline.
- SSN field → “Patient SSN”, confidence 0.90
- Account number field → “Patient Account Number”, confidence 0.72
- The pipeline translates the plain-text business rules into structured validation logic it can enforce, and writes each rule back out in plain language so a reviewer can confirm it means what the client intended:
- “SSN must be exactly 9 characters” → error, reject the row if it fails
- “Account number must not be blank” → warning, flag but keep the row
- “Patient Account Number must be unique for this client” → deduplication rule, matched against existing records before the row is written, with any duplicate held for a steward to resolve
- Confidence decides what happens next. The SSN mapping, at 0.90, applies automatically and no one has to look at it. The account number mapping, at 0.72, falls below the threshold for an unattended write, and the field also serves as the entity’s dedup key, so it goes to a data steward to confirm before anything is committed.
- Once confirmed, the mapping and the rules become that client’s configuration. The next file from the same client skips this step, because the pipeline already knows how to read it.
Every mapping therefore arrives as a scored proposal. It earns an automatic write only when the score clears the threshold set for that field, and a person decides the rest.
Solution
- Purpose-built models. Column profiling, mapping, rule authoring, and drift detection each run as a separate model, sized for that one job.
- Schema-validated outputs. Every model response is checked against a strict output schema (field types, required fields, a confidence score bounded between 0 and 1) before the pipeline acts on it. A malformed or out-of-range response is rejected.
- Confidence-banded automation. Every AI output carries a confidence score. High-confidence results write straight to the pipeline, mid-confidence results go to a reviewer, and low-confidence results wait for a human decision before anything is committed.
- Human-in-the-loop review. A data steward works through a queue of flagged items, edits a proposed mapping or rule directly, and submits it. The correction becomes part of what the pipeline knows going forward.
- A shared, governed concept model. Every mapped field is grounded against one vocabulary, so what the pipeline learns for one client carries to the next.
- End-to-end traceability. Every value in the mastered record traces back to its source file, the rule or model that produced it, and any human review it passed through.
Implementation Nuances
- Config-driven engine. The same pipeline handles a decades-old fixed-width mainframe extract and a modern delimited file without a code change. The difference lives in configuration.
- Idempotent merges. Re-running a load never creates duplicate records, which matters whenever a run has to be retried or replayed.
- Per-rule severity. A failed validation can reject the offending row, flag it for review, or simply record it, so one bad record does not cost the whole file.
- Privacy by construction. PHI never leaves the platform boundary and never reaches the AI model. The mapping is inferred from field shapes, positions, and statistical patterns alone.
Scalability to Other Domains & Business Lines
The engagement covers Bad Debt placement, though nothing in the architecture is specific to bad debt. The same deterministic-first, confidence-banded method of reading an unfamiliar file and proposing a mapping applies to early-out receivables and insurance claims data, both of which the client also handles, without rebuilding the core engine. Extending onboarding to a new business line is largely a configuration exercise: define the target canonical model for that domain, then let the same pipeline learn its file patterns.
Business Value
- Compliance answered in the architecture. The Cortex AI layer never sees patient-identifiable data. Only the shape of a file reaches the model: field positions, lengths, data types, statistical patterns. The PHI stays inside the Snowflake boundary it landed in. The client can therefore answer a HIPAA question with its design, because no step in the process hands PHI to an external or general-purpose AI service.
- Errors contained before they land. The mapping errors that used to reach the collection system undetected are now structurally harder to produce. A low-confidence proposal is held for review before it ever touches the golden record.
- Specialist time redirected. The engineers who spent a week hand-mapping a single client’s file now review the handful of items the system flags as uncertain. Their work moves from manual processing to exception handling.
- Faster onboarding, faster collections. The MDM layer exists to feed the client’s bad debt collection software. Cutting onboarding from about a week to a few hours means newly placed receivables start being worked sooner.
- Growth without proportional cost. Mappings and rules carry across clients, so onboarding the next 100 clients costs less per client than onboarding the first 100 did.
Conceptual Takeaways
- Deterministic-first beats AI-first. Resolving a known pattern without a model call is faster and more predictable, and it costs nothing. AI earns its place only where no pattern exists yet.
- Confidence makes automation auditable. The pipeline treats a mapping that is probably right differently from one that is certainly right, so every automatic write has a recorded reason behind it.
- Reuse compounds. A mapping learned once, for one client, lowers the cost of onboarding the next, so the pipeline gets cheaper to extend over time.
Artefacts Generated
- A column-mapping engine grounded in a shared concept model, usable across every client file shape encountered so far.
- Human-in-the-loop review applications for confirming AI-proposed mappings and authoring business rules in plain language.
- A monitoring and audit trail covering pipeline runs and every decision taken within them.
- A governance record connecting every mastered field back to its source file and the logic that produced it.
Way Forward
Technical
The pipeline writes data into the MDM layer and does not yet deliver it back out. The next phase adds an outbound delivery layer: unified client views over the mastered data and a single writer for each client’s output format, so the collections platform and other downstream systems draw directly from the golden record.
Functional
The mastered Bad Debt data also supports uses beyond the collections hand-off: portfolio-level reporting on receivables performance, trend analysis across the client base, and a template for bringing early-out and insurance onboarding onto the same platform once Bad Debt is proven.
