Stream

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

Manage the lifecycle of new and existing streams
Manage the grants of a stream

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 public preview.

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

Default Configuration
Data Products Configuration

databases:
  <database-name>:
    schemas:
      <schema-name>:
        streams:
          <stream-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

- stream:
    name: <stream-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)

Supported parameters

The engine supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
`append_only`	Optional	Boolean	Specifies whether this is an append-only stream. Append-only streams track row inserts only. Update and delete operations (including table truncates) are not recorded.
`comment`	Optional	String	Specifies a comment for the stream
`deleted`	Optional	Boolean: `True` enables deletion prevention, `False` does nothing	Specifies what objects are allowed to be deleted
`insert_only`	Optional	Boolean	Creates an insert-only stream type. Caution: Creating an `insert_only` stream is only supported on external tables. Setting the value of this parameter to `True` on non-external tables throws an error in Snowflake.
`grants`	Optional	Map: See Supported Stream Grants to Roles	List of Privileges and Roles to which privileges are granted on the current stream
`manage_mode`	Optional	String: `all` (default), `none`, `grants`	Configures what properties to manage for the stream. See Changing Manage Mode before changing the value.
`on_table`	Optional	String/Object: See here for a definition of `on_table`	The table whose changes are tracked by the stream
`show_initial_rows`	Optional	Boolean	Specifies whether to return all existing rows in the source table as row inserts the first time the stream is consumed

warning

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

`on_table` parameter

In the on_table parameter of the stream, you can specify either the table's name (if the table belongs to the same schema and database as the external table) or the schema and database names.

See Database-Level Object Reference section for more information

The on_table parameter supports the following parameters if explicitly provided:

Configuration Key	Required/Optional	Data Types and Values	Description
`database`	Required - if the table is present in another database than the current one	String	Name of the database in which the table exists
`schema`	Required - if the table is present in another database or another schema	String	Name of the schema in which the table exists
`table`	Required	String	Name of the table

Example

on_table:
  table: TABLE_1
  schema: SCHEMA_1
  database: DATABASE_2

Supported stream grants to roles

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

ALL PRIVILEGES
SELECT
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

Default Configuration
Data Products Configuration

databases:
  SALES_RECORD:
    schemas:
      SALES:
        streams:
          STREAM_1:
            append_only: false
            comment: "test stream"
            on_table:
              table: TABLE_1
              schema: SCHEMA_1
              database: DATABASE_2
            grants:
              select:
                - SYSADMIN
                - ACCOUNTADMIN

- stream:
    name: STREAM_1
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    comment: "test stream"
    on_table:
      table: rel(table.TABLE_1)
      schema: rel(schema.SCHEMA_1)
      database: rel(database.DATABASE_2)
    grants:
      select:
        - SYSADMIN
        - ACCOUNTADMIN

Usage​

Supported parameters​

on_table parameter​

Example​

Supported stream grants to roles​

Examples​