DBT custom schema using folder structure

0
votes

is there a way in DBT to create custom schemas for a model in a derived way by looking at the folder structure?

For example, say this is my structure:

models
└-- product1
    └-- team1
    |   └-- model1.sql
    └-- team2
        └-- model2.sql

In this case, model1.sql would be created in the schema product1_team1 whereas model2.sql would be created in the schema product1_team2. I guess I can specify those "by hand" in the dbt_project.yml file, but I was wondering if there was a way to do this in an automated way - so that every new model or folder is automatically created in the right schema.

I was looking at custom schema macros (https://docs.getdbt.com/docs/building-a-dbt-project/building-models/using-custom-schemas) but it seems to be plain jinja or simple Python built-ins. Not sure how I would be able to access folder paths in those macros.

Also, is there a way to write a macro in Python? as it could be relatively straightforward knowing the file path and with the os module.

dbt