Transfer Data from Amazon S3 to Snowflake

If your data is currently sitting in Amazon S3, you can transfer it to Snowflake or another relational data warehouse using the ELT process (Extract Load Transform).

Automated Load Process

This automated load process moves data into Snowflake, which will allow you to use the Braze Looker Blocks to visualize that data in Looker to help drive insights and feedback into your Campaigns, Canvases, and Segments.

Once you have a Currents to S3 export set up and are receiving live events data, it is time to configure your live ELT pipeline in Snowflake by configuring the following components:

AWS(c) SQS Queues

Auto-ingest Snowpipes rely on SQS queues for sending notification from S3 to Snowpipe. This process is managed by Snowflake after configuring SQS.

Configure the External S3 Stage

In AWS, create a new public-private key pair with grants according to your organization’s security requirements.

Then, in Snowflake, create a Snowflake S3 Stage (called braze_data) as followis:

1
2
3
4
create or replace stage currents.public.braze_data
    url='s3://snowpipe-demo/'
    credentials = (AWS_KEY_ID = '...' AWS_SECRET_KEY = '...' );
show stages;

Next, define the AVRO file format for our stage.

1
create file format currents.public.currents_avro type = 'avro' compression = 'auto';
1
alter stage currents.public.braze_data set file_format = currents.public.currents_avro;

Finally, use the show stages; command to show your SQS information. The name of the SQS queue will be visible in a new column called NOTIFICATION_CHANNEL.

Create Bucket Events

In AWS, navigate to the corresponding bucket of the new Snowflake stage. Then, under the Properties tab, go to Events.

AWS Properties

In Events, create new events for each set of Currents Data, as needed (Messaging and/or User Behavior).

AWS Events

Check the appropriate box for the object create notifications, as well as the ARN on the bottom of the form (from the notification channel column in Snowflake).

Snowpipe Setup

In order for the above configuration to produce the correct tables, you must define the structure of the incoming data properly using the examples below and the structures determined in our Message Engagement or Messaging Events and/or User or Customer Behavior Events Currents documentation.

It is critical that your tables are structured in accordance to the Braze Currents schemas, as Braze Currents will continuously load data into them via specific fields with specific data types (a user_id will always be loaded as a string and called a user_id in Currents data).

First, create a table INTO which we will continuously load using the following structure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE USERS_BEHAVIORS_APP_FIRSTSESSION
    (
        id               STRING,
        user_id          STRING,
        external_user_id STRING,
        app_id           STRING,
        time             INT,
        session_id       STRING,
        gender           STRING,
        country          STRING,
        timezone         STRING,
        language         STRING,
        device_id        STRING,
        sdk_version      STRING,
        platform         STRING,
        os_version       STRING,
        device_model     STRING
    );

Then, create the AUTO continuous load pipe and specify

  1. which table to load and
  2. how to load the following table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE PIPE PIPE_USERS_BEHAVIORS_APP_FIRSTSESSION
                            auto_ingest=true AS
COPY INTO USERS_BEHAVIORS_APP_FIRSTSESSION
          FROM 
            (select $1:id::STRING,
            $1:user_id::STRING,
            $1:external_user_id::STRING,
            $1:app_id::STRING,
            $1:time::INT,
            $1:session_id::STRING,
            $1:gender::STRING,
            $1:country::STRING,
            $1:timezone::STRING,
            $1:language::STRING,
            $1:device_id::STRING,
            $1:sdk_version::STRING,
            $1:platform::STRING,
            $1:os_version::STRING,
            $1:device_model::STRING from @currents.public.braze_data/currents/dataexport.prod-01.S3.integration.INTEGRATION_ID_GOES_HERE/event_type=users.behaviors.app.FirstSession/);

First, create a table INTO which we will continuously load using the following structure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE "CURRENTS_PM"."PUBLIC".PUBLIC_USERS_MESSAGES_PUSHNOTIFICATION_OPEN (
        id STRING,
        user_id STRING,
        external_user_id STRING,
        time INT,
        timezone STRING,
        app_id STRING,
        campaign_id STRING,
        campaign_name STRING,
        message_variation_id STRING,
        canvas_id STRING,
        canvas_name STRING,
        canvas_variation_id STRING,
        canvas_step_id STRING,
        canvas_step_message_variation_id STRING,
        platform STRING,
        os_version STRING,
        device_model STRING,
        send_id STRING,
        device_id STRING,
        button_action_type STRING,
        button_string STRING
        );

Then, create the AUTO continuous load pipe and specify

  1. which table to load and
  2. how to load the following table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE PIPE PIPE_USERS_MESSAGES_PUSHNOTIFICATION_OPEN
                            auto_ingest=true
COPY INTO "CURRENTS_PM"."PUBLIC".USERS_MESSAGES_PUSHNOTIFICATION_OPEN
          FROM 
           (select $1:id::STRING,
            $1:user_id::STRING,
            $1:external_user_id::STRING,
            $1:time::INT,
            $1:timezone::STRING,
            $1:app_id::STRING,
            $1:campaign_id::STRING,
            $1:campaign_name::STRING,
            $1:message_variation_id::STRING,
            $1:canvas_id::STRING,
            $1:canvas_name::STRING,
            $1:canvas_variation_id::STRING,
            $1:canvas_step_id::STRING,
            $1:canvas_step_message_variation_id::STRING,
            $1:platform::STRING,
            $1:os_version::STRING,
            $1:device_model::STRING,
            $1:send_id::STRING,
            $1:device_id::STRING,
            $1:button_action_type::STRING,
            $1:button_string::STRING from @currents.public.braze_data/currents/dataexport.prod-01.S3.integration.INTEGRATION_ID_GOES_HERE/event_type=users.messages.pushnotification.Open/);

To see the types of analytics you can perform using Braze Currents, please consult our Looker Blocks.

WAS THIS PAGE HELPFUL?
New Stuff!