Question 309895 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – DBT dispatch method is not seen by the compiler

I’m running a code written for Snowflake SQL dialect on database with DuckDB(Postgres) dialect. I need to dispatch the functions that do not exist in the target one.
For example I have error regarding endswith function – it’s available in Snowflake, but in Postgres it’s ends_with. That’s how error looks like:

Catalog Error: Scalar Function with name endswith does not exist!
Did you mean "ends_with"?

So I wrote a dispatcher method and I stored it in macros/endswith.sql:

{% macro endswith (expr_1, expr_2) -%}
    {{ adapter.dispatch('endswith')(expr_1, expr_2) }}
{%- endmacro %}

{% macro default__endswith (expr_1, expr_2) -%}
    {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
{%- endmacro %}

{% macro snowflake__endswith (expr_1, expr_2) -%}
    {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
{%- endmacro %}

{% macro duckdb__endswith (expr_1, expr_2) -%}
    {{ return(ends_with(expr_1, expr_2)) }}
{%- endmacro %}

{% macro postgres__endswith (expr_1, expr_2) -%}
    {{ return(ends_with(expr_1, expr_2)) }}
{%- endmacro %}

I tried also:

{% macro endswith (expr_1, expr_2) -%}
    {{ adapter.dispatch('endswith')(expr_1, expr_2) }}
{%- endmacro %}

{% macro default__endswith (expr_1, expr_2) -%}
    dbt_utils.endswith(expr_1, expr_2)
{%- endmacro %}

{% macro snowflake__endswith (expr_1, expr_2) -%}
    dbt_utils.endswith(expr_1, expr_2)
{%- endmacro %}

{% macro duckdb__endswith (expr_1, expr_2) -%}
    ends_with(expr_1, expr_2)
{%- endmacro %}

{% macro postgres__endswith (expr_1, expr_2) -%}
    ends_with(expr_1, expr_2)
{%- endmacro %}

It throws no errors, but also acts as it’s not seen by the compiler. When I compile, I get normal endswith in the output and when I run I get the same error as I did.

I expected it might be that there is something wrong with macro visibility, but when I delete some macros from directory I have missing dependencies.

There are 3 sites with documentation regarding that:

  1. about dispatch config
  2. dispatch
  3. enter link description here

they provide this tool:

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['my_project', 'dbt_utils']

in theory it shouldn’t be necessary, as the default method should be one in my project folder, but I tried, and it didn’t change anything (maybe I did it wrong?).
To help it out I also tried what is visible in the dbt_utils source code:

adapter.dispatch('endswith', 'dbt_utils')

but it also didn’t change anything.
During trials, I reinstalled the adapter, updated the dbt also to experimental versions, manipulated with profile.
I’m still not sure if dbt knows what is the target dialect, but I assumed it’s being chosen by the profile, which in my case looks like this:

duck_db: target: dev outputs:
dev:
type: duckdb
path: ‘~/data/mock.db’
extensions:
– httpfs
– parquet

At this point I don’t have any other ideas on how to solve this issue. How should I use the adapter?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    OP:

    Update: I'm almost sure the problem was solved by installing postgres adapter along with duck one. The dispatcher was already built in dbt and not necessary to write as a separate macro.

    Previous: I don't know if it's correct solution as it led me to another error, but as for now the macro is seen by the compiler (which rises macro duplicated names error) - the problem was I didn't add endswith to the models.yml file. Also, when I had it modified incorrectly - without the arguments specified - there was no error, but compiler didn't see it.

    My current theory is that the new error (separate for every dispatcher option) is due to the fact that there is already some dispatcher of endswith built-in and the dbt compiler doesn't know it should be using that (I can't find it in project files nor external libraries). I'll edit this post when I'll know.


  2. 0

    dbt-duckdb developer here. I think the syntax for adapter.dispatch needs to be like this:

    {% macro endswith (expr_1, expr_2) -%}
    {{ return(adapter.dispatch('endswith')(expr_1, expr_2)) }}
{%- endmacro %}

    That is, the return statement is doing actual work here, I don’t think you can leave it out.

    Login or Signup to reply.
  3. 0

    By examining the audit_helper package example, it’s apparent that the absence of a return statement within their functions implies that the root cause may reside elsewhere.

    {% macro get_columns_in_relation_sql(relation) %}

{{ adapter.dispatch('get_columns_in_relation_sql', 'audit_helper')(relation) }}

{% endmacro %}

    What about running a single custom adapter dispatch like this:

    {% macro endswith(expr_1, expr_2, adapter='default') -%}
    {%- if adapter == 'default' -%}
        {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
    {%- elif adapter == 'snowflake' -%}
        {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
    {%- elif adapter == 'duckdb' -%}
        {{ return(ends_with(expr_1, expr_2)) }}
    {%- elif adapter == 'postgres' -%}
        {{ return(ends_with(expr_1, expr_2)) }}
    {%- else -%}
        {{ return('Unsupported adapter') }}
    {%- endif -%}
{%- endmacro %}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search