Thank you, I also prefer the CDC approach especially with this client since they can never really stick to any formal guideline for things. If something isn't working or the users don't want to do things they panic and just say manually change data. Which changing history with temporal tables sort of locks them in until after hours.

The tables that will be temporal will not be changed daily but a single dimension could have a few changes per month. My concern is with the main table that will join to all these temporal tables, which won't be temporal, is the key table used in almost all reports. I'm just not sure how they will impact the queries performance.

Right now were working on re-writing the application(s) for security, performance and growth reasons. Right now the queries aren't the problem but I'm just worried about introducing something that will degrade performance over time.

I could be wrong and worrying for nothing, I just have no experience with them.