What We Learned From the Agricultural Specialist Migration

In H2 2025, we took on an engagement that most migration consultancies would have walked away from. An agricultural specialist insurer – Farm, Ranch, and Equine lines – had acquired a book of business stuck on a legacy PAS with no documentation, no vendor support, and no data dictionary. The previous vendor had already tried and given up.

This was our engagement, delivered by our team. It's the experience that proved our methodology works – and the lessons from it are shaping everything we're building into KeystoneMigrate.

The starting position

The insurer had acquired a specialist book through an acquisition. The policies were live, premiums were being collected, claims were being paid – but the underlying system was a black box.

No data dictionary. The original vendor had been acquired twice since the system was built, and documentation had been lost in each transition. No API documentation. The system exposed data through batch exports and direct database queries, but nobody had a comprehensive understanding of the schema.

The policy classes – Farm, Ranch, and Equine – each had their own rating structures, endorsement patterns, and business logic. Some of this logic existed only in scheduled batch jobs that ran overnight. Some existed in stored procedures that hadn't been modified in a decade. Some existed only in the knowledge of underwriters who had learned the system's quirks over years of use.

The insurer needed the data migrated to a modern platform. They needed it done without disrupting live operations. And they needed evidence that the migration was accurate – because the book carried regulatory obligations and policyholder contracts that couldn't tolerate silent data corruption.

Discovery: rebuilding what didn't exist

Traditional migration discovery assumes you have a starting point: a data dictionary, vendor documentation, schema diagrams. We had none of that.

Instead, we used AI-powered schema inference to reverse-engineer the source system's data model from the actual data. DataArc mapping techniques allowed us to identify relationships between tables that weren't formally defined through foreign keys – relationships that existed only in application logic.

What we found:

• 47 distinct tables with meaningful policy data, many with column names that gave no indication of their purpose (columns like FLD_17, CALC_AMT_3, RSV_CD)
• Endorsement chains where the chaining logic was implemented in a batch job, not in the database schema. The database stored endorsements as flat records; the batch job applied them sequentially to reconstruct the current policy state.
• Custom rating algorithms for each policy class that existed only in stored procedures. Farm policies used acreage-based rating. Ranch policies used livestock count bands. Equine policies used individual animal valuation. None of this was documented outside the code.
• Regulatory reporting dependencies where bordereaux outputs were generated by a separate batch process that re-read the policy data and applied its own transformation logic – meaning the bordereaux didn't always match what the policy administration screens showed.

The discovery phase achieved 96% source-to-target data mapping. The remaining 4% were fields that turned out to be genuinely unused – legacy columns from features that had been decommissioned years earlier but never removed from the schema.

Execution: 100% programmatic, zero manual rekeying

Once the mapping was complete, we executed the migration entirely programmatically. No manual rekeying. No "we'll handle the exceptions by hand." Every record migrated through the same automated pipeline with full data lineage.

This matters more than it might sound. Manual rekeying is how most complex migrations handle edge cases – an underwriter sits at the target system and re-enters policies that the automated pipeline couldn't handle. The problem: manual rekeying has no audit trail, introduces human error, and is impossible to reconcile at scale.

Our approach:

• Endorsement chain reconstruction. We rebuilt the batch job logic that the source system used to apply endorsements sequentially. Each endorsement was migrated with its chain position and cumulative effect, not as an independent record. This meant the target system could reconstruct the full endorsement history for any policy.

• Policy class-specific validation. Farm, Ranch, and Equine policies each had their own validation rules derived from the rating algorithms we'd extracted during discovery. A Farm policy's premium was validated against acreage-based rating. An Equine policy's premium was validated against individual animal valuation schedules.

• Real-time anomaly detection. When the migration pipeline encountered a record that didn't match the expected pattern, it flagged it immediately rather than continuing and hoping it would resolve later. Each anomaly was investigated, root-caused, and resolved before the pipeline proceeded.

The entire migration ran within the insurer's data environment. Policy data never left their security perimeter. Full data lineage was maintained for every record – from source table to target table, with every transformation step documented.

Evidence: proving it worked

The final phase produced the evidence package that the board needed to approve the migration:

• Full-book reconciliation: Every policy in the source system accounted for in the target system. Policy counts, premium totals, and coverage summaries matched at the portfolio level.
• Endorsement chain verification: Every endorsement chain validated as a chain, confirming that the cumulative effect of sequential endorsements produced the correct current policy state.
• Reserve calculation comparison: Reserves re-derived from migrated data and compared against source. Variances identified and explained (in several cases, the migrated calculation was more accurate than the source).
• Bordereaux output matching: Bordereaux generated from migrated data and compared line-by-line against source bordereaux outputs.

The board didn't have to take our word for it. They had auditable evidence.

What we learned

1. AI-powered discovery is a genuine step change

The traditional approach to undocumented systems is to hire consultants who spend months interviewing stakeholders and manually tracing data flows. We compressed this dramatically using automated schema inference and relationship mapping.

This doesn't replace human expertise – the underwriters' knowledge of how Farm vs Ranch vs Equine policies actually worked was irreplaceable. But it meant we arrived at those conversations with a 96% complete data model instead of a blank whiteboard.

2. Zero manual rekeying isn't just efficient – it's a data integrity requirement

Every manual intervention is a potential data integrity issue. When you're migrating policyholder data that carries regulatory obligations, "we'll fix the exceptions by hand" isn't an acceptable approach. It might be faster in the short term, but it's impossible to audit and impossible to reconcile at scale.

3. Evidence generation must be built into the methodology, not bolted on

On previous programmes, we'd seen evidence generation treated as a post-migration activity. "The migration is done, now let's prove it worked." This approach fails because by the time you discover a problem during evidence generation, you've already committed to the migration.

In the agricultural specialist engagement, evidence generation was continuous. Every checkpoint produced reconciliation data. Every anomaly was resolved with documented reasoning. By the time we reached the final evidence phase, there were no surprises.

4. Insurance-specific tooling matters

General ETL platforms can move data. They can't validate that an endorsement chain preserves the correct contractual position. They can't verify that a reserve calculation re-derivation produces the same result. They can't generate bordereaux from migrated data and compare against source.

The agricultural specialist engagement confirmed that insurance migration needs purpose-built tooling. That's what we're building with KeystoneMigrate.

How this shapes KeystoneMigrate

Every feature in KeystoneMigrate's roadmap traces back to something we learned in this engagement:

• Automated schema inference – because the next undocumented system shouldn't require months of manual discovery
• Endorsement chain validation – because flat-record migration breaks insurance data
• Continuous evidence generation – because the board needs proof, not promises
• Policy class-specific validation rules – because Farm and Equine aren't the same, and neither are Marine and D&O
• Zero-rekeying pipeline architecture – because manual intervention is a data integrity risk

We delivered this engagement in 6 months. The methodology worked. Now we're building the product to make it repeatable.

---

Dealing with an undocumented legacy system or a migration that's stalled? Book a discovery call to discuss what we learned and how it applies to your book.