DataSync for Snowflake is a single-direction (ServiceNow to Snowflake) integration that Bulk Shares and Dynamic shares to synchronize data for the collection of ServiceNow data into a Snowflake data warehouse.
Prerequisites
Request the databaseConfig.json file for your DataSync Agent by contacting Perspectium Support.
The meshlet connects to Snowflake using a JDBC driver with a specified <user> and <password>. The user specified must have the role of SYSADMIN or a custom role of equivalent privileges in order to create objects (tables) and write records.
See Snowflake's list of default roles here.
Related to the meshlet's minimum database user requirements, the following Snowflake access control privileges are needed by the Snowflake role/user configured in the meshlet:
Object | Privileges |
---|---|
Virtual Warehouse | USAGE |
Database | USAGE |
Schema* | USAGE, CREATE TABLE, CREATE FILE FORMAT, CREATE STAGE |
Table^ | SELECT, INSERT, UPDATE, DELETE, TRUNCATE |
Stage | WRITE |
File Format | USAGE |
*The meshlet will executed the SHOW <objects> command. As long as the role/user has one privilege on the Schema (and it needs the ones listed above), then it will have the ability to execute this command.
^The meshlet will use the ALTER TABLE command to add new columns to tables when a new column is created in ServiceNow. The INSERT privilege allows executing this command. The meshlet uses MERGE for inserting, updating and deleting records from a table and having the above privileges allows it to run this command.
Procedure
To set up a DataSync for Snowflake integration, follow these steps:
Create database in Snowflake
In the Databases page in Snowflake, click Create... to create a new database. Then, fill in the Name field and click Finish.