Nationwide Insurance – AWS Database Migration

Nationwide Insurance – AWS Database Migration

  • NorthBay Blogs

Nationwide Insurance – AWS Database Migration


Nationwide Insurance

Nationwide is one of the largest insurance and financial services companies in the world, focusing on domestic property and casualty insurance, life insurance and retirement savings, asset management, and strategic investments.


  1. Consolidate analytics systems and align with the over cloud strategy
  2. Migrate on-premises data warehouse to Cloud and move SAS analytics to cloud native solution

Solution Delivered to Meet Challenges

  1. A two-week POC was conducted in which all the potential problems were explored
  2. Data was provided as flat files in S3 RAW Bucket and JDBC/ODBC sources
  3. The solution contained multiple PySpark Glue jobs orchestrated using Step Functions
  4. One of the solutions regarding reporting of the data was created on EMR using PySpark due to dependency issues on Glue
  5. Converted the PoC to migration engagement

Architecture Diagram

AWS Services Used

  • AWS Redshift
  • AWS Athena
  • AWS Glue Catalog
  • AWS S3
  • AWS Spectrum
  • AWS Glue
  • AWS Lambda
  • AWS CloudWatch
  • AWS Stepfunctions

Database Workload Migration Details

  • 15 SAS Programs with SAS historical data
  • Approximately, 80 SAS Scripts to Glue PySpark were migrated
  • Sources and Targets:
    • SQL Server -> Redshift
    • SAS Business logic -> PySpark

Migration Patterns Used

  • Most of the SAS scripts were similar to SQL with some properitery functions.
  • Properitery functions were reverse engineered into native PySpark.
  • Above two were used in conjuction to create Spark Dataframes for calculations and were then exported to S3 in Parquet format.

Migration Tools Used

  • Glue (Ingestion)
  • Glue (Transformations)
  • Glue Notebooks (Data Validate & QA)
  • S3 buckets for storage of RAW, Optimized and Transformed data

Third-Party Tools Used

Apache Spark (PySpark)

About NorthBay – We are a fast-growing, 100% AWS focused onshore/offshore AWS Premier Consulting Partner, supporting our customers to accelerate the reinvention of their applications and data for a Cloud-native world. Our >350 AWS Certified Employees excel in developing and deploying Database & Application Migrations, Data Lakes and Analytics, Machine Learning/AI, DevOps and Application and Data Modernization/Development that drive measurable business impact.