Tracking Delta Table Changes with CDF

1. What is Change Data Feed?

Traditionally, identifying row-level changes in a data lake required complex and often inefficient logic, like comparing two large versions of a table to find the differences. Change Data Feed (CDF) simplifies this process immensely.

When you enable CDF on a Delta table, Databricks begins to automatically record row-level changes—inserts, updates, and deletes—as part of the transaction log. This provides a clean, queryable history of all modifications.

To get a quick overview of what CDF is and the problems it solves, let's start with a short conceptual video from Databricks.

The CDF Output

When you query the change feed, you get back not only the row data but also special metadata columns that describe the change. The following image provides a perfect illustration of what this looks like.

To work with this output, you need to understand the key metadata columns.

As you saw, the three essential columns are:

• _change_type: Tells you if the row was an insert, delete, update_preimage, or update_postimage.
• _commit_version: The table version number in which the change occurred.
• _commit_timestamp: The timestamp of the commit.

2. Enabling Change Data Feed

CDF is not enabled by default. You must explicitly enable it on a table-by-table basis. It's a simple change to the table's properties.

Important: CDF is a forward-looking feature. It only begins recording changes after you enable it. It cannot capture historical changes made before it was turned on.

You can enable it in a few ways, as shown in the official documentation.

Here is a quick summary of the SQL commands for your reference:

For a new table:

CREATE TABLE my_table (id INT, name STRING)
TBLPROPERTIES (delta.enableChangeDataFeed = true);

For an existing table:

ALTER TABLE my_table SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

3. Querying Change Data

Once CDF is enabled and some data modifications (INSERT, UPDATE, DELETE, MERGE) have occurred, you can query the changes. This is the core of the feature and where you'll spend most of your time. Given your preference for hands-on work, we will now watch a detailed demonstration.

This video walks through a full, practical scenario:

1. Creating silver and gold tables.
2. Enabling CDF on the silver table.
3. Performing UPDATE and MERGE operations.
4. Querying the resulting changes using the table_changes function.
5. Using those changes to update the gold table.

As you saw in the video, you can query changes in both SQL and PySpark.

SQL Syntax

The table_changes function is the standard way to query CDF in SQL. You can specify a range using versions or timestamps.

-- Query changes between version 1 and 5
SELECT * FROM table_changes('my_table', 1, 5);

-- Query all changes starting from version 3 to the latest
SELECT * FROM table_changes('my_table', 3);

-- Query changes within a specific timestamp range
SELECT * FROM table_changes('my_table', '2023-10-26T00:00:00.000Z', '2023-10-27T00:00:00.000Z');

PySpark Syntax

In PySpark, you use the .option("readChangeData", "true") when creating a DataFrame.

# Query changes between version 1 and 5
changes_df = (spark.read.format("delta")
              .option("readChangeData", "true")
              .option("startingVersion", 1)
              .option("endingVersion", 5)
              .table("my_table"))




# Query all changes starting from version 3
changes_df_from_3 = (spark.read.format("delta")
                     .option("readChangeData", "true")
                     .option("startingVersion", 3)
                     .table("my_table"))

4. A Complete CDC Pattern

Querying the changes is the first step. The next logical step is to use that change data to update downstream systems or aggregated tables, such as propagating changes from a silver table to a gold table in the Medallion Architecture.

The diagram below shows exactly this pattern. The output of table_changes is used as the source for a MERGE operation into a gold table.

To see this pattern implemented with code, let's look at one final video example. It provides a very clear demonstration of using a MERGE statement to apply inserts, updates, and deletes from a CDF source to a target table.

Hands-On Practice

The best way to solidify your understanding is to run the code yourself. The following blog post contains a link to a Databricks notebook that you can import into your workspace to experiment with CDF directly.

Conclusion

You have now learned how to enable and query one of Delta Lake's most critical features for modern data engineering. Capturing row-level changes efficiently is key to maintaining data quality and consistency across your lakehouse.

Key Takeaways:

• Change Data Feed (CDF) tracks row-level INSERT, UPDATE, and DELETE operations on a Delta table after it has been enabled.
• It is enabled via the table property delta.enableChangeDataFeed = true.
• You can query the changes using the table_changes() function in SQL or the readChangeData option in PySpark.
• The output includes metadata columns (_change_type, _commit_version, _commit_timestamp) that describe each change.
• For UPDATE operations, CDF provides both a update_preimage (the 'before' state) and update_postimage (the 'after' state).

Preview of the Next Lesson:
In this lesson, we saw how to query the change feed in batch mode and use it in a MERGE statement. In our next lesson, we will take this a step further. We will implement a Change Data Capture (CDC) pattern using MERGE with streaming data, building an automated pipeline that propagates changes from a source to a target table in near real-time.