DBT Snapshot filling in dbt_valid_to for most recent record

[dhodge250]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

After running the dbt snapshot command and building a snapshot table with the invalidate_hard_deletes parameter set to True, various records are having their dbt_valid_to dates populated with the latest dbt_updated_at value, even though these records are still considered valid and have not been removed from their source (staging) tables. We are using the following code to build each snapshot:

{% snapshot tmp_device_ad %}

{{
    config(
           target_schema = 'dbt',
           strategy = 'timestamp',
           unique_key = 'snap_id',
           updated_at = 'update_dt',
           invalidate_hard_deletes = True
          )
}}

select ad.*

  from {{ ref('stg_device_ad') }} ad

{% endsnapshot %}

The number of records this is affecting is inconsistent between snapshots (sometimes it's a few dozen, or a few hundred from tables up to 30k), but it appears that it is affecting the same records on each run.

Expected Behavior

When building a dbt snapshot table and setting the invalidate_hard_deletes parameter to True, I expect dbt to only fill in the dbt_valid_to value ONLY if a record no longer exists in the source table OR if a record has changed in the source table and a new record is created in the snapshot table and the previous record in the snapshot table is marked as invalid. An active record in the snapshot table SHOULD NOT be marked as having a dbt_valid_to date, instead it should have a value of null.

Steps To Reproduce

1. Environment: Windows 10 Enterprise 21H2 19044.2251
2. Config:

{% snapshot tmp_device_ad %}

{{
    config(
           target_schema = 'dbt',
           strategy = 'timestamp',
           unique_key = 'snap_id',
           updated_at = 'update_dt',
           invalidate_hard_deletes = True
          )
}}

select ad.*

  from {{ ref('stg_device_ad') }} ad

{% endsnapshot %}

3. Run: dbt snapshot
4. No errors are generated

Relevant log output using --debug flag enabled

No response

Environment

- OS: Windows 10 Enterprise 21H2 19044.2251
- Python: 3.9.11
- dbt: 1.3.0

What Oracle database version are you using dbt with?

19c

Additional Context

This issue looks to be similar, if not identical, to #2390 from dbt-core a few years ago that was resolved in v0.17.0. I've created a fork to play around with this and see if the two issues are related.

[aosingh]

Thanks @dhodge250 for the detailed report. I am trying to reproduce this.

[aosingh]

I think the issue could be related to duplicate value of dbt_scd_id. dbt snapshot uses MERGE INTO statement to to update the column dbt_valid_to

MERGE INTO target t 
USING staging s 
ON (s.dbt_scd_id = d.dbt_scd_id)

dbt-oracle uses ORA_HASH to compute dbt_scd_id. It could be that the column dbt_scd_id has the same value for 2 different change types (updates and inserts)

I checked dbt Labs's implementation for PostgresSQL and it seems that they use MD5 hash to compute dbt_scd_id. Although, MD5 still has some chances of a collision.

Let me test with the STANDARD_HASH function Oracle DB provides. it supports MD5 and also multiple stronger hashing alternatives like SHA128, SHA256, etc.

I will share a macro here which you can include in your dbt project and that should override the default implementation of hashing method included in dbt-oracle.

[aosingh]

@dhodge250

Could you include the following macro in your dbt project and then test dbt snapshot ? You can create a file macros/hash.sql in your dbt project and include the macro definition in there.

{% macro oracle__snapshot_hash_arguments(args) -%}
    STANDARD_HASH({%- for arg in args -%}
        coalesce(cast({{ arg }} as varchar(4000) ), '')
        {% if not loop.last %} || '|' || {% endif %}
    {%- endfor -%}, 'SHA256')
{%- endmacro %}

As we are changing the hashing method for dbt_scd_id, it will be better to start fresh else there will be a datatype mismatch error when building the staging table.

Let me know if you have any questions

[dhodge250]

@aosingh thank you for the update and quick response!

I will add the macro to our project and start testing and let you know what I see for results.

[dhodge250]

@aosingh

After adding the hash.sql macro you've provided me with to the macros folder in our dbt project, I'm getting the ORA-01790: expression must have same datatype as corresponding expression error during the Insertions selection. I've added a selection of the dbt log file below.

For additional context, we are utilizing a separate MD5 hashing macro to generate the unique_id's in the source tables used for each snapshot.

{%- macro surrogate_key(field_list) -%}

{#-- Combine strings from list into single string, create hash column--#}
{%- set key_as_string = field_list | join("|| '-' ||") -%}
{{ "rawtohex(standard_hash(" ~ key_as_string ~ ", 'MD5'))" }}

{%- endmacro -%}

To my knowledge, these ID's should not be responsible for the ORA-01790 error, since these really are only used in the generation of the dbt_scd_id. We use this macro to create a standard-formatted unique_id prior to creating each snapshot table.

dbt.log

[aosingh]

@dhodge250

ORA-01790 is because of mismatch in datatype of dbt_scd_id

By default (old method) dbt_scd_id's data type is NUMBER and in the new macro dbt_scd_id's data type is RAW(32 BYTE)

This is due to the difference in between ORA_HASH and STANDARD_HASH functions. However, I highly recommend using STANDARD_HASH because of the strong hash algorithms it supports and avoiding hash collision.

There are 2 options going forward

Option 1:

Drop the table TMP_DEVICE_AD and start fresh. dbt snapshot will create it again.

Option 2:

Second option would be to run an UPDATE on the column dbt_scd_id in TMP_DEVICE_AD using the STANDARD_HASH function as shown below

STANDARD_HASH(coalesce(cast(snap_id as varchar(4000) ), '') || 
       '|' || coalesce(cast(update_dt as varchar(4000) ), ''), 'SHA256')

[dhodge250]

@aosingh

I've opted for your suggestion with Option 2 to save the data from TMP_DEVICE_AD and have updated the data type for dbt_scd_id to RAW(32 BYTE). I was able to successfully run the TMP_DEVICE_AD snapshot using the new hash.sql macro, and have confirmed that the issue we are seeing with the dbt_valid_to values is still present (see image below for example). I've done a compare between the source STG_DEVICE_AD and snapshot tables and found 188 records from the source table (out of 9,831) that are still getting a dbt_valid_to value.

[aosingh]

@dhodge250

Did these records exist prior to running the dbt snapshot command for which you originally reported the issue ? If yes, the dbt_valid_to will not be automatically unset by the snapshot command.

[dhodge250]

@aosingh

No, these incorrect records did not exist prior to running the dbt snapshot command. After each snapshot table is built we run through a script to clean up the incorrect dbt_valid_to values and reset the valid records back to null. I can confirm that after this script is run, if I were to run the dbt snapshot command again the same records that were just cleaned up would then have their dbt_valid_to values populated again incorrectly.

[aosingh]

@dhodge250

Thank you for confirming. I think at this point, it would be good to check what is in the staging table. We update the snapshot table using the staging table. I have added the SQL to create the staging table below. It would be good to check how many entries do we have for the above record in the staging table.

create_staging_table.txt

[aosingh]

@dhodge250
Just wanted to confirm that by staging table, I meant global temporary table which dbt snapshot creates first.

The attached SQL in the above comment looks like this:

CREATE GLOBAL TEMPORARY TABLE o$pt_tmp_device_ad152327
 ON COMMIT PRESERVE ROWS
  AS
    WITH snapshot_query AS (

SELECT ad.*

  FROM dbt.stg_device_ad ad
.....

Let me know if you have any questions

[dhodge250]

@aosingh

I'll try creating the staging table for you in a bit using the SQL you've provided me with. I'll need to remove some of the PII from the table before I submit, but it will still include the required snap_id and update_dt columns to generate the dbt_scd_id value.

EDIT:
As requested, I've attached the global temporary table created by the dbt snapshot:
O_PT_TMP_DEVICE_AD074341_DATA_TABLE.csv

[aosingh]

Thank you @dhodge250 for sharing the staging table records. This was really helpful. As I had suspected, same record is getting picked up for insert and update.

DBT_CHANGE_TYPE	UPDATE_DT	SNAP_ID	DBT_VALID_FROM	DBT_VALID_TO
insert	14-Nov-2022	0124CA8A277B605072F81615D49B47AC	14-NOV-22 11.26.19.000000000 PM
update	14-Nov-2022	0124CA8A277B605072F81615D49B47AC	14-NOV-22 11.26.19.000000000 PM	14-NOV-22 11.26.19.000000000 PM

These 2 rows should not show up in staging table because there were no updates and the record is still valid i.e. DBT_VALID_FROM >= UPDATE_DT.if you look at the above data this condition is True. These should not get picked up.

Rows are picked up for staging if DBT_VALID_FROM < UPDATE_DT.

I am trying to understand how did these get picked up. Also, UPDATE_DT stores truncated date (without time) whereas DBT_VALID_FROM stores the time part as well. After dbt snapshot, they should be of the same format.

• What is the datatype for UPDATE_DT
• Does UPDATE_DT not store the time part ?
• You mentioned there is a script which updates DBT_VALID_TO. Does the script update any other columns ?