Can't delete with pre_hook

Help
1

Hello,

In an incremental model, I want to run a delete statement if is_incremental().
But the delete prehook is completely ignored, I don’t understand why.

The code of my model (called a_test.sql):
{% if is_incremental() %}
{% set pre_hook = “delete from {{ this }}” %}
{% else %}
{# At the first run, nothing to do #}
{% set pre_hook = “” %}
{% endif %}

{{ log (“prehook=” ~pre_hook, info=true)}}

{{
config(
materialized=“incremental”,
pre_hook=pre_hook
)
}}

{{ log (“prehook2=” ~pre_hook, info=true)}}

select 1 as my_col

The log that is generated:
$ dbt run --model a_test
?[0m14:40:54 Running with dbt=1.4.6
?[0m14:40:56 Found 823 models, 69 tests, 0 snapshots, 0 analyses, 560 macros, 1 operation, 0 seed files, 129 sources, 0 exposures, 0 metrics
?[0m14:40:56
?[0m14:41:12 Concurrency: 1 threads (target=‘dev’)
?[0m14:41:12
?[0m14:41:12 1 of 1 START sql incremental model GCO.a_test … [RUN]
?[0m14:41:12 prehook=delete from {{ this }}
?[0m14:41:12 prehook2=delete from {{ this }}
?[0m14:41:14 1 of 1 OK created sql incremental model GCO.a_test … [?[32mSUCCESS 1?[0m in 1.94s]
?[0m14:41:14
?[0m14:41:14 Running 1 on-run-end hook
?[0m14:41:14 1 of 1 START hook: events_to_datavault.on-run-end.0 … [RUN]
?[0m14:41:14 1 of 1 OK hook: events_to_datavault.on-run-end.0 … [?[32mOK?[0m in 0.00s]
?[0m14:41:14
?[0m14:41:14
?[0m14:41:14 Finished running 1 incremental model, 1 hook in 0 hours 0 minutes and 17.98 seconds (17.98s).
?[0m14:41:14
?[0m14:41:14 ?[32mCompleted successfully?[0m
?[0m14:41:14
?[0m14:41:14 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

=> I can see that the prehook is correctly populated, but when I look in the Snowflake console, I can’t find the delete statement. And no rows are never deleted…
I simplified the code as much as possible just to have something short and easy to understand.

Anyone to help please?
Thanks

2

Anyone could help me on this topic please?

3

I believe config happens before anything else in the model - your prehook cannot be set in jinja outside of the config. Rework your prehook into a sql that can discern your criteria for the delete or call a sproc that accomplishes the same thing from the config and remove that from the jinja.

Hope that helps some…

4

OK, thanks. I want to ensure the table exists before trying to delete from it so I’ll have to find a workaround…

5

For anyone stumbling across this topic, I just figured out that if you simply use dbt build this problem is resolved.

6 
{{
config(
materialized=“incremental”,
pre_hook= '{{ "delete from "~this if is_incremental() else "" }}'
)
}}

Try this approach