Dbt: ref'ing an ephemeral model inside of a statement does not work

[drewbanin]

The CTE is not injected into the query, so the user sees an error message that looks like:

relation "__dbt__cte__model_name" does not exist

[carlyfk]

I get this error when referencing a CTE within a call to dbt_utils.get_column_values

[clrcrl]

A common issue people run in to is trying to get column names from an ephemeral model. Currently the star macro only works on tables/views as it uses the get_columns_in_table function which queries the information schema.

If we can get ctes to work in statements, a star_model macro that works for any (including ephemeral) models would be possible, à la:

{% macro star_model(from, except=[]) -%}

  {%- call statement('empty_results', fetch_result=True) -%}
   select
   *
   from {{ref(from)}}
   where false

  {%- endcall -%}

  {% set results = load_result('empty_results') %}

  {% if results %} {# this handles the error on compilation #}
    {%- set cols = load_result('empty_results').table.column_names -%}
  {% else %}
    {%- set cols = [] -%}
  {% endif %}

  {%- set include_cols = [] %}

  {%- for col in cols -%}
    {%- if col.column not in except -%}
      {% set _ = include_cols.append(col.column) %}
    {%- endif %}
  {%- endfor %}

  {%- for col in include_cols %}
    "{{ col }}" {% if not loop.last %},
    {% endif %}
  {%- endfor -%}

{%- endmacro %}

^ note, this macro is untested, may require tweaking.

[drewbanin]

Thanks for the writeup @clrcrl!