Title: Snowflake DBT: "Numeric value not recognized" error in join

Question

I'm encountering a pesky error in Snowflake regarding "numeric value not recognized" within a dbt model:

with cte as (
    select
        field1,
        field2,
        agg_function()
    from table1 t1
        inner join table2 t2
            using (f1, f2)
        inner join table3 t3
            using (f3, f4)
    group by 1, 2
)
select * from cte;

The issue arises in the join between table1 (t1) and table3 (t3). It complains about a numeric value in the t3 column, but I've verified that both fields are of type text. The error: Numeric value 'ec0511f8-1081-405c-b285-21f83851b990' is not recognized

Interestingly, when I remove the join, it runs fine. So, the issue seems to be specifically with t1 + t3 joins.

Has anyone encountered a similar issue before? Any insights or suggestions would be greatly appreciated!

What I tried:

Double-checked the data types of the columns involved in the join (f3 and f4 in table3) to ensure they are indeed of type text. Reviewed the data in table3 to identify any anomalies or unexpected values that might be causing the error. Attempted to isolate the join between table1 and table3 by removing other joins to see if the error persists. Verified the query syntax and join conditions to ensure correctness. Expectation:

I expected the query to execute successfully without encountering the "numeric value not recognized" error. Since both table1 and table3 fields involved in the join are of type text, I anticipated that the join operation should not trigger any type conversion issues related to numeric values.

Answer 1

(Moving the answer from comments to an answer for closure)

The issue happens here:

 from table1 t1
 inner join table2 t2
 using (f1, f2)
 inner join table3 t3
 using (f3, f4)

Op thought that

using was making f3 = f4 but it actually does t1.f3=t3.f3 and t2.f4=t3.f4.

From the docs:

USING( <column_list> )

A list of columns in common between the two tables being joined; these columns are used as the join columns. The columns must have the same name and meaning in each of the tables being joined.

https://docs.snowflake.com/en/sql-reference/constructs/join

Instead do a

 from table1 t1
 inner join table2 t2
 on t1.f1=t2.f2
 inner join table3 t3
 on t3.f3=t2.f4