Snowflake Destination

The Snowflake destination bulk-loads data using Snowflake’s native PUT → COPY INTO path: the pipeline output is staged onto the target table’s internal stage and copied in, with gzip compression handled automatically on the wire. When keyFields is set, the loader upserts via MERGE instead. No Snowflake stages need to be created — the loader uses the table stage (@%table) that every Snowflake table has implicitly. Unlike the bundled PostgreSQL and MongoDB destinations, Snowflake is an external target: credentials are per-pipeline, referenced by name through credentialsSecret — the same model as the S3 destination. Different pipelines can write to different Snowflake accounts.

One-time Snowflake setup

This is the recommended long-term setup for connecting Datris to a company Snowflake account: a dedicated service user with key-pair authentication, a least-privilege role, and an isolated warehouse. Key-pair auth is Snowflake’s recommended method for programmatic access — it never expires, needs no network policy, and supports zero-downtime rotation.

1. Generate a key pair

On any machine you trust (the keys are yours, not Snowflake’s):

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

To encrypt the private key at rest, drop -nocrypt and remember the passphrase — you’ll store it in the secret as privateKeyPassphrase.

2. Create the database, role, warehouse, and service user

In Snowsight (app.snowflake.com), open Projects → Workspaces and create a SQL file, paste the script below, replace the public-key placeholder, then select all and Run All — the run button alone executes only the statement under the cursor. The script is self-contained: it creates everything Datris needs, including a dedicated DATRIS database. Every statement is idempotent (IF NOT EXISTS / GRANT), so re-running it is safe. Rename DATRIS throughout if you’d rather load an existing database.

-- Everything below needs account-level privileges
USE ROLE ACCOUNTADMIN;

-- Least-privilege role for Datris loads, attached to the admin hierarchy
-- so tables Datris creates stay manageable by your admins
CREATE ROLE IF NOT EXISTS DATRIS_LOADER;
GRANT ROLE DATRIS_LOADER TO ROLE SYSADMIN;

-- Dedicated database for Datris pipeline output
CREATE DATABASE IF NOT EXISTS DATRIS;

-- Dedicated warehouse so Datris load cost is isolated and visible
CREATE WAREHOUSE IF NOT EXISTS DATRIS_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

GRANT USAGE ON WAREHOUSE DATRIS_WH TO ROLE DATRIS_LOADER;

-- Database/schema privileges
GRANT USAGE ON DATABASE DATRIS TO ROLE DATRIS_LOADER;
GRANT USAGE, CREATE TABLE ON SCHEMA DATRIS.PUBLIC TO ROLE DATRIS_LOADER;
-- Lets Datris auto-create additional schemas beyond PUBLIC:
GRANT CREATE SCHEMA ON DATABASE DATRIS TO ROLE DATRIS_LOADER;

-- Service user: no password, no MFA prompts — key-pair only
CREATE USER IF NOT EXISTS DATRIS_SVC
  TYPE = SERVICE
  DEFAULT_ROLE = DATRIS_LOADER
  DEFAULT_WAREHOUSE = DATRIS_WH
  RSA_PUBLIC_KEY = '<contents of rsa_key.pub without the BEGIN/END lines>';

GRANT ROLE DATRIS_LOADER TO USER DATRIS_SVC;

-- Verify: should show a fingerprint in RSA_PUBLIC_KEY_FP
DESC USER DATRIS_SVC;

The final DESC USER is the success check — a value in the RSA_PUBLIC_KEY_FP row confirms the key registered. Tables auto-created by Datris are owned by DATRIS_LOADER, so no further table grants are needed. If you manage tables yourself (manageTableManually: true), also grant SELECT, INSERT, UPDATE, DELETE on those tables to DATRIS_LOADER — MERGE upserts and truncation need all four. TYPE = SERVICE marks the user as a machine identity: password login is disabled and MFA does not apply, which is exactly what you want for an unattended integration.

3. Create the platform secret in Datris

On Configuration → Secrets → Platform, create a secret (for example snowflake) with:

Field	Required	Notes
`account`	yes	Account identifier, e.g. `myorg-myaccount` or `xy12345.us-east-1`. Pasting the full account URL (`https://myorg-myaccount.snowflakecomputing.com`) also works — Datris strips the protocol and domain
`user`	yes	The service user, e.g. `DATRIS_SVC`
`privateKey`	yes	Full contents of `rsa_key.p8`, including the BEGIN/END lines
`privateKeyPassphrase`	no	Only if the private key is encrypted
`password`	no	Fallback when no `privateKey` is present — see below

The account lives in the secret rather than the pipeline config so it stays bound to the credential that authorizes it. If a required field is missing, the load fails at resolve time with an explicit error naming the secret and the field. Paste the key file’s entire contents into privateKey — don’t worry about formatting. The secret form is a single-line field that strips line breaks from the paste; Datris reconstructs the strict PEM framing the Snowflake driver requires, so a flattened key (or even just the base64 body without the BEGIN/END lines) works. What it cannot accept is a PKCS#1 key (BEGIN RSA PRIVATE KEY — convert with openssl pkcs8 -topk8 -nocrypt) or an encrypted key without privateKeyPassphrase; both fail with an error saying exactly that.

Key rotation

Snowflake users hold two public-key slots, so rotation has no downtime: generate a new pair, register it with ALTER USER DATRIS_SVC SET RSA_PUBLIC_KEY_2 = '<new public key>', update the Datris secret’s privateKey to the new private key, then clear the old slot with ALTER USER DATRIS_SVC UNSET RSA_PUBLIC_KEY.

Alternative: programmatic access token (PAT)

For a quick evaluation you can skip the key pair: generate a PAT in Snowsight (Settings → Authentication → Programmatic access tokens) and store it in the secret’s password field instead of privateKey. Be aware of the trade-offs before relying on one long-term: PATs expire (one year at most), and Snowflake refuses PAT authentication unless the account or user has an active network policy allowing the caller’s IP. Key-pair auth has neither constraint.

Configuration

{
  "name": "orders_pipeline",
  "source": { "..." : "..." },
  "destination": {
    "database": {
      "useSnowflake": true,
      "credentialsSecret": "snowflake",
      "warehouse": "DATRIS_WH",
      "role": "DATRIS_LOADER",
      "dbName": "DATRIS",
      "schema": "PUBLIC",
      "table": "ORDERS",
      "keyFields": ["ORDER_ID"]
    }
  }
}

Field reference

Field	Required	Default	Description
`useSnowflake`	yes	`false`	Must be `true` to enable the Snowflake destination
`credentialsSecret`	yes		Name of the Platform secret holding `account`/`user`/auth
`warehouse`	yes		Virtual warehouse that runs the `COPY`/`MERGE`
`role`	no		Role to assume; omit to use the user’s default role
`dbName`	yes		Target database
`schema`	yes		Target schema
`table`	yes		Target table
`manageTableManually`	no	`false`	If `true`, skip auto-creation of the table
`truncateBeforeWrite`	no	`false`	Empty the table before loading (see semantics below)
`keyFields`	no		Natural-key columns; switches the load to a `MERGE` upsert
`useTransaction`	no	`true`	Wrap the load in a transaction

warehouse and role are per-pipeline on purpose: a light feed can run on an extra-small warehouse while a wide table gets a larger one, without touching the credential. Identifiers follow Snowflake’s normal resolution rules: simple names (letters, digits, underscores) are passed unquoted, so datris and DATRIS both resolve to the uppercase DATRIS database — the same behavior as every other Snowflake tool. Names containing other characters (hyphens, spaces) are quoted and matched case-sensitively.

Table Management

When manageTableManually is false (the default), the pipeline auto-creates the schema and table if they don’t exist, with a PRIMARY KEY constraint on keyFields when set. Schema changes are additive: new columns in the pipeline schema are added with ALTER TABLE ... ADD COLUMN; columns are never dropped or retyped.

Type Mapping

Source Type	Snowflake Type
`string`	`VARCHAR`
`int` / `integer` / `tinyint` / `smallint` / `bigint`	`NUMBER(38,0)`
`float` / `double`	`FLOAT`
`boolean`	`BOOLEAN`
`date`	`DATE`
`timestamp`	`TIMESTAMP_NTZ`
`_json`	`VARIANT`
`_xml`	`VARCHAR`

Other types (decimal(p,s), varchar(n), char(n)) pass through as-is. Semi-structured _json columns land as VARIANT (parsed with PARSE_JSON during the COPY), so JSON payloads are natively queryable in Snowflake rather than stored as text.

Truncate Before Write

Set truncateBeforeWrite to true to replace the full table contents on each run. The mechanism depends on useTransaction:

useTransaction: true (default): rows are removed with DELETE FROM inside the load transaction. If the load fails, the delete rolls back and the previous rows survive — a failed run never leaves the table empty.
useTransaction: false: rows are removed with TRUNCATE TABLE, which is faster on very large tables but commits immediately (Snowflake DDL auto-commits) — a failure after the truncate leaves the table empty until the next successful run.

Primary Key and Upsert

Specify one or more columns in keyFields to define the natural key. When set (and truncateBeforeWrite is false), the loader stages the batch into a session-temporary table and issues a MERGE on the key columns: matched rows are updated, unmatched rows are inserted. The same rows can be loaded across runs without duplicates. Upsert semantics match the PostgreSQL destination: on match, non-key columns are overwritten in full, including NULLs. If a single batch contains duplicate key values, Snowflake rejects the MERGE as non-deterministic — deduplicate upstream. When keyFields is empty, the loader uses a straight COPY INTO — the fastest path, for append-only ingestion.

Querying from Datris

The Assistant (and any MCP client) can read the destination back with the query_snowflake tool — useful for verifying a load landed and for answering questions over the data without leaving Datris. Queries are pipeline-scoped: the tool takes a pipeline name, and the server connects with that pipeline’s credentials secret, warehouse, and role. Credentials never leave the server, and only read-only statements are accepted — SELECT (with LIMIT applied automatically), plus SHOW and DESCRIBE for discovering databases, schemas, tables, and columns. Queries run on the pipeline’s configured warehouse and consume Snowflake compute like any other query. Results are capped at 100 rows by default.

Warehouse Behavior

The loader assumes AUTO_RESUME = TRUE on the warehouse (Snowflake’s default): a suspended warehouse resumes itself when the COPY/MERGE arrives. Datris never issues ALTER WAREHOUSE ... RESUME, so the loader role does not need the OPERATE privilege. If your warehouse is deliberately configured with AUTO_RESUME = FALSE, resume it before the pipeline runs.

Completion Notification

A pipeline notification is published to ActiveMQ on completion. See Notifications for details.

​One-time Snowflake setup

​1. Generate a key pair

​2. Create the database, role, warehouse, and service user

​3. Create the platform secret in Datris

​Key rotation

​Alternative: programmatic access token (PAT)

​Configuration

​Field reference

​Table Management

​Type Mapping

​Truncate Before Write

​Primary Key and Upsert

​Querying from Datris

​Warehouse Behavior

​Completion Notification