(+351) 21 24 10006  ·  info@bconcepts.pt
Carnaxide, Lisbon

How to do incremental CDC change streaming in Databricks

João Barros 19 de November de 2026 4 min read

This tutorial shows how to do incremental Change Data Capture (CDC) streaming in Databricks to apply continuous upserts to a Delta table. It is useful when you need to keep a target database synchronized with source changes in near real time.

Prerequisites

  • Databricks workspace with a running cluster.
  • Permissions to read the source (for example Kafka, or a log file) and write to DBFS/Delta.
  • Basic knowledge of PySpark and Delta Lake.
  • Example change feed in JSON format with fields: op (I/U/D), id, payload, timestamp.

Step 1: Understand the CDC pattern and why to use Structured Streaming

CDC (Change Data Capture) represents CRUD operations as events. In Databricks, using Structured Streaming allows you to process those events continuously and apply MERGE to Delta tables with low latency. The idea is to read the stream, normalize events and execute micro-batches with upsert.

Step 2: Read the event stream (example with JSON files in DBFS)

For learning, we use a directory in DBFS where JSON files with events are placed. Structured Streaming will detect new files and process them in micro-batches.

# Exemplo PySpark num notebook Databricks
from pyspark.sql.functions import from_json, col, expr
from pyspark.sql.types import StructType, StructField, StringType, TimestampType

schema = StructType([
    StructField("op", StringType()),     # I, U, D
    StructField("id", StringType()),
    StructField("payload", StringType()),
    StructField("ts", TimestampType())
])

input_path = "/mnt/data/cdc_events/"
raw = spark.readStream.format("json").schema(schema).load(input_path)

Step 3: Transform events into upsert format

Convert the payload JSON into columns, normalize operations and mark rows for MERGE. We'll create a column _is_deleted for D (delete) operations.

from pyspark.sql.functions import from_json, col
from pyspark.sql.types import StructType, StructField, StringType

payload_schema = StructType([
    StructField("name", StringType()),
    StructField("value", StringType())
])

events = raw.withColumn("payload_json", from_json(col("payload"), payload_schema)) \
            .select(
                col("op"),
                col("id"),
                col("ts"),
                col("payload_json.*")
            ) \
            .withColumn("_is_deleted", expr("op = 'D'"))

Step 4: Perform incremental upserts to the Delta table using foreachBatch

In each micro-batch, we use foreachBatch to execute a MERGE SQL on a Delta table. This gives control to apply changes transactionally.

target_table = "default.cdc_target"

# Cria tabela alvo se não existir
spark.sql(f"CREATE TABLE IF NOT EXISTS {target_table} (id STRING, name STRING, value STRING, ts TIMESTAMP) USING DELTA")

def upsert_to_delta(batch_df, batch_id):
    batch_df.createOrReplaceTempView("staging_cdc")
    merge_sql = f"""
    MERGE INTO {target_table} AS t
    USING staging_cdc AS s
      ON t.id = s.id
    WHEN MATCHED AND s._is_deleted = true THEN
      DELETE
    WHEN MATCHED THEN
      UPDATE SET name = s.name, value = s.value, ts = s.ts
    WHEN NOT MATCHED AND s._is_deleted = false THEN
      INSERT (id, name, value, ts) VALUES (s.id, s.name, s.value, s.ts)
    """
    spark.sql(merge_sql)

query = events.writeStream.foreachBatch(upsert_to_delta) \
    .outputMode("update") \
    .option("checkpointLocation", "/mnt/checkpoints/cdc_upsert_checkpoint") \
    .start()

Step 5: Manage idempotency and event ordering

Common errors: out-of-order or duplicate events. To maintain idempotency, include a version/timestamp field and apply logic that compares ts to decide updates. Another best practice is to compact the Delta table with OPTIMIZE periodically.

# Exemplo simples: só aplica UPDATE/INSERT se ts do event for maior
merge_sql = f"""
MERGE INTO {target_table} AS t
USING (SELECT *, row_number() OVER (PARTITION BY id ORDER BY ts DESC) AS rn FROM staging_cdc) AS s
  ON t.id = s.id
WHEN MATCHED AND s._is_deleted = true AND s.rn = 1 THEN DELETE
WHEN MATCHED AND s.rn = 1 AND s.ts > t.ts THEN
  UPDATE SET name = s.name, value = s.value, ts = s.ts
WHEN NOT MATCHED AND s.rn = 1 AND s._is_deleted = false THEN
  INSERT (id, name, value, ts) VALUES (s.id, s.name, s.value, s.ts)
"""

Check the result

To confirm that the streaming applies CDC correctly, query the target Delta table and verify timestamps, values and deletions.

-- No notebook (SQL ou PySpark)
spark.sql("SELECT * FROM default.cdc_target ORDER BY ts DESC LIMIT 100").show()

-- Verificar estado do stream
query.isActive
query.status

Conclusion

Following these steps, you have a CDC pipeline in Databricks that reads events, normalizes them and applies upserts to Delta with low latency. Next steps: connect a real source (Kafka, Debezium), add monitoring and optimize consistency (compaction, OPTIMIZE). Tip: test duplication and ordering scenarios before going to production.