get_columns_in_relation macro but using CTE instead of a Relation object

Help
1

Hi everyone!

The problem I’m having

i’m creating several staging tables after flattening them from a json format to a relational table format.

now, say, i have in each table ~100 columns, some of them are strings and i’m converting instances where there is empty string to null.

since the adapter.get_columns_in_relation() only takes a Relation objects and no CTEs, i’m having a problem on how to solve it. I’d prefer to avoid creating additional model just for the sake of this transformation.

Is there maybe any workaround regarding using CTE within the get_columns_in_relation() macro?

What I’ve already tried

I’ve created a simple sql transformation using some jijna:

{%- for col in adapter.get_columns_in_relation(*CTE*) %}

        {% if not loop.first %}, {% endif -%}

            {%- if col.is_string() -%} 

                iff({{ col.name }} = '', null, {{ col.name }}) 

            {%- else -%}

                {{ col.name }}

            {%- endif -%}

        {% endfor %}

FROM *CTE*

note: the above works perfectly fine if i’m using ref(), or source(), but fails obviously when using cte

I’d love to hear any ideas or input you may have.
Thanks in advance for any help!