You are correct that I do not offer any solution to CDC or SCD2 in this blog post - that could, however, be a new post in the future. If you take a look at this repo: https://github.com/ael-computas/sqlserver-to-bigquery , that is one way to do it with sql server. (blog post https://ael-computas.medium.com/copy-sql-server-data-to-bigquery-without-cdc-c520b408bddf)
The Sharkcell link that I had was not the correct one, this should have a bit more information https://www.sharkcell-suite.com/ - although it is still a bit blackbox.
The sharkcell suite do offer delta loads and takes care of everything related to source -> mart (* obvisouly you do need business to build marts) for you, with all the expected metadata columns.
If you are looking for something open source, the "singer" (https://www.singer.io/) tool l has some good ideas, but it does lack cloud native in its core architecture and relies on something vm like to the integrations. Hopefully that will change ;) It does offer integration with a lot of popular sources.