General 

The Snowflake Integration with ChurnZero creates a read-only connection between ChurnZero and your Snowflake Instance. With the Snowflake Integration you can import data from Snowflake into the following objects in ChurnZero:

 

Object	Action	Matches On
Accounts	Create New and Update Existing Records	Account External ID
Contacts	Create New and Update Existing Records	Contact External ID
Events	Create New Only	N/A
Activities	Create New Only	N/A
Custom Tables	Create New and Update Existing Records	Identifier Field created on the Custom Table. If no identifier field exists it will operate as Create New only.

 

ChurnZero utilizes Snowflake's SQL API to extract data from from Snowflake. 

Authentication

ChurnZero utilizes Snowflake OAuth to connect to the instance of Snowflake.  In order to successfully connect Snowflake an OAuth client must be created and then a read only user should be created that has access to that client.

Creating the OAuth Client

In Snowflake the following command can be used to create the OAuth Client via snowsql.  This will create a new OAuth client and then reveal the Host, Authorization Endpoint, Token Endpoint, Client ID, and Client Secret.

>create security integration CHURNZERO_OAUTH
   type = oauth
   enabled = true
   oauth_client = custom
   oauth_client_type = 'CONFIDENTIAL'
   oauth_redirect_uri = '<insert provided Redirect URI>'
   oauth_issue_refresh_tokens = true
   oauth_refresh_token_validity = 7776000
   blocked_roles_list = ('SYSADMIN')

Note: The parameter oauth_refresh_token_validity is a duration in seconds. 7776000 is the default duration which equates to 90 days.  After 90 days, ChurnZero will be unable to maintain the authorization and Snowflake will need to be manually reauthorized via the Snowflake Setup page in ChurnZero.

Once the client is created, the Client Id and endpoints can be revealed with the following command.  The needed Host property can be derived from the Authentication Endpoint.  For example, if the Authentication Endpoint is 'https://ybb88094.us-east-1.snowflakecomputing.com/oauth/authorize' then the host will be 'https://ybb88094.us-east-1.snowflakecomputing.com'.

desc security INTEGRATION CHURNZERO_OAUTH;

To reveal the Client Secret issue the following command.  Only one Client Secret is needed.

select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('CHURNZERO_OAUTH');

More information can be found here about creating a Snowflake OAuth Integration.

Creating a User

It is recommended to create a dedicated read only user for the integration with ChurnZero.  This user should then be granted access to the needed Warehouse, Schema, and Database.  

Authentication in ChurnZero

After filling out the Snowflake Setup form with information about the OAuth Client and identifying the Warehouse, Schema, and Database that are going to be used click the Authenticate button.  This will redirect you to Snowflake where you should authorize with the read only user previously created.

Note: If you have network restrictions created at Snowflake, please reach out to ChurnZero Support so they can provide you with the proper IP addresses to whitelist. The network policy should be created at the Account level to allow the ChurnZero IPs.

Creating an Import Job

To create a new import job, from your navigation panel go to Admin>Snowflake Integration. At the top of the page you will see your authorized  connection information, as well as status information about the connection, such as how many API calls have been made recently.  Click "Add Import Job"  to create a new import. 

Job Info

A modal will pop up to create your new import job. 

• Name- Name of the Import Job
• Description- Short description if you would like to add more information about what data this job is importing. 
• Snowflake Table- The table that you would like to import data from in Snowflake
• ChurnZero Table- The table that exists in ChurnZero where the Snowflake data will be imported into. 

The ChurnZero table must be created before you map data to it using the Snowflake Integration. If you do not see your desired object in the ChurnZero Table dropdown, you will have to add a new custom table. See our Custom Fields Documentation to learn how to add new tables and fields to ChurnZero. 

Table Lookups

Many times database tables are linked together via references, or field lookups. For example, on your contacts table, you may not have the Account External ID of the Account it is related to. Instead, you may have a lookup to your Account table. Table Lookups give you access to any fields referenced on that lookup table. 

Using the contacts example, you may have an Account Reference on your contact table that tells you which account that contact is associated to. Let's configure a lookup table by clicking "Add Lookup Configuration".

From here we will determine how to link our two tables together.

You will need to provide 3 pieces of information

• Lookup Table- The table are we looking up to that has the information you need 
  • In our example, we are looking up to the Accounts table, because that has our Account External ID
• Snowflake Field- The field on the current table (contacts) has the field that references your lookup table (accounts)
  • In our example, there is a field called contacts.account_ID that references the associated account
• Lookup Identifier- The field on the lookup table (accounts) matches that same snowflake field?
  • In our example, our account table has an accounts.id field. The value of this field on the account table matches the same value of contacts.account_id on the contact table. 

Once your lookup is configured successfully, when you go to map your fields, any fields on the lookup table will be available for mapping, allowing us to get to our desired fields.  Below, you can see we now have access to map fields from the Account Table, allowing us to use the "accounts.external_id" field.

 

Sync Settings

 

• Active- Turns the import job on or off. When the job is off, the import will not process at the scheduled time
• Schedule- Determine the cadence at which the import jobs will run. This can happen Hourly, Daily, Weekly, Monthly or Yearly. 
• Primary Key- This is the field on the Snowflake table that will be considered the unique identifier of the record (this is usually the same as the identifier field on the record of your table). This is needed to enforce a processing order for when we make a query to Snowflake. 
• Last Modified Date- A field on the Snowflake table that tells us when the record was last modified. 
  • When the import job runs on it's scheduled cadence, the integration will look at the field mapped here. Any record with a last modified date after the last import was run will be updated. This allows us to only look for new and updated records, saving the amount of data being queried on each import. 
  • This must  be a date/time field in Snowflake, not just a date field. This includes:

    • DATETIME

    • TIMESTAMP

    • TIMESTAMP_TZ

    • TIMESTAMP_LTZ

    • TIMESTAMP_NTZ

Filter

If no filter is applied, ChurnZero will attempt to import all records on the snowflake table. You can add a filter if desired, which will place parameters around the query to Snowflake, limiting the records that will be returned.

Our Logic Builder is an easy way to construct a query. Click "Add Condition" to begin constructing the query. Click the "Add Condition" button to add a filter. The below example shows a filter for only pulling back records where Opportunity Closed=True AND Name CONTAINS "CS Opps".

Once you have constructed your query, you can use the "Evaluate" button to do a preview of which records will be returned. This is also great for testing out new filter logic, as you can evaluate the changes before setting it as your active filter logic.

You can then edit your query, or set it as your Active filter. To do so, click "Activate New Logic". The constructed query will then be copied down from "New" to "Active". 

Field Mappings

Once you have your settings determined, it's time to map your fields. Your ChurnZero Table fields will appear on the left, and then you can select the appropriate Snowflake field from the dropdown.

Only fields marked as "Required" on the ChurnZero custom table will appear by default. Click "Manage Fields" to add or remove any fields to import job. 

Reference Fields

If you want a record to be visible on an Account or Contact record, you must have a Account and/or Contact reference field available on the ChurnZero table to map. The value should be the Account External ID or Contact External ID, respectively. If those fields do not live on your Snowflake table, but rather lookup to another table, you can use the Lookup Tables configuration to get to those values. If you do not see an Account or Contact External ID field on your table, you may have to add a new Reference field type in Admin>Custom Fields.

Identifier Fields

The Snowflake Integration has the ability to update existing records on Account, Contact, and Custom Tables. In order for us to know which records are new vs which are existing, we need to know an identifier field. For Accounts and Contacts, that has handled by the Account and Contact External IDs. However, for Custom Tables, there needs to be an established identifier field. This is a unique identifier of each record, usually a database identifier. ChurnZero will then use that value to determine if we should update an existing record, or create a new one. If you do not see an Identifier type field on your table, you will have to add it in Admin>Custom Fields. 

Managing Import Jobs

Edit Import Jobs

To edit an existing Job, hover over the record and click the pencil icon.

 

You can then edit your import job settings. If you edit an import job and add additional field mappings, you will receive a prompt asking if you would like to initiate a full sync for the next run. This will ensure that all records get updated with the new field mappings.

Initiating a full sync will reset the "Next Import" time for the job to the current time, prompting a sync of all data that meets the filter criteria within 15 minutes. If you choose to keep the partial sync, the job will run at the next scheduled cadence. 

Delete Import Jobs

If you no longer need an import job, you can delete it by clicking the trashcan icon.

This will only delete the import job. The data imported will stay in ChurnZero and would have to be deleted separately. 

Initiating a Full Sync

When you activate an Import Job, the import will then run on it's scheduled cadence. Once the next scheduled sync time comes, ChurnZero will query Snowflake for any records that meet the filter criteria and have been modified since the last sync run. This will ensure that we are only querying for newly modified records. If you want to sync all records, regardless of last modified date, you can initiate a full sync by clicking  on the circular arrow icon of the job list page.

Initiating a full sync will reset the "Next Import" time for the job to the current time, prompting a sync of all data that meets the filter criteria within 15 minutes.

Reorder Import Jobs

Some of your input jobs may have dependencies on each other. For example, you may have a job that imports contacts, but those contacts lookup to an Account. You would ideally want the Account information first so that the account exists before the contact import job runs. This is where Job Scheduling comes in. Jobs will run in the order that is set, depending on the cadence. So if you have all of your Jobs set to import hourly, once the top of the hour comes, the jobs will kick off in order. Use the "Reorder Import Jobs" button to change the order of your jobs.

 

 

Frequently Asked Questions

• Do you integrate with Snowflake Views?
  • We do not currently integrate with Views at this time, only tables. You can restrict access to the specific tables that ChurnZero can access with the permissions you establish via the authorization process
• How many import jobs are allowed?
  • There is a max of 25 import jobs per instance
• Can you integrate with multiple instances of Snowflake?
  • Yes we can accommodate multiple instances
• Can you authenticate with a user that has multi-factor authentication enabled? 
  • Yes, you can authenticate with a user that has MFA enabled. Please reach out to ChurnZero Support if you have any difficulty with authentication.  
• Is this a direct database connection?

  • No, we utilize Snowflakes SQL API which requires the creation an Oauth client in Snowflake. 

• Why can't I initiate a full sync on tables with no identifiers?

  • This is to guard from accidentally importing duplicates. If you want to do a one time import we recommend a manual import.
• Why is my custom table always creating new records?
  • The table needs to have an identifier type field created and properly mapped.
• Can I have a job update a CRM connected custom table?
  • This is not recommended. It is always advisable to update the source directly, rather than going through ChurnZero as a middle tier. For example, if you are trying to import to update Salesforce Opportunities, it is advised that you set up the connection between Snowflake and Salesforce, rather than Snowflake>ChurnZero>Salesforce. 
• What Snowflake data types are not supported?
  • We support all field types except Variant, Object, and Array field types.
• Can I check on the status of my imports?
  • Snowflake job imports will show in the dev console just like any other data import job. 
• Why is my Import Job returning the error - Invalid Warehouse Detected?
  • This error means that there has been a change to the Warehouse, or it has been reauthorized. This causes any Import jobs/tables that were previously connected to the Warehouse to fail. Please reach out to ChurnZero Support for assistance in recreating the Import job and authorization.