dbt macro
Generate Sources
Automatically generate dbt sources, staging models and project configuration directly from your Snowflake database.
Goal
When onboarding a new data source in dbt you have to manually maintain sources.yml, create staging models and extend dbt_project.yml. With dozens of tables that is time-consuming and error-prone. Generate Sources removes that boilerplate by querying the Snowflake information schema and generating the required files for you.
You call the macro in a dbt session, copy the output to the right files and immediately have a working base for your staging layer.
What to expect
The macro consists of three parts that you use separately or in combination:
generate_source_yaml
(database, schemas?, split?)
Generates a ready-to-use sources.yml based on the tables and columns in Snowflake. With split: true the output is split per schema.
generate_staging_models
(database, schemas?)
Generates a simple SQL staging model with a select * from source() for each table. The file name and path are given as comments at the top.
generate_dbt_project_snippet
(database, schemas?)
Generates the staging block for dbt_project.yml, organised per schema in subdirectories. All models default to disabled: true.
Usage
1. Generate sources YAML
Call the macro via dbt run-operation. The output is directly copyable as sources.yml:
dbt run-operation generate_source_yaml \
--args '{"database": "MY_DB", "schemas": ["RAW", "LANDING"]}'2. Generate staging models
Generate a SQL staging file for each table. The path is given as a comment in the output:
dbt run-operation generate_staging_models \
--args '{"database": "MY_DB"}'
-- Example output:
-- models/staging/raw/stg_raw__customers.sql
select * from {{ source('raw', 'customers') }}3. Generate dbt_project.yml snippet
Generate the corresponding project configuration, ready to paste into dbt_project.yml:
dbt run-operation generate_dbt_project_snippet \
--args '{"database": "MY_DB"}'
-- Example output:
models:
my_project:
staging:
raw:
+schema: raw
+enabled: false