Snowflake
The Snowflake connector queries and writes tables, bulk loads and unloads
through COPY INTO, and uploads or downloads stage files. It signs in with a
password or a key pair.
When to use it
- Snowflake is the warehouse you load into.
- You read a large result set out of Snowflake to send to another system.
- You want rows staged and loaded in bulk with
COPY INTOrather than row by row.
What you need
- Your Snowflake account identifier and a warehouse the user can use.
- A Snowflake user with a password, or with a registered public key for key-pair sign-in. Key pair is the usual choice for a service user.
- A dedicated role with only the privileges the data pipeline needs on the database, schema and warehouse.
Connect
The connection form lists its steps on the left. Open any step from that list. The Environments step holds the values that differ per environment.
Test connection is in the form footer. Beside it, Test against picks Base, the connection as saved, or an environment, whose values are applied first.
| Field | Step | What to enter |
|---|---|---|
| Auth Method | Authentication | Password or Key Pair. Default Password. |
| Username | Authentication | The Snowflake user. |
| Password | Authentication | For Password. |
| Private Key (PEM) | Authentication | For Key Pair. The private key whose public key is set on the user. |
| Private Key Passphrase (Optional) | Authentication | For an encrypted private key. |
| Account | Connection | The account identifier, such as xy12345. |
| Region (Optional) | Connection | The region, when the account identifier needs one. |
| Warehouse | Connection | The warehouse that runs queries, such as COMPUTE_WH. |
| Role (Optional) | Connection | The dedicated role, such as DATALUG_PIPELINES. Blank uses the user's default role, or PUBLIC when the user has none. |
| Database | Connection | Optional. Blank means queries name objects in full. |
| Schema | Connection | Optional. Blank means queries name objects in full. |
| Keep Alive | Options | Keeps the session open between queries. Off by default. |
| Query Tag (Optional) | Options | Tags every query, so you can find it in Snowflake query history. |
Role warning
The connection form and the connection's row on the Connections page show a warning when the role is blank or is ACCOUNTADMIN, SECURITYADMIN or ORGADMIN. A blank role runs as the user's default role, which is often an administrator role. Set a dedicated role with only the grants the connection needs. The warning does not block saving or running.
Test connection also reads the role the session actually signed in with. When it is ACCOUNTADMIN, SECURITYADMIN or ORGADMIN, the Access confirmed step shows a warning and the test still passes. A run that signs in with one of these roles writes the same warning to its run log and continues.
Supported operations
| Action | Node | Operations |
|---|---|---|
| Select (Source) | Source | Query, in No-code or SQL mode |
| DML (Target) | Load | Insert, Update, Delete, Merge |
| Unload To Stage (Source) | Source | COPY INTO a stage location |
| Copy Into Table (Target) | Load | COPY INTO a table |
| Stage Download (Source) | Source | GET from a stage |
| Stage Upload (Target) | Load | PUT to a stage |
| DDL Script | DB Script | Create, Alter, Drop, Truncate, Rename |
Refresh Schema on a node reads the action's current columns from Snowflake: a table's or view's columns, or the columns a SQL query returns. Saving a Source query action reads its columns the same way.
Configure Action lists every database the role can see. The connection's database comes first with its procedures, functions and stages; the others list tables and views. Shared and sample databases are marked read only.
Limits
- Reads are paged in a fixed order with an offset.
- Stage Download and Unload To Stage emit one row per downloaded file. With a row limit set on the Source node, they emit at most that many rows.
- Merge, Update and Delete loads send rows in batches, sized by bind count and by request size.
- Stage Upload, Stage Download and Unload To Stage refuse a local file path that contains a single quote or a control character. Stage Upload checks every row before it uploads any file.
Troubleshooting
- Test connection fails with correct credentials. The database must accept connections from the runtime that runs the data pipeline, not from your computer. For a database on a private network, run a Private Agent inside that network or allow-list the runtime's address.
- The test passes but Configure Action lists no objects. The user can sign in but cannot see the catalog. Grant it read access on the schema, then press Get Metadata again.
- A column is missing from the field list. The list is read when metadata is loaded. Press Get Metadata again to read the current columns.
- A load fails on one batch. The run log names the statement and the batch. Check the rows in that batch against the column types and lengths.
- Key-pair sign-in fails with JWT token is invalid. The public key on the
Snowflake user does not match the private key. Set the user's
RSA_PUBLIC_KEYfrom the same key pair, then test again.
Related
- Source node, Load node and DB Script node.
- Connections for environments and sharing.
- PostgreSQL, MySQL, SQL Server and Oracle.