There are two structures inherent in a dbt project: the dependency graph (as defined by your
ref() calls) and the hierarchical folder structure. The graph gets build semi-implicitly as you go through the process of building out your transformations, but the folder structure gives you a whole other dimension to play with. That freedom makes the folder structure (and the hierarchical configuration in
dbt_project.yml) really powerful, but it’s hard to know what to do with all that power! At some level your question is one of data warehouse design and modeling practices, and that’s what I’ll try to hit in my answer.
We’ve started to standardize the way we structure our folders on all of our client work, although for a long time it was pretty freeform. Our ideas on this are loosely based around Kimball data modeling (emphasis on the word loose!).
- First, we have staging models. We put all of these within a folder called
staging. The subfolders within
staging are all directly associated with a single source data system. They’re called things like
adwords. The models inside those folders have a single job: produce a model that acts as a clean interface to each source data table. So, if there are 12 tables in mongo that need to be used in analysis, there should be one model for each of them, and we name each of those models
stg_[entity_name]. Note that it may take multiple models to produce a clean output model for a given source data table if there is a lot of data cleansing required! But there should only be a single model called
- Next we have business data “marts”. These business marts are frequently organized by function (marketing, sales, finance). Within each business mart we have dimension and fact models, named
fct_[fact_name]. Organizing the folders within these business data marts is up to the designer for that mart; the folder structure should be useful in configuring models as cleanly and efficiently as possible.
So, in practice, the folders for our projects look like this:
We’ve found that this works pretty well for us.