Data Superhighway: Streamlining SFDC to Snowflake Migration with AWS AppFlow

AWS AppFlow is a fully managed integration service provided by Amazon Web Services (AWS). It enables you to securely transfer data between various software-as-a-service (SaaS) applications and AWS services. AppFlow simplifies the process of building data flows by automating the data transfer process and handling the underlying complexities.

AWS AppFlow supports integration with a wide range of popular SaaS applications such as Salesforce, Slack, Marketo, ServiceNow, Zendesk, Snowflake, Microsoftsharepoint and many more. It allows you to easily connect and transfer data between these applications and AWS services.

S3, IAM role and Snowflake integration set up

• Create a S3 bucket in same AWS region as your Snowflake region.
• Configure Access Permissions for S3 bucket.
• Login on AWS console
• Navigate to IAM as shown below

• Navigate to Policy section and create a new policy using json give below .


                    Replace  with your S3 bucket name
{
   "Version": "2023-05-1",
   "Statement": [
       {
           "Effect": "Allow",
           "Action": [
             "s3:PutObject",
             "s3:GetObject",
             "s3:GetObjectVersion",
             "s3:DeleteObject",
             "s3:DeleteObjectVersion"
           ],
           "Resource": "arn:aws:s3::://*"
       },
       {
           "Effect": "Allow",
           "Action": [
               "s3:ListBucket",
               "s3:GetBucketLocation"
           ],
           "Resource": "arn:aws:s3:::",
           "Condition": {
               "StringLike": {
                   "s3:prefix": [
                       "/*"
                   ]
               }
           }
       }
   ]
}
                

• Choose Roles and Click the create role button on IAM


Make your selection based on account you would like to use and select S3 I as use case. Click next to policy. Select the policy you have created in above step.Go next window and click on Create role to finish role creation .

We will back and make changes on trusted entities for this role after Snowflake integration setup.

• Create a Cloud Storage Integration in Snowflake

Login with ACCOUNTADMIN role and create integration

                        CREATE STORAGE INTEGRATION <>
 TYPE = EXTERNAL_STAGE
 STORAGE_PROVIDER = 'S3'
 ENABLED = TRUE
 STORAGE_AWS_ROLE_ARN = <:role/>'
 STORAGE_ALLOWED_LOCATIONS = (<>>)
                     

• Execute the DESCRIBE INTEGRATION command to retrieve the ARN for the AWS IAM user that was created automatically for your Snowflake account
• Note values of STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID


Login back to AWS console and navigate role which you create on Step IAM Role.

Click on Edit trust relationship. Modify below json with values noted in step 6

                        
                            {
 "Version": "2023-05-1",
 "Statement": [
   {
     "Sid": "",
     "Effect": "Allow",
     "Principal": {
       "AWS": ""
     },
     "Action": "sts:AssumeRole",
     "Condition": {
       "StringEquals": {
         "sts:ExternalId": ""
       }
     }
   }
 ]
}
                        
                    

• Create a external stage

CREATE STAGE <>
 STORAGE_INTEGRATION = <>
 URL = '<>
10 . Create a ACCOUNT table on snwoflake
create or replace TABLE ACCOUNT (
ID VARCHAR(18),
ISDELETED VARCHAR(5),
MASTERRECORDID VARCHAR(18),
NAME VARCHAR(255),
RECORDTYPEID VARCHAR(18), ..... )