9.1 Change Data Capture (CDC) with Snowflake Streams

9.1 Change Data Capture (CDC) with Snowflake Streams

Continuous data transformation in modern cloud data architectures demands efficient Change Data Capture (CDC). Rather than repeatedly executing costly full-table scans or relying on fragile timestamp polling columns, enterprise architectures leverage Snowflake Streams. A stream provides a lightweight, continuous CDC abstraction that records data manipulation language (DML) changes made to a source table, view, or directory stage without replicating underlying storage.

For the SnowPro Advanced: Architect exam, you must master stream internals: how transaction offsets advance, how updates decompose into pre- and post-images, the performance implications of append-only versus standard streams, and strategies to prevent stream staleness in high-throughput enterprise pipelines.

Streams Architecture & Offset Mechanics

A Snowflake Stream does not store actual data rows. Instead, it creates an offset pointer against the source object's transaction history in Snowflake's metadata layer. When you query a stream, Snowflake dynamically evaluates the micro-partitions that were added, modified, or marked as deleted between the stream's current offset timestamp and the current transaction timestamp.

Source Table Timeline:
───[Commit T0]───►───[Commit T1: Inserts]───►───[Commit T2: Updates]───►───[Commit T3: Deletes]───►
       ▲                                                                           ▲
       │                                                                           │
 Stream Offset Pointer                                                      Current Transaction
 (Captures changes between T0 and T3 dynamically at query runtime)

Offset Advancement Rules

A foundational concept tested on the exam is when and how a stream advances its offset:

1. Read-Only Queries (SELECT) DO NOT Advance the Offset: Executing SELECT * FROM my_stream reads the currently unconsumed change records without modifying the stream. The stream offset remains anchored at its prior position. Multiple read queries will return the exact same delta records until consumed by a DML statement.
2. DML Statements Advance the Offset Upon Commit: The stream offset advances only when the stream is queried as the data source within a DML transaction that successfully commits:
   • INSERT INTO <target> SELECT ... FROM <stream>
   • MERGE INTO <target> USING <stream> ON ...
   • UPDATE <target> FROM <stream> SET ...
   • DELETE FROM <target> USING <stream> WHERE ...
   • CREATE TABLE <target> AS SELECT ... FROM <stream>
3. Rollback Behavior: If the enclosing DML transaction fails or issues a ROLLBACK, the stream offset is not advanced. The unconsumed changes remain visible in the stream for subsequent transaction retries.
4. Multiple Independent Streams: Multiple streams can be created on the same source table. Each stream maintains its own independent offset pointer. Consuming Stream A in a DML transaction advances Stream A's offset, while Stream B remains unaffected.

Architect Exam Warning: If you execute multiple separate DML statements in sequence against the same stream without wrapping them in an explicit transaction (BEGIN TRANSACTION ... COMMIT), the very first DML statement that commits will advance the stream offset, leaving the subsequent DML statements with an empty stream!

Stream Types & Specializations

Snowflake provides three specialized stream types tailored to distinct ingestion architectures and storage formats:

1. Standard Streams (Default)

A standard stream tracks all DML modifications applied to the source table: INSERT, UPDATE, and DELETE. Standard streams maintain row identity tracking (METADATA$ROW_ID) to determine whether rows were inserted, updated, or deleted.

-- Create a standard stream on a transactional orders table
CREATE OR REPLACE STREAM cdc_db.staging.orders_stream 
  ON TABLE cdc_db.raw.orders
  COMMENT = 'Standard CDC stream tracking inserts, updates, and deletes';

2. Append-Only Streams (APPEND_ONLY = TRUE)

Append-only streams track only new row insertions (INSERT). They completely ignore UPDATE and DELETE operations performed on the source table.

-- Create an append-only stream for high-velocity IoT sensor telemetry
CREATE OR REPLACE STREAM cdc_db.staging.iot_sensor_stream 
  ON TABLE cdc_db.raw.iot_sensor_readings
  APPEND_ONLY = TRUE
  COMMENT = 'Append-only stream capturing new telemetry events';

Architectural Advantages of Append-Only Streams:

• Reduced Compute & Metadata Overhead: Standard streams must evaluate row IDs and join pre-images with post-images across micro-partitions to detect updates and deletes. Append-only streams bypass row-matching logic entirely, scanning only newly added micro-partitions.
• Ideal Workloads: High-throughput immutable logs, IoT telemetry, clickstreams, audit logs, and Kafka streaming ingestion where records are inserted once and never modified.

3. Insert-Only Streams (INSERT_ONLY = TRUE)

Insert-only streams are for sources whose files are managed outside Snowflake: external tables, externally managed Apache Iceberg tables, and Delta Direct tables (without partition columns). Snowflake cannot see row-level updates or deletes in those files, so an insert-only stream returns rows from newly added files and does not record deletes (for example, a file removed from the bucket).

-- Create an insert-only stream on an external table
CREATE OR REPLACE STREAM cdc_db.staging.ext_logs_stream 
  ON EXTERNAL TABLE cdc_db.raw.ext_web_logs
  INSERT_ONLY = TRUE;

Specialized Streams: Directory Tables and Views

• Streams on Directory Tables: You can create a stream directly on a Snowflake Stage with directory table tracking enabled (CREATE STREAM stage_stream ON STAGE my_stage). This tracks file-level lifecycle events (METADATA$ACTION = 'INSERT' when a file is staged; 'DELETE' when purged), allowing automated processing of unstructured or semi-structured files upon upload.
• Streams on Views: You can create streams on standard relational views and secure views. For a stream on a view to function, change tracking must be enabled on all underlying base tables (ALTER TABLE ... SET CHANGE_TRACKING = TRUE), and the view query must satisfy specific deterministic requirements (e.g., no non-deterministic functions, no window functions, and limited join constructs).

Stream Types Comparison

Stream Metadata Columns Deep-Dive

When querying a stream, Snowflake appends three system-defined metadata columns to the source object's schema. Understanding how these columns behave during DML operations is a core requirement for passing the ARA-C01 exam.

Metadata Column Definitions

1. METADATA$ACTION (VARCHAR(6)):
   • Indicates the DML action: 'INSERT' or 'DELETE'.
2. METADATA$ISUPDATE (BOOLEAN):
   • Indicates whether the record was generated as part of an UPDATE statement (TRUE), or as a standalone INSERT or DELETE statement (FALSE).
3. METADATA$ROW_ID:
   • A unique, immutable identifier for the row that lets you track changes to the same row over time. The delete and insert records produced by an update share the same METADATA$ROW_ID, allowing downstream pipelines to match before and after images.

How Operations Manifest in Stream Records

Exam Trap: Notice that an UPDATE statement never produces an action called 'UPDATE'. Instead, Snowflake decomposes the update into a pair of records: a 'DELETE' record representing the pre-image, followed by an 'INSERT' record representing the post-image. Both rows have METADATA$ISUPDATE = TRUE and share identical METADATA$ROW_ID values.

Consuming Streams with MERGE

The standard architectural pattern for synchronizing a target table from a CDC stream is the SQL MERGE statement. Because a single stream can contain both inserts and deletes (as well as update pairs), the merge query must handle each action deterministically:

-- Atomic CDC consumption into target table using MERGE
MERGE INTO cdc_db.analytics.dim_customers AS target
USING (
  SELECT 
    customer_id,
    customer_name,
    email,
    tier,
    updated_at,
    METADATA$ACTION,
    METADATA$ISUPDATE,
    METADATA$ROW_ID
  FROM cdc_db.staging.customers_stream
  -- Best practice: In complex updates, filter for latest post-image or handle deletes
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id 
    ORDER BY updated_at DESC, METADATA$ACTION DESC
  ) = 1
) AS src
ON target.customer_id = src.customer_id
-- Case 1: Delete action in stream
WHEN MATCHED AND src.METADATA$ACTION = 'DELETE' AND src.METADATA$ISUPDATE = FALSE THEN
  DELETE
-- Case 2: Update action in stream (post-image)
WHEN MATCHED AND src.METADATA$ACTION = 'INSERT' THEN
  UPDATE SET 
    target.customer_name = src.customer_name,
    target.email = src.email,
    target.tier = src.tier,
    target.updated_at = src.updated_at
-- Case 3: New insert action in stream
WHEN NOT MATCHED AND src.METADATA$ACTION = 'INSERT' THEN
  INSERT (customer_id, customer_name, email, tier, updated_at)
  VALUES (src.customer_id, src.customer_name, src.email, src.tier, src.updated_at);

Stream Staleness & Time Travel Extension

In enterprise environments, downstream ingestion pipelines may experience unexpected delays, warehouse outages, or holiday pauses. If a stream is not consumed for an extended period, it risks becoming stale.

What Causes Stream Staleness?

A stream's offset points to a specific transaction in the source table's history. Under normal conditions, Snowflake relies on the source table's Time Travel retention window (DATA_RETENTION_TIME_IN_DAYS) to reconstruct delta records. If the stream offset falls older than the source table's Time Travel retention period, the stream becomes stale:

[Stream Offset: Day 1] ───► [Time Travel Retention Window: Day 8 to Day 10] ───► [Current Day 10]
          │                                     │
          └─────────── G A P ───────────────────┘
     Stream offset is outside the Time Travel window!
     Result: Stream becomes permanently STALE.

Consequences of a Stale Stream

• The stream becomes completely unreadable. Any query against the stream fails immediately with the error: Stream <stream_name> is stale and cannot be used.
• A stale stream cannot be revived, extended, or refreshed. You cannot reset its offset backwards.
• Resolution: The stale stream must be dropped and recreated (CREATE OR REPLACE STREAM). Recreating the stream anchors its offset at the current timestamp, resulting in the permanent loss of all unconsumed CDC changes that occurred while the stream was stale.

Preventing Staleness: MAX_DATA_EXTENSION_TIME_IN_DAYS

To safeguard pipelines against staleness, Snowflake provides the MAX_DATA_EXTENSION_TIME_IN_DAYS object parameter (available on tables, schemas, databases, and accounts):

-- Configure maximum data extension time on the source table
ALTER TABLE cdc_db.raw.orders 
  SET MAX_DATA_EXTENSION_TIME_IN_DAYS = 30;

How Data Extension Works:

1. When an active, unconsumed stream exists on a table, Snowflake automatically extends the source table's effective Time Travel retention beyond DATA_RETENTION_TIME_IN_DAYS to prevent the stream from becoming stale.
2. The extension continues until either:
   • The stream is consumed by a DML statement (advancing the offset to a fresh timestamp).
   • The elapsed time reaches MAX_DATA_EXTENSION_TIME_IN_DAYS (default 14 days, maximum 90 days, regardless of edition; 0 disables extension).
3. Storage Cost Impact: While Snowflake extends retention to protect the stream, all modified and deleted micro-partitions are preserved in Time Travel storage. This incurs additional storage credit charges until the stream is consumed and the historical partitions are released.

Monitoring Stream Staleness with SHOW STREAMS

Architects must implement automated alerting by inspecting metadata returned by SHOW STREAMS:

SHOW STREAMS IN SCHEMA cdc_db.staging;

-- Inspect key staleness columns
SELECT 
  "name" AS stream_name,
  "table_name" AS source_table,
  "stale" AS is_stale,
  "stale_after" AS will_become_stale_at,
  "mode" AS stream_mode
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "stale" = TRUE OR "stale_after" <= DATEADD('hour', 24, CURRENT_TIMESTAMP());

Critical Architectural Rules & Exam Traps

1. Truncates Are Captured by Standard Streams, Not Append-Only Streams: A standard stream tracks inserts, updates, and deletes including table truncates, so downstream consumers see delete records for truncated rows. Append-only streams ignore updates, deletes, and truncates. Also note that a standard stream returns the net change: a row inserted and then deleted between two offsets does not appear at all.
2. Recreating the Source Breaks the Stream: If the source table is dropped or replaced with CREATE OR REPLACE TABLE, the stream no longer tracks it (check stale, invalid, and invalid_reason in SHOW STREAMS), and recreating or dropping any underlying table of a view makes a stream on that view stale. Plan schema migrations with ALTER TABLE instead of replace, or recreate streams deliberately.
3. Multiple Consumers from a Single Stream: If multiple downstream pipelines need to process the same CDC records, you cannot share a single stream between them. The first pipeline to run will advance the offset, leaving zero records for the second pipeline. Solution: Create dedicated, independent streams on the source table for each consumer pipeline.
4. Change Tracking Parameter: In order to create a stream on a view or use stream features across complex joins, change tracking must be explicitly enabled on the base tables using ALTER TABLE <name> SET CHANGE_TRACKING = TRUE. Enabling change tracking adds hidden metadata columns to track micro-partition transaction versions.