Snowflake

👍

The release of Snowflake integration will be in the next couple of days.

Snowflake is a popular cloud-based data storage platform that offers data warehousing, storage, and analytics services. Using our official, native Snowflake integration, you can power Bloomreach Engagement with data stored in your Snowflake tables.

You can import the following data from Snowflake:

• Customers
• Events
• Catalogs

The integration can automatically sync changes in the source data to Engagement as often as every 15 minutes.

This guide describes the integration's prerequisites, setup, working, and possible use cases.

📘

Note

This integration adds Snowflake as an additional data source to Engagement's existing import features. The following documentation is recommended to learn more about importing data:

• Imports, Importing customers and Importing events
• Catalogs setup guide and Importing records

🚧

Important

Contact your Customer Success Manager to enable Snowflake support for catalog imports in your project.

How the integration works

The integration connects natively to Snowflake and requires setting up the connection with Snowflake only once.

Once set up, it can import from tables, views, and the results of user-defined queries. When importing from a table or view with change tracking enabled, it can automatically import subsequent changes from Snowflake as often as every 15 minutes to keep your data in Engagement up to date.

Limitations

The Snowflake integration's delta updates don't support "delete" database operations. Deleting a record in Snowflake that was previously imported into Engagement won't cause that record to be deleted in Engagement on the next sync.

Prerequisites

Access requirements

You need a Snowflake account with account admin privileges to set up the integration on the Snowflake end. You can use either the SnowSQL CLI client or the Snowsight web interface.

The integration requires a Snowflake user to have access to the relevant account and database.

Data format requirements

Engagement has flexible data format requirements. When setting up an import, you map the source data format to the relevant data structure.

The following columns must be present in the source tables or views:

The following attribute data types are supported:

• Text
• Long text
• Number
• Boolean
• Date
• Datetime
• List
• URL
• JSON

Delta update requirements

Change tracking

The integration supports delta updates from tables and views to import changes in the source data since the previous import. This feature requires CHANGE_TRACKING to be enabled on the source table or view in Snowflake.

Table example:

ALTER TABLE CUSTOMER_100 SET CHANGE_TRACKING=TRUE;

View example:

ALTER VIEW VIEW_CATALOG_100 SET CHANGE_TRACKING=TRUE;

Customer update timestamp

As a best practice, we recommend adding an extra column to your customer table with a timestamp indicating when customer properties were last updated. The timestamp should be set to the current time on every change of customer data that gets imported to Bloomreach.

Map this column to updated_timestamp when setting up the import.

This will prevent the delta update from overwriting any customer property values that were tracked in Engagement since the previous import.

Set up the Snowflake integration

1. Go to Data & Assets > Integrations and click on + Add new integration in the top right corner.

2. In the Available integrations dialog, enter "snowflake" in the search box. The Snowflake Data Source integration should appear. Click on + Add integration.

3. In the next screen you'll configure your Snowflake connection.
   
   Enter all the information required to connect to your Snowflake account, including:

   Field	Description
   Account identifier	- The name of the account prefixed by its organization (for example myorg-account123). - Use the query SELECT CURRENT_ORGANIZATION_NAME() | CURRENT_ACCOUNT_NAME();\ to get your account identifier. - Alternatively, in Snowsight, click on your name in the bottom left, navigate to Account > View account details, and copy the Account identifier.
   Username	- The username of the relevant Snowflake account to connect with. - Alternatively, in Snowsight, navigate to Admin > Users & Roles to list all users.
   Warehouse	- The name of the warehouse to connect to. - Use the query SHOW WAREHOUSES; to list the available warehouses. - Alternatively, in Snowsight, navigate to Admin > Warehouses to list the available warehouses.
   Database	- The name of the database to connect to. - Use the query SHOW DATABASES; to list the available databases. - Alternatively, in Snowsight, navigate to Data > Databases to list the available databases.
   Schema	- The name of the schema to use. - Use the queries USE DATABASE <your_database_name>; and SHOW SCHEMAS;' to list the schemas in the database. - Alternatively, in Snowsight, navigate to Data > Databases and click on the relevant database to reveal its schemas.
   Role (optional)	- The user role that defines the access permissions for the connection. - If left empty, the default user role will be used.

   
   

4. In the Key pair dropdown, select Generate new key:

   

5. In the Generate new key pair dialog, enter a Key name and click on Generate key pair:

   

6. Copy or download the new key, then add the key to the relevant Snowflake user by running the following query in Snowflake:

   SQL

   ALTER USER <username> SET RSA_PUBLIC_KEY='<public_key>';
   

   Replace <username> and <public_key> with the appropriate values.

   

7. Select the new key you generated in the Key pair dropdown.

   

8. Click on Save integration. If all the information you entered is valid, you'll see a Success! popup:

   

You are now ready to import data from Snowflake into Engagement!

👍

Good to know

Removing the Snowflake data source integration from an Engagement project will NOT delete any data in the project that was already imported from Snowflake. However, this will cancel any future delta updates using the integration.

Import data from Snowflake

Import customers

1. Navigate to Data & Assets > Imports and click on + New import.

2. Select Customers.

3. Enter a name for the import (for example, "Snowflake customers import").

4. On the Database tab, in the SQL Integration dropdown, select the Snowflake integration.

5. Select Table, then select the table to import from in the Source Table dropdown.

6. Click on Preview data to verify the data source is working, then click Next.

7. Map your Engagement customer ID (typically registered) to the matching column in the Snowflake table using drag and drop, then click Next.

8. Optionally, map the update_timestamp field to the matching column if you don't want to overwrite customer property values tracked since the previous import.

9. Under Schedule execution for customers, select one of the following:

   Single import	A one-off import of all records.
   Repeated import	A scheduled, recurring import of all records.
   Sync updates	A scheduled, recurring delta import of changes since the previous import. Specify the schedule (every 15/30/45 minutes, 1 hour, 2 hours) and, optionally, the time range (start and end dates).

   
   

10. Click Finish to start the import.

👍

Good to know

Deleting imported customer profiles in an Engagement project will NOT trigger the recreation of those customer profiles during the next delta update unless the source record has changed.

Import events

1. Navigate to Data & Assets > Imports and click on + New import.

2. Select Events and select or enter the event type to import (for example, "view"), then click Next.

3. Enter a name for the import (for example, "Snowflake events import").

4. On the Database tab, in the SQL Integration dropdown, select the Snowflake integration.

5. Select Table, then select the table to import from in the Source Table dropdown.

6. Click on Preview data to verify the data source is working, then click Next.

7. Map your Engagement customer ID (typically registered) to the matching column in the Snowflake table using drag and drop, then click Next.

8. Under Schedule execution for customers, select one of the following:

   Single import	A one-off import of all records.
   Repeated import	A scheduled, recurring import of all records.
   Sync updates	A scheduled, recurring delta import of changes since the previous import. Specify the schedule (every 15/30/45 minutes, 1 hour, 2 hours) and, optionally, the time range (start and end dates).

   It depends on the case, but we generally recommend Sync Updates to keep both platforms synchronized. You will have what you have in your source of truth - Snowflake, in Bloomreach. This approach generates computation costs on your side, so use it only for the most crucial data. Use Single imports for cases where you want to bring some fixed data that don't change, and there is no need for regular imports.

9. Click Finish to start the import.

👍

Good to know

• When importing events, you can import one event type (for example, "view") per import, specified when setting up the import. You can import multiple event types (for example: "view", "purchase", "campaign") by setting up an import for each event type.
• Events in Bloomreach Engagement are immutable. While delta updates will import new events, they won't update previously imported events.

Catalog

1. Navigate to Data & Assets > Imports and click on + New import.

2. Select Catalog and select the catalog to import to, then click Next.

3. Enter a name for the import (for example, "Snowflake catalog import").

4. Under Import settings, select the Type:

   Full feed	Imports all data and replaces all existing records with the imported data.
   Delta feed (Replace items/rows)	Replaces existing records based on matching IDs or adds new records.
   Delta feed (Partial)	Partially updates data using columns of your data source without overwriting existing data if no matching column is found or adds new records if no matching IDs.

   
   

5. On the Database tab, in the SQL Integration dropdown, select the Snowflake integration.

6. Select Table, then select the table to import from in the Source Table dropdown.

7. Click on Preview data to verify the data source is working, then click Next.

8. Map your Engagement item ID (typically item_id) to the matching column in the Snowflake table using drag and drop, then click Next.

9. Under Schedule execution for customers, select one of the following:

   Single import	A one-off import of all records.
   Repeated import	A scheduled, recurring import of all records.
   Sync updates	A scheduled, recurring delta import of changes since the previous import. Only available if you selected Delta feed as import type. Specify the schedule (every 15/30/45 minutes, 1 hour, 2 hours) and, optionally, the time range (start and end dates).

   
   

10. Click Finish to start the import.

👍

Good to know

Deleting imported catalog items in an Engagement project will NOT trigger the recreation of those catalog items during the next delta update unless the source record has changed.

Permissions to set up an import

The following privileges are required:

• USAGE on the desired warehouse
• USAGE on the desired database
• USAGE on the desired database schema
• SELECT on the desired database
• SELECT on the desired database schema
• SELECT on FUTURE TABLES in the desired database schema
• SELECT on FUTURE VIEWS in the desired database schema

More about Snowflake access control.

📘

Imporant notes

Privileges are granted to roles, and roles are granted to users.

Having both super_admin (like ACCOUNTADMIN ) and read_only roles assigned to the same user results in super admin access (able to access everything); read_only is ignored.

If you want the user to have only read_only privileges, ensure the read_only role is the only one assigned to the user.

Streams have to be created before integration creation by higher-level role.

Delete data from Snowflake

Using our API, you can anonymize customers individually or in-bulk. If you want to delete customers, you can mark them with an attribute to be filtered out and delete them manually in the UI. You can also delete events by filtering only in the UI. Catalog items can also be deleted using our Delete catalog item API endpoint.

Example use cases

• One-time imports - Importing purchase history
• Regular delta imports - One-way synchronization of customer attributes