I am trying to create a DLT pipeline to implement SCD type 1. However, after my initial full refresh, when I am trying to do an incremental refresh, it fails giving below error:
"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 <> to a live table instead of a streaming live table. To resolve this issue, perform a Full Refresh to table <>. A Full Refresh will attempt to clear all data from table <> and then load all data from the streaming source. "
As per Databricks doc,
"By default, streaming tables require append-only sources. When a streaming table uses another streaming table as a source, and the source streaming table requires updates or deletes, for example, GDPR “right to be forgotten” processing, the skipChangeCommits flag can be set on the target streaming table to ignore those changes. "
But my doubt is autoloader anyways wont pick any changes to existing files and only new files being added would be picked unless we do an Full refresh of DLT pipeline. In that case what is the use of skipChangeCommits?
Can someone explain what is the exact use case for skipChangeCommits?
I tried setting skipChangeCOmmits to True in my streaming table but error is not resolve.