dbt_utils package union_relations not working with Trino

[mdesmet]

In our project we make extensive use of the dbt_utils library. the dbt_utils.union_relations doesn't work because it generates invalid sql

create table "dataproduct_levenslangleren"."zone_levenslangleren"."levenslang_leren__dbt_tmp"
    
  as (
    

        (
            select

                cast('"dataproduct_levenslangleren"."zone_levenslangleren"."levenslang_leren_ho"' as 
    string
) as _dbt_source_relation,
                
                    cast("laadoperatie" as character varying(256)) as "laadoperatie" ,
                    cast("record max laadoperatie" as character varying(256)) as "record max laadoperatie" ,

Two issues here:

• one is with dbt core: the varying(256) is hardcoded in dbt core and doesn't use the overriden TrinoColumn class methods. see [CT-78] [Bug] Adapters should be able to override string and numeric type of columns dbt-labs/dbt-core#4603 (PR submitted)
• in dbt utils there is following method
  https://github.com/dbt-labs/dbt-utils/blob/73a65562bf3d9b31a1eca0b27118056ad048d8fc/macros/cross_db_utils/datatypes.sql#L3-L6

This requires the adapter to provide specific implementation if the default ('string') is not supported:

{% macro trino__type_string() %}
    varchar
{% endmacro %}

Will post PR shortly.

[findinpath]

Note that you can use in the meantime the custom macro on your project until the dbt-trino PR gets merged and a new release will be made available.

[findinpath]

@mdesmet in which context is dbt utils method https://github.com/dbt-labs/dbt-utils/blob/73a65562bf3d9b31a1eca0b27118056ad048d8fc/macros/cross_db_utils/datatypes.sql#L3-L6 used?

[findinpath]

@mdesmet can you please post the (simplified) source code of your dbt model issuing the problem?

[findinpath]

@mdesmet https://github.com/dbt-labs/dbt-utils#union_relations-source i see here that there is a possibility to provide a column_override mapping.

[mdesmet]

Indeed it might be possible but still the default is not correct. That's why we need the standard overrides.

The issue is with the "generated column" the _dbt_source_relation.

{{ 
  dbt_utils.union_relations([
    ref('levenslang_leren_ho'),
    ref('levenslang_leren_so'),
    ref('levenslang_leren_dko'),
    ref('levenslang_leren_buso_ov3'),
    ref('levenslang_leren_bo'),
    ref('levenslang_leren_ex')
  ]) 
}}

[findinpath]

@mdesmet In which context does trino__type_string get used?

[mdesmet]

See following lines:

https://github.com/dbt-labs/dbt-utils/blob/73a65562bf3d9b31a1eca0b27118056ad048d8fc/macros/sql/union.sql#L69

https://github.com/dbt-labs/dbt-utils/blob/73a65562bf3d9b31a1eca0b27118056ad048d8fc/macros/cross_db_utils/datatypes.sql#L7-L9

[mdesmet]

Based on the discussion on slack.

Currently other databases support this specific cross db macros this through providing shim packages, see spark-utils
and tsql-utils. This shim packages provide not only adapter specific overrides but also macro overrides for commonly used packages within dbt (dbt-expectations, dbt-utils, ...) and also run the integrations tests of these packages.

There are two future directions that this can go (see the discussion):

• either cross db macros are included in the adapter packages.
• either cross db macros stay in dbt_utils package (current situation)

I think whatever decision is taken, I think it will still make sense to ensure these packages work well if we want the dbt-trino ecosystem to thrive.

Therefore I think it makes more sense to follow the shim path for now. I setup an initial repo, the tests can already be launched, however still some things to do to make it work. See https://github.com/innoverio/dbt-trino-utils

Do you guys agree on this path? I also think this dbt-trino-utils should be under this organization to ensure continued support.

[hovaesco]

I do think that having and maintain dbt-trino-utils shim package would be beneficial to dbt-trino adoption.

One question regarding testing, what idea you have to execute tests of dbt-expectations and dbt-utils?
Does all dbt-utils tests are covered by: https://github.com/innoverio/dbt-trino-utils/blob/main/Makefile ?
I assume similar approach for dbt-expectations, so include it as git module in dbt-trino-utils and execute them in container. Would you mind adding a note in dbt-trino-utils README.md about testing and how to perform them?

I will bring internally topic about moving dbt-trino-utils under starburstdata organization and come back to you.

Thanks @mdesmet, it is highly appreciated.

[hovaesco]

My understanding is that we would not need shim package if dbt-labs agree to move adapter related overrides/extension into dbt-core package, am I right?

[mdesmet]

Not true actually. We would still need the shim if we want to ensure that dbt_utils actually works with trino. dbt_utils might for example use specific SQL queries/macro's that need to be overriden that are not necessarily adapter related.