Skip to main content

snowflake_write output

Uploads batches of messages to a Snowflake stage as files, optionally loading them with Snowpipe.

# Common config fields, showing default values
output:
  label: ""
  snowflake_write:
    account: "" # No default (required)
    user: "" # No default (required)
    role: "" # No default (optional)
    warehouse: "" # No default (optional)
    database: "" # No default (required)
    schema: "" # No default (required)
    stage: "" # No default (required)
    path: ""
    file_name: ""
    file_extension: "json"
    compression: "AUTO"
    password: "" # No default (optional)
    private_key: "" # No default (optional)
    private_key_file: "" # No default (optional)
    private_key_pass: "" # No default (optional)
    snowpipe: "" # No default (optional)
    max_in_flight: 1
    batching:
      count: 0
      byte_size: 0
      period: ""
      check: ""

Writes each batch as newline-delimited files in a Snowflake internal stage, one per distinct stage, snowpipe, path and file name, compressed as configured. Messages that are JSON are compacted onto one line each; other messages are written as-is. Batching controls the file size.

With snowpipe set, each uploaded file is also sent to that Snowpipe, which loads it into its table. The pipe must read from this stage's root with a JSON file format. Without it, files stay in the stage for you to load with COPY INTO.

Authenticate with key-pair auth (recommended, and required for Snowpipe) or a password.

A batch is acknowledged once its files are uploaded (and, with Snowpipe, queued for loading; Snowpipe reports load failures in its load history, not to this output). With Snowpipe, a message that is not JSON fails on its own rather than making Snowpipe skip the file. A redelivered batch uploads again under a new request id, so delivery is at-least-once, and a file whose Snowpipe notification failed stays in the stage unloaded.

Examples​

Stage JSON and load it with Snowpipe​

Uploads batches of JSON to a stage every 30 seconds and asks a Snowpipe to load each file.

output:
  snowflake_write:
    account: myorg-myaccount
    user: LOADER
    role: LOADER_ROLE
    database: ANALYTICS
    schema: RAW
    stage: "@EVENTS_STAGE"
    path: events
    snowpipe: ANALYTICS.RAW.EVENTS_PIPE
    private_key: ${SNOWFLAKE_PRIVATE_KEY}
    batching:
      count: 50000
      period: 30s

Fields​

account​

The Snowflake account identifier, preferably in organisation-account form (myorg-myaccount). An account locator (xy12345, or xy12345.us-east-2.aws) is also accepted.

Type: string

user​

The Snowflake user to authenticate as.

Type: string

region​

The region, for an account locator whose URL needs it (e.g. us-east-2). Not needed with an organisation-account identifier.

Type: string

cloud​

The cloud (aws, azure or gcp), for an account locator whose URL needs it.

Type: string

role​

The role to use. When unset, the user's default role applies.

Type: string

warehouse​

The warehouse for the session. Uploading to a stage does not need one; set it if your account requires a warehouse for every session.

Type: string

database​

The database of the stage.

Type: string

schema​

The schema of the stage.

Type: string

stage​

The internal stage to upload to, e.g. @MY_STAGE, @~ (the user stage) or @%MY_TABLE (a table stage). Messages are grouped by stage, so this may vary per message.

This field supports interpolation functions.

Type: string

path​

The directory within the stage, e.g. events/${! now().ts_format("2006/01/02") }.

This field supports interpolation functions.

Type: string
Default: ""

file_name​

The staged file's name, without extension or directories. Defaults to the request_id, a new UUID per file. The edge appends its node ID, the execution ID and a short hash of the output, so nodes (or executions of an updated job) never write the same file. Within one execution a retried batch keeps its name when the name is derived from the data (for example a partition and offset); a time-based name is reused by different batches. Messages whose stage, snowpipe, path or file_name differ go to separate files. An existing file of the same name is overwritten, and Snowpipe does not load a file name it has already loaded, so keep names unique when snowpipe is set.

This field supports interpolation functions.

Type: string
Default: ""

file_extension​

The file extension, before any compression suffix such as .gz.

Type: string
Default: "json"

compression​

How each staged file is compressed. AUTO is gzip. The compression suffix (.gz, .zst, .deflate, .raw_deflate) is appended to the file name.

Type: string
Default: "AUTO"

Options: AUTO, NONE, GZIP, DEFLATE, RAW_DEFLATE, ZSTD

password​

The user's password, as an alternative to key-pair auth. Snowpipe needs key-pair auth.

Secret

This field contains sensitive information. Use a secret reference rather than a literal value.

Type: string

private_key​

The user's RSA private key as PEM (PKCS#8, encrypted PKCS#8 or PKCS#1). Newlines collapsed to \n and bare base64 DER are also accepted, so the key can come from an environment variable, e.g. ${SNOWFLAKE_PRIVATE_KEY}. The matching public key must be registered with ALTER USER ... SET RSA_PUBLIC_KEY.

Secret

This field contains sensitive information. Use a secret reference rather than a literal value.

Type: string

private_key_file​

Path to a file holding the RSA private key, as an alternative to private_key.

Type: string

private_key_pass​

Passphrase for an encrypted private key.

Secret

This field contains sensitive information. Use a secret reference rather than a literal value.

Type: string

snowpipe​

A Snowpipe (DATABASE.SCHEMA.PIPE) to notify of each staged file, so it is loaded into its table. The pipe must copy from this stage's root and read JSON (FILE_FORMAT = (TYPE = JSON)). Requires key-pair auth.

This field supports interpolation functions.

Type: string

request_id​

The identifier sent with each Snowpipe request, and the default file name.

This field supports interpolation functions.

Type: string
Default: "${! uuid_v4() }"

upload_parallel_threads​

The number of parallel threads the upload uses (1 to 99).

Type: int
Default: 4

max_in_flight​

The maximum number of batches uploaded in parallel.

Type: int
Default: 1

client_session_keep_alive​

Keep the session alive with a heartbeat so it does not expire after four hours of inactivity.

Type: bool
Default: false

batching​

How messages are grouped into files: each batch becomes one file per distinct stage, snowpipe, path and file name. Without batching every message becomes its own file; set a count or byte_size and a period.

Type: object

batching.count​

A number of messages at which the batch should be flushed. If 0 disables count based batching.

Type: int
Default: 0

batching.byte_size​

An amount of bytes at which the batch should be flushed. If 0 disables size based batching.

Type: int
Default: 0

batching.period​

A period in which an incomplete batch should be flushed regardless of its size.

Type: string
Default: ""

batching.check​

A Bloblang query that should return a boolean value indicating whether a message should end a batch.

Type: string
Default: ""

batching.processors​

A list of processors to apply to a batch as it is flushed. This allows you to aggregate and archive the batch however you see fit. Please note that all resulting messages are flushed as a single batch, therefore splitting the batch into smaller batches using these processors is a no-op.

Type: array of processor

_file_scope​

Internal, filled in by the Expanso runtime. Do not set it; submissions that do are rejected.

Type: string
Default: ""