Incremental model generates error in sql-server

[hermandr]

This is my model.

{{
    config(
        as_columnstore = false,
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        unique_key='Id'
    )
}}

WITH q AS (
    SELECT * FROM {{ source("assessment_models", "Question") }} WHERE Id < 2000
)
SELECT
    Id
    , CreationTime
    , CreatorUserId
    , LastModificationTime
    , LastModifierUserId
    , IsDeleted
    , DeleterUserId
    , DeletionTime
    , [Name]
    , [Description]
    , "Order"
    , [File]
    , [Type]
    , AssessmentDomainId
    , ComponentStr
    , VendorDocumentRequired
    , Code
    , TenantId
    , Weighting
    , RiskStatus
    , [Condition]
    , HasConditionalLogic
    , IsVisibleIfConditional
    , HiddenInSurveyForConditional
    , DisplayDocumentUpload
    , QuestionGroupId
    , Suborder
    , RootQuestionId
    , ParentQuestionId
    , IsMandatory
    , IdRef
    , coalesce(LastModificationTime, CreationTime) updated_at
    , getdate() sys_updated_at
FROM q


{% if is_incremental() %}

    -- this filter will only be applied on an incremental run
    WHERE
        coalesce(LastModificationTime, CreationTime) >= (
            SELECT max(coalesce(LastModificationTime, CreationTime))
            FROM {{ this }}
        )

{% endif %}

dbt run with full refresh works as expected.

Subsequent dbt run generates error:

14:09:04 | Concurrency: 1 threads (target='test')

14:09:04 | 

14:09:04 | 1 of 1 START incremental model test_dbt_cicd.Question_insert_update.. [RUN]

Unhandled error while executing model.sixclicks_dev_orig.Question_insert_update
('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'Question_insert_update' when IDENTITY_INSERT is set to OFF. (544) (SQLMoreResults)")

14:09:05 | 1 of 1 ERROR creating incremental model test_dbt_cicd.Question_insert_update [ERROR in 1.27s]

14:09:05 | 

14:09:05 | Finished running 1 incremental model in 3.32s.



Completed with 1 error and 0 warnings:



('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'Question_insert_update' when IDENTITY_INSERT is set to OFF. (544) (SQLMoreResults)")


Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Dbt version

installed version: 0.21.1
   latest version: 1.0.0

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:
  - postgres: 0.21.1
  - redshift: 0.21.1
  - bigquery: 0.21.1
  - snowflake: 0.21.1
  - sqlserver: 0.21.1

[Bikbai]

dbt does not support neither aut-generate nor Identity columns yet. Just remove ID column from select

[sdebruyn]

I don't think we can support incremental models with auto-generated ID columns. Feel free to comment with suggestions.

[morindo]

I think I found a solution. In the select clause of the sql model, if we cast the id as a type, the table that is created by dbt-sqlserver won't have an identity column, so dbt will be able to run the incremental update on the next run.

{{
    config(
        materialized="incremental",
        unique_key="property_id",
        on_schema_change="append_new_columns",
    )
}}


with
    source as (select * from {{ source("my_source_name", "my_table_name") }}),
    renamed as (
        select
            id as property_id, -- this will create an identity column because in the source table it's configure with identity column.
            cast(id as int) as property_id  -- this won't create an identity column even if in the source table it's configure with identity column.
            {{ adapter.quote("name") }},
            {{ adapter.quote("createdon") }},
            '{{ invocation_id }}' as dbt_batch_id

        from source
    )
select *
from renamed
{% if is_incremental() %}

    where createdon > (select max(createdon) from {{ this }})

{% endif %}

Does this make sense?

Also, why when creating a table, dbt-sqlserver creates the model table with an identity column if the source table has one? If I understand correctly, dbt does not support auto-increment or identity column, right? Should we disable identity column creation in dbt-sqlserver?