Build your first models

With our starter project setup, it's time to get to the fun part — building models! We're going to take the query from the Setting up instructions, and turn it into a model in our dbt project.

Checkout a new git branch

dbt Cloud

1. Ensure you're in the Develop interface. If you're not, click the hamburger menu, and then Develop. The master branch should now be set to "read only" mode, so you'll need to create a new branch.
2. Click the "create new branch" button, and name your branch add-customers-model.

Checkout a new branch

dbt CLI

It's a good idea to check out a new git branch when working on new code.

1. Run git checkout -b add-customers-model to create a new branch.

Checkout a new branch

Build your first model

dbt Cloud

1. Ensure you're in the Develop interface. If you're not, click the hamburger menu, and then Develop.
2. Create a new file in the models directory named models/customers.sql.
3. Paste the query from the Setting up instructions into the file.
4. Execute dbt run in the command prompt at the bottom of the screen. You should get a successful run, like so:

A successful run with dbt Cloud

If you switch back to the BigQuery console you'll be able to select from this model.

dbt CLI

1. Open your project in a code editor
2. Create a new SQL file in the models directory, named models/customers.sql.
3. Paste the query from the Setting up instructions into the file.
4. From the command line, execute dbt run. Your output should look like this:

A successful run with the dbt CLI

If you switch back to the BigQuery console you'll be able to select from this model.

FAQs

 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.

 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.

 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.

 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.

 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.

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. Let's see this in action.

Using the + sign in your dbt_project.yml

These videos were recorded with a slightly older version of dbt (dbt v0.15.0), which did not use the + sign in the dbt_project.yml file (this was introduced in dbt v0.17.0).

We'll try to update the videos soon, but for now, take extra note of the + signs in the code samples below, under the models: key.

• dbt Cloud
• dbt CLI

1. Edit the following in your dbt_project.yml file:

dbt_project.yml

models:
  jaffle_shop:
    +materialized: table
    example:
      +materialized: view

2. Execute dbt run. Your model, customers should now be built as a table!

   info

   To do this, dbt had to first run a drop view statement (or API call on BigQuery), then a create table as statement.

3. Edit models/customers.sql to have the following snippet at the top:

models/customers.sql

{{
  config(
    materialized='view'
  )
}}

with customers as (

    select
        id as customer_id
        ...

)

4. Execute dbt run. Your model, customers should be built as a view. You may need to run dbt run --full-refresh for this to take effect on BigQuery.

FAQs

 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.

 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.

 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 model configurations to learn more.

Delete the example models

We don't need the sample files that dbt created for us anymore! Let's delete them.

• dbt Cloud
• dbt CLI

1. Delete the models/example/ directory
2. Delete the example: key from your dbt_project.yml file, and any configurations that are listed under it

dbt_project.yml

# before
models:
  jaffle_shop:
    +materialized: table
    example:
      +materialized: view

dbt_project.yml

# after
models:
  jaffle_shop:
    +materialized: table

FAQs

 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.

 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.

Build models on top of other models

Often, it's a good idea to clean your data in one place, before doing additional transformations downstream. Our query already uses CTEs to this effect, but now we're going to experiment with using the ref function to separate this clean-up into upstream models, like so:

The DAG we want for our dbt project

• dbt Cloud
• dbt CLI

1. Create a new SQL file, models/stg_customers.sql, with the SQL from the customers CTE in our original query:

models/stg_customers.sql

select
    id as customer_id,
    first_name,
    last_name

from `dbt-tutorial`.jaffle_shop.customers

2. Create a second new SQL file, models/stg_orders.sql, with the SQL from the orders CTE in our original query:

models/stg_orders.sql

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from `dbt-tutorial`.jaffle_shop.orders

3. Edit the SQL in your models/customers.sql file as follows:

models/customers.sql

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

4. Execute dbt run

This time when dbt ran, separate views/tables were created for stg_customers, stg_orders and customers. dbt was able to infer the order in which to run these models in — customers depends on stg_customers and stg_orders, so gets built last. There's no need to explicitly define these dependencies.

FAQs

 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.

 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.

 As I create more models, how should I keep my project organized? What should I name my models?

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.

Extra exercises

• Write some bad SQL to cause an error — can you debug this error?
• Run only a single model at a time (docs)
• Group your models with a stg_ prefix into a staging subdirectory (i.e. models/staging/stg_customers.sql)
  • Configure your staging models to be views
  • Run only the staging models

• dbt Cloud
• dbt CLI