Phase 3: Data Pipelines & ETL

Log-based, trigger-based & timestamp-based CDC

Advanced ~2 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you're a super chef running a busy kitchen, making lots of different dishes for a big party. Sometimes, you need to know exactly what's new or different in your kitchen since the last time you checked. Maybe you added a new ingredient to a recipe, or changed how long something bakes. Instead of re-reading all your thousands of recipes and notes every single time to find tiny changes, you need a smart way to just spot what's different. That's what Change Data Capture, or CDC, helps with in computers. It's like having different clever systems to notice exactly what new things happened in a big digital kitchen, so you don't have to check everything from scratch.

One super clever way is like having a secret, super-detailed chef's journal. Every single time you do anything in your kitchen – even something tiny like slicing an onion, stirring a pot, or turning on the oven – you quickly write it down in this special journal. It's a continuous, never-ending list of everything that ever happened, in the exact order it happened. So, if you want to know all the changes to your "Chocolate Dream Cake" recipe, you just look at your journal entries from the time you started making it. This method is amazing because it catches everything without you having to specially ask it to, and it doesn't slow down your cooking at all.

Another way is to have little kitchen helpers. For certain important dishes, you tell a helper, "Hey, if anyone ever adds anything to the pizza dough, or takes anything out of the cookie jar, write it on a sticky note for me!" So, the helper just stands there, patiently watching only those specific things you asked about. When something changes, they immediately write down the old ingredient and the new ingredient on a sticky note and put it on a special "changes board." This is great because you get to decide exactly what you want to watch. But, it means your helpers are always a little busy, adding tiny extra steps every time someone touches the pizza dough or cookie jar.

A third way is like taking a snapshot of your entire kitchen every hour. Imagine you have a camera that takes a picture of all your ingredients, all your dishes, and how everything looks right now. Then, an hour later, you take another picture. To see what's changed, you put the two pictures side-by-side and carefully compare every detail. "Oh, the flour bin is lower!" or "Hey, that new cake wasn't here before!" This is simple to understand, but if your kitchen is HUGE, comparing every single detail in two giant pictures can take a lot of time and effort, and you might miss a very quick change that happened in between the pictures.

So, when people build big computer systems that handle lots of information, like for online shops or social media, they use these clever ways to track changes. This means they don't have to reload all the millions of product listings or every single post every time. They just get the new stuff, which makes everything run super fast and smoothly!

Change Data Capture (CDC) is crucial for data integration, enabling efficient, incremental data movement by identifying and capturing changes made to source data. Among its core methods, log-based CDC stands out for its robustness and minimal impact. It operates by directly reading the transaction logs (e.g., PostgreSQL's WAL or MySQL's binlog) of the source database. These logs record every DML operation (inserts, updates, deletes) at a very low level, along with transaction boundaries. This method is highly performant, offers comprehensive, near real-time capture of all changes, and is ideal for building resilient streaming data pipelines using tools like Debezium or Fivetran, as it doesn't interfere with database operations.

In contrast, trigger-based CDC involves creating database triggers directly on source tables. When a DML operation occurs on a monitored table, the trigger fires, capturing the change details (old and new values) and writing them into a separate "shadow" table, a message queue, or a staging area. This approach provides granular control over which specific changes are captured. However, it introduces direct overhead to every transaction on the source database, potentially impacting OLTP performance. Furthermore, managing triggers across numerous tables can become complex, and there's a risk of missing changes if triggers are temporarily disabled or bypassed by direct database operations.

Timestamp-based CDC is the simplest to implement, often favored for batch-oriented data movement. This method relies on a dedicated last_updated_at (or similar) column in the source tables. To capture changes, the data pipeline periodically queries the source table, retrieving all rows where last_updated_at is newer than the last extraction timestamp. While easy to set up, this method has significant limitations: it cannot natively capture deletions (unless using "soft deletes") and only provides the latest state of a row, not a complete history of intermediate changes. It's best suited for scenarios where occasional data freshness, append-only data, and missing physical deletes are acceptable tradeoffs.

Key Takeaways

  • Log-based CDC is the most robust and preferred method for real-time, comprehensive, low-impact change capture, leveraging database transaction logs.
  • Trigger-based CDC offers granular control over specific table changes but introduces direct overhead to OLTP transactions and can be complex to manage at scale.
  • Timestamp-based CDC is the simplest for batch extractions but is limited by its inability to detect physical deletes and capture full change history.
  • The choice of CDC method depends on your latency requirements, acceptable impact on the source system, and the criticality of capturing all DML operations (including deletes).

Code Example

sql
-- Example: Fetching changes using timestamp-based CDC
SELECT
    id,
    product_name,
    price,
    last_updated_at
FROM
    products
WHERE
    last_updated_at > '2023-10-26 10:00:00'
ORDER BY
    last_updated_at ASC;

How this code works

This SQL code is designed to implement timestamp-based Change Data Capture (CDC), a method for efficiently identifying and extracting only the data that has been modified or newly added since a previous point in time. Its job is to retrieve only the changes from the products table, rather than reprocessing the entire dataset. The SELECT statement specifies the columns (id, product_name, price, last_updated_at) that are relevant for the changed records from the products table.

The core of this CDC strategy relies on the WHERE last_updated_at > '2023-10-26 10:00:00' clause. This filter acts as a historical marker, ensuring that only rows updated after the specified timestamp are returned. This timestamp would typically be the record of when the last CDC run successfully completed. The ORDER BY last_updated_at ASC clause then sorts these changed records chronologically, which can be useful for processing events in the order they occurred. A subtle but crucial aspect for beginners is that the timestamp in the WHERE clause must be dynamically updated for each subsequent run to avoid missing changes; it should reflect the maximum last_updated_at value from the previous successful data extraction.

Related Articles

Hand-picked reads to go deeper on change data capture (cdc).

Back to Data Engineer Roadmap