HashedNonKeyColumns in NB_FMD_LOAD_BRONZE_SILVER

[NickZeeuw]

Hi all,

I was looking at the notebook for loading from bronze to silver. I noticed that the logic for determining the HashedNonKeyColumns for the dfDataChanged only excluded the HashedPKColumn

non_key_columns = [column for column in dfDataChanged.columns if column not in ('HashedPKColumn')]
dfDataChanged = dfDataChanged.withColumn("HashedNonKeyColumns", md5(concat_ws("||", *non_key_columns).cast(StringType())))

But as far as I can see, the HashedNonKeyColumns from the bronze table is then also included in the HashedNonKeyColumns.

Is this by design? I would expect all technical fields should be left out of the hash. Am I missing something here? I checked the same key in bronze and silver and they both had a different HashedNonKeyColumns, while there where no cleansingrules applied between bronze and silver. Where I then also would expect the same HashedNonKeyColumns.

[edkreuk]

@NickZeeuw, I think you are right. Don;t know where that went wrong.

I think this should be the correct solution:
non_key_columns = [column for column in dfDataChanged.columns if column not in ('HashedPKColumn','HashedNonKeyColumns','RecordLoadDate')]

this are the technical colums which have been added in Bronze and should not be part in the Hash of Silver

[NickZeeuw]

@edkreuk Ok, thanks for checking.

Shouldn't the key columns of the table also be excluded? In loading landing to bronze the key columns of the table are also excluded from the HashedNonKeyColumns.

key_columns = PrimaryKeys
print(f": {', '.join(key_columns)}")
# Check if all PK's exist in source
for pk_column in key_columns:
    if pk_column not in dfDataChanged.columns:
        raise ValueError(f"PK: {pk_column} doesn't exist in the source.")
        # Define all the Non-Key columns => HashExcludeColumns

non_key_columns = [column for column in dfDataChanged.columns if column not in key_columns]

#add a hashed cloumn to detect changes
dfDataChanged = dfDataChanged.withColumn("HashedNonKeyColumns", md5(concat_ws("||", *non_key_columns).cast(StringType())))

[NickZeeuw]

@edkreuk And in the load landing to bronze, I noticed that the HashedPKColumn is being included in the HashedNonKeyColumns hash. Because the HashedPKColumn is first added to the landing df and then the non_key_columns is determined by excluding the key_columns, but not the HashedPKColumn just added.

dfDataChanged = (dfDataChanged
                .withColumn("HashedPKColumn", sha2(concat_ws("||", *read_key_columns), 256)))

non_key_columns = [column for column in dfDataChanged.columns if column not in key_columns]

#add a hashed cloumn to detect changes
dfDataChanged = dfDataChanged.withColumn("HashedNonKeyColumns", md5(concat_ws("||", *non_key_columns).cast(StringType())))

[edkreuk]

This is partially because we have that in our commercials solution. In that Solution you can exclude columns you do not want to expose in Silver and that is the reason that I'm adding these. More for future use. Let me check the Bronze part as well. Will come back to you.