Concurrent append exception - Two streaming sources writing to same record on the delta table

kmaley — Mon, 01 Jul 2024 04:32:02 GMT

Hi All, I have a scenario where there are 2 streaming sources Stream1( id, col1, col2) and Stream 2( id, col3, col4) and my delta table has columns (id, col1, col2, col3, col4).

My requirement is to insert the record into the delta table if the corresponding is record is not present and would like to update corresponding stream values if the id record is already present on the delta table.

I have no control on the source to do stream joins by adding watemark.

I tried implementing this using 2 merge statements one for each of the streaming source, but i am facing concurrent append exception when both the streams has same id record at the same time.

For now, to implement this I have used union of the streaming sources and foreachbatch. In the foreach batch, I would filter the records to 2 separate data frames and apply merge with delta table again. This is working as the merge statements are executing in sequence instead of parallel execution.

Could someone please help if there is any better way to implement this solution in databricks.

Re: Concurrent append exception - Two streaming sources writing to same record on the delta table

Witold — Mon, 01 Jul 2024 08:07:32 GMT

I would keep both write operations separate, i.e. they should write in own tables/partitions. In later stages (e.g. silver), you can easily merge them.

topic Concurrent append exception - Two streaming sources writing to same record on the delta table in Data Engineering

Concurrent append exception - Two streaming sources writing to same record on the delta table

Re: Concurrent append exception - Two streaming sources writing to same record on the delta table