Hi @Dharinip , excuse me for the super late reply! I had been very busy last month...

Thanks for providing the additional details. It seems you would like to have both SCD Type 1 and 2.

Are you using a DLT pipeline to implement this? I assume so, because my previous demo example didn't resonate with you, which used a standalone materialized view approach.

So, I just created a simple DLT pipeline based on your example. Having a bronze table (client_raw), apply changes into a SCD type 2 streaming table (client_history), then create a MV (client_current). The client_current MV should be updated incrementally using a Serverless used. Related doc can be found here.

I've uploaded the sample DLT source code here. I hope it suites your need. https://gist.github.com/koji-kawamura-db/999b7fb9e114a236c15442776c5c0a0f

BTW, creating both type 1 and 2 as two Streaming Tables might be an alternative solution. So, the sample pipeline includes such a route just in case.

The DLT pipeline looks like this: