> ## Documentation Index > Fetch the complete documentation index at: https://docs.datris.ai/llms.txt > Use this file to discover all available pages before exploring further. # Snowflake Destination > Send data to Snowflake with per-pipeline credentials 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](/destinations/s3). 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): ```bash theme={null} 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. ```sql theme={null} -- 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 = ''; 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 = ''`, 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 ```json theme={null} { "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 `NULL`s. 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](/notifications) for details.