# ref() not working properly?

**URL:** <https://discourse.getdbt.com/t/ref-not-working-properly/9862>\
**Category:** Help\
**Tags:** dbt-cloud\
**Created:** [September 6, 2023, 2:43pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862 "2023-09-06T14:43:42Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![janoman](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@janoman](https://discourse.getdbt.com/u/janoman)\
**Post date:** [September 6, 2023, 2:43pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/1 "2023-09-06T14:43:42Z")

</div>

## 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?

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [September 6, 2023, 2:51pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/2 "2023-09-06T14:51:59Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1694011912200069?thread_ts=1694011443.539449&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![janoman](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@janoman](https://discourse.getdbt.com/u/janoman)\
**Post date:** [September 6, 2023, 3:41pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/3 "2023-09-06T15:41:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![janoman](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@janoman](https://discourse.getdbt.com/u/janoman)\
**Post date:** [September 6, 2023, 3:59pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/4 "2023-09-06T15:59:01Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [September 6, 2023, 4:09pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/5 "2023-09-06T16:09:56Z")

</div>

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](https://github.com/dbt-labs/dbt-utils#get_query_results_as_dict-source)

Note: `@Owen` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1694016589114359?thread_ts=1694011443.539449&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![janoman](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@janoman](https://discourse.getdbt.com/u/janoman)\
**Post date:** [September 8, 2023, 1:09pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/6 "2023-09-08T13:09:23Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Brandyn-sfetcu](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brandyn-sfetcu/32/2964_2.png) [@Brandyn-sfetcu](https://discourse.getdbt.com/u/Brandyn-sfetcu)\
**Post date:** [September 15, 2023, 1:09pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/7 "2023-09-15T13:09:05Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![janoman](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@janoman](https://discourse.getdbt.com/u/janoman)\
**Post date:** [September 18, 2023, 1:10pm UTC](https://discourse.getdbt.com/t/ref-not-working-properly/9862/8 "2023-09-18T13:10:59Z")

</div>

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