CDC into Snowflake

Snowflake's ingestion is append-only. A bulk load is a COPY INTO, a change stream is Snowpipe Streaming, and neither can express an update or a delete — only one more row, appended. CDC therefore records what happened and applies it in a second step: changes stream in through Snowpipe Streaming, which is billed by the volume ingested rather than by warehouse uptime, and a Snowflake task merges them into the table you query.

Sources are MySQL and PostgreSQL; Snowflake is a target only. Setting up the connection itself — account, key pair, role and warehouse — is on the Snowflake target page, and CDC needs that connection to use key-pair authentication.

CDC mode:

Nothing is staged and nothing is uploaded: changes go straight into Snowflake as they happen. For a source table ORDERS, a CDC stream creates five objects, of which two hold rows:

ORDERS is a plain table and holds its own rows — not a view, and nothing derived. The changes beside it are there when you need to see how a row got that way.

The changes are yours to manage

ORDERS__CHANGES grows for the life of the stream: every superseded version of every row stays in it, and there is no retention setting yet.

Changes that have been applied are yours to delete. Trim them or empty the table entirely, and ORDERS keeps every row — it holds its own data and is not derived from the history. What you lose is the record of how rows reached their current values, and nothing else.

Changes that have not been applied yet are a different matter: the task applies what the stream has not yet consumed, so deleting those rows before it runs means they never reach ORDERS. Empty the changes table after an apply, not between one and the next.

What CDC into Snowflake requires

• Key-pair authentication. Snowpipe Streaming accepts a signed key and neither a password nor an access token. A stream on a password connection is refused before it starts. New Snowflake connections are created with a key pair for this reason; a connection already saved with a password keeps it and can still be a load target until Snowflake stops accepting passwords from service users during 2026.
• A primary key on every replicated table. Without one there is nothing to collapse the changes by, and the stream stops with that explanation.
• EXECUTE TASK on the account. The task that applies changes cannot run without it, and it is an account-level privilege the connecting role usually cannot grant itself. An account administrator runs this once:

  USE ROLE ACCOUNTADMIN;
  GRANT EXECUTE TASK ON ACCOUNT TO ROLE <the role your connection uses>;
  

  
  Without it, changes still reach ORDERS__CHANGES and never reach ORDERS. The stream says so, with that statement in the error, rather than leaving you to find an empty table.

How often changes are applied, and what it costs

Changes reach ORDERS__CHANGES within seconds, always. What you choose is how often they are merged into ORDERS — the schedule of the apply task, set in the stream wizard under Applying changes and as target.spec.snowflake.currentState through the API.

The slider runs from 1 min through 5 min and 15 min to 1 hour, and the line above it names the position while the line below says what it costs — at five minutes, At most twelve wake-ups an hour. A fair balance, and the default.

The slider offers four intervals; the API takes any of them and everything in between, written as a number and a unit — 5 minutes, 2 hours — from one minute to eight days, which is the range a Snowflake task allows. Anything outside it is refused before the stream starts rather than part-way through creating the objects.

The cost is not the merge itself; it is waking the warehouse. Snowflake bills a minimum of one minute of warehouse time each time one resumes, so what the schedule really sets is how many of those you buy an hour:

A run that finds nothing checks SYSTEM$STREAM_HAS_DATA and does not wake the warehouse at all, so a quiet stream costs nothing whatever the schedule says.

Related docs

• Snowflake target — connection, authentication and Load mode
• CDC Mode
• Target Schema and Data Policies
• Stream Configuration Reference