topic Re: Delta Live Table - Flow detected an update or delete to one or more rows in the source table in Data Engineering

Delta Live Table - Flow detected an update or delete to one or more rows in the source table

manish1987c — Fri, 14 Jun 2024 05:01:50 GMT

I have create a pipeline where i am ingesting the data from bronze to silver and using SCD 1, however when i am trying to create gold table as dlt it is giving me error as "Flow 'user_silver' has FAILED fatally. An error occurred because we detected an update or delete to one or more rows in the source table."

#DLT #DeltaLiveTables

Flow 'gold_table_test' has FAILED fatally. An error occurred because we detected an update or delete to one or more rows in the source table. Streaming tables may only use append-only streaming sources. If you expect to delete or update rows to the source table in the future, please convert table gold_table_test to a materialized view instead of a streaming table. If you are fine to skip changes, set the option 'skipChangeCommits' to 'true'. If you only have a one-time change in the source table and will only have append-only commits from now on, you can perform a full refresh to table gold_table_test to resolve the issue. A Full Refresh will attempt to clear all data from table gold_table_test and then load all data from the streaming source. The non-append change can be found at version 27. Operation: MERGE Username: mk1987c@outlook.com Source table name: silver_load_test

Re: Delta Live Table - Flow detected an update or delete to one or more rows in the source table

shan_chandra — Fri, 14 Jun 2024 19:51:35 GMT

@manish1987c -The Streaming does not handle input that is not an append. you can set skipChangeCommits to true

Re: Delta Live Table - Flow detected an update or delete to one or more rows in the source table

manish1987c — Sat, 15 Jun 2024 04:18:44 GMT

Can you please share some sample code... Like how to use it..

Re: Delta Live Table - Flow detected an update or delete to one or more rows in the source table

xj2193 — Fri, 11 Jul 2025 20:45:20 GMT

@manish1987c I developed similar project. I think the issue might because it's a materialized view prior to your last gold layer. The materialized view only contain the most recent records, not the whole logging of the table. I wonder if it works for you to change it to streaming table.

For me, a streaming table doesn't work because I need to apply some window functions to clean the data. I haven't figured out a way to fix this problem.

Re: Delta Live Table - Flow detected an update or delete to one or more rows in the source table

Pat — Fri, 11 Jul 2025 21:06:47 GMT

Streaming tables in Delta Live Tables (DLT) only support append-only operations in the SOURCE.

The error occurs because:

1. Your silver table uses SCD Type 1, which performs UPDATE and DELETE operations on existing records
2. Your gold table is defined as a streaming table, which only supports append-only operations
3. DLT detected non-append operations (MERGE operations from SCD Type 1) in the source silver table

Change your gold table from a streaming table to a materialized view.

Re: Delta Live Table - Flow detected an update or delete to one or more rows in the source table

xj2193 — Tue, 15 Jul 2025 13:31:26 GMT

@Pat thanks for your input! In my use case, I want to apply "create_auto_cdc_flow" on my last table. If I change my last table to be materialized view, I will get an unsupported destination error:

java.lang.IllegalArgumentException: Unsupported destination cdc_notes_silver in flow: 
cdc_notes_silver_merge at transformerWithDestination

Do you have any suggestion on how to solve this issue?

Re: Delta Live Table - Flow detected an update or delete to one or more rows in the source table

Pat — Tue, 15 Jul 2025 20:03:15 GMT

@xj2193 I am not sure why you are getting this error, maybe if you could share some code to understand it.

Also, you could consider to use `create_auto_cdc_from_snapshot_flow`

dlt.create_streaming_table("target") dlt.create_auto_cdc_from_snapshot_flow( target="target", source="source", keys=["key"], stored_as_scd_type=2 )

https://docs.databricks.com/gcp/en/dlt-ref/dlt-python-ref-apply-changes-from-snapshot