issue with multi active satellite

[saivigneshk]

Hi,
I am trying to create a multi active satellite with one main hash key, hash difference and a link hash key using bigquery/stage.sql.
In compiled code ma_hashdiff_prep CTE, I am getting extra comma. due to that the compilation is failing.
is that a bug?

[tkirschke]

Hi @saivigneshk and thanks for reaching out!

To better understand the issue, could you please provide:

• Which datavault4dbt version you have installed
• The model that causes the issue
• The compile SQL for that model

Kind regards,
Tim

[saivigneshk]

Hi @tkirschke
version: v1.0.17

model link: https://github.com/ScalefreeCOM/datavault4dbt/blob/main/macros/staging/bigquery/stage.sql

`ma_hashdiff_prep AS (

SELECT
  
  IFNULL(TO_HEX(LOWER(MD5(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(
    IFNULL((CONCAT('\"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(CAST(`d_member_code` AS STRING)), r'\\', r'\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^')
    ), r'\n', '') 
    , r'\t', '') 
    , r'\v', '') 
    , r'\r', '') AS STRING), '^^')))), '00000000000000000000000000000000') AS hk_member_h, 
, 
IFNULL(TO_HEX(LOWER(MD5(STRING_AGG(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(
    IFNULL((CONCAT('\"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(CAST(`zip_code` AS STRING)), r'\\', r'\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^'),'||',
    IFNULL((CONCAT('\"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(CAST(`channel` AS STRING)), r'\\', r'\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^'),'||',
    IFNULL((CONCAT('\"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(CAST(`zone_id` AS STRING)), r'\\', r'\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^'),'||',
    IFNULL((CONCAT('\"', REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(CAST(`enabled` AS STRING)), r'\\', r'\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^')
    ), r'\n', '') 
    , r'\t', '') 
    , r'\v', '') 
    , r'\r', '') AS STRING), '^^||^^||^^||^^') ORDER BY zone_id)))), '00000000000000000000000000000000') AS hd_member_zone_hsat,
  ldts

FROM derived_columns
GROUP BY hk_member_h, ldts

),`

we are getting extra comma after hk_member_h

[tkirschke]

Hi @saivigneshk !

Thanks for providing me the generated SQL!
I tested it again on BigQuery, but on my side it works fine. Could you please provide me the actual model (.sql file) that you wrote and executed? You have only linked the general macro called in the background.

Kind regards,
Tim

[saivigneshk]

Hi @tkirschke
sorry, I can't send u original sql file

issue.txt

In above txt file is same as original one with different column names
The extra comma is coming because of creating hk_mlink_l

Thanks

[tkirschke]

Hi @saivigneshk ,

Now I understand the issue and was able to reproduce it within my environment.
I found the problem in the code and created an issue out of this discussion thread.
I fixed the problem in this branch. Please review and check if this modification fixes it for you.

You can install it by changing your packages.yml to:

packages: 
  - git: 'https://github.com/ScalefreeCOM/datavault4dbt.git'
    revision: '93-multi-active-satellite-wrong-comma-when-having-multiple-hashkeys'

[saivigneshk]

Hi @tkirschke
I have generated the query using the new branch, now we are getting the output as expected.

Thank you for fixing the issue and please let me know when the branch is merged with main branch.

Kind regards,
saivigneshk