Andy Brownsword builds a hash key:
The issue: there was no indicator of which records had been modified and as a result the process took way too long, and downstream reporting wasn’t available on time.
After reviewing and stepping through the process it became clear that the vast majority of data didn’t change. This was a daily process handling 12 months of data, yet over 99% had no changes at all. However the process ingested the whole dataset (~250m records) and processed it in SQL.
Click through for an architectural-level discussion. In practice, HASHBYTES() works really well, especially when you use CONCAT() or CONCAT_WS() to put together the columns you care about