CODEWITHSUNDEEP
macrosdbt
Seba
Seba

Reputation: 89

usage of macros in dbt_project config-version:2

Our dbt_project.yml file, config-version: 1 has two variables across different incremental models, for which one we use a macro called today()

vars:
  start_date: '{{ today(offset_days=-1) }}'
  end_date: '2999-12-31'

When testing the migration to dbt 0.17.2, config-version:2, we face the following issue

Running with dbt=0.17.2
Encountered an error:
Compilation Error
  Could not render {{ today(offset_days=-1) }}: 'today' is undefined

The macro we built it end of last year. I believe dbt changed the way macro variables are referenced, however not sure how to tackle this.

-- returns current hour in YYYY-MM-DD-HH format,
-- optionally offset by N days (-N or +N) or M hours (-M or +M)
{%- macro current_hour(offset_days=0, offset_hours=0) -%}
  {%- set now = modules.datetime.datetime.now(modules.pytz.utc) -%}
  {%- set dt = now + modules.datetime.timedelta(days=offset_days, hours=offset_hours) -%}
  {{- dt.strftime("%Y-%m-%d-%H") -}}
{%- endmacro -%}

-- returns current day in YYYY-MM-DD format,
-- optionally offset by N days (-N or +N) or M hours (-M or +M)
{%- macro today(offset_days=0, offset_hours=0) -%}
  {{- current_hour(offset_days, offset_hours)[0:10] -}}
{%- endmacro -%}

-- accepts a timestamp string and returns a timestamo string
-- formatted like 'YYYY-MM-DD HH24:MI:SS.US', e.g. '2019-11-02 06:11:42.690000'
{%- macro dt_to_utc(ts_string) -%}
  TO_CHAR({{ ts_string }}::TIMESTAMPTZ, 'YYYY-MM-DD HH24:MI:SS.US')
{%- endmacro -%}

Upvotes: 4

Views: 8992

Answers (2)

Claire Carroll
Claire Carroll

Reputation: 759

I tried this out locally and got it to work. In addition to the things to check above, have you also moved vars to be at the same level as models in your dbt_project.yml file (docs)?

models:
  ...

vars:
  start_date: '{{ today(offset_days=-1) }}'
  end_date: '2999-12-31'

I'm actually surprised this works! Variables are best for hard-coded objects rather than dynamic objects.

I'm curious — why not just call the macro directly instead of using the variable?

Current:

where created_at >= {{ var('start_date') }}

Proposed:

where created_at >= {{ today(offset_days=-1) }}

That being said, my spidey-senses are tingling a little here — this code makes your dbt project non-idempotent — if your production run of dbt stopped for a few days straight, this filter would result in missing data.

Typically we avoid a pattern like this. Instead, we use our existing versions of models to build any cutoff dates — you can see more examples of it here.

Upvotes: 3

sgdata
sgdata

Reputation: 2763

Hmm, just some basics that come to mind first because I hadn't made all these changes when I moved to 0.17.2 and had some similar issue:

  1. Are you listing your macro path in the dbt_project.yml?

    Ex. macro-paths: ["macros"]

  2. I assume you added config-version: 2 to your dbt_project.yml?

  3. Try double quotes? "{{ today(offset_days=-1) }}"

Optional: Did you build out a macro "schema.yml"

Upvotes: 0

Related Questions