Tealium Snowflake connector app

The Tealium Snowflake connector app helps you integrate Tealium AudienceStream with Snowflake. This app provides helpful SQL commands and stored procedures to help you prepare your account to receive data from Tealium and to create and maintain a flattened view of visitor data in Snowflake.

This app is intended for Snowflake account administrators and data engineers responsible for configuring Tealium connectors, creating landing tables, and managing ongoing data transformations.

How it works

The SQL commands and stored procedures in this app are provided to help with the following tasks:

Prepare your Snowflake account with the required users and permissions for Tealium to connect.
Create Snowflake landing tables with
VARIANT
columns to receive JSON data from Tealium.
Create and maintain a flattened view of visitor JSON data from landing tables.
Retrieve the Snowflake configuration values needed to set up the Tealium Snowflake Streaming connector.

Setup overview

If you are setting up Tealium and Snowflake for the first time, follow these steps in order.

If you already have the Tealium Snowflake Streaming connector set up and sending data to Snowflake, follow steps 3, 6, and 7.

Step 1: Assign the app admin role
Step 2: Create a data writer user for Tealium
Step 3: Create landing tables for incoming Tealium data
Step 4: Configure the Snowflake connector in Tealium
Step 5: Collect data from Tealium
Step 6: Create and update the flattened view
Step 7: Customize the flattened view

Step 1: Assign the app admin role

Grant the

app_admin

role to the Snowflake account role that will be used by all users managing the app:


GRANTAPPLICATIONROLE TEALIUM_SNOWFLAKE_CONNECTOR.app_admin TOROLE my_app_manager_role;

Step 2: Create a data writer user for Tealium

Tealium needs a Snowflake user to connect directly to the app and write data to a table. This user has permission inside the app to write to data landing tables, but does not have permissions outside this app.

Create a Snowflake user with permission to write to landing tables within this app and set an RSA public key for authentication purposes. In these examples, the username is

data_writer_user

, but you can use a different name.

In Tealium, inside the Snowflake Streaming Connector setup, click "Generate" to create the required keys. Then, assign this public key to your user.

For more information, see Assign the public key to a Snowflake user.


CREATEUSER data_writer_user
  RSA_PUBLIC_KEY ='MIIBIjxAxBkhkiGwBEFA...'
  MUST_CHANGE_PASSWORD =FALSE;

Assign the correct application permissions to the user. This is done by connecting an Account Role to the Application Role.


CREATEROLE tealium_app_data_writer_role;
GRANTAPPLICATIONROLE TEALIUM_SNOWFLAKE_CONNECTOR.tealium_app_data_writer TOROLE tealium_app_data_writer_role;
GRANTROLE tealium_app_data_writer_role TOUSER data_writer_user;

(Optional) Save the role and user to the app config:


USEDATABASE TEALIUM_SNOWFLAKE_CONNECTOR;
USESCHEMA CORE;
CALL update_app_config_entry('app_writer_user_name','data_writer_user');
CALL update_app_config_entry('app_writer_role_name','tealium_app_data_writer_role');

Step 3: Create landing tables for incoming Tealium data

Create a landing table for each audience or event feed you want to receive from Tealium. The landing table contains a

VARIANT

column to store the event or visitor JSON data received from Tealium. Run this command for each event feed and audience that you want to send from Tealium.

We recommend prefixing the table name with
events_
or
audience_
to make them easier to reference.

Examples

To create a landing table for the

Repeat Visitors

audience:


CALL create_new_data_table('audience_repeat_visitors');

To create a landing table for the

Purchases

event feed:


CALL create_new_data_table('events_purchases');

Step 4: Configure the Snowflake connector in Tealium

In Tealium, configure the Snowflake Streaming Connector using the role and user you created earlier.

Run the following stored procedure in the app to retrieve the required Snowflake configuration values:


CALL get_tealium_connector_config();

It outputs the values in this format:


{
"Account Identifier":"YOUR-ACCOUNT-ID.snowflakecomputing.com",
"Database Name":"TEALIUM_SNOWFLAKE_CONNECTOR",
"Schema Name":"CORE",
"Table Name":"",
"User Name":"data_writer_user"
}

Step 5: Collect data from Tealium

After the Tealium Snowflake Streaming connector is configured and published, data will begin appearing in your Snowflake landing tables. Wait for these tables to receive data before proceeding to the next step.

Step 6: Create and update the flattened view

The flattened view of the audience tables separates the properties of the visitor JSON object into columns. When visitor attributes are added or removed in Tealium, the columns in your Snowflake views become out of sync with the incoming data. Use these stored procedures to detect when the Tealium attributes have changed and to update the Snowflake views.

To create or update a view at any time, follow these steps:

Scan the visitor attributes
Scan the visitor landing table for added or removed attributes with this command:
```
CALL scan_data_for_new_visitor_attribute_names('audience_repeat_visitors');
```
Create or replace the view
Create (or replace) the flattened view using the newly detected attributes with this command:
```
CALL create_view_of_data_table('audience_repeat_visitors');
```

The resulting view is named with a

view_

prefix:


DESCRIBEVIEW data.view_audience_repeat_visitors;

Step 7: Customize the flattened view

A visitor record from Tealium might contain hundreds of attributes, so the flattened view can have more columns than you need. Use the

column_config

table to control which attributes appear as columns in the flattened view and what they are named.

After you update the

column_config

table, run this command to recreate the view with your custom configuration:


CALL create_view_of_data_table('audience_repeat_visitors');

Disable an attribute column

To omit an attribute column from the view, run this command:


UPDATE data.column_config
SET is_enabled =FALSE
WHERE COLUMN_NAME ='last_event_url';

Rename an attribute column

If you prefer a different name for a column than what is set in Tealium, use this command to change it:


UPDATE data.column_config
SET COLUMN_NAME ='high_event_count'
WHEREKEY='Many Events Audience (3 or more)';

Troubleshooting

Preview data

Check that you are receiving the data you expect in the view:


SELECT*FROM data.view_audience_repeat_visitors
LIMIT1000;

Confirm there is data in the landing table:


SELECT*FROM data.audience_repeat_visitors
LIMIT1000;

Check RSA public key

Verify that the data writer user has an RSA key configured:


SHOWUSERS;

SELECT"login_name","has_rsa_public_key"
FROMTABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE"has_rsa_public_key"=TRUE;

Verify grants

Check that the correct privileges are applied:


SHOWGRANTSTOUSER"DATA_WRITER_USER";

Confirm Tealium integration

Check whether Tealium successfully created a channel in your Snowflake environment:


SHOWCHANNELSINDATABASE TEALIUM_SNOWFLAKE_CONNECTOR;

Stored procedures

To call any stored procedure in the app, use the database and schema, then call the procedure:


USEDATABASE TEALIUM_SNOWFLAKE_CONNECTOR;
USESCHEMA CORE;
CALL get_tealium_connector_config();

Or use the fully qualified name:


CALL TEALIUM_SNOWFLAKE_CONNECTOR.CORE.get_tealium_connector_config();

create_new_data_table()

Creates a landing table for the audience or event feed sent from Tealium. The table has two columns:

TIMESTAMP_TEALIUM
: A
TIMESTAMP_NTZ
column containing the relevant timestamp of the data.
DATA
: A
VARIANT
column containing JSON object values of the visitor or event sent from Tealium.


CALL create_new_data_table('audience_repeat_visitors');
CALL create_new_data_table('events_purchases');

create_view_of_data_table()

Creates or recreates the view of the flattened visitor attributes from the table with the JSON

VARIANT

column. Call this procedure after making changes to the

column_config

table to refresh the view. The name of the resulting view is the table name with the prefix

view_

.

The following Tealium visitor attribute types are flattened. Each audience or badge appears as a column with boolean values indicating whether the visitor belongs to the audience or has been assigned the badge.

Audiences
Badges
Strings
Numbers
Booleans


-- Create view_audience_repeat_visitors
CALL create_view_of_data_table('audience_repeat_visitors');

get_tealium_connector_config()

Returns the Snowflake configuration values required to set up the Snowflake Streaming connector in Tealium:


CALL get_tealium_connector_config();

The return value contains:


{
"Account Identifier":"YOUR-ACCOUNT-ID.snowflakecomputing.com",
"Database Name":"TEALIUM_SNOWFLAKE_CONNECTOR",
"Schema Name":"CORE",
"Table Name":"",
"User Name":"data_writer_user"
}