Three feeds describe the same drug moving to the same patients, and they never agree. Your distributor’s EDI 867 says one number. The specialty pharmacy network says another. The syndicated sales feed says a third. The brand team wants to know which one is right by Wednesday.
Usually all three are right about different things. The work is making them describe the same thing before you compare them.
Four normalizations before any reconciliation
Pick one event date
No two specialty pharmacies agree on what ship date, fill date, or dispense date means. One counts a referral the day it arrives, the next the day benefits clear, the third when the first box goes out the door. The same patient can land in different weeks depending on which pharmacy filled them.
Pick one calendar
The time and calendar dimension is where three vendors carry three definitions of a week, all reasonable, none the same. Map every feed to your fiscal week before comparing weekly totals.
Pick one unit
An 867 quantity column can arrive as units one month and packages the next, or a price can land in cents. One product shows a 10x jump. Nobody flagged the unit-of-measure change.
Pick one NDC level
Your 867 files and claims carry package-level NDCs in 11-digit form. Your product master carries the two-segment product form. And a 10-digit NDC with its hyphens stripped is ambiguous, as this illustrative value shows:
| FDA layout | Segmented | Padded to 11 digits |
|---|---|---|
| 4-4-2 | 1234-5678-90 | 01234567890 |
| 5-3-2 | 12345-678-90 | 12345067890 |
| 5-4-1 | 12345-6789-0 | 12345678900 |
An illustrative value, not a real product. The same ten characters pad to three different 11-digit codes, and nothing in the value says which layout you’re holding.
That is why TestGen’s Valid NDC rejects a bare 10-digit string by default. Treating it as valid is how the wrong product ends up in a report. It is also why the NDC tests default to Warning: a format mismatch between a hyphenated vendor feed and an unhyphenated master is usually a convention difference, not a broken value.
Then test each feed against itself
Reconciling across feeds assumes every feed arrived whole. Check that first.
| Symptom | Likely cause | What to run |
|---|---|---|
| Specialty pharmacy dispenses short this week | Partial file: 26,000 rows against a normal 40,000, load green | Volume bounds learned from that feed’s history |
| Patient count higher than dispenses suggest | Same patient under three de-identified tokens | None reliably: you cannot tell the tokens apart, so treat the count as an upper bound |
| Four records for one patient on one day | End-of-day status snapshot | Duplicate Rows on patient and day, after keeping the last status |
| Metric empty after a vendor change | Column renamed from RX_CNT to RxCount, nulls filled | Schema drift |
| One product 10x | Units became packages | Metric Trend against history |
| Group totals swap between baskets | NDC remapped to a different market basket | Aggregate Balance |
| Last quarter moved in the sales feed | Vendor restatement | Snapshot diff against the prior delivery |
Every row loads without an error. That is the whole problem.
The partial file and the renamed column come from the commercial data most likely to be wrong. The four-records-per-patient pattern comes from a small pharma whose specialty pharmacy sends four files a day, patient status, dispense, inventory, and demographics, and whose status file is an end-of-day snapshot: a patient who moves through every stage in one day arrives as four records. The market basket remap is the Aggregate Balance example from our testing post. Restatement in the syndicated feed gets its own treatment in the feed-by-feed reference.
What’s left is the real gap
Once the event date, the week, the unit, and the NDC level line up, and each feed has passed against its own history, the remaining difference between 867 and dispense is something worth explaining to the brand team. Before that, you are reconciling definitions.
Two more residuals show up after normalization. Copay redemptions that never reconcile to a dispense usually trace to a plan or bridge-file mapping change. And a chain that stops reporting stocking data reads as lost stocking rather than missing data. Both look like market movement until a test says otherwise.
Specialty pharmacy integration is almost always somebody’s afterthought, right up until it becomes the number the access team is betting the launch on.