DLT pipeline failed: streaming table query reading from an unexpected Delta table ID

[kevinzhang29]

Hi everyone,

I'm running a DLT pipeline that loads data from Bronze to Silver using dlt.apply_changes(SCD type 2)

The first run of the pipeline worked fine -- data was written successfully into the target Silver tables.

However, when I ingested new data the next day and re-ran the pipeline, it failed with the following error:

Query [id = 85706ddc-02af-426c-9ba8-ab1da903b5c8, runId = 1507aa2e-c77e-4309-906b-1ed0afe25eed] terminated with exception: [DIFFERENT_DELTA_TABLE_READ_BY_STREAMING_SOURCE] The streaming query was reading from an unexpected Delta table (id = '77d0eb65-f733-4f7d-b6b5-5f7c25fc9264').
It used to read from another Delta table (id = '474c3622-d677-4882-8f31-ceb9275e90d9') according to checkpoint.
This may happen when you changed the code to read from a new table or you deleted and
re-created a table. Please revert your change or delete your streaming query checkpoint
to restart from scratch.

I understand this means the source Delta table ID has changed, but I didn't intentionally modify the table schema or logic.

It looks like the issue happens when dlt.apply_changes tries to update existing data on the second run.

Questions:

1. What is the best practice to prevent this "unexpected Delta table ID" error?
2. Is there a way to safely refresh or modify the source tables without breaking the streaming checkpoints?

[mark_ott · Accepted solution]

This “unexpected Delta table ID” error typically means your Delta Live Tables (DLT) pipeline detected that the underlying Delta table it was reading from has changed since the last checkpoint. When you use dlt.apply_changes() (for SCD Type 2), this is almost always caused by a mismatch between the checkpoint state and the physical table metadata.

Here are the best practices to prevent and resolve this issue:

1. Avoid Recreating Source Tables

The most common cause is that the source table (in Bronze or upstream stage) was deleted and recreated during development or testing. Delta assigns a new internal table ID on recreation, which invalidates existing checkpoint references.
Best practice: Do not drop and recreate the source tables. Instead, use TRUNCATE TABLE to clear data without changing the table ID.​

2. Keep Stable Table Names Across Runs

Ensure that the logic in your pipeline references the same physical table every run. Even using CREATE OR REPLACE TABLE changes the table ID.
Best practice: Initialize tables once outside of the pipeline setup. From that point, only update them incrementally.​

3. Manage the Checkpoint Path

Each DLT autogenerates checkpoints for streaming tables. If you modify a table dependency, the existing checkpoint may reference an outdated source table ID.
Resolution option: Delete or clear the checkpoint directory associated with the stream (e.g., /pipelines/<pipeline_id>/system/_checkpoints/)
Preventive best practice: Use consistent checkpoint locations tied to stable tables; avoid changing pipeline IDs or table definitions between runs.​

4. Do Not Use Target Tables of apply_changes as Sources

If your Silver layer depends directly on another table updated via dlt.apply_changes, that dependency can recreate Delta metadata unexpectedly. Instead, use a view or materialized table to read the intermediate table.​

5. Maintain Schema Consistency

Altering schema (adding columns, changing types) in the Bronze or Silver source will cause Delta to refresh internal metadata, creating a new table ID.
Best practice: Use explicit schemas and schema evolution policies (mergeSchema for controlled evolution).​

6. Use Development Isolation

When testing, use isolated pipelines instead of repeatedly destroying and redeploying production DLT pipelines. This prevents checkpoint conflicts and Delta ID mismatches under CI/CD re-deploy scenarios.​

7. If All Else Fails — Reset the Stream

If table recreation was unavoidable, you can reset by deleting the DLT checkpoints and running a full refresh. This lets the stream establish new state consistent with the new table IDs.

In short: ensure stable table existence, avoid resetting the source Delta tables, keep schema consistent, and manage checkpoints carefully. These are the proven best practices to prevent the “DIFFERENT_DELTA_TABLE_READ_BY_STREAMING_SOURCE” error in dlt.apply_changes(SCD type 2) pipelines.