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

[manish1987c]

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 

[shan_chandra]

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

[manish1987c]

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

[xj2193]

@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.

[Pat]

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.

[xj2193]

@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?

[Pat]

@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