Snowflake Stream & Change Data Capture | Chapter-17 | Snowflake Hands-on Tutorial

Summary of the subtitles (Snowflake Stream & CDC hands-on tutorial)

The video explains how Snowflake implements Change Data Capture (CDC) using Stream objects, positioned as a simpler alternative to traditional CDC mechanisms such as update timestamps, row versioning, or log scanning.

---

Key technological concepts & why Streams

• Traditional CDC approaches can be complex/cumbersome, for example:
  • Comparing update timestamps
  • Using row-version fields
  • Scanning transaction logs
• Snowflake Streams provide an “elegant” CDC solution by tracking DML changes directly.
• A Stream object is a first-class object created within a schema, on top of a source table.
• When you create a stream, Snowflake adds three metadata columns to track changes:
  • metadata$action (type of change: insert/update/delete)
  • metadata$is_update (true for updates; false for insert/delete)
  • metadata$row_id (row identifier)

When querying:

• The base table shows the full current dataset.
• The stream shows only the changes since the stream offset (not the full table).

---

Stream types and parameters

1) Standard “delta” stream (default)

• Captures all DML: insert, update, delete
• Also mentions truncate as being tracked
• Stream type shown as delta

2) Append-only streams (parameter-driven CDC reduction)

For cases where you only care about new inserts:

• Use append_only = true when creating the stream on normal tables.

Result:

• Inserts are returned in the stream.
• Updates and deletes are not captured.

3) Insert-only streams (external tables only)

• For external tables, the stream requires insert_only = true.

Behavior:

• Works like an inserts-only CDC feed for data arriving into the external table (updates/deletes not captured).

---

Managing multiple streams, cloning, and independence

• You can create multiple streams on the same source table, each with its own configuration (e.g., one delta stream + one append-only stream).
• Streams can be cloned using the clone keyword:
  • The clone begins tracking from the time it is created.
  • The clone is independent, but references the same source table for ongoing changes.
• The video emphasizes that streams don’t store the entire dataset; they maintain an offset to track what changes have been consumed.

---

Offset model (how changes are determined)

The tutorial uses a timeline example with offsets (e.g., versions v0, v1, v2):

• Streams store an offset (stream position/version).
• When querying a stream:
  • Snowflake compares the current table version with the stream’s offset
  • It returns only changes between those points
• When you consume data (query/insert from the stream in the tutorial), the stream offset advances.
• This design is described as preventing duplicate consumption within the same transaction, assuming transactions are designed correctly.

---

DML-to-stream behavior (how updates appear)

• Insert: produces one row in the stream.
• Delete: produces one row in the stream.
• Update: represented as two rows in the stream:
  • One row for the old version (deleted)
  • One row for the new version (inserted)
• metadata$is_update indicates whether an action corresponds to an update.

---

Hands-on walkthrough examples covered

• Creating a customer table and a corresponding delta stream:
  • Verified that inserts/deletes/updates appear with the metadata columns.
• Demonstrating inspection of stream definitions and state:
  • show streams
  • get_ddl(stream_name) for DDL
  • alter stream ... set/unset comment
  • describe stream
  • drop stream to remove tracking
• Demonstrated:
  • Creating multiple streams on the same table
  • Append-only stream behavior (insert captured; delete/update not)
  • Streams on transient tables behaving the same as permanent tables
• Time travel / retention relationship:
  • A source table with DATA_RETENTION_TIME_IN_DAYS = 0 does not stop stream CDC behavior.
  • Streams have their own retention window (described as 14 days by default).

---

Consuming stream changes into downstream tables

The tutorial shows a pipeline pattern:

• Create a downstream table (e.g., customer_dim) fed from a source/raw table (e.g., customer_raw).
• Create a stream on the raw table.
• Filter stream output using metadata, for example:
  • WHERE metadata$action = 'INSERT' AND metadata$is_update = FALSE
• Demonstrated consumption within a transaction (begin/commit), then verifying:
  • Stream offset advances
  • No data remains available in the stream after consumption

---

Streams on external tables

• Stream creation differs for external tables:
  • Must use insert_only = true
• Behavior and stream mode shown as insert only.

---

Automation note (next topic teased)

• The video concludes by introducing Tasks as the mechanism to automate SQL pipelines that move data from streams to other tables, pointing to Chapter 18 for details.

---

Main speakers / sources

• Primary speaker: the instructor/host of “Data Engineering Simplified” (mentioned repeatedly as teaching Snowflake hands-on).
• Primary source referenced: Snowflake documentation (used for stream behavior/limits claims; described as not giving a hard limit on stream creation).