`dbt snapshot` not working with existing `timestamp` column

[cnlee1702]

trafficstars

dbt snapshot not working with SQL Server 2019 using a source table containing a timestamp column.

dbt snapshot

{% snapshot <snapshot_name> %}

{{
    config(
        unique_key='<unique_row_id>,
        strategy='timestamp',
        updated_at='<timestamp_column>',
        target_schema='<schema_name>',
    )

}}

select  * from {{ source('<schema>','<table>') }}

{% endsnapshot %}

Error message

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A table can only have one timestamp column. Because table '<table_name>' already has one, the column 'dbt_updated_at' cannot be added. (2738) (SQLMoreResults)")

SQL Server version

Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) - 15.0.2104.1 (X64) 
	Aug 16 2023 00:09:21 
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

[ericmuijsvanoord]

Might not be related to dbt?: https://stackoverflow.com/questions/12063850/multiple-timestamp-columns-in-sql-server-2000 Cast to datetime2 datatypes in the view might help solve the issues.

[cnlee1702]

@ericmuijsvanoord thank you for looking into this.

Unfortunately casting to datatime2 did not work in this case.

SQL Error [529] [S0002]: Explicit conversion from data type timestamp to datetime2 is not allowed.

Was the first time encountering this issue. Sounds like also not a common issue on your end?

Looks like a data source anomaly that I'll work around some other way. Thanks!

[ericmuijsvanoord]

timestamp is some internal database type that tracks changes on a table. Basically it is not "data" but more metadata on the table by the sql server engine. It is handy for tracking changes so you might be able to search and fine a way to convert it to datetime or integer for tracking changes.

https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format

[cnlee1702]

Overriding the column datatype to BIGINT with the union_relations dbt-utils macro will likely be a viable path with this source data.

Again -- I great appreciate your efforts looking into this @ericmuijsvanoord!

[axellpadilla]

This should be closed, this is a special datatype that isn't really a timestamp, as already explained by eric you could just use a check strategy over that column to generate the snapshot:

timestamp is some internal database type that tracks changes on a table. Basically it is not "data" but more metadata on the table by the sql server engine. It is handy for tracking changes so you might be able to search and fine a way to convert it to datetime or integer for tracking changes.

https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format