Setting up

In this tutorial, we will be turning the below query into a dbt project that is tested, documented, and deployed — you can check out the generated documentation for the project we're building here.

This tutorial is geared at first-time users who want detailed instructions on how to go from zero to a deployed dbt project. You'll need a working knowledge of SQL in order to do this tutorial.

We recommend you go through this project once from beginning to end. Once you've completed it, you should go back through and read some of the FAQs to broaden your understanding of dbt.

This tutorial is based on a fictional business named jaffle_shop, so you'll see this name used throughout the project. At this organization, the following query is used to build a customers table.

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

Create a BigQuery project

For this tutorial, we've created a public dataset in BigQuery that anyone can select from.

We're using BigQuery since anyone with a Google Account can use BigQuery, but dbt works with many data warehouses.

info

BigQuery has generous free tier. If you have an existing GCP account that has surpassed these tiers on BigQuery, running queries for this tutorial will incur a very small (less than a few USD) cost.

1. Go to the BigQuery Console — if you don't have a Google Cloud Platform account you will be asked to create one.
2. Create a new project for this tutorial — if you've just created a BigQuery account, you'll be prompted to create a new project straight away. If you already have an existing you can select the project drop down in the header bar, and create a new project from there.
   

   Create a new GCP project

3. Head back to the the BigQuery Console, and ensure your new project is selected. Copy and paste the above query into the Query Editor to validate that you are able to run it successfully.
   

   Ensure you can run the above query

FAQs

 The data in this tutorial is already loaded into BigQuery. 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.

Generate BigQuery credentials

In order to let dbt connect to your warehouse, you'll need generate a keyfile. This is analogous to using a database user name and password with most other data warehouses.

1. Go to the BigQuery credential wizard. Ensure that your new project is selected in the header bar.
2. Generate credentials with the following options:
   • Which API are you using? BigQuery API
   • Are you planning to use this API with App Engine or Compute Engine? No
   • Service account name: dbt-user
   • Role: BigQuery User
   • Key type: JSON
3. Download the JSON file and save it in an easy-to-remember spot, with a clear filename (e.g. dbt-user-creds.json)

FAQs

 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.

Choose the way you want to develop

There’s two main ways of working with dbt:

1. Edit files and run projects using the web-based Integrated Development Environment (IDE) in dbt Cloud.
2. Edit files locally using a code editor, and run projects using the Command Line Interface (dbt CLI).

To use the CLI, it's important that you know some basics of your terminal. In particular, you should understand cd, ls and pwd to navigate through the directory structure of your computer easily. As such, if you are new to programming, we recommend using dbt Cloud for this tutorial.

If you wish to use the CLI, please follow the installation instructions for your operating system.