Data Engineering · Topic 2 of 16
Streaming Data, CDC, OLTP and API Sources
Design Snowflake ingestion for streams of events, change data capture, OLTP databases and APIs, with Snowpipe, Kafka, streams, MERGE and Openflow.
- 5 min
- Files arriving: Snowpipe. Rows in seconds: Snowpipe Streaming.
- CDC has two halves: capture (connector, Openflow, Debezium and Kafka) and apply (stream, task, MERGE).
- Log based CDC has the lowest source load and handles deletes.
What it is
Data in motion arrives continuously and cannot wait for a nightly batch. The exam asks which design keeps latency low, applies updates and deletes correctly, and does not overload the source system.
There are four source shapes: event streams (Kafka, devices), files that keep landing, change records from a transactional (OLTP) database, and data you must pull from an API. Each has a standard Snowflake answer.
How it works
Streaming data and Snowpipe
For streams of files, Snowpipe loads each file shortly after it arrives. A pipe wraps a COPY INTO statement. With auto ingest, a cloud event notification from the bucket tells the pipe about new files, and serverless compute loads them, usually within about a minute.
For streams of rows with no files, use Snowpipe Streaming. An application or the Kafka connector writes rows to a channel, and data is queryable with seconds of latency. The ingestion page of this chapter compares the two in detail.
| Need | Snowpipe | Snowpipe Streaming |
|---|---|---|
| Unit of input | Files in a stage | Rows over a channel |
| Typical latency | About a minute or more | Seconds |
| Needs file creation | Yes | No |
| Best for | Micro batch files | Events, IoT, Kafka topics |
Change Data Capture (CDC)
CDC means capturing inserts, updates and deletes from a source and applying them in the target in the right order. Two separate problems appear.
Getting the changes into Snowflake. Options include the Snowflake connectors for databases such as PostgreSQL and MySQL, Snowflake Openflow, and Kafka with a CDC tool such as Debezium feeding the Kafka connector. The change rows land in a raw table, each with an operation type and a sequence or timestamp.
Applying the changes inside Snowflake. A stream on the raw table records new rows. A task reads the stream and runs MERGE into the target table. Streams expose the columns METADATA$ACTION, METADATA$ISUPDATE and METADATA$ROW_ID, and consuming the stream in a DML statement advances its offset.
CREATE TASK apply_changes
WAREHOUSE = etl_wh
SCHEDULE = '1 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
MERGE INTO orders t
USING raw_orders_stream s ON t.id = s.id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT (id, amount) VALUES (s.id, s.amount);
The WHEN SYSTEM$STREAM_HAS_DATA condition makes the task skip a run, and avoids resuming the warehouse, when the stream is empty.
An append only stream records inserts only. A standard stream reports inserts, updates and deletes, so it is the type for tracking changes made by DML on a table.
OLTP and RDBMS sources
The golden rule is to avoid heavy queries against the production database. Reading the log (CDC) puts far less load on the source than repeated full extracts.
| Approach | Load on source | Handles deletes | Latency |
|---|---|---|---|
| Full extract on a schedule | High | Yes, by replacing the table | Hours |
| Incremental extract by a timestamp or key | Medium | No, unless rows are soft deleted | Minutes to hours |
| Log based CDC | Low | Yes | Seconds to minutes |
Incremental extracts by a "last modified" column miss deleted rows and any row whose timestamp is not updated. If the business needs deletes, choose log based CDC or a full reload.
API sources
Snowflake does not poll arbitrary web APIs by itself. Common patterns are an external tool or Openflow connector that calls the API and loads the results, or a stored procedure or UDF that uses an external access integration with a network rule to call the API from inside Snowflake. Results land in a table, usually as VARIANT.
A secret object holds the credentials, and the network rule limits which hosts the code may reach. Many SaaS sources also have a ready made Snowflake connector, which is preferable to custom code.
Code running in Snowflake cannot reach external hosts until a network rule and an external access integration allow it. A function that "just calls the API" fails without them.
Common mistakes
Using a timestamp extract and expecting deletes. A "modified since" query never sees rows that were removed.
Applying CDC rows without ordering. Updates must be applied in source order. Keep a sequence column and MERGE on the latest change per key.
Running a task on a fixed schedule with no stream check. The task resumes the warehouse every run. Add SYSTEM$STREAM_HAS_DATA.
Picking Snowpipe for second level latency. Snowpipe loads files, so file creation and notification add delay. Rows needed in seconds point to Snowpipe Streaming.
What to remember for the exam
- Files arriving: Snowpipe. Rows in seconds: Snowpipe Streaming.
- CDC has two halves: capture (connector, Openflow, Debezium and Kafka) and apply (stream, task, MERGE).
- Log based CDC has the lowest source load and handles deletes.
- A task condition with
SYSTEM$STREAM_HAS_DATAavoids empty runs. - API calls from inside Snowflake need a network rule, a secret and an external access integration.
Check yourself
Why is log based CDC preferred over repeated full extracts from an OLTP database?
It puts little load on the source and captures updates and deletes with low latency.
How do you apply captured change rows to a target table?
Put a stream on the raw change table and run a task that MERGEs the stream into the target.
What lets a Snowflake stored procedure call an external API?
A network rule and a secret, combined in an external access integration that the procedure uses.
Topic 2 of 16 in Data Engineering
Test yourself on Data Engineering
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.