Snowflake - Data

Snowflake’s Data Cloud is powered by an advanced data platform provided as Software-as-a-Service (SaaS). Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings. Store data about your customers, your business, or anything on Snowflake and export to a system of your choice to build analytics and reporting on top of your data.

You can integrate Snowflake and Blueshift using either of the following methods:

• Direct integration by using the Snowflake app in Blueshift's App Hub.
• Via Amazon S3

Direct Integration with Snowflake

With the direct integration of Blueshift and Snowflake, importing customer data, catalogs, and real-time customer interaction data (events) into your Blueshift account is done easily and builds an up to date 360 degree view of all your customers. You can also export campaign activity reports to Snowflake by using Snowflake's Secure Data Sharing feature.

Import data

Complete the prerequisites and then set up the direct integration with Snowflake. After you have set up the integration, you can create a task to import customer data, catalogs, or events data into Blueshift.

Watch this video for a quick overview of the integration.

Prerequisites

• Before you can integrate with Blueshift, you must have your Snowflake account set up.
• In order to import data from Snowflake, Blueshift must be able to access data. Decide whether you will provide Direct Access to the data (Read and Write Access) or through a Reader Account.
• If you want to set up incremental import, you can either set up Change Data Capture using Table Streams in Snowflake or use a specific column in your Snowflake tables to identify the increment. 
• During the set up, you will have to run some commands to create a user in Snowflake, assign the required permissions to the user, and give Blueshift permission to run APIs on your data warehouse. The user who runs these commands in Snowflake must have the CREATE and REPLACE Role privilege in Snowflake.

Watch this video for a quick overview of the integration.

Set up integration

To give Blueshift access to the data in Snowflake, complete the following steps:

1. Sign in to the Blueshift app, and click App Hub in the left navigation menu.
2. Go to All Apps, search for, and select Snowflake.
3. You can also go to Data Platform Apps and select Snowflake.
4. Click Configure to view all the configured adapters.
5. Click +ADAPTER to add an adapter.
   
   

   

6. Add a Name for the adapter.  If you have multiple adapters, the adapter name helps you to identify the integration.
7. For How would you like to import data from Snowflake?, select either Setup Direct Account Access or Setup Reader Account Access.
8. Select the Is streams enabled? option if you have enabled change data capture using streams.
9. Provide the details of the data warehouse and the user to access to the data warehouse. The commands required to create the required user in Snowflake are generated using this information.

   Note: You must enter the data warehouse details  (Warehouse name, Database Name, Schema Name, and so on) in exactly the same case as defined in Snowflake. Otherwise, an error might occur when you set up an import task.

   

   Host URL of your data warehouse	The URL to access your Snowflake warehouse. For most accounts, the format of the URL is <account_name>.region.snowflakecomputing.com. For example, https://ab12345.ap-south-1.aws.snowflakecomputing.com.  If you are using Snowflake’s Snowsight View, use any of the following ways to find the hostname: Go to Admin>Accounts>Locator to find the the hostname. Click the link icon besides the locator value of the Account you want to import data from. Click the account name in the account selector, located at the bottom of the left nav. The selector lists accounts that you have previously signed in to. Click the current account name in the list. Click the link icon to copy the Account URL. For more information, see Account Identifiers.
   Role of the user	The role of the user in Snowflake.
   Warehouse name	The name of the data warehouse in Snowflake.
   Database name	The database name in Snowflake.
   Schema name	The schema name in Snowflake.
   Username	The username of the user to access the Snowflake data.
   Password	The password for the user.

10. Go to Step 2 on the adapter screen. Use the commands provided to create a user in Snowflake and assign the required permissions to the user.

    Note: The user who runs these commands in Snowflake must have the CREATE and REPLACE Role privilege in Snowflake.

    

    1. Click the Copy to Clipboard icon to copy the commands.
    2. In the commands provided, edit the BLUESHIFT_PASSWORD and replace <ENTER_YOUR_PASSWORD_HERE> with the Password you provided in the data warehouse details in step 9.
    3. Log into your Snowflake account, go to Worksheets, and run the copied commands.
11. Go to Step 3 on the adapter screen and give Blueshift permission to run APIs on your data warehouse.
    
    

    

    1. Click Generate Public key.
    2. Click the Copy to Clipboard icon to copy the command.
    3. In your Snowflake account, go to Worksheets, and run the copied command.
    4. Click Check Access Status to verify that Blueshift can access the data.
12. Click Save.

Note: There is a one to one mapping between the User and the token/public key generated while setting up the adapter. If the same User is used for two different adapters then access would work only for the adapter which has the latest token/public key. Hence it is recommended to have one User per adapter.

Next steps

Create a task to import customer data, catalogs, or events data into Blueshift.

Export data

You can also export campaign activity reports from Blueshift into your Snowflake account by using Snowflake's Secure Data Sharing feature. You can then further analyze the data using business intelligence tools and gain more insights from it.

In addition to the ability to set up this export within Blueshift, you can also request a campaign activity export to Snowflake through our listing on the Snowflake marketplace.

Integration with Snowflake via Amazon S3

Blueshift also supports integration with Snowflake via Amazon S3. With the integration of Blueshift and Snowflake via Amazon S3, you can import data about customers and their interaction into your Blueshift account very easily. You can also export data related to campaign activities, recommendation, predictive score, and user profile attributes from Blueshift to Snowflake.

Import data from Snowflake via S3

You can import data from Snowflake tables as customers or events into Blueshift. You can do this using AWS S3. Just unload data from Snowflake on AWS S3, and then upload it into Blueshift.

1. Send data from Snowflake to AWS S3. For more information, see Unloading data from Snowflake into Amazon S3.
2. Setup Blueshift to periodically fetch data from S3 and upload to Blueshift.

Once you import the data from Snowflake to Blueshift, you can instantly use that data to build rich user profiles, use it in campaigns, or use it in syndications.

Export data to Snowflake via S3

You can export data about campaign activities from Blueshift into Snowflake. You can use this data in your Snowflake account to build reports and dashboards for performance analysis and effectiveness of your campaigns. 

You can also export recommendations, predictive scores and user profiles of segments into your Snowflake account. This data can then be used to measure the campaign performance on paid media platform.

Follow the steps mentioned below to export campaign activity data.

1. Setup Blueshift to export campaign activity data from Blueshift to AWS S3.
2. Setup Snowflake to load data from AWS S3. For more information, see Bulk loading data from Amazon S3 into Snowflake.

To export data of a segment into your Snowflake account, follow the steps below.

1. Setup a customer data syndication in Blueshift.
2. Setup Snowflake to load data from AWS S3. For more information, see Bulk loading data from Amazon S3 into Snowflake.