# Snowflake

The target-snowflake loader loads extracted data into a Snowflake data warehouse.

To learn more about target-snowflake, refer to the repository at https://github.com/datamill-co/target-snowflake.

# Alternative variants

Multiple variants of target-snowflake are available. This document describes the default datamill-co variant, which is recommended for new users.

Alternative options are transferwise and meltano.

# Getting Started

# Prerequisites

If you haven't already, follow the initial steps of the Getting Started guide:

  1. Install Meltano
  2. Create your Meltano project
  3. Add an extractor to pull data from a source

# Installation and configuration

# Using the Command Line Interface

  1. Add the target-snowflake loader to your project using meltano add:

    meltano add loader target-snowflake
    
  2. Configure the settings below using meltano config.

# Using Meltano UI

  1. Start Meltano UI using meltano ui:

    meltano ui
    
  2. Open the Loaders interface at http://localhost:5000/loaders.

  3. Click the "Add to project" button for "Snowflake".

  4. Configure the settings below in the "Configuration" interface that opens automatically.

# Next steps

Follow the remaining step of the Getting Started guide:

  1. Run a data integration (EL) pipeline

# Settings

target-snowflake requires the configuration of the following settings:

These and other supported settings are documented below. To quickly find the setting you're looking for, use the Table of Contents in the sidebar.

# Minimal configuration

A minimal configuration of target-snowflake in your meltano.yml project file will look like this:






 
 
 
 
 
 

plugins:
  loaders:
  - name: target-snowflake
    variant: datamill-co
    pip_url: target-snowflake
    config:
      snowflake_account: my_account
      snowflake_username: MY_USER
      snowflake_database: MY_DATABASE
      snowflake_warehouse: MY_WAREHOUSE
      # snowflake_schema: MY_SCHEMA     # override if default (see below) is not appropriate

Sensitive values are most appropriately stored in the environment or your project's .env file:

export TARGET_SNOWFLAKE_PASSWORD=my_password

# Snowflake Account

You can find your account name in your Snowflake URL: https://<account>.snowflakecomputing.com.

It might require the region and cloud platform where your account is located, in the form of: <your_account_name>.<region_id>.<cloud> (e.g. xy12345.east-us-2.azure)

See https://docs.snowflake.net/manuals/user-guide/connecting.html#your-snowflake-account-name-and-url.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_account <account>

export TARGET_SNOWFLAKE_ACCOUNT=<account>

# Snowflake Username

  • Name: snowflake_username
  • Environment variable: TARGET_SNOWFLAKE_USERNAME, alias: TARGET_SNOWFLAKE_SNOWFLAKE_USERNAME, SF_USER

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_username <username>

export TARGET_SNOWFLAKE_USERNAME=<username>

# Snowflake Password

  • Name: snowflake_password
  • Environment variable: TARGET_SNOWFLAKE_PASSWORD, alias: TARGET_SNOWFLAKE_SNOWFLAKE_PASSWORD, SF_PASSWORD

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_password <password>

export TARGET_SNOWFLAKE_PASSWORD=<password>

# Snowflake Role

  • Name: snowflake_role
  • Environment variable: TARGET_SNOWFLAKE_ROLE, alias: TARGET_SNOWFLAKE_SNOWFLAKE_ROLE, SF_ROLE

If not specified, Snowflake will use the user's default role.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_role <role>

export TARGET_SNOWFLAKE_ROLE=<role>

# Snowflake Database

  • Name: snowflake_database
  • Environment variable: TARGET_SNOWFLAKE_DATABASE, alias: TARGET_SNOWFLAKE_SNOWFLAKE_DATABASE, SF_DATABASE

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_database <database>

export TARGET_SNOWFLAKE_DATABASE=<database>

# Snowflake Authenticator

  • Name: snowflake_authenticator
  • Environment variable: TARGET_SNOWFLAKE_AUTHENTICATOR
  • Default:TARGET_SNOWFLAKE_SNOWFLAKE_AUTHENTICATOR, snowflake

Specifies the authentication provider for snowflake to use. Valud options are the internal one (snowflake), a browser session (externalbrowser), or Okta (https://<your_okta_account_name>.okta.com). See the snowflake docs for more details.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_authenticator externalbrowser

export TARGET_SNOWFLAKE_AUTHENTICATOR=externalbrowser

# Snowflake Warehouse

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_warehouse <warehouse>

export TARGET_SNOWFLAKE_WAREHOUSE=<warehouse>

# Snowflake Schema

  • Name: snowflake_schema
  • Environment variable: TARGET_SNOWFLAKE_SCHEMA, alias: TARGET_SNOWFLAKE_SNOWFLAKE_SCHEMA, SF_SCHEMA
  • Default: $MELTANO_EXTRACT__LOAD_SCHEMA, which will expand to the value of the load_schema extra for the extractor used in the pipeline, which defaults to the extractor's namespace, e.g. tap_gitlab for tap-gitlab. Values are automatically converted to uppercase before they're passed on to the plugin, so tap_gitlab becomes TAP_GITLAB.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set snowflake_schema <schema>

export TARGET_SNOWFLAKE_SCHEMA=<schema>

# Invalid Records Detect

  • Name: invalid_records_detect
  • Environment variable: TARGET_SNOWFLAKE_INVALID_RECORDS_DETECT
  • Default: true

Include false in your config to disable crashing on invalid records.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set invalid_records_detect false

export TARGET_SNOWFLAKE_INVALID_RECORDS_DETECT=false

# Invalid Records Threshold

  • Name: invalid_records_threshold
  • Environment variable: TARGET_SNOWFLAKE_INVALID_RECORDS_THRESHOLD
  • Default: 0

Include a positive value n in your config to allow at most n invalid records per stream before giving up.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set invalid_records_threshold 5

export TARGET_SNOWFLAKE_INVALID_RECORDS_THRESHOLD=5

# Disable Collection

Include true in your config to disable Singer Usage Logging.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set disable_collection true

export TARGET_SNOWFLAKE_DISABLE_COLLECTION=true

# Logging Level

  • Name: logging_level
  • Environment variable: TARGET_SNOWFLAKE_LOGGING_LEVEL
  • Options: DEBUG, INFO, WARNING, ERROR, CRITICAL
  • Default: INFO

The level for logging. Set to DEBUG to get things like queries executed, timing of those queries, etc. See Python's Logger Levels for information about valid values.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set logging_level DEBUG

export TARGET_SNOWFLAKE_LOGGING_LEVEL=DEBUG

# Persist Empty Tables

  • Name: persist_empty_tables
  • Environment variable: TARGET_SNOWFLAKE_PERSIST_EMPTY_TABLES
  • Default: false

Whether the Target should create tables which have no records present in Remote.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set persist_empty_tables true

export TARGET_SNOWFLAKE_PERSIST_EMPTY_TABLES=true

# State Support

Whether the Target should emit STATE messages to stdout for further consumption.

In this mode, which is on by default, STATE messages are buffered in memory until all the records that occurred before them are flushed according to the batch flushing schedule the target is configured with.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set state_support false

export TARGET_SNOWFLAKE_STATE_SUPPORT=false

# Target S3: Bucket

When included, use S3 to stage files.

Bucket where staging files should be uploaded to.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set target_s3 bucket <bucket>

export TARGET_SNOWFLAKE_TARGET_S3_BUCKET=<bucket>

# Target S3: Key Prefix

Prefix for staging file uploads to allow for better delineation of tmp files.

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set target_s3 key_prefix <prefix>

export TARGET_SNOWFLAKE_TARGET_S3_KEY_PREFIX=<prefix>

# Target S3: AWS Access Key ID

  • Name: target_s3.aws_access_key_id
  • Environment variable: TARGET_SNOWFLAKE_TARGET_S3_AWS_ACCESS_KEY_ID

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set target_s3 aws_access_key_id <key_id>

export TARGET_SNOWFLAKE_TARGET_S3_AWS_ACCESS_KEY_ID=<key_id>

# Target S3: AWS Secret Access Key

  • Name: target_s3.aws_secret_access_key
  • Environment variable: TARGET_SNOWFLAKE_TARGET_S3_AWS_SECRET_ACCESS_KEY

# How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-snowflake set target_s3 aws_secret_access_key <key>

export TARGET_SNOWFLAKE_TARGET_S3_AWS_SECRET_ACCESS_KEY=<key>