Skip to main content

Build your first models

Now that you set up your sample project and had a successful run, you can get to the fun part — building models! You will take a sample query and turn it into a model in your dbt project. A model in dbt is a select statement.

Checkout a new git branch

  1. Make sure you're in the Develop interface. If you're not, click hamburger icon, then click Develop. The master branch should now be set to read-only mode, so you'll need to create a new branch.

  2. Click + create new branch, and name your branch add-customers-model.

    Checkout a new branch

    Checkout a new branch

Build your first model

  1. If you're not already in the Develop interface, click hamburger icon, and then Develop.

  2. Click the models directory, then click kebob icon, then select New file.

  3. Name the file models/customers.sql, then click Create.

  4. Paste the following query into the file and click save.

    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
  5. Enter dbt run in the command prompt at the bottom of the screen. You should get a successful run and see three models under DETAILS:

    A successful run with dbt Cloud

    A successful run with dbt Cloud

In the future, you would connect your business intelligence (BI) tools to these views and tables so they only read cleaned up data rather than raw data in your BI tool.

FAQs

 
 
 
 
 

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 by materializing everything in jaffle_shop as a table. Everything in the example project will still be materialized as a view.

  1. Edit your dbt_project.yml file.

    • Update your project name to:

      dbt_project.yml
      name: 'jaffle_shop'
    • Update your models config block to:

      dbt_project.yml
      models:
      jaffle_shop:
      +materialized: table
      example:
      +materialized: view
  2. Enter the dbt run command. Your customers model 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 override the dbt_project.yml for the customers model only by adding the following snippet to the top:

    models/customers.sql
    {{
    config(
    materialized='view'
    )
    }}

    with customers as (

    select
    id as customer_id
    ...

    )

  4. Enter the dbt run command. Your model, customers should now build as a view.

  5. Enter the dbt run --full-refresh command for this to take effect in your warehouse.

FAQs

 
 
 

Delete the example models

You can now delete the files that dbt created when you initialized the project:

  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
  3. Save your changes.

FAQs

 
 

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:

The DAG we want for our dbt project

The DAG we want for our dbt project

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

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

    models/stg_customers.sql
    select
    id as customer_id,
    first_name,
    last_name

    from `dbt-tutorial`.jaffle_shop.customers
    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 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

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

Next steps

Once you build your first model, you're ready to test and document your project.

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 a staging subdirectory. For example, models/staging/stg_customers.sql.
    • Configure your staging models to be views.
    • Run only the staging models.