Documentation / Product / Integrations / Snowflake

Snowflake: Bulk Audience Export

Export user profiles including user fields and audience memberships from Lytics to Snowflake.

Integration Details

  • Implementation Type: Server-side.
  • Implementation Technique: File based transfer.
  • Frequency: Batch every 24 hours. Each batch contains the entire audience, and replaces the previous table.
  • Resulting data: User fields will be exported to Snowflake as rows in a Snowflake table.

The resulting Snowflake schema is determined automatically by the workflow and will consist of the following Snowflake types according to the field's type in Lytics:

When a job is started, the workflow will:

  1. Create a temporary table in Snowflake with the appropriate schema of the form USERS_{audience_slug}_{unix_timestamp}.
  2. Scan the audience for export, and load in CSV format to the Lytics-managed GCS storage integration for your account (see setup instructions below).
  3. Load the data from GCS to your Snowflake account via COPY INTO.
  4. Once the load is complete, the temporary table will be renamed to a permanent table of the form USERS_{audience_slug}. NOTE: if a table of this name already exists in the target Snowflake schema with an identical schema to the temporary table, it will be dropped and replaced. If the schemas differ, the permanent table name will include an incremental suffix i.e. USERS_{audience_slug}1.
  5. If the export is configured to run continuously, the workflow will sleep for 24 hours before repeating steps 1 through 4.

Fields

If fields are selected during job configuration, only those fields will be included in the resulting Snowflake table. If no fields are selected, all fields on the profile will be exported.

Required Snowflake Setup

Exporting to Snowflake requires additional configuration in your Snowflake account. You will need to create a storage integration in your Snowflake account that references a Lytics-owned GCS bucket. You will then need to retrieve the GCS service account associated with your storage integration in order to authorize in Lytics. More information about creating GCS storage integrations can be found here. Note that step 3 in the Snowflake docs linked is not necessary, as the GCS bucket is owned and managed by Lytics.

In your Snowflake account, run the following queries to set up a storage integration for Lytics. Note: you will need ACCOUNTADMIN permissions to run the following queries.

First, create your storage integration:

create storage integration GCS_INT_LYTICS
  type = external_stage
  storage_provider = gcs
  enabled = true
  storage_allowed_locations = ('gcs://aid-{your-aid}-snowflake-exports-lyticsio/')

Note that you will need to replace {your-aid} with the AID for your Lytics account in the query above. If you don't know your AID, contact your account manager for assistance.

Second, retrieve your storage integration service account, you will need this to authorize the workflow:

desc storage integration GCS_INT_LYTICS

Finally, you will need to grant the role you authorized with permissions to use the newly created storage integration:

grant usage on integration GCS_INT_LYTICS to role {your-role}

Configuration

Follow these steps to set up and configure an export job for Snowflake in the Lytics platform. If you are new to creating jobs in Lytics, see the Jobs Dashboard documentation for more information.

  1. Select Snowflake from the list of providers.
  2. Select the Bulk Export job type from the list.
  3. Select the authorization you would like to use or create a new one.
  4. Enter a Label to identify this job you are creating in Lytics.
  5. (Optional) Enter a Description for further context on your job.
  6. Select the audience to export.

  7. Complete the configuration steps for your job. snowflake export configuration

  8. Using the Database dropdown menu, select the database you would like to import data from.

  9. From the Schema input, select the name of the schema to export to.
  10. (Optional) From the Export Fields input, choose a list of fields to export. If none are selected, all fields on the profile will be included.
  11. (Optional) Select the Keep Updated checkbox to export the audience continuously.
  12. (Optional) From the Time of Day input, select the time of day to start continuous exports.
  13. (Optional) From the Timezone input, select a timezone for time of day.
  14. Click Start Export.