Use value from macro inside a model

Help
1

Hi, first message here. I’m new to dbt :slight_smile:

The problem I’m having

I’m trying to use a value that i get from a macro - specifically a max_date i calculate for a given table

The context of why I’m trying to do this

I want to build a generic macro that i will use in a lot of my incremental models. My target is to get a table MAX(DATE(created_at)) and use it in models

What I’ve already tried

I’ve wrote the macro and it seems to return some value. i’m not sure how to properly use it

Some example code or error messages

macro: get_table_max_date.sql

{% macro get_max_date(table_name, column_name) %}
    {% set query %}
        SELECT MAX(DATE({{ column_name }})) FROM {{ table_name }}
    {% endset %}
    {% set results = run_query(query) %}
    {{ return(results) }}
{% endmacro %}

model: my_table.sql

{{
    config(
        materialized='incremental',
        incremental_strategy='append')
}}
{% set max_date = get_max_date('my_table', 'created_at') %}

WITH results AS
    (
        SELECT *
        FROM {{ ref("some_other_table") }}
        {% if is_incremental() %}
            WHERE DATE(created_at) > {{ max_date }}
        {% endif %}
    ),

And i get the following error:
18:19:37 001003 (42000): SQL compilation error: 18:19:37 syntax error line 15 at position 37 unexpected '|'. 18:19:37 syntax error line 15 at position 39 unexpected 'column'. 18:19:37 syntax error line 16 at position 0 unexpected '|'. 18:19:37 syntax error line 17 at position 0 unexpected '|'. 18:19:37 syntax error line 20 at position 4 unexpected ')'.

Another thing i want to achieve after solving this issue:
Can i call the macro {% set max_date = get_max_date('my_table', 'created_at') %} with {{ this }} instead of providing the table name?
will {% set max_date = get_max_date(this, 'created_at') %} should work?

Thank you !

2

Hi @shayms8, I don’t know if it will solve the problem, but the first thing I would do in your case is to return a single value in your macro, because the run_query macro returns a table, not a value.

There are two ways you can do that

  • If you are able to use dbt_utils >= 1.0.0 you can use the get_single_value macro instead of run_query GitHub - dbt-labs/dbt-utils: Utility functions for dbt projects.

  • If you can’t youse dbt_utils >= 1.0.0` you can return a single value from your results acessing the first column and row of the results, as you would do in a dataframe

{% set results = run_query(query) %}
{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0][0] %}
{% else %}
{% set results_list = <a default value> %}
{% endif %}
2 Likes