generate_dbt_yml_schema - BigQuery

SELECT `justfunctions.eu.generate_dbt_yml_schema`("justfunctions", "eu")
                                            
/*--Output--
version: 2
models: - name: country_distinct columns: - name: service_country - name: official_name - name: alpha_2 
*/

CREATE OR REPLACE FUNCTION `your_project_id.your_dataset_id.generate_dbt_yml_schema`(`project_id` string, `dataset_id` string) 
  RETURNS string AS ((with columns as (
    select '- name: ' || column_name || '' as column_statement,
        table_name
    from `your_project_id.your_dataset_id`.INFORMATION_SCHEMA.COLUMNS

),
tables as (
select table_name,
"  - name: " || table_name || "\n" ||
"    columns:\n" ||
string_agg('      ' || column_statement, "\n") as yml_file
from columns
group by table_name
order by table_name
)
select
"version: 2\n" ||
"\n" ||
"models:\n" ||
string_agg(yml_file, "\n") as yml_file
from tables)
)
  OPTIONS ( description = '''Generates a DBT schema.yml using the information_schema of the generated tables for the specified <project_id> and <dataset_id>. For this function to work you need to create your own function in your dataset.''')

SQL User Defined Function (SQL UDF)