topic Re: Error : . If you expect to delete or update rows to the source table in the future....... in Data Engineering

Error : . If you expect to delete or update rows to the source table in the future.......

rt-slowth — Wed, 07 Feb 2024 06:04:55 GMT

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. 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 user_silver to a live table instead of a streaming live table. To resolve this issue, perform a Full Refresh to table user_silver. A Full Refresh will attempt to clear all data from table user_silver and then load all data from the streaming source.

The non-append change can be found at version 11.
Operation: MERGE
Username: doheekim

When I create a silver table in Databricks that uses the table to which dlt.apply_changes is applied as the source data, why does the first run work fine, but an error is thrown saying that only appned is possible from the second run?
Source table name: user

Re: Error : . If you expect to delete or update rows to the source table in the future.......

rt-slowth — Wed, 07 Feb 2024 06:45:35 GMT

@Retired_mod
Can you give me some example code on how to switch to live tables using pyspark?

Re: Error : . If you expect to delete or update rows to the source table in the future.......

Palash01 — Wed, 07 Feb 2024 07:01:31 GMT

Hey @rt-slowth

Adding to @Retired_mod's points and refereeing to your bronze and silver code you posted in questions-about-the-design-of-bronze-silver-and-gold-for-live post. Looks like you are doing a SCD operation on your bronze layer which explains why the pipeline is erroring out in the subsequent run. To over come this you can try as explained by Kaniz in her post.

Also, the code to switch to live tables is using readStream and writeStream functions in your code.

For writing the streaming table:

query = transformed_stream \ .writeStream \ .format("delta") \ .option("checkpointLocation", "/databricks/dbfs/checkpoints") \ .trigger(processingTime="5 minutes") \ .start("/databricks/dbfs/live_tables/my_live_table")

For reading a streaming table:

source_stream = spark.readStream \ .format("delta") \ .option("path", "/databricks/dbfs/mnt/landing_zone") \ .option("processingTime", "1 minute") \ .load()

Re: Error : . If you expect to delete or update rows to the source table in the future.......

Palash01 — Wed, 14 Feb 2024 06:41:53 GMT

Hey @rt-slowth

Just checking in if the provided solution was helpful to you. If yes, please accept this as a Best Solution so that this thread can be considered closed.