Dbt table naming conventions

Hi, as discussed on Slack I move this discussion to this page to make it available for future reference :slight_smile:
This question was probably asked several times before but I haven’t found anything with regards to it.
Is it possible to have multiple files with the same name in dbt?
Let’s say a staging table which already filters data from another table is called stg_events . I am using this with different definitions in different models but in the same project as I am filtering this huge event table based on my requirements for a certain model.
As I am using custom schemas the database does not have any problems with it. But the dbt ref function will have problems with it, correct?
And if that’s true, do you have any recommendations for a table naming convention?

Thanks a lot.
Best,
Andre

Hey @drege :wave:
Thanks so much for moving this to Discourse!

You’re completely right, filenames for models in dbt must be unique, even if they live in different directories, or are materialized in separate custom schemas.

This is because the ref function only takes one argument – the filename. I you try to have two files with the same name, dbt will give you back an error like this:

$ dbt run
Running with dbt=0.14.2
Encountered an error:
Compilation Error
  dbt found two resources with the name "stg_payments". Since these resources have the same name,
  dbt will be unable to find the correct resource when ref("stg_payments") is used. To fix this,
  change the name of one of these resources:
  - model.jaffle_shop.stg_payments (models/staging/jaffle_shop/stg_payments.sql)
  - model.jaffle_shop.stg_payments (models/staging/stripe/stg_payments.sql)

To get around this limitation, we often following a naming pattern like <source>__<object>.sql. For this example we’d have:

  • stg_stripe__payments.sql, and
  • stg_jaffle_shop__payments.sql

We’ve written lots more on the naming conventions we use over in this article:

Pro tip: if you consistently use the same separator in your filenames, you can actually tell dbt to use the first part of your filename as the custom schema, and the second part as the view/table name. @brandyn recently wrote a great post on this over here:

1 Like