Streams for change data capture in Snowflake - Deep Dive

Overview - Streams for change data capture

What is it?

Streams in Snowflake are a way to track changes made to a table over time. They record inserts, updates, and deletes so you can see what changed since the last time you checked. This helps you capture data changes without scanning the entire table again. Streams make it easier to build processes that react only to new or changed data.

Why it matters

Without streams, you would have to scan entire tables repeatedly to find what changed, which wastes time and computing power. Streams let you focus only on new or updated data, making data processing faster and cheaper. This is important for keeping data fresh in reports, syncing systems, or triggering actions based on changes.

Where it fits

Before learning streams, you should understand basic Snowflake tables and SQL queries. After streams, you can learn about tasks and pipes to automate processing of changed data. Streams fit into the data pipeline as the change detector that feeds downstream processes.

Mental Model

Core Idea

A stream is like a bookmark that remembers where you left off reading changes in a table, so you only see new changes each time.

Think of it like...

Imagine reading a newspaper every day. Instead of reading the whole paper again, you mark the last article you read. The next day, you start reading only the new articles published since your bookmark. Streams work the same way for data changes.

┌───────────────┐       ┌───────────────┐
│   Table Data  │──────▶│   Stream      │
│ (full records)│       │ (records only │
│               │       │  changes)     │
└───────────────┘       └───────────────┘
         ▲                      │
         │                      ▼
   Data changes          Query stream for
   (insert/update/      new changes since
    delete)             last read position

Build-Up - 6 Steps

1

Foundation

What is a Snowflake Stream

Concept: Introduce the basic idea of a stream as a change tracker on a table.

A Snowflake stream is an object that tracks changes made to a table. When you create a stream on a table, Snowflake records every insert, update, and delete that happens. The stream does not store the full data but keeps a record of what changed since the last time you read from it.

Result

You get a way to query only the new or changed rows without scanning the whole table.

Understanding that streams track changes incrementally helps you avoid expensive full table scans.

2

Foundation

Types of Streams in Snowflake

Concept: Explain the different stream types and their behavior.

Snowflake supports two main stream types: standard streams and append-only streams. Standard streams track inserts, updates, and deletes. Append-only streams track only new inserts. Knowing which type to use depends on your data change patterns and use case.

Result

You can choose the right stream type to efficiently capture the changes you care about.

Knowing stream types helps you optimize for your specific change data capture needs.

3

Intermediate

How to Create and Query a Stream

🤔Before reading on: Do you think querying a stream returns full table data or only changes? Commit to your answer.

Concept: Learn the syntax to create a stream and how to query it for changes.

To create a stream, use: CREATE STREAM my_stream ON TABLE my_table; Query the stream like a table: SELECT * FROM my_stream; This returns only rows that changed since the last query or offset reset. The stream tracks the offset internally.

Result

You get a result set with only new, updated, or deleted rows since last read.

Understanding that streams behave like tables but only show changes is key to using them effectively.

4

Intermediate

Using Streams for Incremental Data Processing

🤔Before reading on: Do you think streams automatically update downstream tables or require manual processing? Commit to your answer.

Concept: Streams enable incremental data pipelines by feeding only changed data to downstream processes.

You can write SQL or tasks that read from streams and apply changes to other tables or systems. For example, you can insert new rows from the stream into a reporting table. This avoids reprocessing unchanged data and keeps pipelines efficient.

Result

Your data pipelines become faster and cheaper by processing only changes.

Knowing streams support incremental processing helps you design scalable data workflows.

5

Advanced

Handling Stream Offsets and Data Consistency

🤔Before reading on: Do you think reading from a stream consumes the changes or leaves them for next reads? Commit to your answer.

Concept: Streams maintain offsets that move forward when you read from them, ensuring changes are consumed once.

When you query a stream, Snowflake advances the offset, marking those changes as consumed. If you don't consume changes, the stream keeps them available. This ensures exactly-once processing if you read carefully. You can also reset offsets if needed.

Result

You get consistent, non-duplicated change data for reliable pipelines.

Understanding offset management prevents data loss or duplication in change capture.

6

Expert

Streams with Tasks and Pipes for Automation

🤔Before reading on: Do you think streams alone can automate data movement or need other components? Commit to your answer.

Concept: Streams combined with tasks and pipes enable fully automated, continuous change data capture pipelines.

Tasks can schedule SQL statements that read from streams and apply changes. Pipes can load data from external stages. Together, they automate incremental data movement without manual intervention. This is how production CDC pipelines run smoothly in Snowflake.

Result

You achieve hands-free, real-time data synchronization and processing.

Knowing how streams integrate with tasks and pipes unlocks powerful automation capabilities.

Under the Hood

Internally, Snowflake streams track changes by recording metadata about row-level operations in a change table linked to the source table. Each stream maintains an offset pointer marking the last consumed change. When queried, the stream returns changes after this offset and advances it. This avoids scanning the full table and stores only delta information efficiently.

Why designed this way?

Streams were designed to provide efficient, incremental change capture without duplicating full data or requiring complex triggers. Snowflake's architecture separates storage and compute, so streams leverage metadata tracking to minimize compute costs and latency. Alternatives like triggers or full table scans were too costly or complex.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│   Source      │      │  Change Table │      │   Stream      │
│   Table       │─────▶│  (metadata)   │─────▶│  Offset Track │
│ (full data)   │      │               │      │  & Query API  │
└───────────────┘      └───────────────┘      └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does querying a stream return the full table data or only changes? Commit to your answer.

Common Belief: Querying a stream returns the entire table data like a normal SELECT.

Reality: Querying a stream returns only the rows that have changed since the last time the stream was read or offset reset.

Why it matters: Believing streams return full data leads to inefficient queries and misunderstanding of incremental processing benefits.

Quick: Do streams store full copies of changed rows or just metadata? Commit to your answer.

Common Belief: Streams store full copies of all changed rows, duplicating data.

Reality: Streams store only metadata about changes, not full data copies, making them lightweight and efficient.

Why it matters: Thinking streams duplicate data can cause unnecessary storage concerns and design hesitation.

Quick: Does reading from a stream leave changes available for next reads? Commit to your answer.

Common Belief: Reading from a stream does not consume changes; they remain available indefinitely.

Reality: Reading from a stream advances its offset, marking those changes as consumed and not returned again unless offset is reset.

Why it matters: Misunderstanding offset consumption can cause duplicate processing or missed changes in pipelines.

Quick: Can streams detect changes in external tables or views? Commit to your answer.

Common Belief: Streams can track changes on any table, including external tables and views.

Reality: Streams only work on Snowflake internal tables; they cannot track changes on external tables or views.

Why it matters: Expecting streams to work on unsupported objects leads to failed implementations and confusion.

Expert Zone

1

Streams do not store data themselves but rely on Snowflake's underlying micro-partition metadata to track changes efficiently.

2

Offset management in streams is crucial for exactly-once processing; careless offset resets can cause data loss or duplication.

3

Streams combined with tasks enable event-driven architectures inside Snowflake without external orchestration tools.

When NOT to use

Streams are not suitable when you need to capture changes from external systems or non-Snowflake tables. In such cases, use external CDC tools or Snowflake's Snowpipe for continuous data ingestion.

Production Patterns

In production, streams are often paired with scheduled tasks that read changes and merge them into target tables. This pattern supports incremental ETL pipelines, real-time analytics, and data synchronization across systems.

Connections

Event Sourcing

Streams implement a similar pattern by recording changes as events that can be replayed or processed incrementally.

Understanding streams as event logs helps grasp how state changes can be tracked and rebuilt over time.

Message Queues

Streams act like a message queue for database changes, delivering change events to consumers in order.

Seeing streams as queues clarifies their role in decoupling data producers and consumers in pipelines.

Version Control Systems

Streams track changes over time similar to how version control tracks code changes with commits and diffs.

This connection helps appreciate the importance of offsets as checkpoints to avoid reprocessing or missing changes.

Common Pitfalls

#1 Querying the stream multiple times without consuming changes causes duplicate processing.

Wrong approach:

SELECT * FROM my_stream;
SELECT * FROM my_stream; -- runs again without offset advance

Correct approach:

SELECT * FROM my_stream; -- consumes changes
-- wait for new changes before next query

Root cause:

Not understanding that querying advances the stream offset and that repeated queries without new changes return empty results.

#2 Creating a stream on a view or external table which is unsupported.

Wrong approach:

CREATE STREAM my_stream ON VIEW my_view;

Correct approach:

CREATE STREAM my_stream ON TABLE my_table;

Root cause:

Misunderstanding that streams only work on base tables inside Snowflake.

#3 Resetting stream offset incorrectly causing loss of unprocessed changes.

Wrong approach:

ALTER STREAM my_stream SET OFFSET = CURRENT_TIMESTAMP;

Correct approach:

Use careful offset management or let Snowflake handle offsets automatically by consuming changes.

Root cause:

Lack of knowledge about offset semantics and risks of manual offset manipulation.

Key Takeaways

Streams in Snowflake track only the changes made to tables, enabling efficient incremental data processing.

They maintain an internal offset to remember which changes have been consumed, preventing duplicate processing.

Choosing the right stream type and managing offsets carefully is essential for reliable change data capture.

Streams integrate with tasks and pipes to automate continuous data pipelines inside Snowflake.

Understanding streams as change trackers rather than full data stores helps design scalable and cost-effective data workflows.