SQL Server modification timestamps

Question

I'm implementing ingestion job from SQL Server 2016. For performance reasons we want to implement incremental loads. Few facts:

1. Customer has read replica as a part of always on availability group used for analytical purpose.
2. OLTP model doesn't have enough information to track changes (no columns like updated_at, is_deleted etc).
3. Read replica is currently used to extract data to DW and this is always full load.

So far we have considered:

1. Standard SQL Server CDC - we have descoped this, as this will have to be enabled on primary node and without performance tests implemented we don't understand impact of it.
2. Create another SQL Server node with transactional replication outside of 'always on AG' and implement CDC / triggers on top of it. Potentially decommission existing read replica.

Right now we are aiming with point 2. Are there any other options worth to consider?

Answer 1

One option is to put an AG replica on the target server, to serve as a staging database, and then upsert from there to the DW.