-
Notifications
You must be signed in to change notification settings - Fork 1.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
BigQuery - Unable to use the "set_sql_header" macro to declare variables for incremental models #2940
Comments
set_sql_header
macro to declare variables for incremental models
I knew someone would run into this one, someday. My first thought was to suggest a new config, The approach you've suggested is much better than that:
We could take this one step further: #2596 introduced Here's what I'd love to see here: {% set partition_metadata = adapter.get_partitions_metadata(target_relation) %}
{% set _dbt_max_partition = ... some agate/jinja magic to get the max value from partition_metadata ... %}
declare _dbt_max_partition {{ partition_by.data_type }} default '{{ _dbt_max_partition }}'; As far as improving the "dynamic"
Is that code you're interested in contributing @pcasteran? |
Yes @jtcohen6, I can have a look at it this weekend or maybe next week. |
Describe the bug
I used the
set_sql_header
macro to declare a BigQuery scripting variable and initialize it so it can be used in the context of my model. The model materialization is of typeincremental
withincremental_strategy="insert_overwrite"
and creates a partitioned table.The initial run (i.e. not incremental) works fine. However a subsequent run (incremental) fails due to syntax error in the executed query.
Steps To Reproduce
Here is the model definition:
Here is the executed query for the initial run:
query
And here is the executed query for the incremental run that fails:
query
We can see that the definition and initialization of my variable happens after the ones for the
_dbt_max_partition
variable. Unfortunately, this is done in two steps for_dbt_max_partition
: first a DECLARE statement then a SET statement.However, as per the BigQuery documentation on variable definition:
Expected behavior
I should be able to declare variables that are available in my model, whether it's an initial or incremental run.
A simple solution to achieve this, could be merging the DECLARE and SET statements used for the
_dbt_max_partition
variable into one single statement (DECLARE + DEFAULT) as this:I can submit a PR with that change if the proposed solution is accepted.
Screenshots and log output
Completed with 1 error and 0 warnings: Database Error in model my_incremental_model (models/perso_test/my_incremental_model.sql) Variable declarations are allowed only at the start of a block or script at [14:1]
System information
Which database are you using dbt with?
The output of
dbt --version
:The text was updated successfully, but these errors were encountered: