Do I understand correctly that your table that today is refreshed, you would change the update method to only modify/add changed/new records? I just want to be sure, since change data capture normally describes how to fetch updats/inserts at the source, not the target. Manage target tables in more "intelligent" way is called historization, or SCD Type 2.
If the later, here SAS Data Integration Studio is nice tool if you are building a data warehouse or similar.
Data set audit is nice feature, but I would say that is more for audit (of data management itself), and usually works best with small/moderate data sizes.
If you want to track changes, and want to "easily" query it, SCD Type 2 sounds more what you need.
Suggested approach:
1. Inner join/lookup daily transactions with target table.
2. Compare columns values to see if there is any change.
3. If yes trigger an insert with a date/timestamp
4. Insert records that didn't get a match in the initial join
Some SCD Type 2 models also use end-dating of the record that is being updated (have anew veriosn inserted). This causes some more cost in the data loading aprt, but eases the query (you could use WHERE date BETWEEN StartDate AND EndDate), and typically a bit more performant than without it (for SAS data stores at least).
... View more
