Create hubs from staging table where null is possible

[mattiasthalen]

Hi,

I have a dataset that includes (among other) the fields region, health center, and healthcare provider. These are transformed to hubs. However, some fields might be null, but they are still added to the hubs.

Shouldn't there be some kind of IS NOT NULL logic for the hubs, or should I ensure that the hash key in my staging table is null when my business key is null?

[tkiehn]

Hi @mattiasthalen and thanks for reaching out!

For the case that a business key is Null the hashkey should be the zero key. ( More information regarding zero keys can be found here.)

In that case, the Null business key is not added to the hub because the zero key from the created ghost record is already present.

Which database are you using?

Kind regards
Theo

[mattiasthalen]

Hi @mattiasthalen and thanks for reaching out!

For the case that a business key is Null the hashkey should be the zero key. ( More information regarding zero keys can be found here.)

In that case, the Null business key is not added to the hub because the zero key from the created ghost record is already present.

Which database are you using?

Kind regards

Theo

Hi Theo,

Ok, I will read up a bit.

It’s duckdb, so I’ve used the postgresql macros as a baseline. Might have to check the macros to see what’s missing. I also noticed that these end up in the links as well. That’s how I discovered them, using relationship tests.

Kind regards
Mattias

[tkiehn]

Hi Mattias and thank your for the additional details!

The attribute standardization logic for Null keys depends on the CONCAT() method returning Null as soon as one of the values is Null.
On Postgres this is not the case, instead the || operator has to be used.

To fix this following steps could be taken:

1. Add information if it is a hashkey or hashdiff to the attribute_standardise call inside the hash-macro (Commit from the dev branch)
2. Modify concatenation logic to return Null if the hashkey expression is Null. (Commit from the dev branch)

For this to work as intended you would have to ensure that concatenating strings with the || operator returns Null as soon as one of the values is null on DuckDB.

Let me know if this resolves your issue!

Kind regards
Theo

[mattiasthalen]

Not entirely sure that's where the problem lies.

The code generated looks like this:

COALESCE(LOWER(MD5(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(
        COALESCE((CONCAT('"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(BOTH ' ' FROM CAST(parent_id AS VARCHAR)), '\\', '\\\\'), '"', '\"'), '^^', '--'), '"')), '^^')
        ), '\n', '') 
        , '\t', '') 
        , '\v', '') 
        , '\r', '') AS VARCHAR), '^^'))), '00000000000000000000000000000000') AS parent_unit_hk

And when running a query that drops the hashing, and just keeps the NULLIF and its contents...

    NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(
        COALESCE((CONCAT('"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(BOTH ' ' FROM CAST(parent_id AS VARCHAR)), '\\', '\\\\'), '"', '\"'), '^^', '--'), '"')), '^^')
        ), '\n', '') 
        , '\t', '') 
        , '\v', '') 
        , '\r', '') AS VARCHAR), '^^') AS parent_unit_hk_input

The output I get is this:

So it looks like it does a hash of ""instead of getting ^^ as input.

The workaround I've done is to wrap a second NULLIF like this: NULLIF(NULLIF(..., '^^'), '""')

[tkiehn]

It is hashing "" because the CONCAT()-Method implicitly converts the null value to an empty string, resulting in CONCAT('"', '', '"').

If you try out using the double pipe || like so:

COALESCE(LOWER(MD5(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(
        COALESCE(('"' || REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(BOTH ' ' FROM CAST(parent_id AS VARCHAR)), '\\', '\\\\'), '"', '\"'), '^^', '--') || '"'), '^^')
        ), '\n', '') 
        , '\t', '') 
        , '\v', '') 
        , '\r', '') AS VARCHAR), '^^'))), '00000000000000000000000000000000') AS parent_unit_hk

it should return null and therefore the zero key as hashkey.

My code:

select
COALESCE(LOWER(MD5(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(
        COALESCE(('"' || REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(BOTH ' ' FROM CAST(null AS VARCHAR)), '\\', '\\\\'), '"', '\"'), '^^', '--') || '"'), '^^')
        ), '\n', '') 
        , '\t', '') 
        , '\v', '') 
        , '\r', '') AS VARCHAR), '^^'))), '00000000000000000000000000000000') AS hk_with_pipe,

COALESCE(LOWER(MD5(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(
        COALESCE((CONCAT('"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(BOTH ' ' FROM CAST(null AS VARCHAR)), '\\', '\\\\'), '"', '\"'), '^^', '--'), '"')), '^^')
        ), '\n', '') 
        , '\t', '') 
        , '\v', '') 
        , '\r', '') AS VARCHAR), '^^'))), '00000000000000000000000000000000') AS hk_with_concat

results in:

Kind regards
Theo

[mattiasthalen]

Aaah, thanks for pointing that out!