Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 42 views

    21.streams in Snowflake

    Uploaded by

    mysites220
    Streams in Snowflake record changes made to tables via DML operations like inserts, updates, and deletes. Streams maintain metadata about each change including the action, whether it was part of an update, and a unique row ID. The stream tracks changes using an offset that moves to the next point once changes are consumed. Streams can be combined with tasks to create continuous data pipelines. There are standard streams that track all changes, append-only streams for inserts only, and insert-only streams for external tables that only track inserts.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    21.streams in Snowflake

    Uploaded by

    mysites220
    42 views8 pages
    Streams in Snowflake record changes made to tables via DML operations like inserts, updates, and deletes. Streams maintain metadata about each change including the action, whether it was part of an update, and a unique row ID. The stream tracks changes using an offset that moves to the next point once changes are consumed. Streams can be combined with tasks to create continuous data pipelines. There are standard streams that track all changes, append-only streams for inserts only, and insert-only streams for external tables that only track inserts.

    Original Description:

    Original Title

    21.Streams_in_Snowflake

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Streams in Snowflake record changes made to tables via DML operations like inserts, updates, and deletes. Streams maintain metadata about each change including the action, whether it was part of an update, and a unique row ID. The stream tracks changes using an offset that moves to the next point once changes are consumed. Streams can be combined with tasks to create continuous data pipelines. There are standard streams that track all changes, append-only streams for inserts only, and insert-only streams for external tables that only track inserts.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    42 views8 pages

    21.streams in Snowflake

    Uploaded by

    mysites220
    Streams in Snowflake record changes made to tables via DML operations like inserts, updates, and deletes. Streams maintain metadata about each change including the action, whether it was part of an update, and a unique row ID. The stream tracks changes using an offset that moves to the next point once changes are consumed. Streams can be combined with tasks to create continuous data pipelines. There are standard streams that track all changes, append-only streams for inserts only, and insert-only streams for external tables that only track inserts.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 8

    Streams in Snowflake

    by
    Janardhan
    Bandi
    Agenda
    • What is a stream?
    • Metadata of streams (Stream Columns)
    • How a stream works?
    • Consuming data from streams
    • Types of Streams
    What is a Stream?
    • A stream object records DML changes made to tables including inserts, updates, and
    deletes.
    • Also Stream stores metadata about each change, so that actions can be taken using this
    metadata.
    • We call this process as change data capture (CDC).
    • Streams tracks all row level changes to a source table using offset but doesn't store the
    changed data.
    • Once these changes are consumed by the Target table, this offset moves to the next
    point.
    • Streams can be combined with tasks to set continuous data pipelines.
    • Snowpipe + Stream + Task  Continuous Data Load.
    Metadata of Streams
    Along with the changes made to the source table, Streams maintain 3 metadata fields.
    1. METADATA$ACTION: Indicates the DML operation (INSERT, DELETE) recorded.
    2. METADATA$ISUPDATE: Indicates whether the operation was part of an UPDATE
    statement. Updates to rows in the source object are represented as a pair of DELETE and
    INSERT records in the stream with a metadata column METADATA$ISUPDATE values set
    to TRUE.
    3. METADATA$ROW_ID: Specifies the unique and immutable ID for the row, which can be
    used to track changes to specific rows over time.
    How a Stream works? or Data flow in a Stream
    Consuming data from Streams
    We use merge statement for consuming the changes from stream and applying the same
    on Target tables.
    // To identify Insert records
    WHERE metadata$action = 'INSERT AND metadata$isupdate = 'FALSE’;

    // To identify Update records


    WHERE metadata$action = 'INSERT' AND metadata$isupdate = 'TRUE’;

    To identify Delete records


    WHERE metadata$action = 'DELETE' AND metadata$isupdate = 'FALSE’;

    Note: If we want to consume these changes to multiple tables then we have to create multiple
    streams.
    Types of Streams
    1. Standard Streams: A Standard stream tracks all DML changes to the source object,
    including inserts, updates, and deletes (including table truncates).
    CREATE OR REPLACE STREAM my_stream ON TABLE my_table;

    2. Append-only Streams: An Append-only stream tracks row inserts only. Update and
    delete operations (including table truncates) are not recorded.
    CREATE OR REPLACE STREAM my_stream ON TABLE my_table
    APPEND_ONLY = TRUE;

    3. Insert-only Streams: Supported for External tables only. An insert-only stream tracks
    row inserts only. They do not record delete operations.
    CREATE OR REPLACE STREAM my_stream ON EXTERNAL TABLE my_table
    INSERT_ONLY = TRUE;
    Thank You

    You might also like