Categories:

Data Pipeline DDL

ALTER STREAM

Modifies the properties, columns, or constraints for an existing stream.

See also:

CREATE STREAM , DROP STREAM , SHOW STREAMS , DESCRIBE STREAM

In this Topic:

Syntax

ALTER STREAM [ IF EXISTS ] <name> SET
  [ APPEND_ONLY = TRUE | FALSE ]
  [ INSERT_ONLY = TRUE ]
  [ TAG <tag_key> = '<tag_value>' [ , <tag_key> = '<tag_value>' ... ] ]
  [ COMMENT = '<string_literal>' ]

ALTER STREAM [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER STREAM <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

ALTER STREAM [ IF EXISTS ] <name> UNSET COMMENT

Parameters

name

Identifier for the stream to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

SET ...

Specifies the properties to set for the stream:

APPEND_ONLY = TRUE | FALSE

Only supported for streams on standard tables or streams on views that query standard tables only. 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. For example, if 10 rows are inserted into a table and then 5 of those rows are deleted before the offset for an append-only stream is advanced, the stream records 10 rows.

This type of stream improves query performance over standard streams and is very useful for extract, load, transform (ELT) and similar scenarios that depend exclusively on row inserts.

A standard stream joins the deleted and inserted rows in the change set to determine which rows were deleted and which were updated. An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream. For example, the source table can be truncated immediately after the rows in an append-only stream are consumed, and the record deletions do not contribute to the overhead the next time the stream is queried or consumed.

Note

  • If both APPEND_ONLY and INSERT_ONLY are unset or set to FALSE, the stream is a standard (delta) stream.

  • Either APPEND_ONLY or INSERT_ONLY can be set to TRUE on a stream, but not both. The stream type can be changed at any time.

INSERT_ONLY = TRUE | FALSE

Supported on external tables only. Specifies whether this is an insert-only stream. Insert-only streams track row inserts only; they do not record delete operations that remove rows from an inserted set (i.e. no-ops). For example, in-between any two offsets, if File1 is removed from the cloud storage location referenced by the external table, and File2 is added, the stream returns records for the rows in File2 only. Unlike when tracking CDC data for standard tables, Snowflake cannot access the historical records for files in cloud storage.

Note

  • If both APPEND_ONLY and INSERT_ONLY are unset or set to FALSE, the stream is a standard (delta) stream.

  • Either APPEND_ONLY or INSERT_ONLY can be set to TRUE on a stream, but not both. The stream type can be changed at any time.

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name (i.e. the key) and the tag value.

The tag value is always a string, and the maximum number of characters for the tag value is 256. The maximum number of unique tag keys that can be set on an object is 20.

COMMENT = 'string'

Adds a comment or overwrites an existing comment for the stream.

UNSET ...

Specifies one or more properties/parameters to unset for the stream, which resets them back to their defaults:

  • APPEND_ONLY

  • INSERT_ONLY

  • TAG tag_key [ , tag_key ... ]

  • COMMENT

Usage Notes

Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

Examples

Add a comment for a stream:

ALTER STREAM mystream SET COMMENT = 'New comment for stream';
Back to top