Incremental model with no data to merge

Hi all,
My team is using Fivetran and DBT to load/transform the data into Snowflake and we’re working on using incremental models.

What works:

  • The initial load of the data works fine
  • if there IS data to merge into Snowflake works fine

What fails:

  • When there ISN’T any new data to merge then we get an error in Fivetran similar to below:
[0m20:49:18  Database Error in model develop_test_model (models/develop/misc/develop_test_model.sql)
e[0m20:49:18    001003 (42000): SQL compilation error:
e[0m20:49:18    syntax error line 19 at position 6 unexpected ')'.

Looking into the Snowflake query history it eventually errors on a query like this (please ignore line positions, i removed whitespaces):

create or replace temporary table WAREHOUSE.DATABASE.develop_test_model__dbt_tmp  as ( );

Is there a way to skip the incremental load process if there is no data to merge in?

Any help would be appreciated!

Can you post the code of the incremental model you’re having trouble with?

Your code needs to be valid both when is_incremental() is true or false - it sounds like you have your entire model logic inside an if block. You should return no rows when a model has no changes, but there should still be sql.

you were correct. i did have everything in an if block so when it was evaluating what to put into the temporary table, there was nothing to put in there. i added an else statement that just returns 0 rows and that fixed the issue. thank you for the assistance!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.