Hash value comparison: new record is not loaded, if the same hash value exist in older record.

[nnikolov84]

When using hash value comparison, new record is not loaded, if the same hash value exist in older record.

Input data:

CUSTOMER_ID	CUSTOMER_ADDRESS	UPDATE_ON	Comment
1	"ADDRESS_1"	1/1/2000	Initial address.
1	"ADDRESS_2"	1/20/2000	Customer changed address.
1	"ADDRESS_1"	1/22/2000	Customer changed back the address to the value from line 1.

The third value in the table will not be processed. This is happening, because when generating "ids_to_update", the historical records are not filtered.

with ids_to_update as 
   (select col1, HASH_COLUMN from `....TEST.TEST_TABLE_SRC_01`        
     except distinct         
   (select col1, HASH_COLUMN from `....TEST.source_data_scd_updates`)

In order for the process to work properly, only most recent record per KEY should be compared to the input data.
Possible solution is:

with ids_to_update as 
   (select col1, HASH_COLUMN from `....TEST.TEST_TABLE_SRC_01`        
     except distinct         
   (select col1, HASH_COLUMN from `....TEST.source_data_scd_updates`
      qualify row_number() over (partition by col1 order by updated_at desc) = 1)

[denziljoseph]

if your source table had unique records and it updated the record with new hash, will it be still an issue?

[nnikolov84]

if your source table had unique records and it updated the record with new hash, will it be still an issue?

I'm not sure I understand what you mean.
The data coming from the source is unique, but off course it is changing from delivery to delivery. This being SCD, it should show how a record changed in time, but it fails to do so, if a value was already delivered in the past. *see the table my first comment.

[denziljoseph]

In addition to your input data you need to have an incremental model with customer_id as unique key. And this new incremental model should be the source to your scd.

[Liscor]

@nnikolov84 is correct. Take this example
That's the source table.

And this is the output after calling scd.

As you can see, the new row is not shown in the output table because the hash value from the last row is exactly the same as the hash from the second row.