Snowflake Write-back Integration Implementation

Overview

This document outlines the process and requirements for integrating your marketing attribution platform with a customer's Snowflake instance. The integration will allow data from your platform to be written back into the customer’s Snowflake instance. Two primary approaches are covered:

1. RevSure Airflow Spark Job to Snowflake: Direct integration using Spark to load data into Snowflake.
   

2. RevSure Airflow to S3 to Snowflake: Data loaded to S3 first, then copied into Snowflake.

Flow Diagram

Write-back flow gets initialized after the Primary Connection Sync (e.g., Salesforce) finishes.

1. RevSure Airflow Spark Job to Snowflake: Data processing is managed by Spark jobs that directly write data into Snowflake via the Snowflake Spark connector.
   

2. RevSure Airflow to S3 to Snowflake: Data is first uploaded to an S3 bucket and then loaded into Snowflake via Snowflake's COPY INTO command.

Pre-requisites

The prerequisites listed below are to be provided by the customer to RevSure.

Common For Both Approaches

Snowflake Setup

1. The customer should have a Snowflake instance, database, schema, and tables where the data will be written.

2. A dedicated Snowflake user account with necessary permissions (INSERT, UPDATE, DELETE).

3. IP whitelisting or Private Link configuration to enable secure access from the platform’s network to Snowflake.

Security

1. Use encryption (TLS/SSL) for data in transit and at rest (Snowflake and S3).

2. Ensure that appropriate IAM roles and policies are in place to secure the interaction between services.

For RevSure Airflow to S3 to Snowflake

S3 Setup

1. AWS S3 bucket for intermediary storage.

2. AWS access credentials (Access Key ID and Secret Access Key) or role-based access for S3 uploads.

Technical Implementation

RevSure Airflow Spark Job to Snowflake

Architecture Overview

In this setup, after the Primary Connection finishes the Projection Pipeline, RevSure Airflow triggers a Spark job that processes and writes data directly into Snowflake using the Snowflake Spark connector. This is an efficient solution for large datasets and complex transformations.

Steps

1. Connection Setup:

   1. Snowflake JDBC URL: Use the JDBC URL provided by the customer for Snowflake.

   2. Credentials Storage: Store credentials securely using Airflow Variables or GCP Secret Manager.

Example JDBC URL:

jdbc:snowflake://<account>.snowflakecomputing.com/?user=<user>&password=<password>&warehouse=<warehouse>&db=<db>&schema=<schema>

2. Spark Job:

   1. Install the Snowflake Spark connector on the cluster and configure the data flow to Snowflake.

Example Scala Code:

val snowflakeOptions = Map(
"sfURL" -> "<account>.snowflakecomputing.com",
"sfUser" -> "<user>",
"sfPassword" -> "<password>",
"sfDatabase" -> "<database>",
"sfSchema" -> "<schema>",
"sfWarehouse" -> "<warehouse>"
)

val df = spark.read
.format("snowflake")
.options(snowflakeOptions)
.option("dbtable", "your_table")
.load()

RevSure Airflow to S3 to Snowflake

Architecture Overview

Data is written to an intermediate S3 bucket before being loaded into Snowflake using the COPY INTO command. This approach is simpler to manage, especially when dealing with moderate data volumes.

Steps

1. Data Upload to S3:

   1. Configure Airflow to upload files to S3 using AWS SDK (boto3).

Example Python Code for Airflow Task:

import boto3
def upload_to_s3(**kwargs):
s3 = boto3.client('s3')
s3.upload_file('/path/to/local/file.csv', 'bucket_name', 'file.csv')

2. Copy Data into Snowflake:

   1. Use the Snowflake COPY INTO command to load data from the S3 bucket into Snowflake.

Example SQL Command:

COPY INTO my_table FROM @my_s3_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"') ON_ERROR = 'skip_file';