PIPE

Config path: /<database>/<schema>/pipe/<name>.yaml

Example:

copy:
  table: test_table_2
  stage: test_external_stage
  transform:
    id: "GET($1, 'id')"
    name: "GET($1, 'name')"

auto_ingest: false

Schema

• copy (dict)

  • table (ident) - target table for COPY INTO

  • stage (ident) - source stage for COPY INTO

  • path (str) - path prefix for files in stage

  • pattern (str) - regular expression to filter files in stage

  • file_format (ident) - file format for files in stage

  • match_by_column_name (str) - case_sensitive / case_insensitive

  • include_metadata (dict)

    • {key} (ident) - column name in target table

    • {value} (ident) - column name in metadata, e.g. METADATA$FILENAME

  • transform (dict)

    • {key} (ident) - column name in target table

    • {value} (str) - SQL expression to extract column value from source stage

  • options (dict)

    • {key} (ident) - COPY option name

    • {value} (bool, float, int, list, str) - COPY option value

• auto_ingest (bool) - enable AUTO_INGEST

• aws_sns_topic (str) - SNS topic for S3 bucket

• integration (ident) - notification integration name for Azure

• error_integration (ident) - notification integration to monitor ingestion errors

• comment (str)

Usage notes

1. Re-creating pipes correctly is complicated. Make sure you read & fully understand pipe recreation considerations. SnowDDL can only suggest or apply DDL queries to change pipes, but it cannot pause, monitor and refresh pipes for you.
2. Maintenance of active pipes implicitly incurs additional costs.
3. file_format can only be specified by name referencing to FILE_FORMAT object.

4. Notification integration mentioned in error_integration parameter must be additionally specified in schema parameter owner_integration_usage. Otherwise schema owner role will not be able to send notifications.

Additional privileges

In order for PIPE objects to operate properly, the following additional grants should be added to OWNER role in schema config:

• owner_integration_usage - please specify names of INTEGRATION objects used by pipes. Pipes may work without explicit INTEGRATION USAGE grant to OWNER role, but it is not guaranteed for Snowflake keep it this way forever.

Links

• CREATE PIPE
• ALTER PIPE
• SHOW PIPES
• Parser & JSON Schema (GitHub)