Snowflake

Setting up the Snowflake destination takes two parts: creating a warehouse, database, user and role for Pipeloom in Snowflake, then creating the destination in Pipeloom.

The Snowflake destination connector:

• Writes each stream into a typed table in the schema you choose.
• Supports every sync mode, including deduplication.
• Maps each source namespace to a Snowflake schema, and creates schemas as needed.

:::warning Use key pair authentication Snowflake is phasing out single-factor password sign-ins (Snowflake's announcement), enforcing strong authentication account by account between August and October 2026. Once it's enforced on your account, a destination that signs in with a username and password stops working. Use key pair authentication, as described below. :::

Before you start

You need a Snowflake account with the ACCOUNTADMIN role. If you don't have one, ask your Snowflake administrator to set one up for you.

Step 1: Create a key pair

Key pair authentication uses an RSA key pair (2048 bits or more) instead of a password. If you don't have one yet, create one with the openssl command line tool. For the details, see Snowflake's key pair authentication docs.

Create a private key

In a terminal, run one of these commands. Each writes a private key in PKCS#8 PEM format.

Unencrypted key (simpler; no passphrase needed when connecting):

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

Encrypted key (recommended for production; the key file is protected by a passphrase):

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes-256-cbc -inform PEM -out rsa_key.p8

Encrypted keys made with -v2 des3, as Snowflake documents, work too, as do the other key formats and algorithms Snowflake supports (see Snowflake's docs for the list). You can also set up key rotation to replace keys without downtime.

:::tip Snowflake recommends an encrypted private key, with a passphrase that meets your organization's security standards. Keep the passphrase somewhere safe. :::

Create the matching public key

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Keep the keys safe

Copy both files to a secure local directory and restrict access to the private key file. You're responsible for keeping the key secure while it isn't in use.

If key pair sign-in doesn't work, see Snowflake's troubleshooting docs.

Step 2: Create a warehouse, database, user and role for Pipeloom

Give Pipeloom its own warehouse, database, service user and role. The role gets OWNERSHIP so it can write data, and keeping everything separate lets you track Pipeloom's costs and control its permissions precisely.

1. Sign in to Snowflake.

2. Open a new worksheet and paste the script below. Rename the objects if you like, and replace <public_key_value> with the public key from Step 1.

   Note: new names must follow Snowflake's identifier rules.

-- set variables (these need to be uppercase)
set pipeloom_role = 'PIPELOOM_ROLE';
set pipeloom_username = 'PIPELOOM_USER';
set pipeloom_warehouse = 'PIPELOOM_WAREHOUSE';
set pipeloom_database = 'PIPELOOM_DATABASE';
set pipeloom_schema = 'PIPELOOM_SCHEMA';

begin;

-- create the Pipeloom role
use role securityadmin;
create role if not exists identifier($pipeloom_role);
grant role identifier($pipeloom_role) to role SYSADMIN;

-- create the Pipeloom service user with key pair authentication
create user if not exists identifier($pipeloom_username)
type = service
default_role = $pipeloom_role
default_warehouse = $pipeloom_warehouse;

grant role identifier($pipeloom_role) to user identifier($pipeloom_username);

-- assign the RSA public key to the service user
-- Replace <public_key_value> with the contents of your rsa_key.pub file (excluding the
-- -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- header/footer lines).
alter user identifier($pipeloom_username) set rsa_public_key='<public_key_value>';

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

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

-- create the Pipeloom database
create database if not exists identifier($pipeloom_database);

-- grant warehouse access
grant USAGE
on warehouse identifier($pipeloom_warehouse)
to role identifier($pipeloom_role);

-- grant database access
grant OWNERSHIP
on database identifier($pipeloom_database)
to role identifier($pipeloom_role);

commit;

3. Run the script from the worksheet or Snowsight. In the Classic Console, tick All Queries; in Snowsight, select the whole script first.

:::info Pipeloom creates the schemas it needs in the destination database, so the user needs CREATE SCHEMA on that database. You can create schemas yourself instead, but then the user needs OWNERSHIP on them, so it can manage the tables and other objects it writes. :::

:::info Keeping costs down Snowflake bills compute per second, and the warehouse resumes, and bills, each time Pipeloom loads data. We recommend a dedicated X-Small warehouse that suspends after one minute idle: the smallest compute size, shut down soon after each load. The script above already sets this up (warehouse_size = xsmall, auto_suspend = 60). :::

Step 3: Check network policies

By default, Snowflake accepts connections from any IP address. A security administrator (the SECURITYADMIN role or higher) can create a network policy that allows or blocks specific addresses. If one applies to your account or to Pipeloom's user, it must allow Pipeloom to connect.

To check whether a network policy is set, run SHOW PARAMETERS:

For the account

SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;

For a user

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

See Snowflake's network policy docs for more.

Step 4: Data loading

Pipeloom loads data through Snowflake's internal stage. Make sure the role has the USAGE privilege on the database and schema.

Step 5: Create the Snowflake destination in Pipeloom

Create a new destination and choose Snowflake. You can do this from the Destinations page, or from a Destination step on a pipeline canvas. Sign in with the private key from Step 1.

• Host: your Snowflake account's host, ending in snowflakecomputing.com. Use accountname.snowflakecomputing.com, or for accounts with a region-based locator, accountname.region.cloud.snowflakecomputing.com. Example: accountname.us-east-2.aws.snowflakecomputing.com

• Role: the role you created in Step 2. Example: PIPELOOM_ROLE

• Warehouse: the warehouse you created in Step 2. Example: PIPELOOM_WAREHOUSE

• Database: the database you created in Step 2. Example: PIPELOOM_DATABASE

• Default Schema: the schema used for any stream that doesn't name its own.

• Username: the service user you created in Step 2. Example: PIPELOOM_USER

• Authorization Method: choose Key Pair Authentication.

  • Private Key: paste the whole of rsa_key.p8, including the -----BEGIN ... PRIVATE KEY----- and -----END ... PRIVATE KEY----- lines.
  • Passphrase: the key's passphrase, if you created an encrypted key. Leave it empty for an unencrypted key.

  Username and Password (Deprecated) is still offered for existing destinations, but it will be removed, and Snowflake is blocking password-only sign-ins (see the warning at the top). If a destination still uses a password, create a key pair as in Step 1, assign its public key to the user with alter user <username> set rsa_public_key='<public_key_value>'; (see Step 2), and switch the destination to Key Pair Authentication.

Optional settings:

• CDC deletion mode: what happens here when a row is deleted in a source that uses change data capture. Hard delete (the default) deletes the row here too; Soft delete keeps it as a tombstone record that marks it as deleted.

• JDBC URL Params: extra connection properties, as key=value pairs separated by &. Example: key1=value1&key2=value2&key3=value3

• Legacy raw tables: write only the legacy raw-table format, for compatibility with older setups. See Output tables. The format of _airbyte_data is fairly stable, but the other metadata columns may change.

• Internal table dataset name: the schema for the connector's internal tables, and for raw tables in legacy raw-tables mode. Defaults to airbyte_internal.

• Trim Whitespace from String Fields: on by default, so Snowflake trims leading and trailing whitespace from text as it loads. Turn it off if that whitespace is meaningful and should be kept.

• Data Retention Period (days): how many days of Snowflake Time Travel to keep for the tables. Defaults to 1. A nonzero value adds storage costs in Snowflake.

• Decimal Data Type: the Snowflake type for decimal number columns. NUMBER(38,9) (recommended) stores values exactly, which suits money and other exact decimals. FLOAT (the default) stores them approximately, which suits scientific values and very large magnitudes. See Decimal numbers. If you change it on an existing destination, clear the sync's data so the next run copies everything again.

Save the destination. Pipeloom tests the connection, and the destination is ready once the test passes.

Output tables

Each stream is written to a final table with typed columns. The connector also keeps a raw table per stream, in the airbyte_internal schema unless you set a different Internal table dataset name. Raw tables aren't deduplicated.

:::info The connector creates permanent tables. If you prefer transient tables, give Pipeloom a transient database: every table created in it is then transient, which avoids the extra cost and storage of Fail-safe. Transient tables can't be recovered after an operational or system failure.

If you set a custom internal schema, keep it and the default schema in databases of the same kind (both transient or both permanent), so both get the same data protection. See Working with Temporary and Transient Tables. :::

Raw table columns

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

_airbyte_data holds the record as JSON. See metadata columns for the others.

:::info The contents of _airbyte_data are fairly stable, but the raw table's columns may change. :::

Final table columns

Besides the columns in your stream's schema, each final table has:

• _AIRBYTE_RAW_ID
• _AIRBYTE_GENERATION_ID
• _AIRBYTE_EXTRACTED_AT
• _AIRBYTE_LOADED_AT
• _AIRBYTE_META

See metadata columns for what they hold.

Data type mapping

Decimal numbers

The Decimal Data Type setting picks the Snowflake type for decimal number columns:

• NUMBER(38,9) (recommended): an exact fixed-point decimal, with up to 29 digits before the decimal point and 9 after it. Use it for financial data and anything else that needs exact decimals within that range.
• FLOAT (the default): an approximate binary floating-point number with about 15 significant digits and magnitudes up to about 10^308. Use it for scientific values, statistics, model scores and very large numbers, where range matters more than exact decimals.

The setting doesn't affect:

• Legacy raw tables, which have no typed columns: every value is in the _airbyte_data VARIANT column.
• Whole-number database types such as NUMERIC(10,0). Sources read those as integers, which are always stored as Snowflake NUMBER.

:::warning After changing this setting on an existing destination, clear the sync's data so the next run copies everything again. Otherwise the next sync converts the existing number columns in place, and data can be lost:

• To NUMBER(38,9): stored FLOAT values with more than 29 digits before the decimal point become NULL. Snowflake converts the column in place, so these changes aren't recorded in _airbyte_meta. Values that lost precision when they were loaded as FLOAT (flagged TRUNCATED in _airbyte_meta) stay imprecise; only a full re-copy brings back the source values.
• To FLOAT: values become approximate again, and query results may differ slightly. :::

Precision and size limits

Snowflake limits numeric precision:

• FLOAT: a standard 64-bit floating point value, about 15 significant digits.
• NUMBER(38,9) (if you chose it for decimals): at most 29 digits before the decimal point. Larger values are set to null and flagged in _airbyte_meta; if your data has values that large, use FLOAT. Digits beyond 9 decimal places are truncated and flagged in _airbyte_meta.
• NUMBER (used for integers): at most 38 digits.

A value outside these bounds is set to null. A value within bounds but with too much precision is rounded. Either way, the record's _airbyte_meta column gets a changes entry saying so.

Snowflake also limits the size of text and semi-structured values:

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

Larger values are set to null, and _airbyte_meta records the change.

Schema changes

When the source schema changes, the connector updates the destination tables: it adds new columns and changes column types as needed. When a column is removed from the source, the connector keeps the column and the data it already holds in the destination table, and writes NULL to it for new rows. The user needs ALTER TABLE on the destination tables for this.

Column names that are ANSI SQL reserved keywords (such as SELECT or ORDER) get an underscore in front (_SELECT, _ORDER).

Sync modes

This destination supports every sync mode:

:::note In Legacy raw tables mode nothing is deduplicated: sync modes that would deduplicate append to the raw table instead. :::

It supports namespaces: each namespace maps to a Snowflake schema.

Troubleshooting

Current role does not have permissions on the target schema

The role in the destination's settings doesn't have permissions on the schema being written to. This often happens when a sync's Destination Namespace is set to use the source's schemas (Source-defined, or Mirror source structure), which can make it write to a schema such as PUBLIC that the role can't use.

Either switch the sync's Destination Namespace to the destination's default schema (Destination-defined, or Destination default), or grant the role the permissions it needs on the schema it's writing to.