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​

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. The major difference is in how you define Snowflake objects in the configuration file. Rather than having a grouped collection of objects, SOLE for Data Products goes for modular, self-describing, and explicit object definition.
Learn more about SOLE for Data Products, currently available as a private preview.

We have also introduced Data products as an extra layer on top of the Dataops.live platform capabilities making managing data products easier than ever. Learn more about Data Products, currently available as a private preview.

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.
caution

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
Last updated on