Hello,

Could anyone please help regarding the scenario below?

Scenario

• I'm using the DLT SQL Language
• Parquet files are landed each day from a source system.
• Each day, the data contains the 7 previous days of data. The source system can have very late arriving data and can be unstable from time to time.
• There will be a lot of duplicate data that is precisely the same or has been updated via a timestamp.
• I'm reading the stream using cloud_files into a bronze table.

Requirements

• I need to de-duplicate the streamed data from landing to bronze to keep the size down.

Problem

• Windowing functions i.e. "row_number over(partition by row_id order by date_modified desc) as row_ver" is not supported in structured streaming tables.
• DISTINCT would only capture exact duplicated rows.

Question

Before, using streaming merge, I could use foreachBatch and de-duplicate the batch before merging the data to the target. That worked quite well, although a bit fiddly.

• What are the guidelines for de-duplicating streaming data in Delta Live Tables?
• Is there a way to do this in DLT Python, i.e. dropDuplicates?
• What is the community doing about data de-duplication from streaming sources?

Any advice or guidance is much appreciated.

Thank you.