Delta Change Data Feed: Enable It and Read Row-Level Changes

1.Why a table needs to expose its own changes

Take a 500-million-row dimension table where a few thousand rows change each day. Downstream consumers such as a silver-to-gold job, a replica or an audit table only care about those few thousand rows. Without a change log they have two choices: rescan the whole table, or compare two snapshots to work out what was inserted, updated or deleted. Both cost far more than the change itself.

Change Data Capture (CDC) solves this for operational databases. Instead of sending entire datasets, it produces a feed of only the modified records. Delta tables have their own version of this, called the Change Data Feed (CDF). The table records what happened to each row at each version, so a consumer can ask for "everything that changed since version N" and get back only those rows.

Sources12

2.Turning it on: legacy vs automatic change data feed

Databricks now offers two ways to get a change feed, and the exam guide's property name, delta.enableChangeDataFeed, belongs to the older one.

Legacy change data feed writes the changes out at write time. It works only on Delta Lake tables, and you enable it per table with a table property, either at creation or later with ALTER TABLE ... SET TBLPROPERTIES. Because it materialises changes on write, storage costs can rise slightly, since changes might be recorded in separate files. Some operations, such as insert-only writes or full-partition deletes, produce no change data files at all. Databricks computes their changes straight from the transaction log instead.

Automatic change data feed works out row-level changes at query time instead. It uses row tracking on Delta tables (row lineage on Iceberg v3). You don't configure it per table: any table that meets the requirements supports it automatically. The requirements are Databricks Runtime 19 or above and a Unity Catalog table, either a managed Delta table with row tracking, an Iceberg v3 table, or an external Delta table with row tracking. MERGE INTO and UPDATE no longer pay a change-computation cost on every write, which improves write performance and reduces storage. Both approaches share the same read APIs, table_changes() and readChangeFeed. For Delta tables, only Databricks readers can query the automatic feed.

ALTER TABLE myDeltaTable
  SET TBLPROPERTIES (delta.enableChangeDataFeed = true)

> CREATE TABLE myschema.t(c1 INT, c2 STRING) TBLPROPERTIES(delta.enableChangeDataFeed=true);

To migrate a Delta table from legacy to automatic, first check that the table meets the requirements, then remove the legacy property. You can't run both feeds on one table. Like any table-property change, setting or unsetting this property conflicts with concurrent writes, so do it when nothing else is writing to the table.

ALTER TABLE <table_name> UNSET TBLPROPERTIES ('delta.enableChangeDataFeed');

Sources13

3.What a change row looks like: updates and deletes exposed

A change feed read returns every data column of the table, using the schema of the latest table version, plus three metadata columns. These columns turn a plain set of rows into an ordered list of events. Inserts and deletes each produce one row. An update produces two: the preimage holds the values before the update and the postimage holds the values after it. That pair is what lets a downstream job tell what changed as well as what the row is now.

A table can't use change data feed if it already has columns with these names. Rename them before you enable the feed.

Here is the worked example from the table_changes reference. A CDF-enabled table gets two inserts, an UPDATE on two rows and a DELETE of one row:

-- Modify the table
> INSERT INTO myschema.t VALUES (1, 'Hello'), (2, 'World');
> INSERT INTO myschema.t VALUES (3, '!');
> UPDATE myschema.t SET c2 = upper(c2) WHERE c1 < 3;
> DELETE FROM myschema.t WHERE c1 = 3;

> SELECT * FROM table_changes('`myschema`.`t`', 2);
 c1 c2     _change_type    _commit_version _commit_timestamp
  3 !      insert                        2 2022-09-01T18:32:28.000+0000
  2 WORLD  update_postimage              3 2022-09-01T18:32:32.000+0000
  2 World  update_preimage               3 2022-09-01T18:32:32.000+0000
  1 Hello  update_preimage               3 2022-09-01T18:32:32.000+0000
  1 HELLO  update_postimage              3 2022-09-01T18:32:32.000+0000
  3 !      delete                        4 2022-09-01T18:32:35.000+0000

Sources1

4.Reading a range of changes in batch

For a one-off or scheduled batch read, you give a starting point and, optionally, an ending point. Each can be a version number or a timestamp. In SQL the entry point is the table-valued function table_changes(table_str, start [, end]). If you leave out end, you get every change from start up to the current version. To call it you need SELECT on the table, ownership of the table, or admin privileges.

Rules that come up on the exam: - A start is mandatory for batch reads. - Both bounds are inclusive. table_changes('t', 0, 10) includes versions 0 and 10. - You can't go back before enablement. A start version from before change data feed was enabled raises an error. - Timestamps are strings in the format yyyy-MM-dd[ HH:mm:ss[.SSS]], and versions are integers.

SELECT * FROM table_changes('tableName', 0, 10)

spark.read \
  .option("readChangeFeed", "true") \
  .option("startingVersion", 0) \
  .option("endingVersion", 10) \
  .table("myDeltaTable")

spark.read \
  .option(" ? ", "true") \
  .option("startingVersion", 0) \
  .table("<table_name>")

Sources14

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.An UPDATE of one row produces one change row with the new values.Why is that wrong?

   Each updated row produces two rows: an update_preimage with the old values and an update_postimage with the new ones. Downstream logic has to filter for or handle both.

   Covered in What a change row looks like: updates and deletes exposed

2. 2.You can keep delta.enableChangeDataFeed set while also using automatic change data feed.Why is that wrong?

   Legacy and automatic CDF are mutually exclusive. Migrating means unsetting the legacy property.

   Covered in Turning it on: legacy vs automatic change data feed

3. 3.You can read a table's change feed from version 0 even if CDF was enabled later.Why is that wrong?

   The feed only exists from the point it was enabled. Asking for an earlier version raises an error.

   Covered in Reading a range of changes in batch