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:
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?
OP:
Update: There was no dispatcher built-in and I don't know what was the reason I got that error, I could not reproduce it after reinstallation of duck and postgres adapters, but I found a definitive answer: to make adapter work, I had to wrap every instance of the problematic function being called in macros in the curly braces. Example:
{{endswith(column_alias, '__test')}}
so it's rendered in jinja during compilation.
Old prost: 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.