This guide walks through how to build a modern data architecture on Snowflake and AWS that reads open table format Iceberg data from AWS and combines it with Snowflake Native tables, transforms and enriches the data using Snowpark Connect for Apache Spark, and delivers AI-powered insights through Cortex Analyst and Snowflake Intelligence. It provides an end-to-end blueprint for combining open table formats, feature engineering, and natural language analytics to accelerate fraud detection and claims processing.

This solution leverages AWS and Snowflake services to create an end-to-end data pipeline for insurance claims analytics.

AWS Account (US-West-2)

Snowflake (US-West-2)

The Snowflake Horizon Catalog acts as the central governance layer throughout this entire process, ensuring every user and component (from Snowpark job to Snowflake Intelligence) only accesses data according to defined security policies.

1. Navigate to AWS Console → S3 → Create bucket
2. Enter the bucket name: <account-id>-us-west-2-insurance-claims-iceberg-data
3. Select the AWS Region: us-west-2 (must match your Glue catalog region)
4. Leave Block all public access enabled (default)
5. Enable Bucket Versioning (recommended for Iceberg table consistency)
6. Click Create bucket
7. Once created, create the following folder structure inside the bucket:
   • fnol_reports/ — First Notice of Loss reports
   • adjuster_notes/ — Adjuster notes and assessments
   • initial_estimates/ — Initial claim estimates

1. Verify that the Glue Data Catalog is enabled in your region (enabled by default in most regions)
   • Navigate to AWS Console → Glue → Data Catalog → Databases
2. Create a Glue Database
   • Navigate to AWS Console → Glue → Databases → Add database
   • Database name: insurance_claims_iceberg_glue_db
   • Location: s3://<account-id>-us-west-2-insurance-claims-iceberg-data/
   • Click Create database
     

1. Create an IAM Policy

   • Navigate to AWS Console → IAM → Policies → Create policy
   • Select the JSON tab and paste the following policy. Update the account-id placeholder before saving the policy.
   • Policy name: insurance-claims-iceberg-data-policy

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "GlueAndS3ScopedAccess",
               "Effect": "Allow",
               "Action": [
                   "glue:CreateTable",
                   "glue:DeleteDatabase",
                   "glue:GetTables",
                   "glue:GetPartitions",
                   "glue:UpdateTable",
                   "glue:DeleteTable",
                   "glue:GetDatabases",
                   "glue:GetTable",
                   "glue:GetDatabase",
                   "glue:GetPartition",
                   "glue:GetCatalogs",
                   "glue:CreateDatabase",
                   "glue:GetCatalog",
                   "s3:ListBucket",
                   "s3:GetBucketLocation"
               ],
               "Resource": [
                   "arn:aws:glue:*:*:table/*/*",
                   "arn:aws:glue:*:*:catalog",
                   "arn:aws:glue:*:*:database/insurance_claims_iceberg_glue_db",
                   "arn:aws:s3:::<account-id>-us-west-2-insurance-claims-iceberg-data"
               ]
           },
           {
               "Sid": "GlueAndS3GlobalAccess",
               "Effect": "Allow",
               "Action": [
                   "glue:GetTables",
                   "glue:GetCatalog",
                   "glue:GetTable",
                   "s3:ListAllMyBuckets",
                   "s3:AbortMultipartUpload",
                   "s3:ListMultipartUploadParts",
                   "s3:GetBucketCors",
                   "s3:GetBucketVersioning",
                   "s3:GetBucketAcl",
                   "s3:GetBucketNotification",
                   "s3:GetBucketPolicy",
                   "lakeformation:GetDataAccess"
               ],
               "Resource": "*"
           },
           {
               "Sid": "S3DataAccess",
               "Effect": "Allow",
               "Action": [
                   "s3:GetObject",
                   "s3:PutObject",
                   "s3:DeleteObject",
                   "s3:ListBucket",
                   "s3:GetBucketLocation",
                   "s3:ListAllMyBuckets"
               ],
               "Resource": [
                   "arn:aws:s3:::<account-id>-us-west-2-insurance-claims-iceberg-data",
                   "arn:aws:s3:::<account-id>-us-west-2-insurance-claims-iceberg-data/*"
               ]
           }
       ]
   }
   

2. Create an IAM Role

   • Navigate to AWS Console → IAM → Roles → Create role
   • Trusted entity type: AWS account
   • Select Another AWS account (enter a placeholder account ID for now; this will be updated after the Snowflake catalog integration is created)
   • Role name: insurance-claims-iceberg-data-role
   • Attach the insurance-claims-iceberg-data-policy created above
   • Click Create role

1. Enable Lake Formation

   • Navigate to AWS Console → Lake Formation → Get started
   • Accept the default settings to initialize Lake Formation in your account

2. Register the S3 Data Lake Location

   • Navigate to Lake Formation → Data lake locations → Register location
   • S3 path: s3://<account-id>-us-west-2-insurance-claims-iceberg-data/
   • IAM role: Select insurance-claims-iceberg-data-role (created in the previous step)
   • Permission mode: Select Lake Formation
   • Click Register location
   

3. Grant Data Permissions to the IAM Role

   • Navigate to Lake Formation → Data permissions → Grant
   • Principal type: IAM users and roles
   • IAM role: insurance-claims-iceberg-data-role
   • Database: insurance_claims_iceberg_glue_db
   • Table: All tables
   • Table permissions: Select, Describe
   • Click Grant
   

   

   

4. Register the S3 Data Location

   • Navigate to Lake Formation → Data locations → Grant
   • Storage locations: s3://<account-id>-us-west-2-insurance-claims-iceberg-data/
   • IAM users and roles: Select insurance-claims-iceberg-data-role (created in the previous step)
   • Registered account location should be the AWS Account ID
   • Checkmark Grantable
   • Click Grant
   

5. Verify Lake Formation Permissions

   • Navigate to Lake Formation → Data permissions
   • Confirm that insurance-claims-iceberg-data-role has all the permissions on all tables in insurance_claims_iceberg_glue_db

1. Create the Catalog Integration

   Run the following SQL in Snowflake (via Cortex Code CLI, Snowsight, or SnowSQL). Replace the placeholder values with your AWS account details:

   CREATE OR REPLACE CATALOG INTEGRATION glue_db_catalog_integration
     CATALOG_SOURCE = ICEBERG_REST
     TABLE_FORMAT = ICEBERG
     REST_CONFIG = (
       CATALOG_URI = 'https://glue.<region>.amazonaws.com/iceberg'
       CATALOG_API_TYPE = AWS_GLUE
       CATALOG_NAME = '<12_digit_aws_account_id>'
       ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS
     )
     REST_AUTHENTICATION = (
       TYPE = SIGV4
       SIGV4_IAM_ROLE = 'arn:aws:iam::<account_id>:role/insurance-claims-iceberg-data-role'
       SIGV4_SIGNING_REGION = '<region>'
     )
     ENABLED = TRUE;
   

2. Retrieve Snowflake's IAM User ARN and External ID

   Run the following to get the values needed for the AWS trust policy:

   DESCRIBE CATALOG INTEGRATION glue_db_catalog_integration;
   

   Note the values for:

   • IAM_USER_ARN — e.g., arn:aws:iam::<aws-account-id>:user/gqx31000-s
   • EXTERNAL_ID — e.g., IWB00000_SFCRole=7_Hz7Ebsyt1q+08P9P7XXXXXXX=

3. Update the AWS IAM Trust Policy

   Go back to AWS Console → IAM → Roles → insurance-claims-iceberg-data-role → Trust relationships → Edit trust policy and replace the <SNOWFLAKE_IAM_USER_ARN> and <SNOWFLAKE_EXTERNAL_ID> placeholders with the values from the previous step.

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "SnowflakeAccess",
               "Effect": "Allow",
               "Principal": {
                   "AWS": "<SNOWFLAKE_IAM_USER_ARN>"
               },
               "Action": "sts:AssumeRole",
               "Condition": {
                   "StringEquals": {
                       "sts:ExternalId": "<SNOWFLAKE_EXTERNAL_ID>"
                   }
               }
           },
           {
               "Sid": "GlueAndLakeFormationAccess",
               "Effect": "Allow",
               "Principal": {
                   "Service": [
                       "lakeformation.amazonaws.com",
                       "glue.amazonaws.com"
                   ]
               },
               "Action": [
                   "sts:AssumeRole",
                   "sts:SetContext"
               ]
           }
       ]
   }
   

4. Verify the Integration

   SELECT SYSTEM$VERIFY_CATALOG_INTEGRATION('glue_db_catalog_integration');
   SELECT SYSTEM$LIST_NAMESPACES_FROM_CATALOG('glue_db_catalog_integration');
   

   • The first command should return a success status confirming Snowflake can connect to AWS Glue
   • The second command should list the insurance_claims_iceberg_glue_db namespace

Clone the git repository into your local machine and navigate to the folder site/sfguides/src/modern-data-architecture-for-ai-powered-insurance-claims-insights/assets/

Run the setup.sql script by replacing the <account-id> with <01234554634> and access the data to upload into the stage from the data folder in this path

The setup script performs the following sub-steps in order:

1. Create a Notebook in Snowsight

   • Navigate to Snowsight → Notebooks → Create Notebook
   • Upload the notebook from the cloned repository located in assets folder
     
   • Also, Upload the fraud_model.py script to the notebook environment

2. Install the Snowpark Connect package

   • In the notebook, install the snowpark-connect package from the Packages panel
     

3. Run the Feature Engineering notebook

   • Run through Insurance-Claims-Feature-Engineering.ipynb notebook from SnowSight UI
   • This notebook reads from both Iceberg tables (via Catalog Linked Database) and Native tables, performs feature engineering and fraud scoring, and writes the enriched output to the CLAIMS_PROCESSED_FEATURES table

Run through the cortex_analyst_snowflake_intelligence_setup.sql script using CoCo CLI with the following prompt:

Run the cortex_analyst_snowflake_intelligence_setup.sql script to create the semantic view by uploading the claims_semantic_view.yaml from assets folder, setup Cortex Analyst, Cortex Agent and enable Snowflake Intelligence

This runs through the following steps:

1. Upload the semantic model YAML to SEMANTIC_MODEL_STAGE
2. Create the Semantic View using SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML
3. Create the Snowflake Intelligence database and schema (SNOWFLAKE_INTELLIGENCE.AGENTS)
4. Create the Cortex Agent with the cortex_analyst_text_to_sql tool pointing to the semantic view
5. Grant access permissions to the agent, semantic view, and underlying table

1. Verify by navigating to Snowsight → AI & ML → Snowflake Intelligence and selecting the "Insurance Claims Analyst" agent
2. Ask Questions
   • "Show me all high risk fraud claims"
   • "What is the total claim amount by loss type?"
   • "How many claims are under SIU investigation?"