Skip to content

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

- 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>)
databases:
  <database-name>:
    schemas:
      <schema-name>:
        streams:
          <stream-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <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
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this stream depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
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
on_view Optional String/Object: See here for a definition of on_view The view 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

on_view parameter

In the on_view parameter of the stream, you can specify either the view's name (if the view 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_view parameter supports the following parameters if explicitly provided:

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

Example

on_view:
  view: VIEW_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

on_table

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

on_view

- stream:
    name: STREAM_1
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    comment: "test stream"
    on_view:
      view: rel(view.VIEW_1)
      schema: rel(schema.SCHEMA_1)
      database: rel(database.DATABASE_2)
    grants:
      select:
        - SYSADMIN
        - ACCOUNTADMIN