Multi-active satellite: src_ma_key need to be included in hashdiff calcluation?

[cmettler]

Hey Scalefree Team,
i am finishing a sqlserver adapter based on the previous sqlserver branch. I am trying to understand the ma sat template from the other adapters.
if i understand the template code + remarks correct the hashdiff should be something like the attached sample sql queries.

All three queries would create the same hashhdiff but for different ma groups. The delta check would not work.
But if i include the multi active key in the payload the hashdiffs would be different for the delta check to work.
I think this should be documented in the wiki or the ma keys automatically added to the hashdiff calc if not explicitly added in the src_payload. Or did i miss something else ?
Kind Regards,
Christoph
masat.sql.txt

[tkirschke]

Hi @cmettler and thanks for reaching out!

Your input made me do some research, and apparently STRING_AGG() (SQL Server), LISTAGG() (Snowflake), and STRING_AGG() (BigQuery) all ignore NULL rows in the input.

A row in the current multi-active hashdiff calculation is set to NULL, if all input columns are NULL, achieved by the "NULLIF" function around the CONCAT().

To fix this, that NULLIF needs to be replaced, e.g. by a REPLACE() function, that scans for the "all_null" string, which would indicate that all columns are NULL, and replace that by the NULL placeholder "^^".

Based on the example code you provided, a SQL Server multi active hash calculation would need to look like this:

WITH source_data AS
(
SELECT 0 AS hk,'MA1' AS ma_attribute, 'col_a1' col_a, 'col_b1' col_b
UNION all
SELECT 0 AS hk,'MA3' AS ma_attribute, 'col_a1' col_a, 'col_b1' col_b
)
SELECT
hk,
STRING_AGG(
	REPLACE(
		CONCAT(ISNULL(col_a,'^^'),'||',ISNULL(col_b,'^^')),
		'^^||^^', '^^'
),'--') WITHIN GROUP (ORDER BY ma_attribute) hd
FROM source_data
GROUP BY hk
;
WITH source_data AS
(
SELECT 0 AS hk,'MA1' AS ma_attribute, 'col_a1' col_a, 'col_b1' col_b
UNION all
SELECT 0 AS hk,'MA2' AS ma_attribute, NULL col_a, NULL col_b
UNION all
SELECT 0 AS hk,'MA3' AS ma_attribute, 'col_a1' col_a, 'col_b1' col_b
)
SELECT
hk,
STRING_AGG(
	REPLACE(
		CONCAT(ISNULL(col_a,'^^'),'||',ISNULL(col_b,'^^')),
		'^^||^^', '^^'
),'--') WITHIN GROUP (ORDER BY ma_attribute) hd
FROM source_data
GROUP BY hk

Please note that the multi-active attribute should not be included in the hashdiff calculation!

Hope this helps! Will change the Code for Snowflake and BigQuery accordingly .

[cmettler]

Hey Tim,
This is a beautiful solution! I think a '||' instead of '--' should be used as the string_agg delimiter for the later [ALL_NULLS] check.
Another thing i was thinking about is if the source data batch contains duplicates then these duplicates would be part of the hashdiff calulation?
Thanks!
Christoph

[tkirschke]

Hi Christoph,
I think it would not really matter what we select as the string_agg delimiter, because the later [ALL_NULLS] check would no longer work for a multi-active hashdiff. To do that, we would need to know how many rows are part of one hashdiff, to then correctly generate the ALL_NULL comparison string.
Maybe there is a programmatic way to get that number and use it for this generation, but I would not directly be aware of one.

Regarding the deduplication: in a real example scenario, a load-date column would be there as well, and that would be used in the GROUP BY statement in addition to the hk. So the only problem would be real duplicates regarding multiple times the same descriptive information for one hashkey and one load-date.
If you can't guarantee that your source data is deduplicated already, you need to ensure this by providing a layer in front of the stage which deduplicates.

[cmettler]

Hey Tim,
the number of rows are known. Here is a snippet dynamically creating the all_nulls based on row count at runtime. All what is needed is the delimiter and null replacement constants

DECLARE @delimiter NVARCHAR(MAX) = '||'
DECLARE @null_replacement NVARCHAR(MAX) = '^^'

;
WITH source_data AS
(
SELECT 0 AS hk,'MA1' AS ma_attribute, null col_a, null col_b
UNION all
SELECT 0 AS hk,'MA2' AS ma_attribute, NULL col_a, NULL col_b
UNION all
SELECT 0 AS hk,'MA2' AS ma_attribute, NULL col_a, NULL col_b
UNION all
SELECT 0 AS hk,'MA3' AS ma_attribute, null col_a, null col_b
UNION all
SELECT 1 AS hk,'MA2' AS ma_attribute, 'col_a' col_a, NULL col_b
UNION all
SELECT 1 AS hk,'MA3' AS ma_attribute, null col_a, null col_b
),h1 AS
(
SELECT
hk,
STRING_AGG(
REPLACE(
CONCAT(ISNULL(col_a,'^^'),'||',ISNULL(col_b,'^^')),
'^^||^^', '^^'
),'||') WITHIN GROUP (ORDER BY ma_attribute) hdiffstr,
LEFT(REPLICATE(CAST(@null_replacement+@delimiter AS varchar(max)),COUNT()),(LEN(@null_replacement+@delimiter)COUNT())-LEN(@delimiter)) AS all_null
FROM source_data
GROUP BY hk
)
SELECT
h.,
CASE
WHEN hdiffstr = all_null then CAST(REPLICATE(0x0,16) AS BINARY(16))
ELSE HASHBYTES('md5',h.hdiffstr)
END hdiff
FROM
h1 h

[cmettler]

all the stars in the count(star) are lost from pasting :-)

[cmettler]

mahashdiffsnippet.sql.txt