Change Data Feed

Databricks · Delta Lake

Theory

Change Data Feed (CDF) makes a Delta table record row-level changes, not just new versions. You enable it with the table property delta.enableChangeDataFeed = true. From then on, every INSERT, UPDATE, DELETE and MERGE also writes change records, and you can read them with the table_changes('main.sales.orders', start_version) SQL function or spark.read.option("readChangeFeed", "true").option("startingVersion", 2).table(...).

Each change row has the table's columns plus three metadata columns: _change_type (insert, update_preimage, update_postimage or delete), _commit_version and _commit_timestamp. An update produces two rows: the old values (preimage) and the new values (postimage). For example, if version 2 updates order 102 from 800 to 850 and version 3 deletes order 103, table_changes from version 2 returns three rows. Downstream jobs can then process only what changed instead of rereading the whole table.

Why we use it

Incremental pipelines need to know which rows changed so silver and gold tables, caches or external systems can be updated cheaply. CDF gives that list directly from the Delta log, including updates and deletes that plain appends cannot show.

When to use it

Enable CDF on tables whose changes feed downstream tables, audits or syncs. It costs some extra storage. For append-only tables, a normal streaming read is enough. CDF only covers changes made after it was enabled.

How it works

  1. Enable CDF on the table
  2. A job runs UPDATE, DELETE or MERGE
  3. Delta writes data files plus change records
  4. Was the row updated? Yes: Record update_preimage and update_postimage. No: Record insert or delete.
  5. Downstream job reads table_changes from its last processed version

Syntax

ALTER TABLE t SET TBLPROPERTIES (delta.enableChangeDataFeed = true)
SELECT * FROM table_changes('main.sales.orders', 2 [, end_version])
spark.read.option("readChangeFeed", "true").option("startingVersion", 2).table(t)
Metadata: _change_type, _commit_version, _commit_timestamp

Example

ALTER TABLE main.sales.orders
  SET TBLPROPERTIES (delta.enableChangeDataFeed = true);   -- version 1

UPDATE main.sales.orders SET amount = 850 WHERE order_id = 102;   -- version 2
DELETE FROM main.sales.orders WHERE order_id = 103;               -- version 3

SELECT order_id, amount, _change_type, _commit_version
FROM table_changes('main.sales.orders', 2)
ORDER BY _commit_version, order_id, _change_type;

# Python, incremental read
changes = (spark.read.option("readChangeFeed", "true")
                .option("startingVersion", 2)
                .table("main.sales.orders"))

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic