Hello Community! I'm running into a very strange issue where I get different results with dbt executed from the CLI, vs. dbt executed from a Prefect flow. 1. when I build an incremental dbt model - append strategy - in a database with no incremental model table, (think --full-refresh) from a Prefect flow using the prefect-dbt package ( I've tried both run() and trigger_dbt_cli_command() to execute dbt, with the same results ) the table from the incremental model is created, but none of the rows are inserted into it. dbt's output, (from Prefect) and Snowflake's activity log both show successful execution of SQL that should've inserted rows into the table; VS. 2. when I run dbt from the CLI using the exact same invocation with the same profile (generated by Prefect) I can successfully build the incremental dbt model table AND the rows are inserted it.The same SQL is generated and executed, but now the rows are inserted. The CLI invocation used for both: dbt build --select +incremental_model --profiles-dir /Users/me/src/data_transformation/iac/transformation --project-dir /Users/me/src/data_transformation/iac/transformation This seems like an issue with prefect-shell, but I'm not able to figure out what it is because no errors are generated, with the output indicating success in both scenarios for the building of each dbt model.

👋 Hi @Ian Thomas. What's the behavior if you specify the

--full-refresh

flag for approaches 1 and 2?

So I methodically peeled back the layers of the onion on this one. TLDR; dbt + Snowflake + Key Pair Authentication + Incremental Model = unexpected results. First, call dbt via DbtCoreOperation.run() Second, call dbt via trigger_dbt_cli_command() Third, call dbt via subprocess.run() Fourth, call dbt via dbtRunner.invoke() They all produced the same result. In the Snowflake Activity UI, the process of initially building the incremental model was split into two steps: creating the table, and inserting the rows into the table. Only the first step was being completed, not the second. dbt, however, was reporting success on that operation. Continuing to peel back the onion. Fifth, switch the authentication method to Snowflake from key pair to password and continue to use dbtRunner.invoke() to call dbt. This finally worked. So, I then left password as the authentication method and moved back to DbtCoreOperaion.run(), because it's the best fit with the Prefect flow this piece of logic executes within. Success. Sometimes, software engineering is hard.

Interesting, that's a new one for me. Nice work troubleshooting it though!