I'm trying to resolve one issue and I would like to get some expert opinion on what the right solution actually is.
I have an SQL server with CDC with tables big_source (containing hundreds of millions of rows) and small_source containing small amount of rows.
I have set up Lakeflow connect to move these tables into our bronze layer - delta table with SCD1. After that I created an SDP pipeline which should
move data from bronze to silver into single table Target, which is essentially a join of the two.
My goal is also to propagate deletes and updates from sources to Target. When change occurs in the big_source, it is fine as I'm joining a small
stream of changes with table small_source. However if change occurs in in small_source table, I'm joining tiny stream of changes with huge big_source table and
I see big data movements (rows processed, bytes read) during processing tiny changes to the small_source. What is the best way how to optimize such operation to minimize data reads of the big_source (I tried to enable clustering on Lakeflow table - but it seems this is not supported, I created a new dp.table which merely mirrors the big_source). Thanks for any hint.