The Grouparoo Blog


Snowflake Source

Tagged in Connections Engineering 
By Brian Leonard on 2020-12-22

Grouparoo connects your Snowflake warehouse so you can use that data in all your tools. Bring in the data as profile properties. Use that to makes groups. Automatically, sync that to your marketing, sales, and operations tools.

Snowflake setup

You will need to know your connection details for your for your Snowflake setup.

The only thing that might be unexpected is the need for a "warehouse" compute server. That lives here in the Snowflake console:

Warehouse setup

Create a Grouparoo app

In Grouparoo, you now need to teach the system about your Snowflake setup. This is done through creating an app.

Creating Grouparoo App

For Snowflake, this starts with the account. This is the name assigned by Snowflake and is the in the url when you are accessing the console. For example, xyz12345.us-east-1 or xy12345.us-east-2.aws.

The rest are normal database credentials, other than the warehouse server mentioned above.

Create a Grouparoo source

With the app created, you will now have two new new possible sources.

Two  types of sources: query and table

There are two possible ways to bring in data from Snowflake: Table and Query. Let's talk about each one.

Table Source

With a Snowflake Table source, the user can note which data they want to pull in and not have to know how to write the correct SQL.

Pick a table to use

You pick which table contains the information you want to make profiles properties from. In this case, I've chosen the PURCHASES table, which contains information about which fruits a customer has bought. You can see a preview of the data available.

Make mapping to existing properties

Here we have taught Grouparoo how to map this to profiles that are currently in our system. In this case, the PROFILE_ID in the table maps to the existing userId profiles property.

Now, we can make a profile property from the data in this table. Let's say we want to know their lifetime value (LTV) in fruit purchases. That would look like this:

Create a sum of the purchases made

We can also add filters here. Maybe we only want their LTV for apples and not all the fruits.

Create another property that filters to specific purchases

Table schedule

An important part of Grouparoo is that these properties we just created will always be up to date. When they are up to date, this changes what groups they are in and what is sent to your destinations.

Grouparoo accomplishes this through creating a schedule. In our Table source case, you only have to teach it which column to use.

Running a schedule to import purchases

In this case, by picking the stamp column, when that updates to a newer value (or a new row is added), the respective profiles Fruit LTV fields will be updated. Now everything is always up to date.

Query Source

Not every use case can be solved without writing SQL, so Grouparoo also has a way to put whatever query you want to create properties. This can be useful when there are transformations or multiple JOINs needed to get the right data.

With this source, you can write any SQL you want to create a profile property.

Query to create a property

Results

Now, every profile has these properties. Those can be used to create groups.

All of the profile and group membership data can then be used to keep your destinations up to date. We have seen people using Snowflake to power Salesforce, Marketo, Mailchimp, Zendesk, and more.

Implementation Details

The weirdest thing about Snowflake seems to be it's preference and default to uppercase table and columns names. This was the first time, we'd seen that an had to update some assumptions from our other implementations (Postgres, Redshift, BigQuery).

All of that was refactored into a single implementation of all SQL sources that is shared between all of the above. The app-templates plugin now implements a table and query template. Each database connector now only implements their nuances. This should make future plugins even easier.


Stay up to date

We will let you know about our launch and new content.

Share this post

Twitter Logo