Send data to Snowflake on Amazon AWS

Snowflake is an analytic data warehouse that is fast, easy to use, and flexible. Snowflake uses a SQL database engine that is designed for the cloud. Snowflake can provide tables as a data source to Amperity.

Amperity can send data to customer-managed instances of Snowflake that are located in the same cloud platform as Amperity in the following ways:

  1. To stages that are configured in the customer-managed instance

  2. Sharing directly between an Amperity-managed and a customer-managed instances

This topic describes the steps that are required to send databases, tables, and query results to Snowflake from Amperity:

  1. Get details

  2. Configure Snowflake objects

  3. Configure Snowflake storage integration

  4. Configure Snowflake tables

  5. Define a database export

  6. Add an Amazon S3 destination

  7. Add data template

Get details

Snowflake requires the following configuration details:

Detail one.

The username and password.

Tip

Most users are configured with an initial password that must be changed. Create the user, manually change the password, and then use the updated password to configure this data source.

Detail two.

The stage name, along with credentials that allow Amperity to send data to a customer-managed Amazon S3 bucket, which is the storage location to which Amperity stages data and from which Snowflake pulls data.

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

Detail three.

The Snowflake account name.

Detail four.

The region in which Snowflake is located.

Note

The Amperity instance of Snowflake should be located in the same region as the customer’s instance of Snowflake.

Detail five.

A list of table names in the Snowflake database to be sent from Amperity.

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.

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.

To add a database export for Snowflake

  1. From the Customer 360 page click Configure Exports. This opens the Database Exports page.

  2. Click Create New Export. This opens the Add Export dialog box.

  3. Add the name of the data export, and then click Add. This will add a draft data export to the Database Exports page.

  4. Open the menu for the draft data export, and then select Edit.

  5. From the Database drop-down, select a database.

  6. From the Entities list, select one (or more) database tables to add to the data export.

  7. Click Activate.

Add a Snowflake destination

Configure Amperity to send databases, tables, and query results directly to Snowflake.

To add a Snowflake destination

Step 1.

Open the Destinations tab to configure a destination for Snowflake. Click the Add Destination button to open the Destination dialog box.

Name, description, choose plugin.

Enter a name for the destination and provide a description. For example: “Snowflake” and “This sends databases, tables, and query results to Snowflake”.

From the Plugin drop-down, start typing “snow” to filter the list, and then select Snowflake.

Step 2.

Credentials allow Amperity to connect to Snowflake.

The credential type is set automatically. You may use an existing credential or you may add a new one.

Choose an existing credential or add credential.

Select an existing credential from the Credential drop-down.

– or –

Select Create a new credential from the Credential drop-down. This opens the Credential dialog box.

Choose an existing credential or add credential.

Enter the name for the credential, and then add a description.

The username and password should be the Amperity username and password created in Snowflake for the user security object.

Add the IAM Access Key and IAM Secret Key for the IAM role with permission to access the Amazon S3 bucket used for Snowflake stages.

When finished, click Save.

Step 3.

Each destination has settings that define how Amperity will deliver data to Snowflake. These settings are listed under the Settings section of the Destination dialog box.

Settings for Snowflake (AWS).

The following settings are specific to Snowflake:

Setting

Description

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

The region ID in Amazon AWS. For example: “us-west-2”.

Stage

The Snowflake stage name, which is created by the CREATE STAGE command. For example: AMPERITY_ABCDEF.CUSTOMER_360.AMP_PROD_STAGE.

Warehouse

The name of the Snowflake data warehouse. This is created by the CREATE WAREHOUSE command. For example: AMPERITY_WH.

Step 4.

Business users are assigned to the Amp360 User and/or AmpIQ User policies. (Amp360 User allows access to queries and orchestrations and AmpIQ User allows access to segments and campaigns.) A business user cannot select a destination that is not visible to them.

Business users – including users assigned to the DataGrid Operator policy – may have restricted access to PII.

What is restricted access to PII?

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.

You can make this destination visible to orchestrations and allow users with restricted access to PII to use this destination by enabling one (or both) of the following options:

Allow business users access to this destination.
Step 5.

Review all settings, and then click Save.

Save the destination.

Important

You must configure a data template for this destination before you can send data to Snowflake.

Add data template

A data template defines how columns in Amperity data structures are sent to downstream workflows. A data template is part of the configuration for sending query and segment results from Amperity to an external location.

To add a data template

Step 1.

From the Destinations tab, open the menu for a destination that is configured for Snowflake, and then select Add data template.

This opens the Add Data Template dialog box.

Step 1

Enter the name of the data template and a description. For example: “Snowflake” and “Send databases, tables, and query results to Snowflake.”.

Step 2.

Verify business user access to queries and orchestrations and access to segments and campaigns.

A business user may also have restricted access to PII, which prevents them from viewing and sending customer profile data.

Step 2.

If business user access was not configured as part of the destination, you may configure access from the data template.

Step 3.

Verify all configuration settings.

Verify settings for the data template.

Note

When the settings required by Snowflake were are not configured as part of the destination, you must configure them as part of the data template before making this destination available to campaigns.

Step 4.

Under Template Settings, specify the Snowflake Location. This is a period-delimited list of the Snowflake database name, the Snowflake schema name, and the Snowflake table name:

SNOWFLAKE_DATABASE_NAME.SNOWFLAKE_SCHEMA.TABLE_NAME

Note

Be sure that the table name is appended.

For example:

AMPERITY.CUSTOMER_360.TABLE_NAME

Specify the option to use for managing the table in Snowflake: create, drop, or truncate.

Option

Description

Create table

Use the Create table option when the table should be created in Snowflake if it is not already there.

Caution

Only use this option when Snowflake is not managed by Business Intelligence Connect and when user roles in Snowflake have permissions set on a per-table basis.

Drop table

Use the Drop table option to overwrite the table and apply an updated schema when the upstream segment or table changes.

Important

Always use this option when Snowflake is managed by Business Intelligence Connect or when user roles in Snowflake have permission to access all tables in the database.

Truncate table

Use the Truncate table option when the contents of the table should be emptied prior to loading data.

Caution

Only use this option when Snowflake is not managed by Business Intelligence Connect and when user roles in Snowflake have permissions set on a per-table basis..

Step 5.

Review all settings, and then click Save.

Save the data template.