Receiving unexpected error in jinja function on dbt model

I'm trying to select all column names from a table in my dbt model into a single column in a sql table. The reason I wish to do this is because I believe it is a scalable way of listing all column names regardless of the size of the table, which may vary from 10s to dozens upon dozens.

I am using the adapter.get_columns_in_relation function with a for loop, a method which I have seen described by numerous sources, including StackOverflow.

My code is as follows;

{%- set table_cols = adapter.get_columns_in_relation(source('my_model_name', 'my_table')) -%}

select
  {% for col in table_cols %}
  my_table.{{col.name}}
  {% endfor %}
  from
  {{source('my_model_name', 'my_table')}}

I see no reason why this shouldn't work, however the error I get is...

Server error: Database Error in rpc request (from remote system)
001003 (42000): SQL compilation error:
syntax error line 5 at position 31 unexpected '.'.

I'd be grateful for any assistance or suggestions of a better way to achieve my aim.

Solution

The approach you are looking for could look like the following:

{%- set table_cols = adapter.get_columns_in_relation(source('your_schema', 'your_table')) -%}

select distinct
  -- concatenate column names gathered in table_cols in a single column within a comma-separated string
  {% for col in table_cols %}
  '{{ col.name }}'
    {% if not loop.last -%} || ', ' || {% endif -%}
  {% endfor %} as cols_list
from {{ source('your_schema', 'your_table') }}

The code ^above creates a model that will gather all the different column names of the source table into a single column. Since I used a distinct, the output will be a single row with the column names separated by a comma (,). See below an example of the output:

|                      cols_list                         |
|--------------------------------------------------------|
| id, customer_id, first_name, last_name, address, email |

The issue with your approach is visible when you check the compiled SQL: you were just calling the column names without any function to capture the different names inside a column name. See an example of the compiled SQL from your code below:

select
  col1 col2 col3 col4 col5 [...]
from your_db.your_schema.your_table