Real-Time CDC Use Cases
Change Data Capture (CDC) is a design pattern used to determine and track data that has changed so that action can be taken using the changed data. In modern architectures, CDC is combined with streaming platforms like Kafka to deliver changes to Snowflake in near real-time.
Topics Covered
- Real-Time CDC Architecture Patterns
- Database → Kafka → Snowflake CDC pipeline
- Debezium + Kafka + Snowflake integration
- Real-Time Inventory Tracking Example
- Handling Out-of-Order Events
Real-Time CDC Architecture Patterns
The most common real-time CDC pattern involves a transaction log miner reading the source database logs, publishing those changes to a message broker, and a connector streaming them to the data warehouse.
The Debezium + Kafka + Snowflake Pattern:
- Source Database (e.g., PostgreSQL, MySQL) commits a transaction.
- Debezium (an open-source CDC platform) reads the database transaction log.
- Debezium publishes an event (Insert, Update, or Delete) to an Apache Kafka Topic.
- The Snowflake Connector for Kafka streams the event directly into a Snowflake target table.
Hands-On Demonstration: Real-Time Inventory Tracking
In this scenario, an e-commerce database tracks inventory in real-time. A Debezium connector reads the changes and streams them to a Snowflake VARIANT column. We will reconstruct the current inventory state.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_CDC_DB;
USE DATABASE BEEMS_CDC_DB;
CREATE SCHEMA IF NOT EXISTS INVENTORY_SCHEMA;
USE SCHEMA INVENTORY_SCHEMA;
2. Creating the Target Streaming Table
When Kafka streams the Debezium payload into Snowflake, it usually arrives in a semi-structured JSON format containing a before state, an after state, and an op (operation: 'c' for create/insert, 'u' for update, 'd' for delete).
CREATE OR REPLACE TABLE INVENTORY_SCHEMA.CDC_INVENTORY_STREAM (
RECORD_METADATA VARIANT,
RECORD_CONTENT VARIANT
);
3. Simulating Debezium CDC Events
Let's simulate the JSON payloads Debezium would send for inventory changes across warehouses managed by warehouse managers across India.
-- Simulate Debezium payloads for warehouse inventory
INSERT INTO INVENTORY_SCHEMA.CDC_INVENTORY_STREAM (RECORD_CONTENT)
SELECT
-- 1. Create Event: New Item added by Manager in Tamil Nadu
PARSE_JSON('{"op":"c", "after":{"item_id":1001, "item_name":"Laptop", "qty":50, "manager":"Srinivas Iyer", "location":"Chennai, Tamil Nadu"}, "source":{"ts_ms":1696150000000}}')
UNION ALL
SELECT
-- 2. Create Event: New Item added by Manager in West Bengal
PARSE_JSON('{"op":"c", "after":{"item_id":1002, "item_name":"Monitor", "qty":20, "manager":"Sunita Chatterjee", "location":"Kolkata, West Bengal"}, "source":{"ts_ms":1696151000000}}')
UNION ALL
SELECT
-- 3. Update Event: Laptop qty drops to 45 (Sale)
PARSE_JSON('{"op":"u", "before":{"item_id":1001, "item_name":"Laptop", "qty":50, "manager":"Srinivas Iyer", "location":"Chennai, Tamil Nadu"}, "after":{"item_id":1001, "item_name":"Laptop", "qty":45, "manager":"Srinivas Iyer", "location":"Chennai, Tamil Nadu"}, "source":{"ts_ms":1696155000000}}')
UNION ALL
SELECT
-- 4. Delete Event: Monitor is discontinued
PARSE_JSON('{"op":"d", "before":{"item_id":1002, "item_name":"Monitor", "qty":20, "manager":"Sunita Chatterjee", "location":"Kolkata, West Bengal"}, "after":null, "source":{"ts_ms":1696160000000}}');
4. Reconstructing the Current State
To find the current state of the inventory, we use Snowflake's QUALIFY clause with the ROW_NUMBER window function. We partition by the item_id and order by the source timestamp descending to get the latest event.
If the latest event is a delete (op='d'), we filter it out.
-- Reconstruct Real-Time Inventory View
CREATE OR REPLACE VIEW INVENTORY_SCHEMA.CURRENT_INVENTORY AS
SELECT
COALESCE(RECORD_CONTENT:after:item_id::INT, RECORD_CONTENT:before:item_id::INT) AS ITEM_ID,
COALESCE(RECORD_CONTENT:after:item_name::STRING, RECORD_CONTENT:before:item_name::STRING) AS ITEM_NAME,
RECORD_CONTENT:after:qty::INT AS QUANTITY,
COALESCE(RECORD_CONTENT:after:manager::STRING, RECORD_CONTENT:before:manager::STRING) AS MANAGER_NAME,
COALESCE(RECORD_CONTENT:after:location::STRING, RECORD_CONTENT:before:location::STRING) AS WAREHOUSE_LOCATION,
RECORD_CONTENT:op::STRING AS LAST_OPERATION,
RECORD_CONTENT:source:ts_ms::INT AS LAST_UPDATED_TS
FROM INVENTORY_SCHEMA.CDC_INVENTORY_STREAM
QUALIFY ROW_NUMBER() OVER (
PARTITION BY COALESCE(RECORD_CONTENT:after:item_id::INT, RECORD_CONTENT:before:item_id::INT)
ORDER BY RECORD_CONTENT:source:ts_ms::INT DESC
) = 1
-- Exclude deleted items from the current state view
HAVING LAST_OPERATION != 'd';
-- Query the reconstructed view
SELECT * FROM INVENTORY_SCHEMA.CURRENT_INVENTORY;
Handling Out-of-Order Events
In distributed streaming systems, messages can occasionally arrive out of order. To handle this:
- Always rely on the source database timestamp (
source.ts_msin Debezium), not the timestamp the event arrived in Kafka or Snowflake. - The
QUALIFY ROW_NUMBER() OVER (... ORDER BY source_timestamp DESC)pattern elegantly handles out-of-order events by ensuring that only the event with the latest chronological source timestamp is displayed as the current state, regardless of arrival order.
Real-Time Customer 360
A similar pattern is used for building a Real-Time Customer 360 profile. When a customer updates their address in an OLTP system, Debezium streams the update. By overlaying a view with QUALIFY, call center agents querying Snowflake always see the absolute latest customer profile.
(Reminder: If you need to verify your current user to ensure you have the SYSADMIN role to build these views, run SELECT CURRENT_USER();)
Summary
- Debezium + Kafka + Snowflake is a powerful real-time CDC pattern.
- CDC events usually arrive in a JSON format indicating the operation type (
c,u,d) and the before/after states. - Reconstruct the current state using Views with the
QUALIFY ROW_NUMBER()function to grab the latest chronological event per primary key.