Hash algorithm SHA2 for Snowflake having defined hash_datatype "BINARY(32)" not working as expected

[migma-ciklum]

We've defined these variables in our dbt_project file (using 1.3.0 version of ScalefreeCOM/datavault4dbt):

• datavault4dbt.hash: 'SHA2'
• datavault4dbt.hash_datatype: 'BINARY(32)'

But when running our models against Snowflake, our hash keys and hash diffs will turn out to be BINARY(8388608) instead of BINARY(32). Is there a way to make the hash keys and hash diffs use the defined datavault4dbt.hash_datatype, including the length?

Thanks in advance for your help and feedback!
BR

[tkirschke]

Hi @migma-ciklum and thanks for reaching out!

I think I found the issue in the Snowflake Hashing. I implemented a potential fix in this branch. Could you install it in your project and check if this fixes it for you?

In your packages.yml, just change it to this to install from a custom branch:

packages:
  - git: "https://github.com/ScalefreeCOM/datavault4dbt.git"
    revision: tkirschke-patch-1

Best regards,
Tim

[migma-ciklum]

Thank you very much for your quick response and the proposed solution!

We've been testing it, but unfortunately, BINARY fields are still being created with a length that is not defined in the global variable (in this case, we're seeing BINARY(64) being generated).

While reviewing the code, we noticed that a call to the CAST() function has been included, but it seems to only apply when the result of the SHA2_BINARY() function is null, not for the rest. Could this perhaps be the problem?

We've been testing a "customized" version of the macro, which, according to the tests we've been conducting, seems to generate the correct data type including the field length. I'm attaching it here for your reference. Essentially, we moved the use of the CAST() function to the beginning, wrapping it around the call to IFNULL().

{%- if 'VARCHAR' in datatype or 'CHAR' in datatype or 'STRING' in datatype or 'TEXT' in datatype %}

    {%- if case_sensitive -%}
        {%- set standardise_prefix = "CAST(IFNULL(LOWER({}(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(CONCAT(".format(hash_alg)-%}

        {%- if alias is not none -%}
            {%- set standardise_suffix = "\n)), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]'))), {}) AS {}) AS {}".format(zero_key, datatype, alias)-%}
        {%- else -%}
            {%- set standardise_suffix = "\n)), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]'))), {}) AS {})".format(zero_key, datatype)-%}
        {%- endif -%}
    {%- else -%}
        {%- set standardise_prefix = "CAST(IFNULL(LOWER({}(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(".format(hash_alg)-%}

        {%- if alias is not none -%}
            {%- set standardise_suffix = "\n), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]'))), {}) AS {}) AS {}".format(zero_key, datatype, alias)-%}
        {%- else -%}
            {%- set standardise_suffix = "\n), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]'))), {}) AS {})".format(zero_key, datatype)-%}
        {%- endif -%}
    {%- endif -%}

{%- else -%}

    {%- if case_sensitive -%}
        {%- set standardise_prefix = "CAST(IFNULL({}(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(CONCAT(".format(hash_alg)-%}

        {%- if alias is not none -%}
            {%- set standardise_suffix = "\n)), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]')), {}) AS {}) AS {}".format(zero_key, datatype, alias)-%}
        {%- else -%}
            {%- set standardise_suffix = "\n)), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]')), {}) AS {})".format(zero_key, datatype)-%}
        {%- endif -%}
    {%- else -%}
        {%- set standardise_prefix = "CAST(IFNULL({}(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(".format(hash_alg)-%}

        {%- if alias is not none -%}
            {%- set standardise_suffix = "\n), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]')), {}) AS {}) AS {}".format(zero_key, datatype, alias)-%}
        {%- else -%}
            {%- set standardise_suffix = "\n), '\\n', '') \n, '\\t', '') \n, '\\v', '') \n, '\\r', '') AS STRING), '[ALL_NULL]')), {}) AS {})".format(zero_key, datatype)-%}
        {%- endif -%}
    {%- endif -%}

{%- endif -%}

Once again, thank you very much for your help and your time.
Best regards!

[tkirschke]

Hi @migma-ciklum ,

That looks like a good solution. Would you be so kind to create a PR out of that proposed solution? I will proceed with testing on my side then.

Best regards,
Tim

[migma-ciklum]

Hi @tkirschke ,

I've created a fork with the changes in the hash_standardization.sql file. As I don't have permissions to commit to your branch or to create a new one, I created this fork. Please, if this is not the right way to share the changes, I would appreciate it if you could tell me the steps to follow.

PR: #174

Best regards!

[tkirschke]

Answered and Solved with #174 .

Thanks @migma-ciklum for your contribution!