Amica, founded in 1907, is the oldest mutual automobile insurer in the United States, with a proven record of customer service. Today they offer a portfolio of insurance products that includes auto, home, marine, life, and personal umbrella.

In the insurance industry, “core applications” refer to purpose-built systems that manage a carrier’s essential operations, including underwriting policies, tracking premiums, processing claims, and maintaining regulatory compliance. These systems serve as the system of record for insurance operations, and selecting the right platform is a foundational architectural decision. Amica entrusts Guidewire Cloud to provide these core capabilities across many of its insurance lines, allowing their engineering teams to focus on value-added services for policyholders.

As the system of record for Amica’s insurance operations, Guidewire Cloud has accumulated tens of terabytes of tabular data, which business users depend on for financial reporting and business intelligence. Previously, Amica would extract this data and provide access to business users via a relational database. But as the volume of data grew, they faced challenges with query performance, schema evolution, and point-in-time analysis. Amica was aware that Apache Iceberg — an open-table format for organizing data lakes in distributed storage — could address these challenges. Yet Iceberg tables require regular maintenance – compaction, snapshot management, and unreferenced file removal – to sustain a level of performance acceptable to Amica’s data consumers, which offsets the value of adoption. When AWS released Amazon S3 Tables, this calculus changed. In this post, you will learn how Amica reduced their ETL job runtimes by 80% by building a data lake for core insurance data using S3 Tables.

Legacy system

Before using S3 Tables, Amica used Amazon EMR to extract and transform Guidewire Cloud data, then loaded that data into a relational database, which served as the primary access point for business users. Over time, they encountered several challenges with this setup:

• Performance-Both the volume of data and the number of users increased, causing slower report times and timeouts. Adding read replicas of the database helped alleviate the problem, but this technique doubled their infrastructure costs.
• Schema evolution-Table schemas frequently changed, with new columns appended as insurance products evolved. Adapting a relational database to these changes required complex data management operations.
• Point-in-time analysis-Amica had to perform the costly and time-consuming process of loading database snapshots to facilitate month- or year-end reporting.

These challenges became more severe as the volume of data grew, so Amica wanted to reinvent their data pipeline.

Solution

To address these limitations, Amica chose to leverage Apache Iceberg, an open-source high-performance format for huge analytic tables, on top of their Parquet files. The team quickly faced several design decisions that had to be addressed. The solution would need to support hundreds of Iceberg tables; managing and fine-tuning this environment would require automation and tooling.

S3 Tables provided Amica with an alternative storage option that delivers the performance benefits of Apache Iceberg without the operational overhead. As a fully managed service, S3 Tables automatically handles table maintenance, compaction, and optimization—eliminating the need for custom tooling and automation.

This shift addressed the core challenges they experienced with their legacy system and allowed them to focus on delivering value to business users.

• S3 Tables eliminated the need for costly read replicas by providing a scalable data lake architecture that handles growing data volumes and user concurrency without degradation in query performance.
• S3 Tables abstracted the constituent data and metadata files that comprise Iceberg tables, eliminating those files as an operational concern.
• Schema evolution became seamless, as the Iceberg format natively supports adding new columns as insurance products evolve, without the complex data management operations required by relational databases.
• Point-in-time analysis became a differentiator. Iceberg’s time travel query capability enables month-end and year-end reporting without the costly and time-consuming process of loading database snapshots, providing instant access to historical data states.

Implementation

Figure 1: Amica’s core application data pipeline architecture, orchestrated by AWS Step Functions, with data access governed by AWS Lake Formation

1. At regular intervals, Guidewire delivers change data capture (CDC) records in Parquet format to an S3 general purpose bucket, located in a Guidewire-owned AWS account. Guidewire also delivers a JSON-formatted manifest to this bucket, which identifies the S3 prefix for new records. Guidewire grants Amica’s AWS account read-access to the bucket.
2. An EventBridge Scheduler invokes an AWS Step Functions state machine several times per day, which orchestrates the ingestion portion of the pipeline.
3. Within the state machine, a Lambda function reads a timestamp checkpoint from a DynamoDB table to identify the time of the last pipeline execution. It also creates a new checkpoint to be used by subsequent executions.
4. A Lambda function reads the manifest to identify the CDC records generated since the last execution.
5. An AWS Glue job transforms incoming Parquet files into two table sets. “Raw” tables contain unedited CDC records across tables. “Consolidated” tables capture each table’s state at the checkpoint. Each set resides in separate S3 table buckets with Lake Formation integration enabled.

Data Authorization

6. When the ingestion state machine finishes execution, a Lambda function records schema changes to a DynamoDB table and creates a ticket in the IT service management (ITSM) system. (Guidewire may introduce new columns but will not remove existing columns.)
7. A data steward reviews the ticket through the ITSM system and classifies new data columns. When the steward resolves the ticket, a Lambda function automatically creates or modifies the Lake Formation data filter based on those classifications.
8. Amica users belong to IAM Identity Center groups based on their data classification authorization. Lake Formation grants assigned to these groups control their Athena query access to S3 Tables data.

Try it yourself

AWS has published open-source sample code that implements a similar architecture for Guidewire CDA, which you can deploy in your own account as a starting point. The sample code follows the same end-to-end pattern described here — a Guidewire CDA manifest drives incremental AWS Step Functions ingestion into consolidated (“merged”) and append-only change-history (“raw”) tables in Amazon S3 Tables, with Athena access governed by AWS Lake Formation — and includes schema evolution, incremental checkpoints, and reconciliation against Guidewire’s row counts. It uses Apache Spark on Amazon EMR Serverless for the transform step, where Amica used AWS Glue, so it serves as a reference for the pattern rather than a copy of Amica’s pipeline. The sample code is provided under the MIT-0 license.

Impact

Before adopting S3 Tables, Amica operated four database instances: a writer and a reader for each of two table sets. By consolidating to two S3 Tables table buckets, Amica reduced monthly infrastructure costs by 83% across production and non-production environments. Their improved data pipeline eliminated the need for time-consuming UPDATE TABLE operations and instead took advantage of Apache Iceberg’s schema evolution capabilities. As a result, data pipeline runtimes improved by 5x, dropping from 20 hours to 4 hours.

Conclusion

Amica’s journey from a legacy relational database to S3 Tables demonstrates how a modern data architecture transforms core insurance operations. By adopting S3 Tables, Amica improved data pipeline efficiency while eliminating the operational overhead of managing relational databases and avoided the maintenance of standard Apache Iceberg tables. Since S3 Tables handled table maintenance, compaction, and optimization automatically, Amica’s engineering team could redirect their attention from operations to business innovation.

For insurance carriers facing similar challenges with growing data volumes from core applications, S3 Tables offers a compelling path forward. The combination of Apache Iceberg’s performance benefits with AWS’s fully managed service eliminates the traditional trade-offs between capability and operational complexity.

To learn more about Amazon S3 Tables, read the S3 Tables documentation. For guidance on implementing data lakes with Apache Iceberg, explore the AWS Lake Formation best practices. To implement a similar pipeline, you can deploy the open-source AWS sample code as a starting point for your own Guidewire CDA data lake.