Snowflake is a cloud data platform that provides a relational SQL data warehouse. Storage, compute (a "warehouse") and cloud services scale independently, and an account runs on AWS, Azure or Google Cloud.

This tap reads tables and views from a Snowflake database through the Snowflake SQLAlchemy dialect. It runs every query against the warehouse that you configure, so an extraction consumes compute credits.

Streams

Streams are discovered dynamically at runtime. Each table and view in the configured database becomes a stream named <schema>-<table>; a column of that stream is selected as <schema>-<table>.<column>. The information_schema schema is excluded from discovery.

Discovery reflects every schema of the database, so a large account produces a large catalog. Step 5 below limits it to the objects that you need.

Setup

1. Create a role and a user

Create a dedicated role and user for the tap, then grant read access to the objects that you extract. The role needs USAGE on the warehouse, on the database, and on each schema that discovery reads:

USE ROLE ACCOUNTADMIN;

CREATE ROLE tap_snowflake;
CREATE USER tap_snowflake
  PASSWORD = '<password>'
  DEFAULT_ROLE = tap_snowflake
  DEFAULT_WAREHOUSE = <warehouse>;
GRANT ROLE tap_snowflake TO USER tap_snowflake;

GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE tap_snowflake;
GRANT USAGE ON DATABASE <database> TO ROLE tap_snowflake;
GRANT USAGE ON ALL SCHEMAS IN DATABASE <database> TO ROLE tap_snowflake;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <database> TO ROLE tap_snowflake;
GRANT SELECT ON ALL TABLES IN DATABASE <database> TO ROLE tap_snowflake;
GRANT SELECT ON FUTURE TABLES IN DATABASE <database> TO ROLE tap_snowflake;
GRANT SELECT ON ALL VIEWS IN DATABASE <database> TO ROLE tap_snowflake;
GRANT SELECT ON FUTURE VIEWS IN DATABASE <database> TO ROLE tap_snowflake;

The FUTURE grants cover an object that someone creates after this point. Without them, a new table is discovered but returns a permission error on extraction.

2. Find your account identifier

The Account setting takes an account identifier, such as myorg-account1. See Account Identifiers. Do not include the .snowflakecomputing.com suffix.

A network policy on the account or on the user restricts the addresses that may connect. Add the address that the pipeline runs from to the allowed list, or Snowflake rejects the connection before it authenticates.

3. Choose an authentication method

Set exactly one of Password, Private Key or Private Key Path. The tap stops with a configuration error when more than one of the three is set.

Password — enter the password of the Snowflake user.

Key pair — key pair authentication replaces the password with an RSA key. Follow Configuring key pair authentication to generate the key pair and to assign the public key to the Snowflake user.

Give the private key to the tap in one of two ways. The Private Key setting holds the base64 encoded contents of the private key file, which the platform decodes back to PEM before the tap reads it:

base64 -w0 rsa_key.p8

Private Key Path points at a key file on disk instead, so it suits a self-hosted run rather than a hosted pipeline, which has no persistent filesystem to hold the key. Set Private Key Passphrase as well when the key carries a passphrase.

Browser SSO — Use Browser Authentication authenticates through your identity provider with browser-based SSO, which opens an external browser. The prompt needs a person to answer it, so this option fits an interactive local run only. It also takes precedence over the three credential settings above.

4. Set the session context

Warehouse, Database, Role and Schema set the initial context of the Snowflake session. Set Warehouse to the warehouse that runs the queries, and Database to the database that holds the objects. Set Role when the user holds more than one role, because a query that runs under the wrong role sees a different set of objects. Schema sets the initial schema only and does not restrict discovery.

5. Limit discovery to the objects you need (optional)

The Tables setting limits discovery to the objects that you list, which keeps a large account from producing a catalog of every table and view. Fully qualify each entry as <schema>.<table>:

tables:
- sales.orders
- sales.customers

Note that the syntax differs from select, which separates the schema and the table with a hyphen. When you add a stream to select, add the same object to Tables as well, or discovery will not produce it.

6. Replicate incrementally (optional)

A stream is replicated in full on each run. To replicate a stream incrementally, set a replication key for it under metadata in your meltano.yml:

plugins:
  extractors:
  - name: tap-snowflake
    variant: meltanolabs
    pip_url: meltanolabs-tap-snowflake~=0.4.0
    metadata:
      <schema>-<table>:
        replication-method: INCREMENTAL
        replication-key: <column>
        key-properties: [<column>]

Use a replication key that never decreases, such as an updated timestamp or an ascending key. The tap resumes with where <replication-key> >= <bookmark>, so the rows that sit on the bookmark are sent again on the next run. Set key-properties as well, so that the target replaces those rows instead of duplicating them. Snowflake does not enforce a primary key constraint, so discovery finds no key properties for a stream and cannot supply them itself.

7. Preserve numeric precision (optional)

Set Use Singer Decimal to emit a NUMBER column as a string with the x-singer.decimal format. A NUMBER column holds up to 38 digits of precision, which a JSON number cannot carry without loss.

8. Enable BATCH mode for large tables (optional)

Instead of one record message per row, the tap can write files and emit batch messages, which a compatible target loads in bulk. Set Batch Encoding Format and Batch Compression Format, plus Batch Storage Root and Batch Storage Prefix for the location of the files.

Caveats — batch messages do not support INCREMENTAL replication in this variant, so a stream in BATCH mode is replicated in full. Only a target that advertises the batch capability processes the messages; leave these settings unset for any other target.

Settings

Account

Your account identifier. See Account Identifiers.

Batch Compression Format

Compression format to use for batch files.

Batch Encoding Format

Format to use for batch files.

Batch Storage Prefix

Prefix to use when writing batch files.

Batch Storage Root

Root path to use when writing batch files.

Database

The initial database for the Snowflake session.

Faker Locale

One or more LCID locale strings to produce localized output for: https://faker.readthedocs.io/en/master/#localization

Faker Seed

Value to seed the Faker generator for deterministic output: https://faker.readthedocs.io/en/master/#seeding-the-generator

Enable Schema Flattening

'True' to enable schema flattening and automatically expand nested properties.

Max Flattening Depth

The max depth to flatten schemas.

Max Key Length

The maximum length of a flattened key.

Password

The password for your Snowflake user. One of [password, private_key, private_key_path] is required.

Private Key

Base64 encoded private key contents for KeyPair authentication. One of [password, private_key, private_key_path] is required.

Private Key Passphrase

The passprhase used to protect the private key

Private Key Path

Path to where the private key is stored. The private key is used to connect to snowflake. One of [password, private_key, private_key_path] is required.

Role

The initial role for the session.

Schema

The initial schema for the Snowflake session.

User Stream Map Configuration

User-defined config values to be used within map expressions.

Stream Maps

Config object for stream maps capability. For more information check out Stream Maps.

Tables

An array of the table names that you want to sync. The table names should be fully qualified, including schema and table name. NOTE: this limits discovery to the tables specified, for performance reasons. Do not specify tables if you intend to discover the entire available catalog. See readme for more details on the tables configuration parameter.

Use Browser Authentication

If authentication should be done using SSO (via external browser). See SSO browser authentication.

Use Singer Decimal

Whether to use use strings with x-singer.decimal format for decimals in the discovered schema. This is useful to avoid precision loss when working with large numbers.

User

The login name for your Snowflake user.

Warehouse

The initial warehouse for the session.