Quickstart for dbt Core from a manual install
- 1 Introduction
- 2 Create a repository
- 3 Create a project
- 4 Connect to BigQuery
- 5 Perform your first dbt run
- 6 Commit your changes
- 7 Checkout a new git branch
- 8 Build your first model
- 9 Change the way your model is materialized
- 10 Delete the example models
- 11 Build models on top of other models
- 12 Add tests to your models
- 13 Document your models
- 14 Commit updated changes
- 15 Schedule a job
Introduction
When you use dbt Core to work with dbt, you will be editing files locally using a code editor, and running projects using a command line interface (CLI).
If you want to edit files and run projects using the web-based dbt Integrated Development Environment (IDE), refer to the dbt Cloud quickstarts. You can also develop and run dbt commands using the dbt Cloud CLI — a dbt Cloud powered command line.
Prerequisites
- To use dbt Core, it's important that you know some basics of the Terminal. In particular, you should understand
cd
,ls
andpwd
to navigate through the directory structure of your computer easily. - Install dbt Core using the installation instructions for your operating system.
- Complete appropriate Setting up and Loading data steps in the Quickstart for dbt Cloud series. For example, for BigQuery, complete Setting up (in BigQuery) and Loading data (BigQuery).
- Create a GitHub account if you don't already have one.
Create a starter project
After setting up BigQuery to work with dbt, you are ready to create a starter project with example models, before building your own models.
Create a repository
The following steps use GitHub as the Git provider for this guide, but you can use any Git provider. You should have already created a GitHub account.
- Create a new GitHub repository named
dbt-tutorial
. - Select Public so the repository can be shared with others. You can always make it private later.
- Leave the default values for all other settings.
- Click Create repository.
- Save the commands from "…or create a new repository on the command line" to use later in Commit your changes.
Create a project
Learn how to use a series of commands using the command line of the Terminal to create your project. dbt Core includes an init
command that helps scaffold a dbt project.
To create your dbt project:
- Make sure you have dbt Core installed and check the version using the
dbt --version
command:
dbt --version
- Initiate the
jaffle_shop
project using theinit
command:
dbt init jaffle_shop
- Navigate into your project's directory:
cd jaffle_shop
- Use
pwd
to confirm that you are in the right spot:
$ pwd
> Users/BBaggins/dbt-tutorial/jaffle_shop
- Use a code editor like Atom or VSCode to open the project directory you created in the previous steps, which we named jaffle_shop. The content includes folders and
.sql
and.yml
files generated by theinit
command.
- dbt provides the following values in the
dbt_project.yml
file:
name: jaffle_shop # Change from the default, `my_new_project`
...
profile: jaffle_shop # Change from the default profile name, `default`
...
models:
jaffle_shop: # Change from `my_new_project` to match the previous value for `name:`
...
Connect to BigQuery
When developing locally, dbt connects to your data warehouseA data warehouse is a data management system used for data storage and computing that allows for analytics activities such as transforming and sharing data. using a profile, which is a YAML file with all the connection details to your warehouse.
- Create a file in the
~/.dbt/
directory namedprofiles.yml
. - Move your BigQuery keyfile into this directory.
- Copy the following and paste into the new profiles.yml file. Make sure you update the values where noted.
jaffle_shop: # this needs to match the profile in your dbt_project.yml file
target: dev
outputs:
dev:
type: bigquery
method: service-account
keyfile: /Users/BBaggins/.dbt/dbt-tutorial-project-331118.json # replace this with the full path to your keyfile
project: grand-highway-265418 # Replace this with your project id
dataset: dbt_bbagins # Replace this with dbt_your_name, e.g. dbt_bilbo
threads: 1
timeout_seconds: 300
location: US
priority: interactive
- Run the
debug
command from your project to confirm that you can successfully connect:
$ dbt debug
> Connection test: OK connection ok
FAQs
The structure of a profile looks different on each warehouse. Check out the Supported Data Platforms page, and navigate to the Profile Setup
section for your warehouse.
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.
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 environments in dbt Core for more information.
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 in dbt Core for more information.
Yes! Check out the docs on environment variables for more information.
Perform your first dbt run
Our sample project has some example models in it. We're going to check that we can run them to confirm everything is in order.
- Enter the
run
command to build example models:
dbt run
You should have an output that looks like this:
Commit your changes
Commit your changes so that the repository contains the latest code.
- Link the GitHub repository you created to your dbt project by running the following commands in Terminal. Make sure you use the correct git URL for your repository, which you should have saved from step 5 in Create a repository.
git init
git branch -M main
git add .
git commit -m "Create a dbt project"
git remote add origin https://github.com/USERNAME/dbt-tutorial.git
git push -u origin main
- Return to your GitHub repository to verify your new files have been added.
Build your first models
Now that you set up your sample project, you can get to the fun part — building models! In the next steps, you will take a sample query and turn it into a model in your dbt project.
Checkout a new git branch
Check out a new git branch to work on new code:
- Create a new branch by using the
checkout
command and passing the-b
flag:
$ git checkout -b add-customers-model
> Switched to a new branch `add-customer-model`
Build your first model
- Open your project in your favorite code editor.
- Create a new SQL file in the
models
directory, namedmodels/customers.sql
. - Paste the following query into the
models/customers.sql
file.
- BigQuery
- Databricks
- Redshift
- Snowflake
with customers as (
select
id as customer_id,
first_name,
last_name
from `dbt-tutorial`.jaffle_shop.customers
),
orders as (
select
id as order_id,
user_id as customer_id,
order_date,
status
from `dbt-tutorial`.jaffle_shop.orders
),
customer_orders as (
select
customer_id,
min(order_date) as first_order_date,
max(order_date) as most_recent_order_date,
count(order_id) as number_of_orders
from orders
group by 1
),
final as (
select
customers.customer_id,
customers.first_name,
customers.last_name,
customer_orders.first_order_date,
customer_orders.most_recent_order_date,
coalesce(customer_orders.number_of_orders, 0) as number_of_orders
from customers
left join customer_orders using (customer_id)
)
select * from final
with customers as (
select
id as customer_id,
first_name,
last_name
from jaffle_shop_customers
),
orders as (
select
id as order_id,
user_id as customer_id,
order_date,
status
from jaffle_shop_orders
),
customer_orders as (
select
customer_id,
min(order_date) as first_order_date,
max(order_date) as most_recent_order_date,
count(order_id) as number_of_orders
from orders
group by 1
),
final as (
select
customers.customer_id,
customers.first_name,
customers.last_name,
customer_orders.first_order_date,
customer_orders.most_recent_order_date,
coalesce(customer_orders.number_of_orders, 0) as number_of_orders
from customers
left join customer_orders using (customer_id)
)
select * from final
with customers as (
select
id as customer_id,
first_name,
last_name
from jaffle_shop.customers
),
orders as (
select
id as order_id,
user_id as customer_id,
order_date,
status
from jaffle_shop.orders
),
customer_orders as (
select
customer_id,
min(order_date) as first_order_date,
max(order_date) as most_recent_order_date,
count(order_id) as number_of_orders
from orders
group by 1
),
final as (
select
customers.customer_id,
customers.first_name,
customers.last_name,
customer_orders.first_order_date,
customer_orders.most_recent_order_date,
coalesce(customer_orders.number_of_orders, 0) as number_of_orders
from customers
left join customer_orders using (customer_id)
)
select * from final
with customers as (
select
id as customer_id,
first_name,
last_name
from raw.jaffle_shop.customers
),
orders as (
select
id as order_id,
user_id as customer_id,
order_date,
status
from raw.jaffle_shop.orders
),
customer_orders as (
select
customer_id,
min(order_date) as first_order_date,
max(order_date) as most_recent_order_date,
count(order_id) as number_of_orders
from orders
group by 1
),
final as (
select
customers.customer_id,
customers.first_name,
customers.last_name,
customer_orders.first_order_date,
customer_orders.most_recent_order_date,
coalesce(customer_orders.number_of_orders, 0) as number_of_orders
from customers
left join customer_orders using (customer_id)
)
select * from final
- From the command line, enter
dbt run
.
When you return to the BigQuery console, you can select
from this model.
FAQs
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 Core:
- The
target/compiled/
directory for compiledselect
statements - The
target/run/
directory for compiledcreate
statements - The
logs/dbt.log
file for verbose logging.
- The
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 dbt Core, this is the
schema:
parameter in yourprofiles.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
.
Nope! dbt will check if the schema exists when it runs. If the schema does not exist, dbt will create it for you.
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.
If there's a mistake in your SQL, dbt will return the error that your database returns.
$ dbt run --select 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 `your-info-12345` 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.
Change the way your model is materialized
One of the most powerful features of dbt is that you can change the way a model is materialized in your warehouse, simply by changing a configuration value. You can change things between tables and views by changing a keyword rather than writing the data definition language (DDL) to do this behind the scenes.
By default, everything gets created as a view. You can override that at the directory level so everything in that directory will materialize to a different materialization.
-
Edit your
dbt_project.yml
file.-
Update your project
name
to:dbt_project.ymlname: 'jaffle_shop'
-
Configure
jaffle_shop
so everything in it will be materialized as a table; and configureexample
so everything in it will be materialized as a view. Update yourmodels
config block to:dbt_project.ymlmodels:
jaffle_shop:
+materialized: table
example:
+materialized: view -
Click Save.
-
-
Enter the
dbt run
command. Yourcustomers
model should now be built as a table!infoTo do this, dbt had to first run a
drop view
statement (or API call on BigQuery), then acreate table as
statement. -
Edit
models/customers.sql
to override thedbt_project.yml
for thecustomers
model only by adding the following snippet to the top, and click Save:models/customers.sql{{
config(
materialized='view'
)
}}
with customers as (
select
id as customer_id
...
) -
Enter the
dbt run
command. Your model,customers
, should now build as a view.- BigQuery users need to run
dbt run --full-refresh
instead ofdbt run
to full apply materialization changes.
- BigQuery users need to run
-
Enter the
dbt run --full-refresh
command for this to take effect in your warehouse.
FAQs
dbt ships with five materializationsThe exact Data Definition Language (DDL) that dbt will use when creating the model’s equivalent in a data warehouse.: view
, table
, incremental
, ephemeral
and materialized_view
.
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.
Start out with viewsA view (as opposed to a table) is a defined passthrough SQL query that can be run against a database (or data warehouse)., 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 materializationThe exact Data Definition Language (DDL) that dbt will use when creating the model’s equivalent in a data warehouse..
You can also configure:
- tags to support easy categorization and graph selection
- custom schemas to split your models across multiple schemas
- aliases if your viewA view (as opposed to a table) is a defined passthrough SQL query that can be run against a database (or data warehouse)./tableIn simplest terms, a table is the direct storage of data in rows and columns. Think excel sheet with raw values in each of the cells. 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
anddist
keys on Redshift,partitions
on BigQuery)
Check out the docs on model configurations to learn more.
Delete the example models
You can now delete the files that dbt created when you initialized the project:
-
Delete the
models/example/
directory. -
Delete the
example:
key from yourdbt_project.yml
file, and any configurations that are listed under it.dbt_project.yml# before
models:
jaffle_shop:
+materialized: table
example:
+materialized: viewdbt_project.yml# after
models:
jaffle_shop:
+materialized: table -
Save your changes.
FAQs
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 viewA view (as opposed to a table) is a defined passthrough SQL query that can be run against a database (or data warehouse). or tableIn simplest terms, a table is the direct storage of data in rows and columns. Think excel sheet with raw values in each of the cells., to ephemeral)
When you remove models from your dbt project, you should manually drop the related relations from your schema.
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.
Build models on top of other models
As a best practice in SQL, you should separate logic that cleans up your data from logic that transforms your data. You have already started doing this in the existing query by using common table expressions (CTEs).
Now you can experiment by separating the logic out into separate models and using the ref function to build models on top of other models:
- Create a new SQL file,
models/stg_customers.sql
, with the SQL from thecustomers
CTE in our original query. - Create a second new SQL file,
models/stg_orders.sql
, with the SQL from theorders
CTE in our original query.
- BigQuery
- Databricks
- Redshift
- Snowflake
select
id as customer_id,
first_name,
last_name
from `dbt-tutorial`.jaffle_shop.customers
select
id as order_id,
user_id as customer_id,
order_date,
status
from `dbt-tutorial`.jaffle_shop.orders
select
id as customer_id,
first_name,
last_name
from jaffle_shop_customers
select
id as order_id,
user_id as customer_id,
order_date,
status
from jaffle_shop_orders
select
id as customer_id,
first_name,
last_name
from jaffle_shop.customers
select
id as order_id,
user_id as customer_id,
order_date,
status
from jaffle_shop.orders
select
id as customer_id,
first_name,
last_name
from raw.jaffle_shop.customers
select
id as order_id,
user_id as customer_id,
order_date,
status
from raw.jaffle_shop.orders
- Edit the SQL in your
models/customers.sql
file as follows:
with customers as (
select * from {{ ref('stg_customers') }}
),
orders as (
select * from {{ ref('stg_orders') }}
),
customer_orders as (
select
customer_id,
min(order_date) as first_order_date,
max(order_date) as most_recent_order_date,
count(order_id) as number_of_orders
from orders
group by 1
),
final as (
select
customers.customer_id,
customers.first_name,
customers.last_name,
customer_orders.first_order_date,
customer_orders.most_recent_order_date,
coalesce(customer_orders.number_of_orders, 0) as number_of_orders
from customers
left join customer_orders using (customer_id)
)
select * from final
- Execute
dbt run
.
This time, when you performed a dbt run
, separate views/tables were created for stg_customers
, stg_orders
and customers
. dbt inferred the order to run these models. Because customers
depends on stg_customers
and stg_orders
, dbt builds customers
last. You do not need to explicitly define these dependencies.
FAQs
To run one model, use the --select
flag (or -s
flag), followed by the name of the model:
$ dbt run --select customers
Check out the model selection syntax documentation for more operators and examples.
Within one project: yes! To build dependencies between resources (such as models, seeds, and snapshots), you need to use the ref
function, and pass in the resource name as an argument. dbt uses that resource name to uniquely resolve the ref
to a specific resource. As a result, these resource names need to be unique, even if they are in distinct folders.
A resource in one project can have the same name as a resource in another project (installed as a dependency). dbt uses the project name to uniquely identify each resource. We call this "namespacing." If you ref
a resource with a duplicated name, it will resolve to the resource within the same namespace (package or project), or raise an error because of an ambiguous reference. Use two-argument ref
to disambiguate references by specifying the namespace.
Those resource will still need to land in distinct locations in the data warehouse. Read the docs on custom aliases and custom schemas for details on how to achieve this.
There's no one best way to structure a project! Every organization is unique.
If you're just getting started, check out how we (dbt Labs) structure our dbt projects.
Next steps
Before moving on from building your first models, make a change and see how it affects your results:
- Write some bad SQL to cause an error — can you debug the error?
- Run only a single model at a time. For more information, see Syntax overview.
- Group your models with a
stg_
prefix into astaging
subdirectory. For example,models/staging/stg_customers.sql
.- Configure your
staging
models to be views. - Run only the
staging
models.
- Configure your
You can also explore:
- The
target
directory to see all of the compiled SQL. Therun
directory shows the create or replace table statements that are running, which are the select statements wrapped in the correct DDL. - The
logs
file to see how dbt Core logs all of the action happening within your project. It shows the select statements that are running and the python logging happening when dbt runs.
Add tests to your models
Adding tests to a project helps validate that your models are working correctly.
To add tests to your project:
-
Create a new YAML file in the
models
directory, namedmodels/schema.yml
-
Add the following contents to the file:
models/schema.ymlversion: 2
models:
- name: customers
columns:
- name: customer_id
tests:
- unique
- not_null
- name: stg_customers
columns:
- name: customer_id
tests:
- unique
- not_null
- name: stg_orders
columns:
- name: order_id
tests:
- unique
- not_null
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
- name: customer_id
tests:
- not_null
- relationships:
to: ref('stg_customers')
field: customer_id -
Run
dbt test
, and confirm that all your tests passed.
When you run dbt test
, dbt iterates through your YAML files, and constructs a query for each test. Each query will return the number of records that fail the test. If this number is 0, then the test is successful.
FAQs
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 data 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.
Note that although you can't document data tests as of yet, we recommend checking out this dbt Core discussion where the dbt community shares ideas.
Running tests on one model looks very similar to running a model: use the --select
flag (or -s
flag), followed by the name of the model:
dbt test --select customers
Check out the model selection syntax documentation for full syntax, and test selection examples in particular.
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 Core:
- 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.
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
Check out the docs for more information.
¹If you're declaring properties for seeds, snapshots, or macros, you can also place this file in the related directory — seeds/
, snapshots/
and macros/
respectively.
Once upon a time, the structure of these .yml
files was very different (s/o to anyone who was using dbt back then!). Adding version: 2
allowed us to make this structure more extensible.
Resource yml files do not currently require this config. We only support version: 2
if it's specified. Although we do not expect to update yml files to version: 3
soon, having this config will make it easier for us to introduce new structures in the future
We recommend that every model has a test on a primary keyA primary key is a non-null column in a database object that uniquely identifies each row., 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.
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).
Document your models
Adding documentation to your project allows you to describe your models in rich detail, and share that information with your team. Here, we're going to add some basic documentation to our project.
-
Update your
models/schema.yml
file to include some descriptions, such as those below.models/schema.ymlversion: 2
models:
- name: customers
description: One record per customer
columns:
- name: customer_id
description: Primary key
tests:
- unique
- not_null
- name: first_order_date
description: NULL when a customer has not yet placed an order.
- name: stg_customers
description: This model cleans up customer data
columns:
- name: customer_id
description: Primary key
tests:
- unique
- not_null
- name: stg_orders
description: This model cleans up order data
columns:
- name: order_id
description: Primary key
tests:
- unique
- not_null
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
- name: customer_id
tests:
- not_null
- relationships:
to: ref('stg_customers')
field: customer_id -
Run
dbt docs generate
to generate the documentation for your project. dbt introspects your project and your warehouse to generate a JSONJSON (JavaScript Object Notation) is a minimal format for semi-structured data used to capture relationships between fields and values. file with rich documentation about your project.
- Run
dbt docs serve
command to launch the documentation in a local website.
FAQs
If you need more than a sentence to explain a model, you can:
- Split your description over multiple lines using
>
. Interior line breaks are removed and Markdown can be used. This method is recommended for simple, single-paragraph descriptions:
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.
- Split your description over multiple lines using
|
. Interior line breaks are maintained and Markdown can be used. This method is recommended for more complex descriptions:
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.
- Use a docs block to write the description in a separate Markdown file.
If you're using dbt Cloud to deploy your project and have the Team or Enterprise plan, you can use dbt Explorer to view your project's resources (such as models, tests, and metrics) and their lineageData lineage provides a holistic view of how data moves through an organization, where it’s transformed and consumed. to gain a better understanding of its latest production state.
Access dbt Explorer in dbt Cloud by clicking the Explore link in the navigation. You can have up to 5 read-only users access the documentation for your project.
dbt Cloud developer plan and dbt Core users can use dbt Docs, which generates basic documentation but it doesn't offer the same speed, metadata, or visibility as dbt Explorer.
Next steps
Before moving on from testing, make a change and see how it affects your results:
- Write a test that fails, for example, omit one of the order statuses in the
accepted_values
list. What does a failing test look like? Can you debug the failure? - Run the tests for one model only. If you grouped your
stg_
models into a directory, try running the tests for all the models in that directory. - Use a docs block to add a Markdown description to a model.
Commit updated changes
You need to commit the changes you made to the project so that the repository has your latest code.
- Add all your changes to git:
git add -A
- Commit your changes:
git commit -m "Add customers model, tests, docs"
- Push your changes to your repository:
git push
- Navigate to your repository, and open a pull request to merge the code into your master branch.
Schedule a job
We recommend using dbt Cloud as the easiest and most reliable way to deploy jobs and automate your dbt project in production.
For more info on how to get started, refer to create and schedule jobs.
For more information about using dbt Core to schedule a job, refer dbt airflow blog post.