snapshot with unique key longer than 30 chars fails on duplicate

[dubravcik]

Hi, I have a snapshot with unique_key on hash and I am getting duplicate error on internal dbt_scd_id in merge phase.

I checked the dbt code and problem is, it converts the id_hash to varchar. SQL Server uses by default 30 (!?wt.f!?) chars if you state varchar without a length.

CONVERT(VARCHAR(32), HashBytes(''MD5'', 
        coalesce(CONVERT(>>>>varchar<<<<, [id_hash], 121), '''')  + ''|'' +     
        coalesce(CONVERT(>>>>varchar<<<<, CONVERT(DATETIME2, ''2021-02-22 19:33:59.020000''), 121), '''') 
    ), 2)

I would probably change it to varchar(max) as it is changed to varchar(32) at the end anyway.

[mikaelene]

Relates to #108. @dubravcik do you think #118 will solve it?

[dubravcik]

I don’t think so, it still uses varchar which defaults to 30 chars. I suggest varchar(max) , I don’t think that it will have a significant performance impact, but I can test it on my data.

[mikaelene]

Ahh. I’ll change to max. There was also an issue when I tried to cast it as a date before. I somehow missed that it uses the key to.

[mikaelene]

closed by #118