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 }
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
{% set result = run_query("select * from (" ~ query ~ ") where 1=0") %}`
{% set columns = result.columns | map(attribute="name") %}
For me, inside an on-run-end statement, the actual query was the node.compiled_code of a test. Using this I could obtain the failing records of a test and unpivot those before storing those in a separate table!
