ref() not working properly?

Help
1

The problem I’m having

Hello first time poster and dbt first-time-user. I have connected dbt-cloud to my Starburst Enterprise and want to perform a data transformation. The problem I am encountering is that he seems to misread the table I am accessing from ? For some reason he is trying to access not from my created model but from a table where he attaches the string I put in my ref() command.

So for illustration a very simple example my first SQL model :
“first.sql” contains following command

SELECT ac.A,vac.B,vac.C
from schema1.table1 ac
inner join schema.table2 vac

and then in second.sql I want to access with

SELECT
{% for value in B %}
do something
{% endfor %}
from {{ ref(‘first’) }}

leads to ERROR table ‘schema1.table1.first’ does not exist. I dont understand what is my mistake?

What I’ve already tried

I tried running all in one file but then I get
Compilation Error in sql_operation inline_query (from remote system.sql) ‘vac’ is undefined. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with “dbt deps”.

I am not sure what kind of packages I need to install since I assume the jinja logic is already integrated?

2

Upon running, model first will contain columns A, B, and C in your database.
The model script you have for second.sql contains a syntax error because vac is not a variable nor a known jinja iteratable.
What is your goal with second.sql?

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.

3

Sorry that was my mistake I have ofc no vac. in my second.sql but rather only if I try to run it in one sql file. I corrected my statement.

4

I am trying to use a loop to generate columns with their value attached to its name:

sum(CASE WHEN B = value THEN C ELSE NULL END) AS "B_value"
5

jinja templating is resolved at compile time–not at runtime. If you are confident that the values in vac.B will not increase over time, you can use something like dbt_utils.get_query_results_as_dict to get the values at compile time and render them into your model query. https://github.com/dbt-labs/dbt-utils#get_query_results_as_dict-source

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.

6

So I tried to now only

SELECT *

FROM {{ ref(‘first’) }}

LIMIT 100;

and he is still giving me the same error
ERROR table ‘schema1.table1.first’ does not exist.

specifically:

Database Error in sql_operation inline_query (from remote system.sql) TrinoUserError(type=USER_ERROR, name=TABLE_NOT_FOUND, message=“line 2:6: Table ‘schema1.table1.first’ does not exist”, query_id=20230908_130653_00892_hdj5v)

Does someone know where the problem is? Do I have to specify something special in the schema.yml file for it to work?

7

Hey Janoman -

I’m not sure if I’m following correctly. Could you please answer the following?

  1. is first.sql located inside of your DIM Model?
  2. is second.sql your staging

If so you’ll need to rename your name and your files accordingly. Once clarified, I can take another look and see if we can figure out what’s going on. Thanks!

8

Hello thank you, the problem has been resolved! It turned out the connector I used for postgreSQL did not support creating views and therefore the ‘dbt run’ failed

1 Like