Configure Select Statement in a Snowflake PIPE

A Snowflake PIPE defines the COPY INTO TABLE statement used by Snowpipe to load data from an ingestion queue into tables.

The PIPE select_statement parameter, part of the copy_statement parameter, simplifies loading data. Using the select statement brings more flexibility to the data-loading process, allowing you to smoothly integrate SQL queries into the copy into operation. It enhances SOLE's adaptability, enabling you to customize data extraction based on specific query criteria, thereby optimizing the efficiency of the overall data management workflow.

Example

Default Configuration

/dataops/snowflake/database.template.yml

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        pipes:
          PIPE_1:
            copy_statement:
              into:
                database: "DATABASE_1"
                schema: "SCHEMA_2"
                table: "TABLE_1"
              from:
                database: "PIPELINEWISE_DATABASE"
                schema: "PUBLIC"
                stage: "PIPELINEWISE_STAGE"
                select_statement: "select $1
                  , $2
                  , $3
                  , $4"
            grants:
              MONITOR:
                - SYSADMIN

Data Products Configuration

/dataops/snowflake/database_1/schema_1/pipe_1.yml

- pipe:
    name: PIPE_1
    schema: rel(schema.SCHEMA_1)
    database: rel(database.DATABASE_1)
    copy_statement:
      into:
        database: "DATABASE_1"
        schema: "SCHEMA_2"
        table: "TABLE_1"
      from:
        database: "PIPELINEWISE_DATABASE"
        schema: "PUBLIC"
        stage: "PIPELINEWISE_STAGE"
        select_statement: "select $1
          , $2
          , $3
          , $4"
    grants:
      MONITOR:
        - SYSADMIN

In the above configuration, a pipe object has been created within the specified database and schema. The copy_statement parameter is pivotal, facilitating the transfer of data from the designated location referred to in the from parameter to the designated location referred to in the into parameter.

The select_statement plays a crucial role, enabling the extraction of a specific data section using SQL queries. This mechanism allows for a precise and controlled copying process, ensuring that only the desired data is transferred.

Overall, the configuration streamlines data movement within the defined database and schema by leveraging SQL queries for selective data retrieval and copying.