Introduction: The Batch ETL Problem

Imagine you run a library and you want to keep a second copy of your card catalog up to date. One approach: every night at closing time, you walk through every shelf and write down anything that changed since yesterday. This works, but it's slow, it misses books that were checked out and returned the same day, and it ties up the library staff for hours. This is exactly what traditional batch ETL does to your database.

The Old Way: Polling

Traditionally, to move data from a transactional database (like Postgres) into a Data Warehouse (like Snowflake), teams used a scheduled "Batch ETL" job. Every night at midnight, a script runs a query pulling every row updated since the last run — something like filtering WHERE updated_at > NOW() - INTERVAL '1 day'.

This approach is simple to understand and easy to implement, which is why it became the default for decades. But it carries three fundamental flaws that compound as your system grows.

The Flaws of Polling

1. Latency: The warehouse is always at least one batch interval behind. If you run batches every 24 hours, the business is making decisions on yesterday's data. Even hourly batches mean a 60-minute blind spot — unacceptable for fraud detection, inventory management, or real-time personalization.

2. Performance Impact: Running SELECT * FROM orders WHERE ... across millions of rows puts a heavy read load on the production database. This competes with live application traffic. Teams often schedule batches at 2 AM to minimize impact, but as data volumes grow, the batch window expands until it overlaps with peak hours.

3. Missed Deletes: This is the most insidious flaw. If a row is INSERTed at 10:00 AM and DELETEd at 2:00 PM, the midnight batch job never sees it. The row doesn't exist at query time, so the deletion is completely invisible to the warehouse. Your warehouse accumulates ghost records — data that no longer exists in production.

There is also a fourth, subtler problem: missed intermediate states. If a record changes 50 times between batch runs, you only see the final state. The history of how it got there is gone forever.

The Solution: Change Data Capture (CDC)

Change Data Capture (CDC) is a pattern that identifies and captures every change made to data in a database — inserts, updates, and deletes — and delivers those changes in near real-time to downstream consumers. Instead of periodically asking "What changed since I last looked?", CDC listens to the database as it announces: "I just changed this, right now."

The key insight is that databases already record every change they make, in a structure called a transaction log. CDC simply taps into that existing record rather than re-querying the data itself.