Hi @wu cong? , thank you for the question.
To be able to compare the 2 datasets, you can union them.
Before you union, make sure you create a column that says that the current data you're working with is the new\old version of the data. After the union, the other version will have empty values in this column, and you can then fill in the gaps.
Once you union them, you can create a count() column while grouping by the primary key column. It would look like so:
If the primary key is indeed unique - each primary key will either appear once or twice. If it appears twice, we know that the new record is a duplicate, meaning either old or updated.
If the primary key appears only once, it's either old\new record (assuming you don't delete records).
If you'd like the extra differentiation between old\updated\new, you can create another column based on a case-condition and the 2 columns we created earlier:
I hope this helps.
Please feel free to ask any additional questions.
Thanks,
Amit.
