Audience

Snowflake is a cloud data warehouse commonly used to centralize customer data for analysis, reporting, and downstream activation. The Snowflake Audience integration lets you forward mParticle audiences directly to Snowflake for activation, analysis, and enrichment.

This guide covers how to set up the integration, including Snowflake prerequisites, mParticle configuration settings, and the schema structure used to store audience data in Snowflake. It also explains upload frequency, audience deletion behavior, and how to query mParticle audience membership from within Snowflake.

Prerequisites

Authentication

Key pair authentication is required to connect to Snowflake. To set this up:

1. Create a new key pair by following the instructions in Key Management.
2. Assign the public key you created to your Snowflake user. You will do this when setting up Snowflake as described in the instructions below.

Set up Snowflake

All of the following steps can be completed from a Snowflake Worksheet.

You may use any names you choose for your warehouse, database, schema, role, user and account, as long as you provide the correct names to mParticle in the Configuration Settings. Note that mParticle does not support Snowflake’s double-quoted identifiers, so do not use double quotes when creating any of these objects.

Create a database and schema

-- Create a warehouse. Adjust size and AUTO_SUSPEND as needed.
CREATE WAREHOUSE sfAudTestWarehouse WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE;

-- Create database
CREATE DATABASE sfAudTestDatabase;

-- Create schema
CREATE SCHEMA sfAudTestSchema WITH MANAGED ACCESS;

Create a role and user

Once your database is ready, you need to create a dedicated role with permissions to manage the database.

-- Create new role:
CREATE ROLE sf_aud_data_loader;

-- Grant access to your warehouse, database and schema, and grant access to modify programmatic authentication methods
GRANT USAGE ON WAREHOUSE sfAudTestWarehouse TO ROLE sf_aud_data_loader;
GRANT USAGE ON DATABASE sfAudTestDatabase TO ROLE sf_aud_data_loader;
GRANT ALL ON SCHEMA sfAudTestSchema TO ROLE sf_aud_data_loader;

-- Create user with your new role. Make sure to set your new user as a LEGACY_SERVICE user to avoid Snowflake's MFA requirement.
CREATE USER sf_aud_test_user
  DEFAULT_ROLE = sf_aud_data_loader
  SET RSA_PUBLIC_KEY='YOUR-PUBLIC-KEY';

GRANT ROLE sf_aud_data_loader TO USER sf_aud_test_user;
GRANT MODIFY PROGRAMMATIC AUTHENTICATION METHODS TO ROLE sf_aud_test_user;

Snowflake IP Allowlisting

If you have created an IP allowlist as part of your Snowflake Network Policy, you can access a current list of IP addresses used by mParticle here.

Create a Snowflake audience output configuration in mParticle

1. Log into your mParticle account and navigate to Data Platform > Setup > Outputs in the left hand nav.
2. Select the Audience tab and add Snowflake as an audience output.
3. Enter a Configuration Name. When later connecting an audience to this output, this name is how you will identify the output configuration.
4. Under Access Credentials, enter the User ID, Account, and Key ID you created during setup.
5. Under Schema Settings, enter the Database Name, Data Warehouse Name, and Schema Name that correspond to the Snowflake objects you created.
6. Under Data Settings, configure how audience data is written to Snowflake. You can enable options like using the external audience name in table names, creating a global audience view, and forwarding user attributes. See the Configuration Settings table for details on each option.
7. Under Connection Settings, optionally select which user and device identities to include in outgoing data.
8. Click Save.

Connect an audience

After creating your Snowflake output configuration, you need to connect it to one or more audiences.

1. Navigate to Audiences in the left hand nav and select the audience you want to forward to Snowflake.
2. Click Connect Output and select the Snowflake output configuration you just created.
3. Optionally, use the Identities to Include setting to specify which user and device identities to include in outgoing data. If left empty, all available identities will be sent.
4. Click Save.

Once connected, mParticle will begin forwarding audience membership updates to Snowflake based on the upload frequency schedule.

Snowflake schema and data structure

All audience data is stored in Snowflake as tables. Each table represents changes to audience membership over time as users are added and removed; it does not represent the current state of the audience at any given moment.

Table naming

The table names are in the format {audience name}_{audience ID}. The audience name is either audience or the external audience name, depending on the Use External Audience Name in Table Name setting. If a table with the configured name does not exist, a new table will be created on the next update.

For example, if your audience ID is 7185 and the external audience name is New Users Low Engagement:

• If Use External Audience Name in Table Name is unchecked, data will be written to a table named audience_7185.
• If Use External Audience Name in Table Name is checked, data will be written to a table named NEWUSERSLOWENGAGEMENT_7185.

Global audience view

You can optionally merge audience membership updates into a single global view by enabling the Create Global Audience View setting. This creates a view that unions all audience tables in the schema.

Table fields

Query audience membership in Snowflake

Because each table stores a log of membership changes rather than current state, you need to query for the most recent record per user to get the current audience membership. Here are some example queries:

-- Selects the most recent CustomerIds for currently active members
WITH latest AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY mpid ORDER BY timestamp DESC) AS rn
    FROM {schema}.{audiencename}_{audienceid}
)
SELECT f.value::STRING AS CustomerId
FROM latest,
     LATERAL FLATTEN(input => CustomerId) f
WHERE rn = 1 AND audiencestatus = 'added';

-- Query the global view (unions all audience tables in the schema)
SELECT *
FROM {schema}.audience_view
ORDER BY timestamp DESC;

Upload Frequency

The Snowflake Audience integration uses Bulk Forwarding. Instead of uploading audience membership updates in real-time, mParticle compiles updates into a queue until a given amount of time has passed since the last upload, or until a certain number of updates are waiting to be sent.

By default, mParticle uploads to Snowflake whenever at least one of the following conditions are met:

• At least 50,000 messages are in the queue.
• At least 15 minutes have passed since the last upload.

Reach out to your mParticle Customer Success Manager if you need to adjust upload frequency.

Deleting an audience

When you delete an audience in mParticle, the corresponding table in Snowflake is not deleted. Instead, mParticle sends a new record for all users in the audience with a membership status of deleted.

Configuration settings

Connection settings