Skip to main content

Pipe

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with pipe:

  • Manage the lifecycle of new and existing pipes
  • Manage grants of pipe

Usage

databases:
<database-name>:
schemas:
<schema-name>:
pipes:
<pipe-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>

Supported parameters

The engine supports the parameters listed below.

Configuration KeyRequired/OptionalData Types and ValuesDescription
copy_statementRequiredObject: See here for a definition of copy_statementSpecifies the copy statement for the pipe.
Check the Snowflake Docs for COPY INTO table copy options and file format.
auto_ingestOptionalBooleanSpecifies an auto_ingest param for the pipe
aws_sns_topic_arnOptionalStringSpecifies the Amazon Resource Name (ARN) for the SNS topic for your S3 bucket
commentOptionalStringSpecifies a comment for the pipe
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
error_integrationOptionalStringSpecifies the name of the notification integration used to communicate with Amazon SNS.
Info: Required only when configuring Snowpipe to send error notifications using Amazon Simple Notification Service (SNS). For more info, see Enabling Error Notifications for Snowpipe..
grantsOptionalMap: See Supported Pipe Grants to RolesList of Privileges and Roles to which privileges are granted on the current pipe
integrationOptionalStringSpecifies the existing notification integration used to access the storage queue.
Info: Required only when configuring AUTO_INGEST for Google Cloud Storage or Microsoft Azure stages. For more info, see Automating Snowpipe for Google Cloud Storage and Automating Snowpipe for Microsoft Azure Blob Storage. Note: AUTO_INGEST must be set to TRUE.
manage_modeOptionalEnumeration: all (default), none, grantsConfigures what properties to manage for the pipe.
See Changing Manage Mode before changing the value.
warning

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phases for a few parameters like copy_statement, auto_ingest, aws_sns_topic_arn, etc.

Supported pipe grants to roles

Following are the privileges you can grant to roles in the pipe definition:

  • ALL PRIVILEGES
  • MONITOR
  • OPERATE
  • OWNERSHIP
ALL PRIVILEGES handling

When you define ALL PRIVILEGES in the SOLE configuration file, you grant all the privileges listed above to roles on this object except OWNERSHIP. However, the management of ALL PRIVILEGES in SOLE differs from its handling in Snowflake. See Handling ALL PRIVILEGES in SOLE for more information.

Examples

Below are some examples of configuring pipes.

Pipe configuration with the type parameter

databases:
SALES_RECORD:
comment: "product sales record"
schemas:
SCHEMA_1:
comment: "sales records for year 2021"
pipes:
PIPE_1:
comment: A pipe.
auto_ingest: false
error_integration: "NOTIFICATION_INTEGRATION_SNS"
copy_statement:
into:
database: "DATABASE_1"
schema: "SCHEMA_1"
table: "TABLE_1"
from:
database: "DUMMY_DATABASE"
schema: "PUBLIC"
stage: "LIFECYCLE_STAGE"
pattern: ".*[.]csv"
file_format:
type: "CSV"
compression: "AUTO"
binary_format: "utf8"
trim_space: false
enable_octal: false
strip_outer_array: false
strip_null_values: false
replace_invalid_characters: false
ignore_utf8_errors: true
skip_byte_order_mark: false
enforce_length: false
truncate_columns: true
grants:
MONITOR:
- SYSADMIN
- ACCOUNTADMIN

Pipe configuration without the type parameter

databases:
SALES_RECORD_FEB:
comment: "product sales record"
schemas:
SCHEMA_1:
comment: "sales records for year 2021 of feb month"
pipes:
PIPE_2:
comment: A pipe
integration: "NOTIFICATION_INTEGRATION_GCP"
auto_ingest: false
copy_statement:
into:
database: "DATABASE_2"
schema: "SCHEMA_2"
table: "TABLE_2"
from:
database: "DUMMY_DATABASE"
schema: "PUBLIC"
stage: "LIFECYCLE_STAGE_GCP"
pattern: ".*[.]csv"
file_format:
format_database: "DUMMY_DB"
format_schema: "PUBLIC"
format_name: "DMY_FORMAT"
grants:
OPERATE:
- SYSADMIN