Macros: Obtain Column List for Non-Relation Output

Help
1

Hello, I’m trying to build a macro and am stuck on a specific problem.

Is there a dbt macro that allows me to get the columns list from a query set result (e.g. CTE)? I am looking for something similar to this function without the requirement for the input to be a relation. The reason being that I am trying to one-shot a macro and do not want to persist the intermediate steps.

For example, I want to be able to do this:

WITH cte 1 AS (
SELECT * FROM table
)
,cte 2 AS (
SELECT ... 
FROM some_table LEFT JOIN cte1 ON
{ for col in cte1}
some_table.{{ col }} = cte1.{{ col }}
{ endfor }
2

Did you manage to find a solution? It seems really hard to do. I just have a sql string. I am thinking about creating a view (f"select * from {query} where 1=0"), and then querying the information schema (I am on databricks) followed by deleting the view again… I was not sure if performing multiple SQL operations was possible at all, but it seems to be using the run_query operation… I will give it a try and let everybody know :slight_smile: