Snowflake

Overview

This Destination Connector allows you to sync data to Snowflake.

Output Schema

Each stream will be output into its own table in Snowflake. Each table will contain the following additional 3 columns:

Snowflake Configuration

We recommend creating an Tarsal-specific warehouse, database, schema, user, and role for writing data into Snowflake so it is possible to track costs specifically related to Tarsal (including the cost of running this warehouse) and control permissions at a granular level. Since the Tarsal user creates, drops, and alters tables, OWNERSHIP permissions are required in Snowflake. If you are not following the recommended script below, please limit the OWNERSHIP permissions to only the necessary database and schema for the Tarsal user.

📘

Snowflake Naming Conventions

The Tarsal Snowflake destination will create tables and schemas using the Unquoted identifiers when possible or fallback to Quoted Identifiers if the names are containing special characters. See Snowflake's Identifier requirements for more information.

We provide the following script to create these resources. Before running, you must change the password to something secure. You may change the names of the other resources if you desire.

-- set variables (these need to be uppercase)
set tarsal_role = 'TARSAL_ROLE';
set tarsal_username = 'TARSAL_USER';
set tarsal_warehouse = 'TARSAL_WAREHOUSE';
set tarsal_database = 'TARSAL_DATABASE';
set tarsal_schema = 'TARSAL_SCHEMA';

-- set user password
set tarsal_password = 'password';

begin;

-- create Tarsal role
use role securityadmin;
create role if not exists identifier($tarsal_role);
grant role identifier($tarsal_role) to role SYSADMIN;

-- create Tarsal user
create user if not exists identifier($tarsal_username)
password = $tarsal_password
default_role = $tarsal_role
default_warehouse = $tarsal_warehouse;

grant role identifier($tarsal_role) to user identifier($tarsal_username);

-- change role to sysadmin for warehouse / database steps
use role sysadmin;

-- create Tarsal warehouse
create warehouse if not exists identifier($tarsal_warehouse)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- create Tarsal database
create database if not exists identifier($tarsal_database);

-- grant Tarsal warehouse access
grant USAGE
on warehouse identifier($tarsal_warehouse)
to role identifier($tarsal_role);

-- grant Tarsal database access
grant OWNERSHIP
on database identifier($tarsal_database)
to role identifier($tarsal_role);

commit;

begin;

USE DATABASE identifier($tarsal_database);

-- create schema for Tarsal data
CREATE SCHEMA IF NOT EXISTS identifier($tarsal_schema);

commit;

begin;

-- grant Tarsal schema access
grant OWNERSHIP
on schema identifier($tarsal_schema)
to role identifier($tarsal_role);

commit;

Tarsal Configuration

The following fields are used to configure the destination connector.

Loading Method

S3 Staging

Writes large batches of records to a file, uploads the file to S3, then uses COPY INTO table to upload the file. Recommended for large production workloads for better speed and scalability.

GCS Staging

Writes large batches of records to a file, uploads the file to GCS, then uses COPY INTO table to upload the file. Recommended for large production workloads for better speed and scalability.

Cloud Storage Staging

By default, Tarsal uses batches of INSERT commands to add data to a temporary table before copying it over to the final table in Snowflake. This is too slow for larger/multi-GB replications. For those larger replications we recommend configuring using cloud storage to allow batch writes and loading.

AWS S3

For AWS S3, you will need to create a bucket and provide credentials to access the bucket. We recommend creating a bucket that is only used for Tarsal to stage data to Snowflake. Tarsal needs read/write access to interact with this bucket.

Google Cloud Storage (GCS)

1. Create a GCS bucket, then run the script below.
   Notes:
   • The script must be run as the account admin for Snowflake.
   • Replace TARSAL_ROLE with the role used for Tarsal's Snowflake configuration.
   • Replace YOURBUCKETNAME with the bucket name
   • The stage name can be modified to any valid name.
   • gcs_tarsal_integration must be used

create storage INTEGRATION gcs_tarsal_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = GCS
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://YOURBUCKETNAME');

create stage gcs_tarsal_stage
  url = 'gcs://io_tarsal_test_staging'
  storage_integration = gcs_tarsal_integration;

GRANT USAGE ON integration gcs_tarsal_integration TO ROLE TARSAL_ROLE;
GRANT USAGE ON stage gcs_tarsal_stage TO ROLE TARSAL_ROLE;

DESC STORAGE INTEGRATION gcs_tarsal_integration;

2. Verify the query. It should should show a STORAGE_GCP_SERVICE_ACCOUNT property with an email as the property value.
3. Finally, add read/write permissions to your bucket with that email.