Snowflake

Overview

Syniti Replicate allows you to replicate data from relational database tables to Snowflake using the following replication modes:

• Refresh or Snapshot Replication: A one-time complete replication from any major relational database source to Snowflake as a target, according to replication settings and scripts. You can schedule the replication, identify the columns to be replicated, and add scripts to transform data during replication. Source databases include Oracle, Microsoft SQL Server, IBM Db2 for i, IBM Db2 LUW, Sybase, Informix, MySQL, and so on.

  Note

  Syniti Replicate support Snowflake as a Source system only in the Refresh mode.

• Continuous Refresh: A regularly scheduled refresh replication as described above. The schedule is defined in the replication settings.

• One-Way Mirroring (or Change Data Capture (CDC)): A continuous update of replicated tables based on changes to the source database that have been recorded in the database server log. Typically, this involves an initial refresh operation, as described above, to set up the target table. Then you can define the replication settings to check the transaction log on the source database at regular intervals. Any changes found in the log are applied to data on the Snowflake platform.

Prerequisites

Before you begin, ensure that you have installed Snowflake ODBC driver version 3.0.3 or above. If not download and install this driver from here.

Note

This driver requires Microsoft Visual C++ Redistributable for Visual Studio 2015 installed in your system.

Limitations

• Geographic, Geometric, XML and UniqueIdentifier datatypes are not supported.

• Maximum precision for Numeric field is 31 using Bulk option.

• Double data type can store values between maximum 10-308 to 10+308 For more information check the Snowflake documentation Numeric datatypes.

• Snowflake is supported in Refresh mode as source and target, as target in Mirroring mode. Synchronization mode is not supported.

• Data Encryption is not supported for Snowflake.

• Data Masking is not supported in Syniti Replicate.

• Database and schema names containing special characters like slash or backslash are not supported.

User Permissions

When Syniti Replicate establishes an ODBC connection to Snowflake, it uses the default SYSADMIN user role on Snowflake. However, if your corporate security policy dictates that third-part applications should not have SYSADMIN privileges, it is possible to create a custom role with fewer privileges and use that instead.

Create a Role:
use role public;
create role replicationrole;

Create a User:
create user sdr password = '[your-password]' default_role = replicationrole
must_change_password = false;

Set permissions for this role:
grant all privileges on future tables in database [database-name] to role replicationrole;
grant select, insert, update, delete, truncate on future tables in database [database-name] to role replicationrole;
grant usage, modify, create table on all schemas in database [database-name] to role replicationrole;

Grant Role to the Syniti Replicate user:
grant role replicationrole to user sdr;

Create a Connection for Snowflake

Enter an ODBC connection string value in the Connection String field using the format below,

DRIVER={SnowflakeDSIIDriver};UID=myUser;PWD=myPassword;DATABASE=myDB;SERVER=myServer;

Note that clicking the ellipses (…) button in the Connection String field opens up a dialog box from the Snowflake ODBC driver that allows you to specify connection parameters but there can be an issue with saving the parameters. It is highly recommended that you specify the connection string by typing or pasting it in.

Create a Connection to Snowflake Using a Private Key

If you are using key-pair authentication (JWT-based), you do not need the password. Instead, you need to provide the private key file path or encrypted private key and the corresponding authenticator. For example,

DSN=;uid=;authenticator=SNOWFLAKE_JWT;private_key_file=;server=.snowflakecomputing.com;database=;schema=;warehouse=;role=;

In the Snowflake Connection Properties dialog box, click Edit and add your connection information.

You can find a step-by-step process to set up an ODBC connection using Key Pair Authentication on Microsoft Windows

Data Type Mapping  

When Snowflake is used as a target system, following is the mapping of Syniti Replicate data types to the corresponding data type in Snowflake.