search

Datavault-UK / automate-dv

A free to use dbt package for creating and loading Data Vault 2.0 compliant Data Warehouses (powered by dbt, an open source data engineering tool, registered trademark of dbt Labs)
https://www.automate-dv.com
Apache License 2.0
485 stars 121 forks source link

[BUG] Wrong Cast in macro type_string for Databricks Hash Function #238

Open ambullo opened 1 month ago

ambullo commented 1 month ago

Describe the bug In the type_string.sql line 22 to 31 you do a cast to a varchar based on the hashtype. This is not appropriate for databricks as databricks does not know the type varchar. The comparable type in databricks would be string and for it no length is defined.

This leads then so an output in the hash algorithm like this: UPPER(SHA2(NULLIF(CONCAT( IFNULL(NULLIF(UPPER(TRIM(CAST(NaturalKey AS VARCHAR(32)))), ''), '^^') ), '^^'), 256)) AS NaturalKey_HK,

This is bad, since the cast to 32 bit could potentially truncate the to be hashed column and lead to unwanted hashing collisions. Luckily, databricks does not truncate the input column. Nevertheless, it should be fixed as it has the potential to corrupt all hash keys.

Environment

dbt version: 1.8.3 automate_dv version: tested on 10.2 but most likely also on 11.0 Database/Platform: Databricks hash algorithm: SHA enable_binary_hash: false

Expected behavior Replace Cast to Varchar with Cast to String

AB#5567

azure-boards[bot] commented 1 month ago

✅ Successfully linked to Azure Boards work item(s):

DVAlexHiggs commented 1 month ago

Hi @ambullo, thanks for this thorough and detailed report.

I believe we fixed this as part of v0.11.0. v0.11.0 was explicitly an update to resolve hashing and handling of hash types correctly in both Databricks and BigQuery. Please can you upgrade and try it out?

If it's still an issue then we'd be happy to take a look, however 😄 Thanks!

ambullo commented 1 month ago

I just checked. this bug report applies also to version v0.11.0