Hi
I have a question on how the logic handles your proposed approach of handling effectivity that comes from the source.
You specify that these attributes should just be handled like normal descriptive attributes.
In the Satellite it contains a parameter for unique key which is typically only the hk and the load date. Is this still the case?
Does dbt create a primary key on the table for this, or is this just used for incremental load checks? Should one include the effectivity dates in here as well?

We did a small test on the sql branch (which we got when it was still on) and it actually seemed to work because there was no primary key on the actual tables, so the fact that there were duplicate "unique keys" didn't seem to error. However we didn't test subsequent loads

Discussed in #105

#105 (comment) :

The loading of non-historized satellites should deduplicate the incoming source data based on the hashkey column, and only select the earliest one per hashkey, based on the load-date. Although we do not expect changes in a non-historized satellite, there might be cases where we have duplicate rows across multiple loads.

Proposed changes:

• modify n_sat macro to always use
  • QUALIFY ROW_NUMBER() OVER (PARTITION BY ORDER BY ) = 1

FYI @26091999 @obause

Hi,

In the macro: macros/supporting/ghost_record_per_datatype.sql there are ghost record definition.

However, in all supported platforms except Exasol, there is not ghost record for DATE data type defined. The effect for this is that any attribute with DATE data type gets NULL value, which is in contrary for ghost record idea.

Snowflake code extract example - no DATE data type:
{%- if ghost_record_type == 'unknown' -%} {%- if datatype in ['TIMESTAMP_NTZ','TIMESTAMP'] %}{{ datavault4dbt.string_to_timestamp(timestamp_format, beginning_of_all_times) }} AS {{ alias }} {%- elif datatype in ['STRING', 'VARCHAR'] %}'{{ unknown_value__STRING }}' AS {{ alias }} (...) {%- elif datatype in ['NUMBER','INT','FLOAT','DECIMAL'] %}0 AS {{ alias }} {%- elif datatype == 'BOOLEAN' %}CAST('FALSE' AS BOOLEAN) AS {{ alias }} {%- elif datatype == 'ARRAY' %} CAST('{{ unknown_value__STRING }}' as ARRAY) as "{{ alias }}" {%- else %}NULL AS {{ alias }} {% endif %}

Is there a reason for it?

Thanks,
Jarek

In datavault4dbt with Snowflake we have identified a hash collision for values with small 'v'. Example: 0Q03Y0000033lviSAA and 0Q03Y0000033liSAA produce the same hash.
The issue seems to be caused by the REGEXP_REPLACE expression that should replace the '\v' values. However, it is also replacing any small 'v' letters which is not correct.
Same issue might be valid for n, t and r characters (haven't checked though).

Discussed in #96

To-Do: Change Multi Active Hashdiff calculation to properly identify NULL rows. Do that, by replacing the "NULLIF" around the "CONCAT()" by a "REPLACE()" to avoid NULL input to STRING_AGG() and LISTAGG().

It would be great to have datavault4dbt and turbovault4dbt available in the linux software center for example in debian or ubuntu. This way installation would be easy and much more users could go their first steps with datavault4dbt. I can imagine a preconfigured Postgres-Database with the TPCH example. Instead of excel libre office could be used for modelling.

One challenge is to package the files correctly.
The second challenge is to obey the licensing rules.
But in my opinion this could make datavault4dbt more popular.

I get duplicate business keys (NULL) for hubs, if I use a calculated column as source.

dbt version: 1.4.5
datavault4dbt version: 1.0.16
database: snowflake

Steps to reproduce the issue:
Config values are set to default.

Staging model definition:

{%- set yaml_metadata -%}
source_model: abc
derived_columns:
  SAMPLE_CALCULATED_COLUMN:
    value: "'TEST_' || ID"
    datatype: string
  OTHER_STRING_COLUMN: NAME
ldts: LOADTIMESTAMP
rsrc: '!123'
{%- endset -%}

Generated sql (shortened):

WITH

source_data AS (
    SELECT
     ...
      "ID",
      "NAME"
    FROM abc
  ),

ldts_rsrc_data AS (

  SELECT
    ...
    "ID",
    "NAME"
    FROM source_data

),

derived_columns AS (

  SELECT

  ...
  "NAME" AS "OTHER_STRING_COLUMN",
  'TEST_' || ID AS "SAMPLE_CALCULATED_COLUMN"
  FROM ldts_rsrc_data

),
unknown_values AS (

    SELECT

    ...
    '(unknown)' AS OTHER_STRING_COLUMN,
      NULL AS SAMPLE_CALCULATED_COLUMN

),

error_values AS (

    SELECT

    ...
    '(error)' AS OTHER_STRING_COLUMN,
    NULL AS SAMPLE_CALCULATED_COLUMN

),

ghost_records AS (
    SELECT * FROM unknown_values
    UNION ALL
    SELECT * FROM error_values
),
...

Expected values for the calculated string column would be '(unknown)' and '(error)'.

  Hi @mjahammel , 

Great to hear that the hotfix worked for you!
The problem that you have mentioned is the logical consequence of my fix, without thinking about where the zero keys are used additionally. Completely forgot the PIT macros 😄

Will create one more Hotfix for that ASAP!

Kind regards,
Tim

Originally posted by @tkirschke in #47 (reply in thread)

Hi,

I stumbled upon our hubs and links not using high-water-mark for their incremental loading.
For this issue I would only go into hubs since links probably share the same cause.

dbt version: 1.4.6
datavault4dbt version: 1.0.17
database: snowflake

We defined our own aliases in the dbt_project.yml

#Column Aliases
  datavault4dbt.ldts_alias: 'CDP_LOADDTS'
  datavault4dbt.rsrc_alias: 'CDP_RECORD_SOURCE'

Every stage defines these two attributes:

ldts: CDP_LOADDTS
rsrc: '[static source identifier]'

Our hub definition is very slim, as we define the matching columns (HK/BK) for the hubs in the source_models.
For multiple sources:

{{ config(materialized='incremental') }}
{%- set yaml_metadata -%}
hashkey: HASHKEY_SK
business_keys: BUSINESSKEY_BK
source_models:
    V_MODEL1: {}
    V_MODEL2: {}
    V_MODEL3: {}
{%- endset -%}

For single source hubs:

{{ config(materialized='incremental') }}
{%- set yaml_metadata -%}
hashkey: HASHKEY_SK
business_keys: BUSINESSKEY_BK
source_models: V_MODEL4
{%- endset -%}

Both definitions always process the entire history of the stage data to determine the delta. Are we missing something in the definition that is necessary to calculate the high-water-mark?

We would like to get the same result for delta calculation analogous to the sat definition and decrease our processing time.

{{ config(materialized='incremental') }}
{%- set yaml_metadata -%}
source_model: V_MODEL4
parent_hashkey: HASHKEY_SK
src_hashdiff:
  source_column: S_SAT_HASHDIFF
  alias: HASHDIFF
src_payload:
  - COL1
  - COL2
{%- endset -%}

Kind regards,
Lars

Hi,
I want to create a Link. The source has 3 Fields Key1, Email1, Email2.
The link should be created from Key1 / Email1 and Key1 / Email2. I created two BK and two Hub-HK from the emails
and two Link-HK containing either email1 or email2.
I get the error that the SQL doesn't find the "normal" hub-hk (without number). It seems that though I created two sources in the link-definition only one is created in the SQL (we had the same issue with hubs)

Regards

Discussed in #164

Problem statement

Encountered while testing in my project this problem.
We have a Satellite that is holding a hashkey lets say for example '123' and hashdiff 'abcd' with load date '2023-09-08 13:08:04.421'
We load new records into the Stage with load date '2023-09-08 14:54:00.818' (few hours later). But those records dont have any delta, holding multiple records (from different files) the same hashdiff for the same hashkey.
The current implementation inserts the same hashdiff again into the satellite, which is not the expected behaviour.

Expected behaviour

None of the records are inserted into the satellite, as they hold the same hashdiff as the latest hashdiff present in the satellite and there are no inbetween deltas in the Stage.

I see that you are using COALESCE(LEAD(TIMESTAMP_SUB({{ ldts }}, INTERVAL 1 MICROSECOND)) in PIT for Redshift. However TIMESTAMP_SUB and INTERVAL 1 MICROSECOND are not supported by Redshift.

Hi, I've started to get a grip on the code

I think this line

should be run_query(rsrc_static_query_source_count) ?

KR
Christoph

Hi, I am working on the sqlserver pre-release and in hub.sql if you have a static record source the select syntax is incorrect that gets generated in line 50 - 62
The subquery needs an alias. It only failed on the 2nd load
datavault4dbt\macros\tables\sqlserver\hub.sql

{%- set rsrc_static_query_source -%}
SELECT count(*) FROM (
{%- for rsrc_static in rsrc_statics -%}
SELECT t.{{ src_rsrc }},
'{{ rsrc_static }}' AS rsrc_static
FROM {{ this }} t
WHERE {{ src_rsrc }} like '{{ rsrc_static }}'
{%- if not loop.last %}
UNION ALL
{% endif -%}
{%- endfor -%}
) sub -- I added this alias to the subquery otherwise it fails
{% endset %}

In the generated code for control_snapshot_v0, the Is_daily flag is currently not correctly set to reflect the user's input for daily_snapshot_time - If this parameter is set to any timestamp other than 00:00:00.

Current macro:
CASE
WHEN EXTRACT(MINUTE FROM sdts) = 0 AND EXTRACT(SECOND FROM sdts) = 0 AND EXTRACT(HOUR FROM sdts) = 0 THEN TRUE
ELSE FALSE
END AS is_daily

Meaning, is_daily flag is only true, if daily snapshot definition is 00:00:00.

Proposal to change the macro code to parse user definition of daily snapshot:
CASE
WHEN
EXTRACT(HOUR FROM sdts) = EXTRACT(HOUR FROM TO_TIME('{{ daily_snapshot_time }}'))
AND EXTRACT(MINUTE FROM sdts) = EXTRACT(MINUTE FROM TO_TIME('{{ daily_snapshot_time }}'))
AND EXTRACT(SECOND FROM sdts) = EXTRACT(SECOND FROM TO_TIME('{{ daily_snapshot_time }}'))
THEN TRUE
ELSE FALSE
END AS is_daily

An error is thrown when trying to create a snapshot control model in BigQuery.

Details:

• The version of dbt being used:
  • dbt-bigquery 1.6.7
  • dbt-core 1.6.6
• The version of datavault4dbt being used.
  • 1.1.4
• Steps to reproduce the issue
  1. Set up a dbt project using BigQuery
  2. Create a model using the example configuration in the docs
  3. execute the dbt run command

The following was the configuration used to reproduce the issue:
{{ config(materialized='incremental') }}

{%- set yaml_metadata -%}
start_date: '2015-01-01'
daily_snapshot_time: '07:30:00'
{%- endset -%}

{%- set metadata_dict = fromyaml(yaml_metadata) -%}

{%- set start_date = metadata_dict['start_date'] -%}
{%- set daily_snapshot_time = metadata_dict['daily_snapshot_time'] -%}

{{ datavault4dbt.control_snap_v0(start_date=start_date,
daily_snapshot_time=daily_snapshot_time) }}

• Any error messages or dbt log files which can give more detail of the problem

The following error message is given by dbt:
Database Error in model xxxxx A valid date part name is required at [55:33]

The following error message is given BigQuery if you execute the dbt compiled SQL in the target/compiled directory:
A valid date part name is required at [44:33]

Potential solution

The following update to line 58 in file datavault4dbt/macros/tables/bigquery/control_snap_v0.sql appears to resolve this issue:

existing SQL to create the is_end_of_month column:

CASE 
    WHEN LAST_DAY(sdts, 'month') = DATE(sdts) THEN TRUE
    ELSE FALSE
END as is_end_of_month,

potential update

CASE 
    WHEN LAST_DAY(DATE(sdts), MONTH) = DATE(sdts) THEN TRUE
    ELSE FALSE
END as is_end_of_month,

Hi,
We are using link as our hashkey and we are having 'Valid_To' as our ma_attribute. However, there should be only one valid record but there are multiple. Below are the sample of my ma_sat v0 and v1.

ma_sat v0

{%- set yaml_metadata -%}
source_model: 'source_model'
parent_hashkey: 'hk_key_l'
src_hashdiff: 'hd_sales_ms'
src_ma_key: 'VALID_TO'
src_payload: 
    - field
{%- endset -%}

{%- set metadata_dict = fromyaml(yaml_metadata) -%}

{{
    datavault4dbt.ma_sat_v0(
        source_model=metadata_dict["source_model"],
        parent_hashkey=metadata_dict["parent_hashkey"],
        src_hashdiff=metadata_dict["src_hashdiff"],
        src_ma_key=metadata_dict["src_ma_key"],
        src_payload=metadata_dict["src_payload"],
    )
}}

ma_sat v1

{%- set yaml_metadata -%}
sat_v0: 'ma_sat_v0'
hashkey: 'hk_key_l'
hashdiff: 'hd_sales_ms' 
ma_attribute:
    - VALID_TO
ledts_alias: 'ledts'
add_is_current_flag: true
{%- endset -%}    

{%- set metadata_dict = fromyaml(yaml_metadata) -%}

{%- set sat_v0 = metadata_dict["sat_v0"] -%}
{%- set hashkey = metadata_dict["hashkey"] -%}
{%- set hashdiff = metadata_dict["hashdiff"] -%}
{%- set ledts_alias = metadata_dict["ledts_alias"] -%}
{%- set ma_attribute = metadata_dict["ma_attribute"] -%}
{%- set add_is_current_flag = metadata_dict["add_is_current_flag"] -%}

{{
    datavault4dbt.ma_sat_v1(
        sat_v0=sat_v0,
        hashkey=hashkey,
        hashdiff=hashdiff,
        ma_attribute=ma_attribute,
        ledts_alias=ledts_alias,
        add_is_current_flag=add_is_current_flag,
    )
}}

Regards,
ShennWooi

in the v1-views from ref_sat and sat the parameter-value of ledts_alieas is being overwritten by
set ledts_alias = var('datavault4dbt.ledts_alias', 'ledts')
instead of something like
set ledts_alias = datavault4dbt.replace_standard(ledts_alias, 'datavault4dbt.ledts_alias', 'ledts')

in ma_sat it is missing completly

Hi guys,
like to test your dbt package but i have trouble because all other packages in my current project have been upgraded and now compatibility to dbt-utils 0.9.2.
Any chance you are able to check if an upgrade of your dependency is possible.

Thanks in advance.

Because of the 'OR' after the other log_logic keys if statements, when there is no 'daily' key defined the snapshot v1 will throw an error.

Example model that fails:

Macro code:

Hi *,

1. I wonder if I can create a PIT from a SAT that keeps a Dependent Child key?
2. The Dimension Key is unclear to me. Is it a field coming from the stage model? Or is it creating a "surrogate key" that can be used in the fact/dimension tables afterwards?

Best regards
Klaus

I tried to recreate the code from the demo session. I get the following error when I run "dbt deps"

Server log summary

Compilation Error in model stg_order (models/staging/datavaultdemo/stg_order.sql)
  The name argument to ref() must be a string, got <class 'dbt.clients.jinja.create_undefined.<locals>.Undefined'>
  
  > in macro snowflake__stage (macros/staging/snowflake/stage.sql)
  > called by macro stage (macros/staging/stage.sql)
  > called by model stg_order (models/staging/datavaultdemo/stg_order.sql)
Compilation Error in model stg_order (models/staging/datavaultdemo/stg_order.sql)
  The name argument to ref() must be a string, got <class 'dbt.clients.jinja.create_undefined.<locals>.Undefined'>
  
  > in macro snowflake__stage (macros/staging/snowflake/stage.sql)
  > called by macro stage (macros/staging/stage.sql)
  > called by model stg_order (models/staging/datavaultdemo/stg_order.sql)

Server log Details

16:02:29  Set downloads directory='/tmp/dbt-downloads-4ifolly8'
16:02:29  Making package index registry request: GET https://hub.getdbt.com/api/v1/index.json
16:02:29  Response from registry index: GET https://hub.getdbt.com/api/v1/index.json 200
16:02:29  Making package registry request: GET https://hub.getdbt.com/api/v1/dbt-labs/dbt_utils.json
16:02:29  Response from registry: GET https://hub.getdbt.com/api/v1/dbt-labs/dbt_utils.json 200
16:02:29  Making package registry request: GET https://hub.getdbt.com/api/v1/ScalefreeCOM/datavault4dbt.json
16:02:29  Response from registry: GET https://hub.getdbt.com/api/v1/ScalefreeCOM/datavault4dbt.json 200
16:02:29  Installing dbt-labs/dbt_utils
16:02:35    Installed from version 0.9.2
16:02:35    Up to date!
16:02:35  Sending event: {'category': 'dbt', 'action': 'package', 'label': '797fd050-690d-4e6b-a0b8-e889d588b98f', 'property_': 'install', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f865004caf0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f865004cc10>]}
16:02:35  Installing ScalefreeCOM/datavault4dbt
16:02:46    Installed from version 1.0.5
16:02:46    Up to date!
16:02:46  Sending event: {'category': 'dbt', 'action': 'package', 'label': '797fd050-690d-4e6b-a0b8-e889d588b98f', 'property_': 'install', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f865004c940>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f865004cac0>]}

My files are

packages.yml

packages:
  - package: dbt-labs/dbt_utils
    version: 0.9.2
  - package:  ScalefreeCOM/datavault4dbt 
    version: 1.0.5

models/staging/datavaultdemo/sources.yml

version: 2

sources: 
  - name: TPC-H_SF1
    database: SNOWFLAKE_SAMPLE_DATA
    schema: TPCH_SF1
    tables:
      - name: customer
  - name: deltas
    database: DATAVAULT4DBT_DEMO
    schema: CORE_SOURCE_SCHEMA
    tables: 
      - name: orders_initial
      - name: orders_1993
      - name: orders_1994
      - name: orders_1995
      - name: orders_1996
      - name: orders_1997
      - name: orders_1998

models/staging/datavaultdemo/stg_order.yml

    {{ config(materialized='view', schema='CORE_SOURCE_SCHEMA') }}

    {%- set yaml_metadata -%}
    source_model: 
          'deltas': 'orders_initial'
    hashed_columns: 
        hk_h_orders:
            - o_orderkey
        hk_h_customers:
            - customer_name
        hk_l_orders_customers:
            - o_orderkey
            - customer_name
        hd_orders_n_s:
            is_hashdiff: true
            columns:
                - o_orderstatus
                - o_totalprice
                - o_orderdate
                - o_orderpriority
                - o_clerk
                - o_shippriority
                - o_comment
                - is_highest_priority
                - description
                - legacy_orderkey
    derived_columns:
        is_highest_priority:
            value: "CASE WHEN (o_orderpriority = '1 - URGENT') THEN true ELSE false END"
            datatype: "BOOLEAN"
            src_cols_required: 'o_orderpriority'
        description:
            value: '!Orders from TPC_H, reference to Customers'
            datatype: 'STRING'
    missing_columns:
        legacy_orderkey: 'STRING'
    prejoined_columns:
        customer_name:
            src_name: 'TPC-H_SF1'
            src_table: 'customer'
            bk: 'c_name'
            this_column_name: 'o_custkey'
            ref_column_name: 'c_custkey'
    ldts: 'edwLoadDate'
    rsrc: '!TPC_H_SF1.Orders'
    {%- endset -%}

    {%- set metadata_dict = fromyaml(yaml_metadata) -%}

    {%- set source_model = metadata_dict['source_model'] -%}
    {%- set ldts = metadata_dict['ldts'] -%}
    {%- set rsrc = metadata_dict['rsrc'] -%}
    {%- set hashed_columns = metadata_dict['hashed_columns'] -%}
    {%- set derived_columns = metadata_dict['derived_columns'] -%}
    {%- set prejoined_columns = metadata_dict['prejoined_columns'] -%}
    {%- set missing_columns = metadata_dict['missing_columns'] -%}

    {{ datavault4dbt.stage(source_model=source_model,
                        ldts=ldts,
                        rsrc=rsrc,
                        hashed_columns=hashed_columns,
                        derived_columns=derived_columns,
                        prejoined_columns=prejoined_columns,
                        missing_columns=missing_columns) }}

Did I make a mistake or is there an issue with dbt cloud? I tried restarting the IDE multiple times

Discussed in #49

To Do

• Change Snap v0 Code for Snowflake to set is_weekly to TRUE, when DAYOFWEEK equals 1 instead of 2
• Check all other adapters again and verify, that is_weekly is set correctly

When specifying a sequence in the stage macro call, an error is generated.
This is due to a missing ghost record (both unknown and error), which is not generated for the sequence column.

Discussed in #108

hd_lineitem_n_s:
    is_hashdiff: true
    columns:
        - col1
        - col2
        - col3
        - ....
        - col50
        - ...

    columns:
      - include: '*' 
        exclude:
          - businessKey
          - hashKey
          - changeHash
          - loaddate
          - recordsource
          - ...

It seems there's a bug, that for stage models, which contain a multi-active config, only the hash key attribute defined within the multi-active config as "main_hashkey_column" is calculated. All other hash key attributes would be ignored - in case of a stage that contains a link hash key and multiple hub hash keys.

In All Kinds of Satellites there is a HWM logic applied for incremental runs. This logic checks

WHERE ldts > (SELECT max(ldts) FROM {{ this }})

But for cases, where this table is truncated outside of dbt, SELECT max(ldts) would return NULL, and on some databases the comparison ldts > NULL throws an error.

To avoid this, we need to insert the beginning-of-all-times for cases when max(ldts) returns NULL, like this:

WHERE ldts > (SELECT COALESCE(max(ldts), {{ datavault4dbt.beginning_of_all_times() }}) FROM {{ this }})

Thanks to

• @dpolishchuk68
• @underwatersven

for pointing out this issue and proposing a solution!

Discussed in #26

The version of dbt being used: 1.1.1
The version of datavault4dbt being used: 1.2.0
Issue in file: datavault4dbt --> macros --> staging --> redshift --> stage.sql
issue in line: 231
issue: below code is used:
left join {{ source(vals['src_name']|string, vals['src_table']) }} as pj_{{loop.index}} on lcte.{{ vals['this_column_name'] }} = pj_{{loop.index}}.{{ vals['ref_column_name'] }}
However, redshift doesn't allow this kind of join. datavault4dbt.multikey should be used to do it properly.

Source table columns schema :

Defined derived_columns in stage model like this:

Compiled code of the stage uppercases the source columns, instead of using the camel case defined in the source col input of derived_columns:

Discussed in #80

Issue

To many commas when defining a multi active stage, that has more hashkeys than the main hashkey. See example SQL here:

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

Whats causing that issue?

In the "hash_columns" macro the comma is set outside of the "if , elif, endif" block, that identifies the main hashkey column and the hashdiff columns:

    {%- for col in columns -%}

        {%- if datavault4dbt.is_something(multi_active_key) -%}
            {% if columns[col] is mapping and columns[col].is_hashdiff -%}
                {{- datavault4dbt.hash(columns=columns[col]['columns'], 
                                alias=col, 
                                is_hashdiff=columns[col]['is_hashdiff'],
                                multi_active_key=multi_active_key,
                                main_hashkey_column=main_hashkey_column) -}}

            {%- elif columns[col] is not mapping and (col|upper) == (main_hashkey_column | upper) -%}
                {{- datavault4dbt.hash(columns=columns[col],
                                alias=col,
                                is_hashdiff=false) -}}  

            {%- endif -%}

            {{- ", \n" if not loop.last -}}

        {%- else -%}

What will fix this issue?

Moving that comma into the conditional logic, like this:

    {%- for col in columns -%}

        {%- if datavault4dbt.is_something(multi_active_key) -%}
            {% if columns[col] is mapping and columns[col].is_hashdiff -%}
                {{- datavault4dbt.hash(columns=columns[col]['columns'], 
                                alias=col, 
                                is_hashdiff=columns[col]['is_hashdiff'],
                                multi_active_key=multi_active_key,
                                main_hashkey_column=main_hashkey_column) -}}

            {{- ", \n" if not loop.last -}}
            
            {%- elif columns[col] is not mapping and (col|upper) == (main_hashkey_column | upper) -%}
                {{- datavault4dbt.hash(columns=columns[col],
                                alias=col,
                                is_hashdiff=false) -}}  

            {{- ", \n" if not loop.last -}}

            {%- endif -%}


        {%- else -%} 

Thanks to @saivigneshk for pointing this out!

Using dbt or coalesce we added a Nanosecond to rows in our import-row to achieve uniqueness.
This generates invalid timeframes in the v1-sats because there a Microsecond is subtracted.

Could this be customizable?
Version is 1.1.2

It would be nice to have the possibility to switch off the generation of ghost records as a whole or separately for unknown values and error values.

When the staging macro creates ghost records, Big Query returns:

Syntax error: Unexpected string literal

because the macro quotes the alias for the date datatype in the default__ghost_record_per_datatype macro in macros/supporting/ghost_record_per_datatype.sql

@tta-scalefree @tkirschke This leads to an error in BigQuery because BigQuery does not allow column names with quotes

Originally posted by @obause in #157 (comment)

The loading of a NH Link can be improved by skipping a full table scan while using the High Water Mark:

This could be broader than sqlserver, but I am testing on sqlserver branch.
I have changed the ldts global variable :
datavault4dbt.ldts_alias: 'loaddate'

The full initial load works, but when I run an incremental load the following code errors on a satellite: ( I removed table names)
delete from database.schema.sat0 table
where exists (
select null
from database.schema.sat0 table__dbt_tmp"
where
database.schema.sat0 table__dbt_tmp".hub_hashkey= database.schema.sat0 table.hub_hashkey
and
database.schema.sat0 table__dbt_tmp".ldts = database.schema.sat0 table.ldts
)
;

The field is correctly named to my alias loaddate in the satellite and thus it errors because ldts does not exist

We are using the ref_hub macro in an incremental model in our Snowflake environment to build our reference tables. We have observed, that the initial load for the reference hub works fine, however, during the incremental loads new ref hub keys are not being added. The SQL syntax looks ok, however it seems not to work with Snowflake.
This issue is only observed in the reference hub model. The ref satellite is being correctly updated.
However, as a consequence new entries are missing in the final reference table, as the new hub entries are missing.
Steps to reproduce:

1. Create new reference hub, example:

{{ config(materialized='incremental') }}

{%- set yaml_metadata -%}
source_models: staging_model
ref_keys:
- KEY1
- KEY2
{%- endset -%}

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ datavault4dbt.ref_hub(source_models=metadata_dict['source_models'],
ref_keys=metadata_dict['ref_keys']) }}

2. Build the model
3. Add new entries in the source with new key combination
4. Re-run model to trigger incremental update
5. Observe that new entries have not been added.

Hi,
I'm using the version 1.3.1. and having trouble to create a stage. It creates an error-message: ambiguous column name 'LDTS' (or RSCR.
I think it is because die attributes are already named like this in the source it is taking both of them...I hadn't had this issue in the version 1.1.0 (which I used before)

Regards
Jan Binge

Could be an option for the value in replacement_sdts to take the closest active snapshot existent in the table, so if a snapshot is not active the replacement_sdts will take the timestamp value from the closest active one.
Idea came from @marcfinger and its based on the technique shown here:
https://www.linkedin.com/pulse/lets-twine-again-lars-r%C3%B6nnb%C3%A4ck/

Hi there, how about adding Databend as it does not seem to be currently supported. The cloud data warehouse of databend is https://app.databend.com.

And I would like to make a pr for it 😄 .

Discussed in #47

IFNULL(LOWER(SHA2_BINARY(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(CONCAT(
        IFNULL((CONCAT('\"', REPLACE(REPLACE(REPLACE(TRIM(CAST("BRANCH_CODE" AS STRING)), '\\', '\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^')
        )), '\n', '')
        , '\t', '')
        , '\v', '')
        , '\r', '') AS STRING), '^^'))), 'TO_BINARY(0000000000000000000000000000000000000000000000000000000000000000)') AS DV_HASHKEY_HUB_BRANCH_TEST

To Do's

• Remove LOWER function in hash default values, in case something else than STRING is defined as hash_datatype
• Fix default unknown and default error value for SHA hash algorithms

On branch sqlserver:

If you have column names with spaces in them, for example "start time", this seems to get handled correctly in hubs, links and stage models. However, the satellites do not add quotation marks when it builds the select statements even though in the model definition these columns are enclosed with quotation marks ("")