Snowflake Data Connector

Query Snowflake tables with federated SQL, authenticate with a password or a key pair, and write rows with INSERT, UPDATE, and DELETE.

The Snowflake Data Connector enables federated SQL queries across datasets in the Snowflake Cloud Data Warehouse.

datasets:
  - from: snowflake:DATABASE.SCHEMA.TABLE
    name: table
    params:
      snowflake_warehouse: COMPUTE_WH
      snowflake_role: accountadmin

Configuration

`from`

A Snowflake fully qualified table name (database.schema.table). For instance snowflake:SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM or snowflake:TAXI_DATA."2024".TAXI_TRIPS

`name`

The dataset name. This will be used as the table name within Spice. The dataset name cannot be a reserved keyword or any of the following keywords that are reserved by Snowflake:

START
CONNECT
MATCH_RECOGNIZE
SAMPLE
TABLESAMPLE
FROM

`params`

Parameter Name	Description
`snowflake_warehouse`	Optional, specifies the Snowflake Warehouse to use
`snowflake_role`	Optional, specifies the role to use for accessing Snowflake data
`snowflake_account`	Required, specifies the Snowflake account-identifier
`snowflake_username`	Required, specifies the Snowflake username to use for accessing Snowflake data
`snowflake_auth_type`	Optional, specifies the authentication type. Accepts `password` (or `snowflake`) and `keypair` (or `snowflake_jwt`); matched case-insensitively. Defaults to password authentication unless only key-pair credentials (`snowflake_private_key` or `snowflake_private_key_path`) are provided, in which case key-pair is selected automatically.
`snowflake_password`	Required for password authentication. Specifies the Snowflake password for authentication.
`snowflake_private_key`	Required (one of) for key-pair authentication. Mutually exclusive with `snowflake_private_key_path`. Specifies the private key content as a string.
`snowflake_private_key_path`	Required (one of) for key-pair authentication. Mutually exclusive with `snowflake_private_key`. Specifies the path to the private key file.
`snowflake_private_key_passphrase`	Required when the private key is encrypted. Specifies the passphrase to decrypt the private key.

Auth

The connector supports password-based and key-pair authentication that must be configured using spice login snowflake or using Secrets Stores. Login requires the account identifier ('orgname-accountname' format) - use Finding the organization and account name for an account instructions.

In Snowsight, the user menu opens Account, then the current account, then a details panel. The Copy account identifier button sits at the top right of that panel.

# Password-based
SPICE_SNOWFLAKE_ACCOUNT="<account-identifier>" \
SPICE_SNOWFLAKE_USERNAME="<username>" \
SPICE_SNOWFLAKE_PASSWORD="<password>" \
spice run
# Key-pair using private key file (the `<private-key-passphrase>` is optional, used for encrypted keys only)
SPICE_SNOWFLAKE_ACCOUNT="<account-identifier>" \
SPICE_SNOWFLAKE_USERNAME="<username>" \
SPICE_SNOWFLAKE_PRIVATE_KEY_PATH="<path-to-private-key>" \
SPICE_SNOWFLAKE_PRIVATE_KEY_PASSPHRASE="<private-key-passphrase>" \
spice run
# Key-pair using private key content (the `<private-key-passphrase>` is optional, used for encrypted keys only)
SPICE_SNOWFLAKE_ACCOUNT="<account-identifier>" \
SPICE_SNOWFLAKE_USERNAME="<username>" \
SPICE_SNOWFLAKE_PRIVATE_KEY="<private-key-pem-content>" \
SPICE_SNOWFLAKE_PRIVATE_KEY_PASSPHRASE="<private-key-passphrase>" \
spice run

or using the Spice CLI:

# Password-based
spice login snowflake -a "<account-identifier>" -u "<username>" -p "<password>"
# Key-pair (the `<private-key-passphrase>` is an optional parameter and is used for encrypted private key only)
spice login snowflake -a "<account-identifier>" -u "<username>" -k "<path-to-private-key>" -s "<private-key-passphrase>"

The CLI will create or update an .env file that looks like:

SPICE_SNOWFLAKE_ACCOUNT="account"
SPICE_SNOWFLAKE_PASSWORD="pass"
SPICE_SNOWFLAKE_USERNAME="user"

Configure the spicepod to load secrets from the env secret store: (Note: This is the default setting)

spicepod.yaml

version: v1
kind: Spicepod
name: spice-app

secrets:
  - from: env
    name: env

datasets:
  - from: snowflake:DATABASE.SCHEMA.TABLE
    name: table
    params:
      snowflake_warehouse: COMPUTE_WH
      snowflake_role: accountadmin
      snowflake_username: ${env:SPICE_SNOWFLAKE_USERNAME}
      snowflake_password: ${env:SPICE_SNOWFLAKE_PASSWORD}
      snowflake_account: ${env:SPICE_SNOWFLAKE_ACCOUNT}

Learn more about Env Secret Store.

# Password-based
kubectl create secret generic snowflake \
  --from-literal=account='<account-identifier>' \
  --from-literal=username='<username>' \
  --from-literal=password='<password>'

# Key-pair using private key file (the `<private-key-passphrase>` is optional, used for encrypted keys only)
kubectl create secret generic snowflake \
  --from-literal=account='<account-identifier>' \
  --from-literal=username='<username>' \
  --from-literal=private_key_path='<path-to-private-key>' \
  --from-literal=private_key_passphrase='<private-key-passphrase>'

# Key-pair using private key content (the `<private-key-passphrase>` is optional, used for encrypted keys only)
kubectl create secret generic snowflake \
  --from-literal=account='<account-identifier>' \
  --from-literal=username='<username>' \
  --from-file=private_key='<path-to-private-key>' \
  --from-literal=private_key_passphrase='<private-key-passphrase>'

spicepod.yaml (password-based)

version: v1
kind: Spicepod
name: spice-app

secrets:
  - from: kubernetes:snowflake
    name: snowflake

datasets:
  - from: snowflake:DATABASE.SCHEMA.TABLE
    name: table
    params:
      snowflake_warehouse: COMPUTE_WH
      snowflake_role: accountadmin
      snowflake_username: ${snowflake:username}
      snowflake_password: ${snowflake:password}
      snowflake_account: ${snowflake:account}

Learn more about Kubernetes Secret Store.

Add new keychain entries (macOS) for user and password:

# Password-based
security add-generic-password -l "Snowflake Secret" \
-a spiced -s spice_snowflake_password\
-w "<password>"

# Key-pair using private key content (the `<private-key-passphrase>` is optional, used for encrypted keys only)
security add-generic-password -l "Snowflake Secret" \
-a spiced -s spice_snowflake_private_key\
-w $(cat '<path-to-private-key>')

spicepod.yaml (key-pair)

version: v1
kind: Spicepod
name: spice-app

secrets:
  - from: keyring
    name: keyring

datasets:
  - from: snowflake:DATABASE.SCHEMA.TABLE
    name: table
    params:
      snowflake_warehouse: COMPUTE_WH
      snowflake_role: accountadmin
      snowflake_auth_type: keypair
      snowflake_username: user_name
      snowflake_private_key: ${keyring:spice_snowflake_private_key}
      snowflake_account: account_identifier

Learn more about Keyring Secret Store.

Write Support

This connector supports writing data to Snowflake tables using SQL INSERT INTO, UPDATE, and DELETE FROM statements.

To enable writes, set access: read_write on the dataset:

datasets:
  - from: snowflake:DATABASE.SCHEMA.TABLE
    name: table
    access: read_write
    params:
      snowflake_warehouse: COMPUTE_WH
      snowflake_role: accountadmin

-- Insert rows
INSERT INTO table (id, name, amount)
VALUES (1, 'Alice', 100.0), (2, 'Bob', 200.0);

-- Update rows
UPDATE table SET amount = 150.0 WHERE id = 1;

-- Delete rows
DELETE FROM table WHERE id = 2;

For more details, see Data Ingestion.

Example

datasets:
  - from: snowflake:SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
    name: lineitem
    params:
      snowflake_warehouse: COMPUTE_WH
      snowflake_role: accountadmin

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook

A cookbook recipe to configure Snowflake as a data connector in Spice. Snowflake Data Connector
A cookbook recipe to ingest data from an HTTP API and write it to a Snowflake table with INSERT. Snowflake DML: HTTP API Ingestion Pipeline