Tech Blog | Erin F

Writing out the schema.yml file (or the whatever-you-want-to-name-it.yml file) for models in a mart / schema in your dbt project can take a long time, especially if you have a mart / schema with lots of tables with lots of columns. Instead of manually typing every table and column name out,

just run the query below in Snowflake and , an automatically generated .yml file is returned as result that you can copy & paste into your .yml file in your dbt project! Just replace the {DATABASE} for the database you are developing in, and then the {TABLE_SCHEMA} with the table schema you want to generate the .yml file for!

with "columns" as (
	select '- name: ' || lower(column_name) || '\n        data_type: '|| lower(DATA_TYPE) || ''
        as column_statement,
		table_name,
        column_name
	from {DATABASE}.information_schema.columns
	where table_schema = '{TABLE_SCHEMA}'
),
tables as (
select table_name,
'
  - name: ' || lower(table_name) || '
     columns:
'    || listagg('      ' || column_statement || '\n') within group ( order by column_name ) as table_desc
from "columns"
group by table_name
)

select '# This file was generated automatically. Please place desired portions into the project manually.
version: 2
models:' || listagg(table_desc) within group ( order by table_name )
from tables;