SteveOstrowski
Databricks Employee
Databricks Employee

Hi @cdn_yyz_yul,

You are running into expected Structured Streaming behavior. Spark tracks the number and identity of streaming sources in the checkpoint metadata. When you change the query plan by adding a new source (e.g., adding df3 to a union that previously only had df1 and df2), the checkpoint no longer matches the new query, and Spark raises the assertion error you saw:

java.lang.AssertionError: assertion failed: There are [1] sources in the checkpoint offsets and now there are [2] sources requested by the query. Cannot continue.

This is documented behavior: changes to the number or type of input sources are not allowed against an existing checkpoint.

Here is a summary of your options and the trade-offs for each.

OPTION 1: SEPARATE STREAMING WRITERS (ONE PER SOURCE)

Each source gets its own streaming query writing to the same target Delta table. Delta Lake fully supports multiple concurrent append writers to the same table. The key rule is that each writer must have its own unique checkpoint location.

df1a.writeStream \
  .format("delta") \
  .outputMode("append") \
  .option("checkpointLocation", checkpoint_path_1) \
  .trigger(availableNow=True) \
  .table(f"{silver_catalog}.{silver_schema}.my_silver_table")

df2a.writeStream \
  .format("delta") \
  .outputMode("append") \
  .option("checkpointLocation", checkpoint_path_2) \
  .trigger(availableNow=True) \
  .table(f"{silver_catalog}.{silver_schema}.my_silver_table")

df3a.writeStream \
  .format("delta") \
  .outputMode("append") \
  .option("checkpointLocation", checkpoint_path_3) \
  .trigger(availableNow=True) \
  .table(f"{silver_catalog}.{silver_schema}.my_silver_table")

Pros:
- Adding a new source (e.g., df4) only requires adding a new writer with a new checkpoint. No existing checkpoints need to be deleted.
- Each source progresses independently, so a failure in one does not block the others.
- Simpler to monitor per-source throughput and latency.

Cons:
- Cannot do cross-source deduplication inline (you would need a separate downstream dedup step if needed).
- You need to manage multiple checkpoint paths.

The error you hit earlier ("DIFFERENT_DELTA_TABLE_READ_BY_STREAMING_SOURCE") was likely because you reused the same checkpoint location for a different streaming query. Each distinct streaming query must have its own checkpoint.

OPTION 2: SINGLE UNION QUERY WITH CHECKPOINT RESET

This is what you described in your original post: union all source DataFrames into one query, then write once. When you add a new source to the union, you must delete the old checkpoint (and optionally the target table) and reprocess from scratch.

df = df1a.unionByName(df2a).unionByName(df3a) \
  .dropDuplicatesWithinWatermark(["your_key_columns"])

df.writeStream \
  .format("delta") \
  .outputMode("append") \
  .option("checkpointLocation", my_checkpoint_path) \
  .trigger(availableNow=True) \
  .table(f"{silver_catalog}.{silver_schema}.my_silver_table")

Pros:
- You can deduplicate across all sources in a single step.
- Single checkpoint to manage.

Cons:
- Every time you add or remove a source, you must delete the checkpoint and reprocess all data from scratch.
- You should also delete (or truncate) the target table to avoid duplicate rows from the reprocessing, unless you have an idempotent merge/upsert pattern downstream.

If you go this route, one improvement over plain dropDuplicates() is to use withWatermark() combined with dropDuplicatesWithinWatermark(). This bounds the state size, which is important for long-running streams:

df = df1a.unionByName(df2a).unionByName(df3a) \
  .withWatermark("event_time", "10 hours") \
  .dropDuplicatesWithinWatermark(["guid"])

See the docs for details:
https://docs.databricks.com/aws/en/structured-streaming/watermarks.html

WHICH OPTION TO CHOOSE

- If your sources change over time (new tables get added periodically) and you do not need cross-source deduplication, Option 1 (separate writers) is the more maintainable approach. Adding a new source is just adding a new writer with a new checkpoint, with zero disruption to existing streams.

- If cross-source deduplication is a hard requirement, Option 2 (single union query) is the way to go. Just plan for the checkpoint reset when the set of sources changes.

To answer your specific question about whether you missed any steps when resetting: deleting the checkpoint is the required step. Deleting the target table is only necessary if you want to avoid duplicate rows from reprocessing data that was already written. If your downstream consumers can tolerate temporary duplicates (or you use a MERGE pattern), you can keep the table and just delete the checkpoint.

Relevant documentation:
- Checkpoints and recovery from query changes: https://docs.databricks.com/aws/en/structured-streaming/checkpoints.html
- Watermarks and dropDuplicatesWithinWatermark: https://docs.databricks.com/aws/en/structured-streaming/watermarks.html
- Delta Lake concurrency control (multiple writers): https://docs.databricks.com/aws/en/delta/concurrency-control.html

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.

If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.

View solution in original post