Snowflake Streaming Destination Beta

Snowflake Streaming is a powerful tool for handling real-time data streams. It allows you to stream data rows directly in Snowflake tables with minimal latency.

Find the open source code for this destination in the GitHub repository.

When to use Snowflake Streaming

Snowflake Streaming is ideal for the following scenarios:

• Continuous data streams: If your data sources produce a steady stream of data in small batches, for example, clickstream data, event logs, etc.
• Low latency requirements: When your application requires immediate/frequent updates to the Snowflake table as new data arrives. Some use cases include real-time dashboards, streaming analytics, etc.
• Cost optimization for streaming data: Snowflake Streaming can potentially reduce costs for real-time pipelines as it allows micro-batch ingestion without needing a traditional Snowflake warehouse running constantly.

Prerequisites

Before setting up the Snowflake Streaming destination in RudderStack:

• You will need to have the ACCOUNTADMIN system role or any account with MANAGE GRANTS privilege.
• You will need to set the correct user permissions in Snowflake for RudderStack to send the data correctly.

The following sections will walk you through the process of setting up a database, a role, and a user in Snowflake.

1. Create a database

RudderStack recommends creating a new database to avoid conflicts with your existing data. Note that RudderStack creates its own tables within this database while storing your events.

Alternatively, you can create a new database by running the following SQL command:

CREATE DATABASE "<DATABASE_NAME>";

2. Create a role for RudderStack

To create a new role with the required permissions to load your data into the above database, run the following SQL commands in the exact order. Make sure to replace the placeholder names with your preferred names.

1. Create a new role called <ROLE>:

CREATE ROLE "<ROLE>";

2. Grant access to the database <DATABASE_NAME>:

GRANT USAGE ON DATABASE "<DATABASE_NAME>" TO ROLE "<ROLE>";
GRANT CREATE SCHEMA ON DATABASE "<DATABASE_NAME>" TO ROLE "<ROLE>";
GRANT ALL ON ALL SCHEMAS IN DATABASE "<DATABASE_NAME>" TO ROLE "<ROLE>";

You can also create a custom role with the necessary permissions and specify it in the dashboard settings while setting up the Snowflake Streaming destination in RudderStack.

3. Create a user

Use the below query to create a Snowflake user that connects RudderStack to your warehouse.

Make sure to replace the placeholder names with your preferred values.

CREATE USER "<USER_NAME>"
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_ROLE = "<ROLE>"
  PASSWORD = "<STRONG_PASSWORD>";
GRANT ROLE "<ROLE>" TO USER "<USER_NAME>";

Setup

If your sources send data to different schemas, RudderStack recommends creating multiple Snowflake Streaming destinations (each with a different namespace) and connecting the relevant sources accordingly.

1. In your RudderStack dashboard, add a source. Then select Snowflake Streaming from the list of destinations.
2. Assign a name to your destination and click Continue.

Connection settings

Snowflake account ID examples

The below table illustrates the slight differences in the account IDs depending on the various cloud providers. See the Snowflake documentation for more information on the account locator formats depending on your region or cloud provider.

Advanced settings

Consent settings

IPs to be allowlisted

To enable network access to RudderStack, allowlist the following RudderStack IPs depending on your region and RudderStack Cloud plan:

All the outbound traffic is routed through these RudderStack IPs.

Migrate from Snowflake destination

This section highlights the steps for transitioning from the Snowflake destination to the Snowflake Streaming destination for storing your events.

Pre-migration steps

Before you migrate to the Snowflake Streaming destination, follow these steps to verify whether you can perform the migration successfully:

1. Set up a Snowflake Streaming destination with a new schema (configured using the Namespace setting).
2. Connect an existing source to this destination. Do not disconnect this source from the Snowflake destination.
3. Send some events and check the existing schema (associated with Snowflake destination) and the new schema (associated with the Snowflake Streaming destination).
4. If they are compatible, you can perform the migration successfully.

Case 1: Sources write to the same schema

This case is applicable in the below scenarios:

• If all the sources writing to the Snowflake destination use the same schema (namespace).
• If only one source is connected to that destination.

1. Create only one Snowflake Streaming destination in the RudderStack dashboard and specify the same namespace as the one used in the Snowflake destination.
2. Connect all the relevant sources to the Snowflake Streaming destination.
3. Disconnect the above sources from the Snowflake destination.

To avoid any event loss, make sure to connect all the relevant sources to the Snowflake Streaming destination before disconnecting them from the Snowflake warehouse destination.

Note that there is a time window between the connection/disconnection process (steps 2 and 3 listed above) where both the destinations deliver events. This should be fine if the Snowflake destination was configured with the merge functionality (Warehouse Append setting turned off) that deduplicates data while merging. Otherwise, there is a possibility of duplicates.

Case 2: Sources write to different schemas

If your sources send data to different schemas, RudderStack recommends creating multiple Snowflake Streaming destinations (each with a different namespace) and connecting the relevant sources accordingly.

FAQ

What is the difference between Snowflake Streaming and a traditional Snowflake warehouse?

The following table highlights the key differences between Snowflake Streaming and the traditional Snowflake warehouse:

Why am I not seeing the users table in the schema for identify events?

This integration sends the identify events exclusively to the identifies table and skips the users table entirely.

Questions? Contact us by email or on Slack