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
In a nutshell
  • 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.

EventsKafka or approwsSnowpipe Streamingsecondsland inRaw tableappend onlytracked byStreamoffsetconsumed byMERGE taskinto target

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.

NeedSnowpipeSnowpipe Streaming
Unit of inputFiles in a stageRows over a channel
Typical latencyAbout a minute or moreSeconds
Needs file creationYesNo
Best forMicro batch filesEvents, 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.

Source DBtransaction logcaptured byCDC toolconnector or OpenflowwritesRaw changestabletracked byStreamoffsetconsumed byMERGEtask into target
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.

Exam tip

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.

ApproachLoad on sourceHandles deletesLatency
Full extract on a scheduleHighYes, by replacing the tableHours
Incremental extract by a timestamp or keyMediumNo, unless rows are soft deletedMinutes to hours
Log based CDCLowYesSeconds 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.

Watch out

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_DATA avoids 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.