# PIPE

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

Example:

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

auto_ingest: false
```

## Schema

* <mark style="background-color:red;">**copy**</mark> (dict)
  * <mark style="background-color:red;">**table**</mark> (ident) - target table for COPY INTO
  * <mark style="background-color:red;">**stage**</mark> (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](https://docs.snowddl.com/basic/yaml-configs/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`&#x20;
  * **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](https://docs.snowddl.com/guides/other-guides/integrations) 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](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-manage.html#recreating-pipes). 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](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-billing.html).
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](https://docs.snowddl.com/basic/yaml-configs/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](https://docs.snowddl.com/basic/yaml-configs/schema):

* `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](https://docs.snowflake.com/en/sql-reference/sql/create-pipe.html)
* [ALTER PIPE](https://docs.snowflake.com/en/sql-reference/sql/alter-pipe.html)
* [SHOW PIPES](https://docs.snowflake.com/en/sql-reference/sql/show-pipes.html)
* [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/pipe.py)