dbt snapshot failure after first run

Help
1

The problem I’m having

I have a table [Site] that is a slowly changing dimension so I am creating a snapshot for this table. When I run “dbt snapshot” the first time the snapshot table is created. Any subsequent run results in the following error:

(‘23000’, “[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table ‘site_snapshot’ when IDENTITY_INSERT is set to OFF. (544) (SQLExecDirectW); [23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Database name ‘DirectoryDW’ ignored, referencing object in tempdb. (2701)”)

I am using dbt sql server and the [Site] table does have an auto-increment identity.

What I’ve already tried

I looked up the issue and only saw an unresolved post on this forum.

I tried setting IDENTITY_INSERT to on for the snapshot table but I’m still getting the same error.

Here is the snapshot code that runs:

{% snapshot site_snapshot %}

{{
    config(
      target_database='DirectoryDW',
      target_schema='dbo',
      unique_key='siteid',

      strategy='check',
      check_cols=['isactive', 'tier']
    )
}}

select [siteid]
      ,[sitename]
      ,[homeurl]
      ,[directoryurl]
      ,[sitetype]
      ,[fullsitename]
      ,[sitenamesuffix]
	,publisherid
      ,[isactive]
      ,[tier]
	  ,[createdate] 
from {{ source('mb', 'Site') }}

{% endsnapshot %}

Thanks,
Matt

1 Like
2

I am facing similar issue with sqlserver identity column. Any suggestions to try are welcome.

Thanks,
Sanjeev

3

Did you solve this? I run into the same issue.

4

I encountered the same problem with SQL Server, dbt-core 1.8.9, dbt-sql-server 1.8.4.

It succeeds the first time, then fails on the second run with the same IDENTITY_INSERT error

I suspect it has something to do with the primary key because when I used a unique_key that was not a primary key, it succeeded.

As a workaround, I used a pre_hook in the snapshot config

  pre_hook="IF OBJECT_ID (N'{{this}}', N'U') IS NOT NULL SET IDENTITY_INSERT {{this}} ON"