Issue while accessing the code from Ephemeral model in the run_query

Help
, , ,
1

The problem I’m having

I am working on a test query where I want to reference a script from a ephemeral model that I need to send to run_query and get the result show the values from result on the CLI.

The issue I am facing is when running the code it is not fetching the script from ephemeral rather it is just displaying ephemeral name when called.

The context of why I’m trying to do this

dummy_val_check.sql – the file I am running

{% set source_query = dummy_check(‘fact_data_ephemeral’) %}

{% set result = run_query(source_query) %}

– – here will be the code to log message on cli

dummy_check.sql – The macro written to fetch the script from ephemeral

{%- macro dummy_check(ephemeral_name) -%}
{% set ep_name = ref(ephemeral_name) %}
SELECT *
FROM {{ep_name}}
{%- endmacro -%}

fact_data_ephemeral.sql

This is ephemeral model where I have put the source code

Some example code or error messages

root@ab:/d/repo22/databases# dbt test --target dev --select dummy_val_check
05:25:13 Running with dbt=1.0.3
05:27:50
05:27:59 Concurrency: 2 threads
05:27:59
05:27:59 1 of 1 START test dummy_val_check… [RUN]
05:28:04 1 of 1 ERROR dummy_val_check… [ERROR in 5.20s]
05:28:05
05:28:05 Finished running 1 test in 14.60s.
05:28:05
05:28:05 Completed with 1 error and 0 warnings:
05:28:05
05:28:05 Database Error in test dummy_val_check2 (tests/qv/login/dummy_val_check.sql)
05:28:05 002003 (42S02): SQL compilation error:
05:28:05 Object ‘__dbt__CTE__FACT_DATA_EPHEMERAL’ does not exist or not authorized.
05:28:05
05:28:05 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

2

Having the same issue, can’t find a way to get the actual CTE from the ephemeral model.

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