Study guide
9.1 Change Data Capture (CDC) with Snowflake Streams
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
9.1 Change Data Capture (CDC) with Snowflake Streams
Key Takeaways Explain This Topic AI
Snowflake Streams capture Change Data Capture (CDC) metadata without duplicating underlying table data, advancing their offset pointer only when consumed within a committed DML transaction.
A stream exposes three system metadata columns: METADATA A C T I O N ( ′ I N S E R T ′ o r ′ D E L E T E ′ ) , M E T A D A T A ACTION ('INSERT' or 'DELETE'), METADATA A CT I ON ( ′ I NSER T ′ o r ′ D E L ET E ′ ) , MET A D A T A ISUPDATE (TRUE if part of an UPDATE statement), and METADATA$ROW_ID (unique row identifier linking pre- and post-update states).
Stream types: standard (inserts, updates, deletes, including truncates) on tables, dynamic tables, Iceberg tables, directory tables, and views; append-only (inserts only) for insert-heavy sources; insert-only for external tables, externally managed Iceberg tables, and Delta Direct tables.
Stream staleness occurs when a stream's offset transaction falls behind the source table's Time Travel data retention window (DATA_RETENTION_TIME_IN_DAYS), rendering the stream unreadable and requiring recreation.
If a table's retention is under 14 days, Snowflake automatically extends it for an unconsumed stream up to MAX_DATA_EXTENSION_TIME_IN_DAYS (default 14, maximum 90, regardless of edition), which keeps extra Time Travel storage until the stream is consumed.
Last updated: September 2026
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:
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.
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>
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.
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 Type Syntax Clause Operations Captured Supported Source Objects Primary Use Case
Standard Default INSERT , UPDATE , DELETE (including truncates) Tables, dynamic tables, Snowflake-managed Iceberg tables, directory tables, views Relational CDC, SCD Type 1 & 2 dimensions, operational reconciliation
Append-Only APPEND_ONLY = TRUE INSERT only Tables, dynamic tables, Snowflake-managed Iceberg tables, views Append-only logs, event feeds, IoT telemetry, Kafka streaming
Insert-Only INSERT_ONLY = TRUE Rows from new files External tables, externally managed Iceberg tables, Delta Direct tables Processing newly detected files in data lake object storage
Directory Stage ON STAGE <stage_name> File additions & removals Stages with Directory Tables Unstructured data processing pipelines, PDF/audio ingestion triggers
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
METADATA$ACTION ( VARCHAR(6) ) :
Indicates the DML action: 'INSERT' or 'DELETE' .
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 ).
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
- Source Table Operation Stream Rows Generated METADATA$ACTION METADATA$ISUPDATE Description
- INSERT 1 row 'INSERT' FALSE New record added to source table
- DELETE 1 row 'DELETE' FALSE Existing record removed from source table
- UPDATE 2 rows 'DELETE' ; 'INSERT' TRUE ; TRUE Pre-update state (old values); Post-update state (new values)
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:
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.
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).
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());
SHOW STREAMS Column Data Type Description
stale BOOLEAN TRUE if the stream has surpassed its retention window and is unreadable; FALSE if healthy.
stale_after TIMESTAMP The exact timestamp when the stream will transition to stale if no DML statement consumes it.
mode VARCHAR The stream mode: DEFAULT (standard), APPEND_ONLY , or INSERT_ONLY .
invalid BOOLEAN TRUE if the underlying source table was dropped, replaced, or altered incompatibly.
Critical Architectural Rules & Exam Traps
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.
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.
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.
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.