Change Data Capture for Snowflake: PostgreSQL, MySQL, and MongoDB

Introduction

Change Data Capture (CDC) replicates database changes to Snowflake in real time. This process synchronizes the target system with the source. It supports analytics, reporting, and machine learning workloads.

This guide covers CDC setup for PostgreSQL, MySQL, and MongoDB using Kafka connectors. The core process is similar across databases. However, each database uses specific mechanisms to log and capture changes.

What Is Change Data Capture (CDC) in Snowflake?

CDC records database changes: inserts, updates, and deletes. It streams these changes to a target system, such as Snowflake. This mechanism supports real-time data pipelines. Kafka manages streaming. AWS Secrets Manager secures credentials.

CDC to Snowflake: Configuration Steps

Apply these steps to any source database:

Configure the Kafka Connector YAML file

Manage Credentials with AWS Secrets Manager

Store usernames, passwords, and private keys in AWS Secrets Manager.

Reference these credentials in the YAML file using ${secretsManager:<path>}.

Validate Source Database Configuration

Enable database replication features required for CDC. Requirements vary by database type:

PostgreSQL: Use logical replication with publications and replication slots.

MySQL: Read binary log (binlog) events.

MongoDB: Monitor the operation log (oplog) in replica sets.

Deploy and Test:

Install the Kafka connector. Verify that the system captures source database changes and sends them to Snowflake.

• Configure the source database.
• Select Snowflake as the destination.
• Apply database-specific settings (see below).

How the Setup Works

The Kafka connector processes data between the source database and Snowflake. The following steps define the CDC setup and operation:

• Change Logging in the Source Database: Each database logs changes differently. PostgreSQL: Logical replication captures changes to specified tables via a publication and writes them to a replication slot. MySQL: Binlog events log all transactions, serving as the source of truth. MongoDB: The oplog, a capped collection in replica sets, logs all write operations.
• Connector Monitoring: The Kafka connector monitors the logs (replication slot, binlog, or oplog) and streams changes to Kafka topics.
• Data Transformation: Kafka processes the changes and formats them for the destination system.
• Streaming to Snowflake: Snowflake ingests the processed data into the target database and schema.

This setup captures and mirrors every source database change in Snowflake in near real-time. For a MySQL-specific setup, see our guide to the MySQL Debezium connector for change data capture.

YAML configuration examples

PostgreSQL

source:
type: postgres
hostname: <hostname>
port: 5432
user: ${secretsManager:<path-to-username>}
password: ${secretsManager:<path-to-password>}
database: <database_name>
publicationName: <publication_name>
slotName: <slot_name>
destination:
type: snowflake
urlName: <snowflake_url>
databaseName: <database_name>
schemaName : <schema_name>
userName : <username>
privateKey : ${secretsManager:<path-to-private-key>}
privateKeyPassphrase : ${secretsManager:<path-to-passphrase>}

MySQL Configuration

source:
type: mysql
hostname: <hostname>
port: 3306
user: ${secretsManager:<path-to-username>}
password : ${secretsManager:<path-to-password>}
serverId : "<unique-server-id>"
destination :
type : snowflake
urlName : <snowflake_url>
databaseName : <database_name>
schemaName : <schema_name>
userName : <username>
privateKey : ${secretsManager:<path-to-private-key>}
privateKeyPassphrase : ${secretsManager:<path-to-passphrase>}

MongoDB Source Configuration

Source
Type: MongoDB
Hosts: <hostname>
User: ${secretsManager:<path-to-username>}
password : ${secretsManager:<path-to-password>}
capturemode : oplog
destination :
type : snowflake
urlName : <snowflake_url>
databaseName : <database_name>
schemaName : <schema_name>
userName : <username>
privateKey : ${secretsManager:<path-to-private-key>}
privateKeyPassphrase : ${secretsManager:<path-to-passphrase>}

Conclusion

Set up CDC for PostgreSQL, MySQL, and MongoDB using a standard process:

• Configure the source and destination in the Kafka connector YAML file.
• Secure credentials.
• Enable the correct replication features on the source database.

The databases log changes using different methods: See also how batch, Snowpipe and real-time approaches load data into Snowflake.

• PostgreSQL: logical replication.
• MySQL: binary log.
• MongoDB: operation log.

The process to stream changes to Snowflake is identical. Understand these mechanisms to build reliable, secure real-time data pipelines. These pipelines keep Snowflake synchronized with operational databases.

Part II covers advanced techniques for CDC with Snowflake:

• Incremental snapshots.
• Best practices to optimize CDC performance.