Dbt timestamp snapshots

[islet-mikaronkko]

Hi!

Documentation only mentions limitations in source table column constraints, but I got an error "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The column 'dbt_updated_at' was specified multiple times for 'insertions_source_data'" with these following steps.

Created new source table in Fabric: create table dbo.mock_orders(order_id int, status VARCHAR(100), created_at date, updated date)

Inserted data in to it: insert into mock_orders values (1,'delivered', '2020-01-01', '2020-01-04'), (1, 'shipped', '2020-01-02', '2020-01-04'), (3,'shipped', '2020-01-03', '2020-01-04'), (4,'processed', '2020-01-04', '2020-01-04')

next, created snapshot sql in dbt:

{% snapshot mock_orders %}

{{
    config(
        target_database = 'demowh',
        target_schema = 'sch_snapshot',
        unique_key = 'order_id',
        strategy = 'timestamp',
        updated_at = 'updated',

    )

}}

select * from mock_orders

{% endsnapshot %}

and ran dbt snapshot which completed successfully and I could see the new table created in schema sch_snapshot.

After that did updates to the source table: update mock_orders set status = 'delivered', updated = '2020-01-05' where order_id >1

and ran dbt snapshot again, but this time dbt raised the error ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The column 'dbt_updated_at' was specified multiple times for 'insertions_source_data'. (8156) (SQLMoreResults)")

[prdpsvs]

@islet-mikaronkko , I tried above steps and the snapshot execution was successful second time as well. Here are few details.

Note, this is second execution. could you please confirm which adapter version your dbt project is pinned to?

Attaching my dbt-log execution. dbt.log

There are few improvements I can make to snapshot execution, however, that should not impact this issue. Please let me know if there is new information I am missing.

[islet-mikaronkko]

Hi @prdpsvs! I have require-dbt-version: [">=1.0.0", "<2.0.0"] in my project-file.

I was running dbt-fabric 1.8.0 earlier but noticed that there was new version so upgraded to version 1.8.4. I did the same steps again, but sadly faced the same error. Log-file attached.

dbt.log

[prdpsvs]

@islet-mikaronkko, Not sure about the differences. pretty much the same as far as I know. If you are available, lets do a working session for 30 minutes. What's the best way to reach you to start a private conversation?

[islet-mikaronkko]

@prdpsvs Thanks for the offer, I however just got it solved. It was just a dbt config-error where I had to specify the schema for the select-clause in the snapshot-sql. Below the working version

{% snapshot mock_orders %}

{{
    config(
        target_database = 'demowh',
        target_schema = 'snapshots',
        unique_key = 'order_id',
        strategy = 'timestamp',
        updated_at = 'updated',

    )

}}

select * from dbo.mock_orders

{% endsnapshot %}