Case Study - Modernizing Actuarial Data Preparation

Overview

Rural Mutual Insurance Company, headquartered in Madison, Wisconsin, has been serving the state’s residents since its founding in 1934 by the Wisconsin Farm Bureau Federation. Initially established to provide affordable auto insurance to Farm Bureau members, the company expanded its offerings over the years to include comprehensive farm insurance and later diversified into personal and commercial insurance products.

Today, Rural Mutual operates exclusively within Wisconsin, maintaining a network of over 150 agents across more than 100 locations statewide. This localized focus ensures that premiums paid by policyholders remain within the state, supporting the local economy and reinforcing the company’s commitment to the community.

Rural Mutual offers a range of insurance products tailored to the needs of Wisconsin residents, including:

• Agriculture and farm insurance
• Business insurance
• Personal auto insurance
• Homeowners and renters insurance

The company has consistently been recognized for its financial strength and operational excellence. It has earned an A+ (Superior) rating from AM Best and has been listed among Ward’s Top 50 property and casualty insurance companies in the United States for 16 consecutive years.

Throughout its history, Rural Mutual Insurance Company has remained dedicated to protecting Wisconsin’s farms, families, and businesses, embodying its motto of “Keeping Wisconsin Strong.”

The Problem

Rural Mutual faced a critical business challenge with their legacy data preparation process. For years, hundreds of Microsoft Access database files had been manually orchestrated by a single data preparation staff member to perform transformations, data cleaning, and aggregations. This manual process took several weeks to complete and was prone to errors due to the lack of documentation and automation.

To complicate matters, the data preparation specialist had announced their retirement, leaving RMIC without a qualified team member to take over this critical workload.

The Engagement

Our engagement with RMIC began with a thorough review of the Microsoft Access database files and queries. However, we quickly realized that our initial understanding was limited to a small portion of the overall collection. To gain a deeper understanding of the data preparation process, we pivoted to an automated approach. Our team developed software to extract data and query definitions, map out external table references between Access database files, identify ODBC connections to source systems, and more.

We then built a parser for Access-flavored SQL and a generator for Snowflake's SQL dialect, allowing us to mechanically translate all queries and relocate the process to Snowflake. Furthermore, we developed an entire process discovery tool that quickly assessed the complexity of any given data flow and sketched replacement dbt models that could replicate the same functionality in Snowflake.

The Solution

Our solution involved migrating Rural's data preparation process to a Snowflake instance, connected to their primary data source (Insurance Management System, or IMS) via Fivetran. Each data preparation process was re-modeled in dbt as a series of transformations based on month-end snapshots of tables in the IMS. We provided an automated way of validating the rebuilt data assets against the original Access process for 1:1 data parity.

The migration allowed Rural Mutual to transition most of their data preparation to Snowflake before the retiring specialist departed, ensuring business continuity and minimizing disruption to operations.

The Outcome

The outcomes of our engagement with Rural Mutual were significant:

• The manual data preparation process was reduced from taking 2-4 weeks to just 20 minutes.
• RMIC was able to run quarterly forecasting on a monthly basis, providing more data to their Actuaries faster.
• The transparent nature of the dbt model has engendered more trust in the data being supplied to other departments for ad hoc analysis.
• RMIC is now able to keep each and every month-end available for historical purposes due to the elastic storage layer in Snowflake.

By modernizing their data preparation process, Rural was able to improve operational efficiency, reduce costs associated with manual data processing, and increase the speed and accuracy of their data-driven decision-making. Our engagement has yielded lasting change and impactful returns for the business, positioning Acme Mutual Insurance for continued success in the future.