incremental model "You can only execute one statement at a time."

[johannes-becker-bt]

Hi, love the idea of a dbt sqlite adapter for demos and stuff. Am working on a demo and wanted to show incremental functionality. If you provide a unique key, then the macro sqlite_incremental_upsert tries to commit two comments (deletion and insert) leading to the "You can only execute one statement at a time." error. I'll try to share the demo as well (will have to first configure github properly) and will try to help once I got deeper into the sqlite adapter. But maybe you already have a quick idea how to solve this (or figure out what I'm doing wrong) Cheers Hannes

The model is basically

{{ config(unique_key='unique_key') }}
SELECT
    *
FROM {{ref('STAGE_TABLE')}}

[codeforkjeff]

Thanks for giving this adapter a try!

What versions of dbt-sqlite and dbt-core are you using? The major/minor versions need to match, so if you're using dbt-core 1.1.x, make sure you're using dbt-sqlite 1.1.2. if you're using dbt-core 1.2.x, there's a 1.2.0a1 pre-release version you can try.

If you still get the error after making sure the versions match, please let me know.

[johannes-becker-bt]

I realized that I had a mismatch after posting but then tried both of the mentioned combinations to no avail. Will try again tomorrow when I have a bit more time. The model works when created but doesn’t work for the second run so maybe it fell through the cracks?

[codeforkjeff]

I was able to reproduce this problem. I released v1.1.3 which hopefully fixes this. Please let me know if it works for your case.

Note that if your unique key consists of multiple fields, this will only work with {{ config(unique_key='concat(field1,field2)') }} and NOT when specifying unique_key as a list, e.g. {{ config(unique_key=[ 'field1', 'field2' ] }}

I opened #32 with some notes for other fixes/improvements that need to be made to the incremental materialization.

[johannes-becker-bt]

Works pretty well. One more question about the unique_key='concat(field1,field2)' I get a "no such function: concat" Weird thing is I used that function before, it's in dbt_utils But now with dbt-core 1.1.2 dbt_sqlite 1.1.3 dbt_utils 0.8.6 it does not find the macro concat anymore

For now I'll use unique_key='field1||field2' but concat would be better...

[johannes-becker-bt]

Also, in case you're interested, here's my work in progress (for now mostly in german, I think I'll translate it later) https://github.com/johannes-becker-bt/dbt_workshop/

[codeforkjeff]

Thank you for the feedback!

I know dbt-core 1.2.x has a concat macro but I don't know whether the macro from dbt_utils works for sqlite. I released 1.2.0a2 with the same fix in case you want to try that.

Since concat is a macro, you would need to do something like this, assuming the string to unique_key gets through jinja: {{ config(unique_key='{{ concat(field1,field2) }}' }}

[johannes-becker-bt]

Oh, I see where my mistake was. it worked with dbt-core 1.2.0 because (as you said) concat is a part of it. I thought I used dbt_utils concat - but I would have needed to write dbt_utils.concat for that... my dbt_utils got rusty. Thanks for all the help and have a nice weekend!