Snowflake (S3 Staging)

Data Warehouse Target

Synopsis

The Snowflake S3 target stages telemetry files to Amazon S3, then executes COPY INTO commands on Snowflake to load data into tables.

Schema

- name: <string>
  description: <string>
  type: amazonsnowflake
  properties:
    account: <string>
    username: <string>
    password: <string>
    database: <string>
    schema: <string>
    warehouse: <string>
    role: <string>
    staging_bucket: <string>
    staging_prefix: <string>
    region: <string>
    key: <string>
    secret: <string>
    session: <string>
    table: <string>
    name: <string>
    format: <string>
    compression: <string>
    extension: <string>
    tables:
      - table: <string>
        schema: <string>
        name: <string>
        format: <string>
        compression: <string>
        extension: <string>
    batch_size: <integer>
    max_size: <integer>
    timeout: <integer>
    part_size: <integer>
    drop_unknown_stream_events: <boolean>
    field_format: <string>
    max_rows_per_rowgroup: <numeric>
    buffer_size: <numeric>
    data_page_version: <string>
    metadata: <key-value>
    debug:
      status: <boolean>
      dont_send_logs: <boolean>

Configuration

The following fields are used to define the target:

Base Target Fields

Field	Required	Default	Description
name	Y	-	Unique identifier for this target
description	N	-	Human-readable description
type	Y	-	Must be amazonsnowflake
pipelines	N	-	Pipeline names to apply before sending
status	N	true	Enable/disable the target

Snowflake Connection

Field	Required	Default	Description
account	Y	-	Snowflake account identifier (e.g., abc123.us-east-1)
username	Y	-	Snowflake username
password	Y	-	Snowflake password
database	Y	-	Snowflake database name
schema	N	PUBLIC	Snowflake schema name. Must be a valid SQL identifier — letters, digits, and underscores only, not starting with a digit.
warehouse	N	-	Snowflake virtual warehouse name
role	N	-	Snowflake role name

S3 Staging Configuration

Field	Required	Default	Description
staging_bucket	Y	-	S3 bucket name for staging files
staging_prefix	N	snowflake-staging/	S3 prefix path
region	Y	-	AWS region for S3 bucket
key	N*	-	AWS access key ID (uses default credentials chain if omitted)
secret	N*	-	AWS secret access key
session	N	-	AWS session token for temporary credentials

* = key and secret take effect only when both are set. session applies only alongside them.

Table Configuration

Field	Required	Default	Description
table	Y*	-	Catch-all table name for all events
schema	Y*	PUBLIC	Avro/Parquet schema for the catch-all table. This is the same key as the Snowflake schema above — see the warning below.
name	N	vmetric.{{.Timestamp}}.{{.Extension}}	File naming template
format	N	json	File format. Use json, avro, or parquet — see the warning below.
compression	N	zstd	Compression algorithm
extension	N	-	File extension override. Defaults to the resolved format.
tables	N	-	Multiple table configurations (see below)
tables.table	Y	-	Target table name
tables.schema	Y*	-	Avro/Parquet schema for this table: a Library name, built-in name, file path, or inline JSON definition
tables.name	Y	-	File naming template for this table
tables.format	N	-	File format for this table. Falls back to the catch-all format.
tables.compression	N	-	Compression algorithm for this table. Falls back to the catch-all compression.
tables.extension	N	-	File extension override for this table. Falls back to the catch-all extension.

warning

schema is a single top-level key read for two different purposes: the Snowflake SQL schema name and the Avro/Parquet schema reference. Its value is validated as a SQL identifier, so a schema file name or path such as event_schema.avsc is rejected at startup and the target does not initialize. Use tables[].schema to reference an Avro or Parquet schema per table.

warning

Only json, avro, and parquet are written correctly. csv, orc, and xml pass configuration validation, but the staged file is written as JSON while the COPY INTO command still declares the requested format, so the load fails.

Parquet Options

These apply only when format is parquet.

Field	Required	Default	Description
max_rows_per_rowgroup	N	10000	Maximum rows per Parquet row group
buffer_size	N	262144	Parquet page buffer size in bytes (256KB)
data_page_version	N	V2	Parquet data page version (V1, V2)
metadata	N	-	Key/value pairs written into the Parquet file metadata

* = At least one of table (catch-all) or tables (multiple) must be configured. For Avro/Parquet formats, schema is required.

Batch Configuration

Field	Required	Default	Description
batch_size	N	100000	Maximum events per file before flush
max_size	N	33554432	Maximum file size in bytes before flush (32MB)
timeout	N	300	COPY INTO command timeout in seconds
part_size	N	5	S3 multipart upload part size in MB. Values below 5 are raised to 5.
drop_unknown_stream_events	N	true	Drop events that do not match any configured table

Normalization

Field	Required	Default	Description
field_format	N	-	Apply format normalization (ECS, ASIM, UDM)

Debug Options

Field	Required	Default	Description
debug.status	N	false	Enable debug logging for this target
debug.dont_send_logs	N	false	Log events without sending to Snowflake

Details

Architecture Overview

The Snowflake S3 target implements a two-stage loading pattern:

1. Stage Files to S3: Events are written to files in S3 using the configured format
2. Execute COPY INTO: SQL commands load data from S3 into Snowflake tables

Snowflake Connection

Account Identifier:

• Format: <account_locator>.<region> (e.g., abc123.us-east-1)
• Account locator is visible in your Snowflake URL
• Region is the cloud region where your Snowflake account is deployed

Authentication:

• Uses username/password authentication
• Credentials are used to connect to Snowflake SQL API v2
• Supports optional warehouse and role specification

Database and Schema:

• Database name is required and must be a valid SQL identifier
• Schema defaults to PUBLIC if not specified
• Both database and schema names are validated for SQL compliance

Snowflake Permissions

The Snowflake user requires permissions to:

• Execute SQL statements using the specified warehouse
• Write data to the target database and schema
• Access the S3 staging location (configured separately in Snowflake)

S3 Staging Operations

File Upload:

• Files are staged to s3://bucket/prefix/table/filename structure
• Uses AWS SDK multipart upload for large files
• Supports AWS credentials chain (access key, IAM role, instance profile)

Cleanup:

• Staged files are automatically deleted after successful COPY INTO execution
• Failed uploads remain in S3 for troubleshooting

File Format Support

Valid Formats:

• CSV: Comma-separated values with optional headers
• JSON: Newline-delimited JSON objects
• AVRO: Schema-based binary format (requires schema)
• ORC: Optimized row columnar format
• PARQUET: Columnar storage format (requires schema)
• XML: XML document format

Schema Requirements:

• Avro and Parquet formats require schema field with valid schema definition
• Schema must match the expected table structure in Snowflake
• Other formats use schema inference from data

Multi-Table Routing

Catch-All Table:

• Use table field to send all events to a single table
• Simplest configuration for single-destination scenarios

Multiple Tables:

• Use tables array to route different event types to different tables
• Each table entry specifies table, schema, name, format fields
• Events routed based on SystemS3 field in pipeline

Example Configuration:

tables:
  - table: security_events
    schema: security_schema.avsc
    name: security.{{.Timestamp}}.parquet
    format: parquet
  - table: access_logs
    schema: access_schema.avsc
    name: access.{{.Timestamp}}.parquet
    format: parquet

Performance Considerations

Batch Processing:

• Events are buffered until batch_size or max_size limits are reached
• Larger batches reduce S3 API calls and COPY INTO operations
• Balance batch size against latency requirements

Upload Optimization:

• Multipart uploads automatically handle large files
• Configure part_size for optimal network performance
• Default part size is AWS SDK default (5 MB)

COPY INTO Performance:

• COPY INTO commands are executed with configurable timeout
• Failed COPY operations return errors for retry logic
• Warehouse must be running (resumed) for COPY INTO to succeed

Warehouse State

Ensure the virtual warehouse is running before sending data. COPY INTO commands will fail if the warehouse is suspended. Configure warehouse auto-resume or manual resume procedures.

Error Handling

Upload Failures:

• Failed S3 uploads are retried based on sender configuration
• Permanent failures prevent COPY INTO execution
• Check S3 bucket permissions and network connectivity

COPY INTO Failures:

• Schema mismatches between files and tables cause failures
• Invalid SQL identifiers (database, schema, table names) are rejected at validation
• Check Snowflake query history for detailed error messages

Examples

Basic Configuration

Sending telemetry to Snowflake using S3 staging with Parquet format...

targets: - name: snowflake-warehouse type: amazonsnowflake properties: account: abc123.us-east-1 username: datastream_user password: "${SNOWFLAKE_PASSWORD}" database: PRODUCTION_DATA warehouse: COMPUTE_WH staging_bucket: datastream-staging region: us-east-1 table: EVENTS schema: event_schema.avsc name: events.{{.Timestamp}}.parquet format: parquet

With AWS Credentials

Using explicit AWS credentials for S3 staging access...

targets: - name: snowflake-secure type: amazonsnowflake properties: account: xyz789.us-west-2 username: security_user password: "${SNOWFLAKE_PASSWORD}" database: SECURITY_ANALYTICS warehouse: SECURITY_WH role: SECURITY_ADMIN staging_bucket: security-logs-staging staging_prefix: snowflake/ region: us-west-2 key: "${AWS_ACCESS_KEY}" secret: "${AWS_SECRET_KEY}" table: SECURITY_EVENTS schema: security_schema.avsc name: security.{{.Timestamp}}.parquet format: parquet

Multi-Table Configuration

Routing different event types to separate Snowflake tables...

targets: - name: snowflake-multi-table type: amazonsnowflake properties: account: abc123.us-east-1 username: analytics_user password: "${SNOWFLAKE_PASSWORD}" database: ANALYTICS warehouse: ANALYTICS_WH staging_bucket: analytics-staging region: us-east-1 tables: - table: AUTHENTICATION_EVENTS schema: auth_schema.avsc name: auth.{{.Timestamp}}.parquet format: parquet - table: NETWORK_EVENTS schema: network_schema.avsc name: network.{{.Timestamp}}.parquet format: parquet - table: APPLICATION_LOGS schema: app_schema.avsc name: app.{{.Timestamp}}.parquet format: parquet

High-Volume Configuration

Optimizing for high-volume ingestion with batch limits and compression...

targets: - name: snowflake-high-volume type: amazonsnowflake properties: account: abc123.us-east-1 username: streaming_user password: "${SNOWFLAKE_PASSWORD}" database: HIGH_VOLUME_DATA warehouse: LARGE_WH staging_bucket: streaming-staging region: us-east-1 batch_size: 100000 max_size: 134217728 part_size: 16 timeout: 600 table: STREAMING_EVENTS schema: streaming_schema.avsc name: stream.{{.Timestamp}}.parquet format: parquet compression: snappy

JSON Format

Using JSON format for flexible schema evolution and debugging...

targets: - name: snowflake-json type: amazonsnowflake properties: account: abc123.us-east-1 username: dev_user password: "${SNOWFLAKE_PASSWORD}" database: DEVELOPMENT warehouse: DEV_WH staging_bucket: dev-staging region: us-east-1 table: TEST_EVENTS name: test.{{.Timestamp}}.json format: json

With Normalization

Applying ECS normalization before loading to Snowflake...

targets: - name: snowflake-normalized type: amazonsnowflake properties: account: abc123.us-east-1 username: security_user password: "${SNOWFLAKE_PASSWORD}" database: SECURITY_DATA warehouse: SECURITY_WH staging_bucket: security-staging region: us-east-1 field_format: ECS table: ECS_EVENTS schema: ecs_schema.avsc name: ecs.{{.Timestamp}}.parquet format: parquet

Production Configuration

Production-ready configuration with performance tuning, AWS credentials, and multi-table routing...

targets: - name: snowflake-production type: amazonsnowflake properties: account: production.us-east-1 username: production_user password: "${SNOWFLAKE_PASSWORD}" database: PRODUCTION_ANALYTICS warehouse: PRODUCTION_WH role: DATA_ENGINEER staging_bucket: production-staging-bucket staging_prefix: datastream/snowflake/ region: us-east-1 key: "${AWS_ACCESS_KEY}" secret: "${AWS_SECRET_KEY}" batch_size: 50000 max_size: 67108864 part_size: 10 timeout: 300 field_format: ASIM tables: - table: SECURITY_EVENTS schema: security_schema.avsc name: security.{{.Timestamp}}.parquet format: parquet compression: snappy - table: AUDIT_LOGS schema: audit_schema.avsc name: audit.{{.Timestamp}}.parquet format: parquet compression: snappy - table: NETWORK_FLOWS schema: network_schema.avsc name: network.{{.Timestamp}}.parquet format: parquet compression: snappy