List of FAQs

#1. What other model configurations are there?

You can also configure:

  • tags to support easy categorization and graph selection
  • custom schemas to split your models across multiple schemas
  • aliases if your view/table name should differ from the filename
  • Snippets of SQL to run at the start or end of a model, known as hooks
  • Warehouse-specific configurations for performance (e.g. sort and dist keys on Redshift, partitions on BigQuery)

Check out the docs on models to learn more.

#2. What materializations are available in dbt?

dbt ships with four materializations: view, table, incremental and ephemeral. Check out the documentation on materializations for more information on each of these options.

You can also create your own custom materializations, if required however this is an advanced feature of dbt.

#3. What tests are available for me to use in dbt?

Out of the box, dbt ships with the following tests:

  • unique
  • not_null
  • accepted_values
  • relationships (i.e. referential integrity)

You can also write your own custom schema tests.

Some additional custom schema tests have been open-sourced in the dbt-utils package, check out the docs on packages to learn how to make these tests available in your project.

#4. How can I see the SQL that dbt is running?

To check out the SQL that dbt is running, you can look in:

  • dbt Cloud:
    • Within the run output, click on a model name, and then select "Details"
  • dbt CLI:
    • The target/compiled/ directory for compiled select statements
    • The target/run/ directory for compiled create statements
    • The logs/dbt.log file for verbose logging.

#5. Do I need to create my target schema before running dbt?

Nope! dbt will check if the schema exists when it runs. If the schema does not exist, dbt will create it for you.

#6. What privileges does my database user need to use dbt?

Your user will need to be able to:

  • select from raw data in your warehouse (i.e. data to be transformed)
  • create schemas, and therefore create tables/views within that schema¹
  • read system views to generate documentation (i.e. views in information_schema)

On Postgres, Redshift, and Snowflake, use a series of grants to ensure that your user has the correct privileges.

On BigQuery, use the "BigQuery User" role to assign these privileges.


¹Alternatively, a separate user can create a schema for the dbt user, and then grant the user privileges to create within this schema. We generally recommend granting your dbt user the ability to create schemas, as it is less complicated to implement.

#7. What happens if one of my runs fails?

If you're using dbt Cloud, we recommend setting up email and Slack notifications (Account Settings > Notifications) for any failed runs. Then, debug these runs the same way you would debug any runs in development.

#8. One of my tests failed, how can I debug it?

To debug a failing test, find the SQL that dbt ran by:

  • dbt Cloud:
    • Within the test output, click on the failed test, and then select "Details"
  • dbt CLI:
    • Open the file path returned as part of the error message.
    • Navigate to the target/compiled/schema_tests directory for all compiled test queries

Copy the SQL into a query editor (in dbt Cloud, you can paste it into a new Statement), and run the query to find the records that failed.

#10. How do I load data into my warehouse?

dbt assumes that you already have a copy of your data, in your data warehouse. We recommend you use an off-the-shelf tool like Stitch or Fivetran to get data into your warehouse.

Can dbt be used to load data?

No, dbt does not extract or load data. It focuses on the transformation step only.

#11. How do I write long-form explanations in my descriptions?

If you need more than a sentence to explain a model, you can: 1. Split your description over multiple lines (yaml docs), like so:

version: 2
models:
- name: customers
description: >
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat.
  1. Use a docs block to write the description in a Markdown file.

#12. Do all my tests go in one file?

No! You can use as many files as you want! Some folks find it useful to have one file per model, we tend to have one per directory.

#13. Can I use environment variables in my profile?

Yes! Check out the docs on environment variables for more information.

#14. What should I name my profile?

We typically use a company name for a profile name, and then use targets to differentiate between dev and prod. Check out the docs on managing environments for more information.

#15. What tests should I add to my project?

We recommend that every model has a test on a primary key, that is, a column that is unique and not_null.

We also recommend that you test any assumptions on your source data. For example, if you believe that your payments can only be one of three payment methods, you should test that assumption regularly — a new payment method may introduce logic errors in your SQL.

In advanced dbt projects, we recommend using sources and running these source data-integrity tests against the sources rather than models.

#16. How do I remove deleted models from my data warehouse?

If you delete a model from your dbt project, dbt does not automatically drop the relation from your schema. This means that you can end up with extra objects in schemas that dbt creates, which can be confusing to other users.

(This can also happen when you switch a model from being a view or table, to ephemeral)

When you remove models from your dbt project, you should manually drop the related relations from your schema.

#17. If I rerun dbt, will there be any downtime as models are rebuilt?

Nope! The SQL that dbt generates behind the scenes ensures that any relations are replaced atomically (i.e. your business users won't experience any downtime).

The implementation of this varies on each warehouse, check out the logs to see the SQL dbt is executing.

#18. How do I run one model at a time?

To run one model, use the --models flag (or -m flag), followed by the name of the model:

$ dbt run --models customers

Check out the model selection syntax documentation for more operators and examples.

#19. What should my profiles.yml file look like for my warehouse?

The structure of a profile looks different on each warehouse. Check out the supported databases page, and navigate to the Profile Setup section for your warehouse.

#20. Does my test file need to be named `schema.yml`?

No! You can name this file whatever you want (including whatever_you_want.yml), so long as:

  • The file is in your models/ directory
  • The file has .yml extension
  • The yaml follows the basic structure.

Check out the docs for more information.

#21. Why are profiles stored outside of my project?

Profiles are stored separately to dbt projects to avoid checking credentials into version control. Database credentials are extremely sensitive information and should never be checked into version control.

#22. How do I share my documentation with my team members?

If you're using dbt Cloud to deploy your project, and have the Team Plan, you can have up to 50 read only users, who will be able access the documentation for your project.

#23. What happens if the SQL in my query is bad?

Or:

I got a Database Error, what does that mean?

If there's a mistake in your SQL, dbt will return the error that your database returns.

$ dbt run --models customers
Running with dbt=0.15.0
Found 3 models, 9 tests, 0 snapshots, 0 analyses, 133 macros, 0 operations, 0 seed files, 0 sources
14:04:12 | Concurrency: 1 threads (target='dev')
14:04:12 |
14:04:12 | 1 of 1 START view model dbt_alice.customers.......................... [RUN]
14:04:13 | 1 of 1 ERROR creating view model dbt_alice.customers................. [ERROR in 0.81s]
14:04:13 |
14:04:13 | Finished running 1 view model in 1.68s.
Completed with 1 error and 0 warnings:
Database Error in model customers (models/customers.sql)
Syntax error: Expected ")" but got identifier `grand-highway-265418` at [13:15]
compiled SQL at target/run/jaffle_shop/customers.sql
Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Any models downstream of this model will also be skipped. Use the error message and the compiled SQL to debug any errors.

#24. How should I structure my project?

There's no one best way to structure a project! Every organization is unique.

If you're just getting started, check out how we (Fishtown Analytics) structure our dbt projects.

#25. What should I name my target?

We typically use targets to differentiate between development and production runs of dbt, naming the targets dev and prod respectively. Check out the docs on managing environments for more information.

#26. How do I test one model at a time?

Running tests on one model looks very similar to running a model: use the --models flag (or -m flag), followed by the name of the model:

dbt test --models customers

Check out the model selection syntax documentation for more operators and examples.

#27. Do model names need to be unique?

Yes! To build dependencies between models, you need to use the ref function. The ref function only takes one argument — the model name (i.e. the filename). As a result, these model names need to be unique, even if they are in distinct folders.

Often, this question comes up because users want to give two models the same name in their warehouse, splitting them across separate schemas (e.g. stripe.users and app.users). Checkout the docs on custom aliases and custom schemas to achieve this.

#28. I got an "unused model configurations" error message, what does this mean?

You might have forgotten to nest your configurations under your project name, or you might be trying to apply configurations to a directory that doesn't exist. Check out this article to understand more.

#29. When should I run my tests?

You should run your tests whenever you are writing new code (to ensure you haven't broken any existing models by changing SQL), and whenever you run your transformations in production (to ensure that your assumptions about your source data are still valid).

#30. Which materialization should I use for my model?

Start out with views, and then change models to tables when required for performance reasons (i.e. downstream queries have slowed).

Check out the docs on materializations for advice on when to use each materialization.

#31. How did dbt choose which schema to build my models in?

By default, dbt builds models in your target schema. To change your target schema:

  • If you're developing in dbt Cloud, these are set for each user when you first use a development environment.
  • If you're developing with the dbt CLI, this is the schema: parameter in your profiles.yml file.

If you wish to split your models across multiple schemas, check out the docs on using custom schemas

Note: on BigQuery, dataset is used interchangeably with schema.