How to declare and init variable in a dbt model in `.sql` file with big query adaptor?

Question

I would like to declare and init a variable in a dbt model customer.sql file. I used the keyword DECLARE to declare a variable like the BigQuery documentation suggests but I got a Syntax error on DECLARE keyword.

Code:

DECLARE myDate VARCHAR DEFAULT '2021-01-01';

with order_bis as (

    select
        order_id

    from
        order
    where
        customer_date > myDate

)

select * from order_bis

Error: Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword DECLARE ...

Lemming

Answer 1

It seems that using SQL variables does not work "yet" with dbt. You can use Jinja variable if you want to use static values in multiple places, so that you can rely on Jinja logic.

{% set myVar = '2017-01-01' %}

...

where
        customer_date > {{myVar}}

...

Lemming

This example is broken. {% set myVar = '2017-01-01' %} select {{ myVar }} yields 2015. The query may run, but it's not doing what you want – Hirohito

what would work in this case is customer_date > "{{ myVar }}" – Fishnet

Seems to be the case in 2024, using Jinja instead. – Violaviolable

Answer 2

You can set them in the SQL header, e.g. as follows. Note that you can't safely use ref() or source() in the header.

-- Supply a SQL header:
{% call set_sql_header(config) %}
DECLARE myDate VARCHAR DEFAULT '2021-01-01';
{% endcall %}

with order_bis as (

    select
        order_id

    from
        order
    where
        customer_date > myDate

)

select * from order_bis

Dosh

Answer 3

Using a sql_header statement to inject variable definitions ahead of the dbt CTAS/INSERT main query is good, as El Yobo answered.

Here is how I do it in a jinja expression, which can use {{ ref('order') }} if it is also used in the query body. Otherwise, you need to add a comment ref to 'force dependencies'.

{% if target.name != 'prod' %}
    {% set and_filter_nonprod = 'and client_date >= current_date - interval 5 day' %}
{% endif %}

select * from order_bis
where true
{{ and_filter_nonprod }}
;

You can test these kind of things with an inline snippet:

dbt compile --inline "{% set date_minus_5 = dbt.dateadd('day', -5, from_date_or_timestamp='current_date') %} select {{ date_minus_five }}"

Here I used the cross-database macro dateadd. The jinja expression is setting a variable which can be accessed later in the file.

Kerbstone