dbt-sqlserver 1.7.2 is_incremental not compiling and overwrites the incremental load table #481
Unanswered
mark-manalo
asked this question in
Q&A
Replies: 8 comments 25 replies
-
|
Does it work as expected on the previous version? |
Beta Was this translation helpful? Give feedback.
4 replies
-
|
Hi Cody, thank you for your reply. But I don’t understand why it shouldn’t work in the previous and current version. I didn’t see any disclaimers on the current version that the incremental materialization isn’t implemented. Best regards, |
Beta Was this translation helpful? Give feedback.
-
|
So to the best of my understand most of the work was lifted from the previous version into the fabric adapter. curious if this is just isolated to the new version or not. There was a pretty big jump between them and everything should have been caught but certainly there has been some gaps already found. The test suite certainly should be brought up with the current dbt core set to see if it fails there. |
Beta Was this translation helpful? Give feedback.
-
|
One more question. What is your python version and os? Also if you run debug can you share the logs if you're comfortable with that. |
Beta Was this translation helpful? Give feedback.
-
|
Hi Cody, I tried it on both OS with python 3.11.7 - Windows 11 and MacOS Sonoma (Apple Silicon). This problem is occuring on both OS.
Here are the results of my dbt run. Here it even said it created the incremental model:
And here are my logs for dbt run on my windows maschine: This is what I think is problematic. Even tho the model was with incremental materiliziation. It is dopping the table first, which I dont understand.
And here finally it make an "SELECT * INTO" instead of a "INSERT INTO" command. And the incremental materialization is ignored fully and also the is_incremental() macro because it didn't even compiled: 
|
Beta Was this translation helpful? Give feedback.
-
|
I tried the following and couldn't recreate your issue. Removed existing log fileif a log file is in log/ then removed it. CREATE TABLEScreate table dbo.tst (
ID BIGINT,
DT DATETIME2
)
GO
INSERT INTO dbo.tst (ID, DT)
VALUES
(1, '2020-01-01'),
(2, '2023-01-01'),
(3, GETDATE());
GOCreate DBT model{{
config(
materialized='incremental'
)
}}
select
*
from dbo.tst
{% if is_incremental() %}
-- this filter will only be applied on an incremental run
-- (uses >= to include records whose timestamp occurred since the last run of this model)
where DT >= (select max(DT) from {{ this }})
{% endif %}Ran dbtexpected and got 3 rows. Added new rowINSERT INTO dbo.tst (ID, DT)
VALUES
(9, GETDATE());
GORan dbt againExpected result would be 5 rows. Max row from before + new additional row. Run full refreshExpected 4 rows. 3 original + 1 new. Can you do the above and send the log file here. feel free to scrub your pii as needed. |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Hi Cody, I dont know how you managed to com with this results. I tried your suggested solution. The first thing it did, is it dropped the table "tst" if it exists: And here as you can see, the is_incremental() macro didn't compile at all: And here the result of the "tst" table. Instead of 4 records, I have just 1 because it dropped the existing "tst" table: |
Beta Was this translation helpful? Give feedback.
3 replies
-
|
Can you create a new project and try that out? Delete the sample models, delete any logs, create the incremental model and run those steps again. Unless you send the log as well I'll have a hard time seeing step by step what going on. |
Beta Was this translation helpful? Give feedback.
-
|
Hi Cody, Will do and let you know. But at the moment could you please share you logs and compiled sql code here with your suggested solution. Could you also please share me your dbt debug logs? Thanks a lot! |
Beta Was this translation helpful? Give feedback.
-
|
Hi Cody, I made a seperate dbt project for this problem. But it doesn't matter because im producing the same errors/problems. From there I referenced the raw.dbo.tst to the incremental model on the dwh.dbo.tst. At the first run yes, it managed to insert the 3 records to the dwh tst table. Next then I truncate the raw.dbo.tst table and insert a new record with the newest datetime as you mentrioned on your solution and re run dbt run. But this time around it dropped the existing table from the dwh model and inserted the latest one. Here is my dbt debug logs:
Here is the results of the first insert: Here the results afte the 2nd dbt run: Here is my dbt logs: |
Beta Was this translation helpful? Give feedback.
-
|
@schlich any chance you could try the above in your project and see if it passes? |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
https://github.com/cody-scott/incremental_test Here is a fresh project that also runs as expected. you can run the sql files to create the db + add the data. I'm using docker + sql server 2022 but it also works in 2019. I added the log files for you to compare. Without seeing more of the structure (how the configs are setup, dbt_project, etc.) i'm at a bit of a loss to understand more here unfortunately. |
Beta Was this translation helpful? Give feedback.
6 replies
-
|
doesn't seem to be the issue for me. I tried (twice) and it was the same behaviour as i normally got. Two runs with it set to table, two with it set to view. https://github.com/cody-scott/incremental_test/tree/main/logs I also tried on postgres and it was a similar case, albeit with different syntax. |
Beta Was this translation helpful? Give feedback.
-
|
Hi Cody, can you please let me know in which dbt-core and dbt-sqlserver you run the example? Thanks a lot! |
Beta Was this translation helpful? Give feedback.
-
|
They will be the current versions of the sql server adapter and the dbt core it pulls. That was a fresh install for both. I can pull the actual values tomorrow. |
Beta Was this translation helpful? Give feedback.
-
|
Hi Cody, could you please add your requirements.txt file in the github repo? That would be very helpful. Thanks |
Beta Was this translation helpful? Give feedback.
-
|
https://github.com/dbt-msft/dbt-sqlserver/files/14566234/requirements.txt |
Beta Was this translation helpful? Give feedback.
-
|
full pip export but the install was |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Is this something that was fixed in 1.7.3 or 1.7.4? |
Beta Was this translation helpful? Give feedback.
10 replies
-
|
@schlich i tried it again on dbt-core 1.7.3 and dbt-sqlserver 1.7.3 but no luck. Same results. I can send you the repo. |
Beta Was this translation helpful? Give feedback.
-
|
@mark-manalo I tested incremental loads on my project today and was successful. Here is my .toml dependency settings: |
Beta Was this translation helpful? Give feedback.
-
|
Hi @prescode, I manage to test it again with your version of dbt-sqlserver 1.7.2 Here is my dbt debug output:
What I'am trying to achieved is: 00_raw - source table dbo.tst My steps:
Here is the result of the incremental model in 03_dwh db table dbo.dwh_tst:
3. Then I truncated the dbo.tst in the 00_raw db and inserted a new one:
4. Then I ran the second: dbt run --debug: Here is the result of the dbo.dwh_tst table in the 03_dwh DB after the second run of dbt run:
The table is overwritten and the 3 old records was deleted. I really dont know how you managed to make it worked? While I tried several times and nothing works. Thanks for you help, Mark |
Beta Was this translation helpful? Give feedback.
-
|
@mark-manalo it looks like it is materializing as a view, double check your config block, I've noticed that a mistake in the config (misspell or other) won't error out but will fall back to the default materialize. Or try changing it to materialize as a table and see if that works. |
Beta Was this translation helpful? Give feedback.
-
|
Hi @prescode, this is what I was mentioning the whole time. I attached my whole project as a zip file. and in there you can see on my config file inside my model dwh_tst.sql as incremental.
And in my dbt_project.yaml file is the default materialization is a table.
And in the MS SQL Server it also materialized as a Table and NOT a view. So it's fine. Just the incremental materialization doesnt work.
Please try to load my project and test it. That would be great... Thanks a lot, Mark |
Beta Was this translation helpful? Give feedback.
-
Something doesn't look right with your model. You have a |
Beta Was this translation helpful? Give feedback.
2 replies
-
|
Hi @prescode please refer to my answer above. Thanks, Mark |
Beta Was this translation helpful? Give feedback.
-
|
Hej @cody-scott & @prescode, are you able to try my project out? Thank you for your feedback. |
Beta Was this translation helpful? Give feedback.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hi Everyone,
I am having difficulties implementing incremental materialized model with the dbt-sqlserver adapter 1.7.2 version.
have the following code:
Target table exists.
But when I run the model with the newest data newest reference_dt it just overwrite the target table with the newest referenced_dt and all the other data is gone.
I look at the compiled code and the if is_incremental() is not evaluated:
I need some help please.
Beta Was this translation helpful? Give feedback.
All reactions