Snowflake

Setting up the Snowflake destination connector involves setting up Snowflake entities (warehouse, database, schema, user, and role) in the Snowflake console and configuring the Snowflake destination connector using the Calabi Connect UI.

This page describes the step-by-step process of setting up the Snowflake destination connector.

Prerequisites

• A Snowflake account with the ACCOUNTADMIN role. If you don’t have an account with the ACCOUNTADMIN role, contact your Snowflake administrator to set one up for you.

Network policies

By default, Snowflake allows users to connect to the service from any computer or device IP address. A security administrator (i.e. users with the SECURITYADMIN role) or higher can create a network policy to allow or deny access to a single IP address or a list of addresses.

If you have any issues connecting with Calabi Connect please make sure that the list of IP addresses is on the allowed list

To determine whether a network policy is set on your account or for a specific user, execute the SHOW PARAMETERS command.

Account

SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;

User

SHOW PARAMETERS LIKE 'network_policy' IN USER <username>;

To read more please check official Snowflake documentation

Setup guide

Step 1: Set up Calabi Connect-specific entities in Snowflake

To set up the Snowflake destination connector, you first need to create Calabi Connect-specific Snowflake entities (a warehouse, database, schema, user, and role) with the OWNERSHIP permission to write data into Snowflake, track costs pertaining to Calabi Connect, and control permissions at a granular level.

You can use the following script in a new Snowflake worksheet to create the entities:

1. Log into your Snowflake account.

2. Edit the following script to change the password to a more secure password and to change the names of other resources if you so desire.

   Note: Make sure you follow the Snowflake identifier requirements while renaming the resources.

-- set variables (these need to be uppercase)
set airbyte_role = 'AIRBYTE_ROLE';
set airbyte_username = 'AIRBYTE_USER';
set airbyte_warehouse = 'AIRBYTE_WAREHOUSE';
set airbyte_database = 'AIRBYTE_DATABASE';
set airbyte_schema = 'AIRBYTE_SCHEMA';

-- set user password
set airbyte_password = 'password';

begin;

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

-- create Airbyte user
create user if not exists identifier($airbyte_username)
password = $airbyte_password
default_role = $airbyte_role
default_warehouse = $airbyte_warehouse;

grant role identifier($airbyte_role) to user identifier($airbyte_username);

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

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

-- create Airbyte database
create database if not exists identifier($airbyte_database);

-- grant Airbyte warehouse access
grant USAGE
on warehouse identifier($airbyte_warehouse)
to role identifier($airbyte_role);

-- grant Airbyte database access
grant OWNERSHIP
on database identifier($airbyte_database)
to role identifier($airbyte_role);

commit;

3. Run the script using the Worksheet page or Snowsight. Make sure to select the All Queries checkbox if using the Classic Console or select and highlight the entire query if you are using Snowsight.

Note: Our integration automatically creates the necessary schemas in your Snowflake destination database. To enable this, ensure the connection user has CREATE SCHEMA privileges on the target database. If you prefer to create schemas manually, that's supported—however, our connection user must have OWNERSHIP privileges on those schemas. This allows us to manage tables and other objects required for the integration to function properly.

Step 2: Set up a data loading method

Calabi Connect uses Snowflake’s Internal Stage to load data.

Make sure the database and schema have the USAGE privilege.

Step 3: Set up Snowflake as a destination in Calabi Connect

Navigate to the Calabi Connect UI to set up Snowflake as a destination. You can authenticate using username/password or key pair authentication:

Login and Password

Field	Description
Host	The host domain of the snowflake instance (must include the account, region, cloud environment, and end with snowflakecomputing.com). Example: accountname.us-east-2.aws.snowflakecomputing.com
Role	The role you created in Step 1 for Calabi Connect to access Snowflake. Example: AIRBYTE_ROLE
Warehouse	The warehouse you created in Step 1 for Calabi Connect to sync data into. Example: AIRBYTE_WAREHOUSE
Database	The database you created in Step 1 for Calabi Connect to sync data into. Example: AIRBYTE_DATABASE
Schema	The default schema used as the target schema for all statements issued from the connection that do not explicitly specify a schema name.
Username	The username you created in Step 1 to allow Calabi Connect to access the database. Example: AIRBYTE_USER
Password	The password associated with the username.
CDC deletion mode	Whether to execute CDC deletions as hard deletes or soft deletes. Hard deletes propagate source deletions to the destination. Soft deletes leave a tombstone record in the destination. Defaults to hard deletes.
JDBC URL Params (Optional)	Additional properties to pass to the JDBC URL string when connecting to the database formatted as key=value pairs separated by the symbol &. Example: key1=value1&key2=value2&key3=value3
Legacy raw tables (Optional)	Write the legacy raw tables format for backwards compatibility with older versions of this connector. See Output schema. The data format in _airbyte_data is fairly stable but there are no guarantees that other metadata columns will remain the same in future versions.
Calabi Connect Internal Table Dataset Name (Optional)	The schema used for Calabi Connect's internal tables. In legacy raw tables mode, the raw tables are stored in this schema. Defaults to airbyte_internal.
Data Retention Period (Optional)	The number of days of Snowflake Time Travel to enable on tables. A nonzero value incurs increased storage costs in your Snowflake instance. Defaults to 1.

Key pair authentication

Output schema

Calabi Connect outputs each stream into its own raw table in airbyte_internal schema by default (you can override this with the Calabi Connect Internal Table Dataset Name setting) and a final table with typed columns. Contents in the raw table are not deduplicated.

Note: By default, Calabi Connect creates permanent tables. If you prefer transient tables, create a dedicated transient database for Calabi Connect. For more information, refer to Working with Temporary and Transient Tables

Raw Table schema

The raw table contains these fields:

• _airbyte_raw_id
• _airbyte_generation_id
• _airbyte_extracted_at
• _airbyte_loaded_at
• _airbyte_meta
• _airbyte_data

_airbyte_data is a JSON blob with the event data. See [here] for more information about the other fields.

Note: Although the contents of the _airbyte_data are fairly stable, schema of the raw table could be subject to change in future versions.

Final Table schema

The final table contains these fields, in addition to the columns declared in your stream schema:

• _AIRBYTE_RAW_ID
• _AIRBYTE_GENERATION_ID
• _AIRBYTE_EXTRACTED_AT
• _AIRBYTE_LOADED_AT
• _AIRBYTE_META

Again, see [here] for more information about these fields.

Data type map

Calabi Connect type	Snowflake type
STRING	TEXT
STRING (BASE64)	TEXT
STRING (BIG_NUMBER)	TEXT
STRING (BIG_INTEGER)	TEXT
NUMBER	FLOAT
INTEGER	NUMBER
BOOLEAN	BOOLEAN
STRING (TIMESTAMP_WITH_TIMEZONE)	TIMESTAMP_TZ
STRING (TIMESTAMP_WITHOUT_TIMEZONE)	TIMESTAMP_NTZ
STRING (TIME_WITH_TIMEZONE)	TEXT
STRING (TIME_WITHOUT_TIMEZONE)	TIME
DATE	DATE
OBJECT	OBJECT
ARRAY	ARRAY
UNION	VARIANT
UNKNOWN	VARIANT

Precision and size limits

Snowflake has precision limits for numeric types:

• FLOAT: Standard 64-bit floating point value.
• NUMBER (INTEGER): Maximum 38 digits.

When a value exceeds the bounds of these types, Calabi Connect nulls it out. Values within the minimum/maximum boundaries but with excessive precision are rounded. In both cases, the _airbyte_meta column contains a changes entry to reflect this.

Snowflake also enforces size limits on text and semi-structured types:

• VARCHAR: Maximum 16 MB (UTF-8 encoded).
• VARIANT (used for OBJECT, ARRAY, UNION, and UNKNOWN types): Maximum 128 MB.

Values that exceed these size limits are nulled out, and the _airbyte_meta column records the change.

Schema evolution

This connector supports automatic schema evolution. When the source schema changes, the connector automatically adds new columns to destination tables, drops removed columns, and modifies column types as needed. The connector requires ALTER TABLE privileges on destination tables to support this feature.

Supported sync modes

The Snowflake destination supports the following sync modes:

Sync mode	Supported
Full Refresh - Overwrite	Yes
Full Refresh - Overwrite + Deduped	Yes
Full Refresh - Append	Yes
Incremental Sync - Append	Yes
Incremental Sync - Append + Deduped	Yes

note

In Legacy raw tables mode, deduplication is not performed. All sync modes that would normally deduplicate instead append records to the raw table without deduplication.

Snowflake tutorials

Now that you have set up the Snowflake destination connector, check out the following Snowflake tutorials:

• Build a data ingestion pipeline from Mailchimp to Snowflake
• Replicate data from a PostgreSQL database to Snowflake
• Migrate your data from Redshift to Snowflake
• Orchestrate ELT pipelines with Prefect, Calabi Connect and dbt

Troubleshooting

'Current role does not have permissions on the target schema'

If you receive an error stating Current role does not have permissions on the target schema make sure that the Snowflake destination SCHEMA is one that the role you've provided has permissions on. When creating a connection, it may allow you to select Mirror source structure for the Destination namespace, which if you have followed some of our default examples and tutorials may result in the connection trying to write to a PUBLIC schema.

A quick fix could be to edit your connection's 'Replication' settings from Mirror source structure to Destination Default. Otherwise, make sure to grant the role the required permissions in the desired namespace.

Namespace support

This destination supports namespaces. The namespace maps to a Snowflake schema.