CODEWITHSUNDEEP
sqlgoogle-bigquerydbt
Zepee
Zepee

Reputation: 1890

DBT filtering for (None) when running on incremental model

I'm trying to configure a DBT model as materialized='incremental', which is failing as DBT seems to be wrapping my model with a check on (None) or (None) is null which causes the model to throw a SQL exception against the target (Bigquery). The (None) checks don't seem to get added for non-incremental models, or when running with --full-refresh which just re-create the table.

According to the docs, incremental models are supposed to be wrapped as follows:

merge into {{ destination_table }} DEST
using ({{ model_sql }}) SRC
...

However what I'm seeing is:

merge into {{ destination_table }} DEST
using ( select * from( {{ model_sql }} ) where (None) or (None) is null) SRC
...

It's not clear to me where the (None) check are coming from, what it's actually trying to achieve by wrapping the query, and what (if any) model config would need to be set to correct this.

My model's config is set as {{ config(materialized='incremental', alias='some_name') }}, and I've tried also setting unique_key just in case with no luck.

I'm running the model with dbt run --profiles-dir dbt_profiles --models ${MODEL} --target development, and can confirm the compiled model is fine and the (None) checks get added for the model run.

I'm running dbt 0.11.1 (old repo version).

Any help would be most appreciated!

Upvotes: 0

Views: 1727

Answers (2)

Zepee
Zepee

Reputation: 1890

Managed to resolve this by looking into the DBT codebase on github for my target version - incremental macro 0.11

Seems like in 0.11 DBT expects a sql_where config flag to be set, which is used to select which records you want to use for the incremental load (pre-cursor to is_incremental() macro).

In my case, as I just want to load all rows in each incremental run and tag with the load timestamp, Setting sql_where='TRUE' generates valid sequel and doesn't filter my results (ie. WHERE TRUE OR TRUE IS NULL)

Upvotes: 1

Anders Swanson
Anders Swanson

Reputation: 3961

have you had an incremental model configured beforehand with 0.11.1? I'm pretty sure you need to use {{ this }} but maybe that didn't exist in version 0.11.1. docs on this

Upvotes: 0

Related Questions