Send data to Snowflake (AWS)

Note

This topic contains information about configuring a destination that sends query results to Snowflake (AWS) using orchestrations. To configure a destination that sends audiences to Snowflake (AWS) using campaigns see this topic .

Snowflake is an analytic data warehouse to which Amperity can send tabular data to customer-managed instances of Snowflake that are located in the same cloud platform as Amperity.

Get details

Review the following details before configuring credentials for Snowflake (AWS) and before configuring Amperity to send email lists to Snowflake (AWS).

Credential settings Username Required A username with permission to access Snowflake (AWS). Password Required The password for the username. IAM access key Required Required. The IAM access key (along with the secret key) that that allows Amperity to autheticate to Snowflake. IAM secret key Required Requried. The IAM secret key (along with the access key) that allows Amperity to autheticate to Snowflake.

Required configuration settings Account name The account name is contained within the URL for the Snowflake instance and is a character code located before snowflakecomputing.com. For example: “ab12345”. Region ID Required The ID for the region in which the Snowflake account name is located. For example: “us-west-2”. Stage Required The Snowflake stage name, which is created by the CREATE STAGE command. For example: AMPERITY_ABCDEF.CUSTOMER_360.AMP_PROD_STAGE Warehouse Required The name of the Snowflake data warehouse. This is created by the CREATE WAREHOUSE command. For example: AMPERITY_WH Snowflake location The table to which data will be loaded. This location is defined using a period-delimited list of Snowflake database name, Snowflake schema name, and Snowflake table name. For example: SNOWFLAKE_DATABASE_NAME.SNOWFLAKE_SCHEMA.TABLE_NAME Create table? Use the Create table option when the table should be created in Snowflake if it is not already there. Truncate table? Use the “Truncate table” option when the contents of the table should be emptied prior to loading data. Drop table? Use the Drop table option to overwrite the table and apply an updated schema when the upstream segment or table changes.

Configure credentials

Configure credentials for Snowflake (AWS) before adding a destination.

An individual with access to Snowflake (AWS) should use SnapPass to securely share “username”, “password”, “IAM access key”, and “IAM secret key” details with the individual who will configure Amperity.

To configure credentials for Snowflake (AWS)

	From the Settings page, select the Credentials tab, and then click the Add credential button.
	In the Credentials settings dialog box, do the following: From the Plugin dropdown, select Snowflake (AWS). Assign the credential a name and description that ensures other users of Amperity can recognize when to use this destination.
	The settings that are available for a credential are determined by the credential type. For the “snowflake” credential type, configure settings, and then click Save. Username Required A username with permission to access Snowflake (AWS). Password Required The password for the username. IAM access key Required Required. The IAM access key (along with the secret key) that that allows Amperity to autheticate to Snowflake. IAM secret key Required Requried. The IAM secret key (along with the access key) that allows Amperity to autheticate to Snowflake. IAM role ARN The IAM role ARN (Amazon Resource Name) that is used by Amperity to access Snowflake.

Snowflake secure data sharing

Snowflake secure data sharing is not enabled by default. If your organization wants to use secure data sharing, your instance of Snowflake must be in the same region as the Amperity instance.

Amperity supports the following regions for Amazon AWS:

US West	us-west-2
US East	us-east-1
Canada	ca-central-1

Note

If your region is not listed you may make a request through your Amperity representative for Amperity engineering to add support for that region.

Configure objects

Amperity requires access to the customer’s instance of Snowflake. This requires write permissions to a warehouse object in Snowflake via a role and a user.

The following objects must be created in the customer’s instance of Snowflake:

1. A role via CREATE ROLE .

   The role must be granted permission to the warehouse via GRANT USAGE .

2. An external stage via CREATE STAGE .

   Note

   The external stage is used by the Amperity destination to stage data that is sent from Amperity in the Amazon S3 bucket. Snowflake picks data up from this location.

   The CREATE STAGE command defaults to CSV file types.

   When sending data from Amperity to Snowflake you must configure an external stage that points to the URL for the Amazon S3 bucket that is included with Amperity. (URL = 's3://bucket[/path/]')

3. A warehouse via CREATE WAREHOUSE .

4. A user via CREATE USER .

   The user must be added to the role via GRANT ROLE .

To configure Snowflake objects

To configure Snowflake objects use a Data Definition Language (DDL) command similar to:

CREATE ROLE AMPERITY;

CREATE DATABASE AMPERITY_DATABASE;

CREATE SCHEMA AMPERITY_SCHEMA.CUSTOMER_360;

CREATE STAGE AMPERITY_SCHEMA.CUSTOMER_360.AMPERITY_STAGE;

CREATE WAREHOUSE AMPERITY_WAREHOUSE WITH
  WAREHOUSE_SIZE = 'XSMALL'
  WAREHOUSE_TYPE = 'STANDARD'
  AUTO_SUSPEND = 600
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 2
  SCALING_POLICY = 'ECONOMY';

GRANT USAGE ON STAGE AMPERITY_STAGE TO ROLE AMPERITY;

GRANT USAGE ON WAREHOUSE AMPERITY_WAREHOUSE TO ROLE AMPERITY;

CREATE USER amperity
  PASSWORD = '********************'
  DEFAULT_WAREHOUSE = AMPERITY_WAREHOUSE
  DEFAULT_ROLE = AMPERITY;

GRANT ROLE AMPERITY TO USER amperity;

Note

You must send a customer who will connect directly to the Snowflake data warehouse the following information using a SnapPass link:

1. The URL for the Snowflake data warehouse.

2. The Snowflake username.

3. The password for that username.

4. Snowflake data warehouse name.

Configure user access

Users that will connect to Snowflake to use data in external applications, such as from Tableau, from Microsoft Power BI, and from Databricks, will need to be granted read-only access to the Snowflake data warehouse.

To configure user access to a Snowflake data warehouse

CREATE ROLE USER_READ;

GRANT SELECT ON ALL TABLES IN SCHEMA AMPERITY_DATABASE.CUSTOMER_360 TO ROLE USER_READ;
GRANT SELECT ON FUTURE TABLES IN SCHEMA AMPERITY_DATABASE.CUSTOMER_360 TO ROLE USER_READ;
GRANT USAGE ON WAREHOUSE AMPERITY_WAREHOUSE ROLE USER_READ;
GRANT USAGE ON DATABASE AMPERITY_DATABASE TO ROLE USER_READ;
GRANT USAGE ON SCHEMA AMPERITY_DATABASE.CUSTOMER_360 TO ROLE USER_READ;

CREATE USER USERNAME
  PASSWORD = 'PASSWORD'
  DEFAULT_WAREHOUSE = AMPERITY_WAREHOUSE
  DEFAULT_ROLE = USER_READ
  FIRST_NAME = 'USER_FIRST_NAME'
  LAST_NAME = 'USER_LAST_NAME'
  EMAIL = 'USER_EMAIL_ADDRESS';

GRANT ROLE USER_READ TO USER USERNAME

Configure storage integration

Amperity can send database table data to Snowflake. This output is written by Amperity to a customer-managed Amazon S3 bucket, from which Snowflake pulls the data.

Use secrets and keys to send table output to the customer-managed Amazon S3 bucket.

Note

You may need to mock the IAM role for the external ID and IAM user name. After the named stage is created in Snowflake, update the IAM role for the external ID and IAM user name.

The IAM role is unique to the Snowflake account. The external ID is unique to the Snowflake stage.

For each database and schema to be output to the Amazon S3 bucket, specify a database and schema via USE , create a stage via CREATE , and then GRANT USAGE .

To configure Snowflake storage integration

To configure Snowflake storage integration use a Data Definition Language (DDL) command similar to:

USE DATABASE <database-name>
USE SCHEMA <schema-name>
CREATE STAGE <stage-name>
  URL = 's3://<bucket>/<tenant-specific-path>/'
  CREDENTIALS = (AWS_ROLE = 'AMPERITY')
  ENCRYPTION = (TYPE = 'AWS_SSE_S3');
GRANT USAGE ON STAGE <stage-name> TO ROLE AMPERITY;
DESC STAGE <stage-name>;

Configure tables

For each table to be sent from Amperity, the Amperity role in Snowflake must be given permission. This requires permissions on both the database and the schema that contain the table. Usage rights to the database and schema do not grant access to the entire database or schema. Additional table-specific grants are required via a combination of GRANT USAGE , USE , and GRANT SELECT .

To configure Snowflake tables

To configure Snowflake tables use a Data Definition Language (DDL) command similar to:

GRANT USAGE ON DATABASE <database-name> TO ROLE AMPERITY;
USE DATABASE <database-name>
GRANT USAGE ON SCHEMA <schema-name> TO ROLE AMPERITY;
USE SCHEMA <schema-name>;
GRANT SELECT ON TABLE <table-name> TO ROLE AMPERITY;

Define a database export

Database tables (or even entire databases) can be sent from Amperity to Snowflake as a database export. A database export is configured from the Customer 360 page, and then added to an orchestration from the Destinations page. A database export can be an ad hoc process or be scheduled as a regular drop to an external filedrop location or data warehouse.

Add destination

Use a sandbox to configure a destination for Snowflake (AWS). Before promoting your changes, send a test audience, and then verify the the results in Snowflake (AWS). After the end-to-end workflow has been verified, push the destination from the sandbox to production.

To add a destination for Snowflake (AWS)

	Open the Destinations page, and then click the Add destination button. To configure a destination for Snowflake (AWS), do one of the following: Click the row in which Snowflake (AWS) is located. Destinations are listed alphabetically and you can scroll up and down the list. Search for Snowflake (AWS). Start typing “snow”. The list will filter to show only matching destinations. Select “Snowflake”.
	Select the credential for Snowflake (AWS) from the Credential drop-down, and then click Continue. Tip Click the “Test connection” link on the “Configure destination” page to verify that Amperity can connect to Snowflake (AWS).
	In the “Destination settings” dialog box, assign the destination a name and description that ensures other users of Amperity can recognize when to use this destination. Configure business user access By default a destination is available to all users who have permission to view personally identifiable information (PII). Enable the Admin only checkbox to restrict access to only users assigned to the Datagrid Operator and Datagrid Administrator policies. Enable the PII setting checkbox to allow users with limited access to PII access to this destination. Restricted PII access is enabled when the Restrict PII access policy option that prevents users who are assigned to that option from viewing data that is marked as PII anywhere in Amperity and from sending that data to any downstream workflow.
	Configure the following settings, and then click “Save”. Account name The account name is contained within the URL for the Snowflake instance and is a character code located before snowflakecomputing.com. For example: “ab12345”. Region ID Required The ID for the region in which the Snowflake account name is located. For example: “us-west-2”. Stage Required The Snowflake stage name, which is created by the CREATE STAGE command. For example: AMPERITY_ABCDEF.CUSTOMER_360.AMP_PROD_STAGE Warehouse Required The name of the Snowflake data warehouse. This is created by the CREATE WAREHOUSE command. For example: AMPERITY_WH Snowflake location The table to which data will be loaded. This location is defined using a period-delimited list of Snowflake database name, Snowflake schema name, and Snowflake table name. For example: SNOWFLAKE_DATABASE_NAME.SNOWFLAKE_SCHEMA.TABLE_NAME Create table? Use the Create table option when the table should be created in Snowflake if it is not already there. Truncate table? Use the “Truncate table” option when the contents of the table should be emptied prior to loading data. Drop table? Use the Drop table option to overwrite the table and apply an updated schema when the upstream segment or table changes.
	After this destination is configured, users may configure Amperity to: Use orchestrations to send query results Use orchestrations and campaigns to send audiences Use orchestrations and campaigns to send offline events to Snowflake (AWS).