Running the "Quickstart for dbt and Snowflake" Tutorial with dbt Projects on Snowflake
#snowflake#dbt#dataengineering
Sagara

Sagara @sagara

About: Data stack enthusiasm/Snowflake Data Superhero 2022-2025/Classmethod,inc (https://classmethod.jp/english/)

Location:
Japan
Joined:
Mar 6, 2025

Running the "Quickstart for dbt and Snowflake" Tutorial with dbt Projects on Snowflake

Publish Date: Aug 13
0 0

This article is an English translation of: https://dev.classmethod.jp/articles/dbt-quickstart-for-dbt-and-snowflake-with-dbt-projects-on-snowflake/

Hi, this is Sagara.

An official tutorial called "Quickstart for dbt and Snowflake" is available for dbt Cloud. This tutorial is recommended as a first step to learn about dbt itself.

https://docs.getdbt.com/guides/snowflake?step=1

Additionally, as of the end of June 2025, dbt Projects on Snowflake, which allows you to develop and run dbt within Snowflake, is in public preview.

https://docs.snowflake.com/en/user-guide/data-engineering/dbt-projects-on-snowflake

https://dev.classmethod.jp/articles/snowflake-pupr-dbt-projects/

I tried running this "Quickstart for dbt and Snowflake" tutorial with dbt Projects on Snowflake, and I'll summarize the experience in this article.

Important Notes

The following dbt Cloud-specific features are out of scope for this article. Please be aware:

  • Using Managed Repository
    • In this tutorial, we'll develop without Git integration
    • For information about Git integration in dbt Projects on Snowflake, please refer to this blog post
  • Viewing documentation
  • Environment and Job configuration (dbt Cloud-only features)
    • In dbt Projects on Snowflake, these are replaced by profiles.yml definitions and dbt Projects deployment to task execution

0. Prerequisites

Creating a Snowflake Trial Account

We'll use a Snowflake trial account for this tutorial, so please create one.

https://signup.snowflake.com/?owner=classmethodlead&plan=enterprise&cloud=aws&region=ap-northeast-1&utm_source=dev.classmethod.jp&utm_medium=banner&utm_content=snowflake&utm_campaign=single_article_foot_ads

Enabling Secondary Roles (if not already enabled)

Next, check if secondary roles are enabled in your worksheet.

Run the following query and confirm that default_secondary_roles is set to ["ALL"]:

show users;
Enter fullscreen mode Exit fullscreen mode

If it shows a different value, run the following query to enable secondary roles:

alter user <username> set default_secondary_roles = ('all');
Enter fullscreen mode Exit fullscreen mode

1. Introduction

https://docs.getdbt.com/guides/snowflake?step=1

This chapter explains what we'll do in this tutorial.

Here's what we'll cover in order:

  • Create a new Snowflake worksheet
  • Load sample data into your Snowflake account
  • Convert sample queries into models in your dbt project (dbt models are SELECT statements)
  • Add sources to your dbt project. Sources allow you to name and describe raw data already loaded into Snowflake
  • Add tests to your models
  • Document your models
  • Schedule job runs
    • *Note: For jobs, we'll substitute with dbt Projects deployment to task execution

2. Create a new Snowflake worksheet

https://docs.getdbt.com/guides/snowflake?step=2

In this chapter, we'll create a worksheet to run queries in Snowflake.

From the left menu in Snowflake, go to Projects, then click Worksheets.

2025-08-13_08h51_05

Click the + button in the upper right to create a new worksheet.

2025-08-13_08h52_45

3. Load data

https://docs.getdbt.com/guides/snowflake?step=3

In this chapter, we'll load the data needed for the tutorial.

First, run the following query to create the necessary database, schemas, tables, and warehouse for the tutorial. The tables will also load data from public S3.

Important: For line 8 create schema analytics.dbt_ssagara;, please use your own name or something that identifies it as your schema. (Since my name is Sagara Satoshi, I'm using dbt_ssagara.)

use role accountadmin;

create warehouse transforming;
create database raw;
create database analytics;
create schema raw.jaffle_shop;
create schema raw.stripe;
create schema analytics.dbt_ssagara; 
create schema analytics.prod;

create table raw.jaffle_shop.customers 
( id integer,
  first_name varchar,
  last_name varchar
);

copy into raw.jaffle_shop.customers (id, first_name, last_name)
from 's3://dbt-tutorial-public/jaffle_shop_customers.csv'
file_format = (
    type = 'CSV'
    field_delimiter = ','
    skip_header = 1
    );

create table raw.jaffle_shop.orders
( id integer,
  user_id integer,
  order_date date,
  status varchar,
  _etl_loaded_at timestamp default current_timestamp
);

copy into raw.jaffle_shop.orders (id, user_id, order_date, status)
from 's3://dbt-tutorial-public/jaffle_shop_orders.csv'
file_format = (
    type = 'CSV'
    field_delimiter = ','
    skip_header = 1
    );

create table raw.stripe.payment 
( id integer,
  orderid integer,
  paymentmethod varchar,
  status varchar,
  amount integer,
  created date,
  _batched_at timestamp default current_timestamp
);

copy into raw.stripe.payment (id, orderid, paymentmethod, status, amount, created)
from 's3://dbt-tutorial-public/stripe_payments.csv'
file_format = (
    type = 'CSV'
    field_delimiter = ','
    skip_header = 1
    );
Enter fullscreen mode Exit fullscreen mode

Next, run the following queries in order to confirm that data has been loaded successfully into each table:

select * from raw.jaffle_shop.customers;
select * from raw.jaffle_shop.orders;
select * from raw.stripe.payment;
Enter fullscreen mode Exit fullscreen mode

2025-08-13_08h58_02

4. Connect dbt to Snowflake

https://docs.getdbt.com/guides/snowflake?step=4

This chapter covers setting up the connection from dbt Cloud to Snowflake, but since this is not needed for dbt Projects on Snowflake, we'll skip it.

5. Set up a dbt managed repository

https://docs.getdbt.com/guides/snowflake?step=5

This chapter covers setting up the Managed Repository, which is a dbt Cloud-specific feature. Since this is not relevant to dbt Projects on Snowflake, we'll skip it.

As mentioned in the important notes at the beginning of this article, we'll develop without Git integration in this tutorial.

For reference, you can learn about Git integration in dbt Projects on Snowflake in the following blog post:

https://dev.classmethod.jp/articles/try-dbt-projects-on-snowflake-with-github/

6. Initialize your dbt project and start developing

https://docs.getdbt.com/guides/snowflake?step=6

In this chapter, we'll set up the dbt Project. Let's move on to dbt Projects on Snowflake operations!

From the left menu in Snowflake, go to Projects, then click Workspaces.

2025-08-13_09h09_12

Click + Add new in the upper left, then click dbt Project.

2025-08-13_09h13_28

In the popup that appears, enter the following information and click Create in the bottom right:

  • Project Name: dbt_quickstart
    • Any name is fine
  • Role: accountadmin
    • The role used when queries are executed through dbt Projects in the Workspace. We're using ACCOUNTADMIN for this tutorial, but any role with read permissions for the databases, schemas, and tables to be used, and create permissions for the target database and schema where dbt will create objects, will work.
  • Warehouse: transforming
    • The warehouse used when queries are executed through dbt Projects in the Workspace
  • Database: analytics
    • The target database for dbt output
  • Schema: dbt_ssagara
    • The development output schema for dbt

2025-08-13_09h24_55

This operation creates a new dbt Project!

The information you entered is written to profiles.yml. The official documentation for profiles.yml is here:

https://docs.getdbt.com/docs/core/connect-data-platform/profiles.yml

Looking at the contents of profiles.yml, you'll see dev: listed. This is the name of the target that can be used to switch between environments (development, production, etc.) where dbt commands are executed. (You can set a name other than dev, but since the currently configured profile is for development, dev is appropriate.)

Also, target: dev means "use dev if no target is specified when running dbt commands." We'll add a production profile to profiles.yml later, but to avoid accidentally modifying the production environment with careless commands, it's recommended to keep target: dev unchanged.

2025-08-13_09h27_20

Let's also try running dbt run at this point. dbt run is a command that executes all SELECT statements written in .sql files in the models folder as CTAS/CVAS statements against the specified database and schema.

Select Run from the command selection button in the upper right and press the play button.

2025-08-13_09h48_18

Logs will appear at the bottom of the Workspace.

2025-08-13_09h49_08

2025-08-13_09h49_25

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You can see that MY_FIRST_DBT_MODEL and MY_SECOND_DBT_MODEL, which were in the default models folder, have been created!

2025-08-13_09h50_58

7. Build your first model

https://docs.getdbt.com/guides/snowflake?step=7

In this chapter, we'll create a new .sql file using the data loaded into Snowflake and run dbt run.

The word "model" appears here - in dbt, .sql files stored in the models folder are called "models."

Click the + next to the models folder, select SQL File, and create a file called customers.sql.

2025-08-13_09h58_05

2025-08-13_09h58_40

You can now edit customers.sql in the editor pane on the right. Copy and paste the following SQL:

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
Enter fullscreen mode Exit fullscreen mode

After pasting, it should look like this:

2025-08-13_09h59_57

To run dbt run, select Run from the command selection button in the upper right and press the play button.

2025-08-13_09h48_18

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You can see that a view called CUSTOMERS has been created.

2025-08-13_10h04_13

8. Change the way your model is materialized

https://docs.getdbt.com/guides/snowflake?step=8

In this chapter, we'll experiment with Materialization, which determines how objects are created in Snowflake (as tables, views, etc.).

First, open dbt_project.yml. This is an essential file for dbt that handles overall project settings and folder path specifications.

2025-08-13_10h20_02

Modify the models: code at the bottom as follows:

  • Before 
models:
  dbt_quickstart:
    +materialized: view
Enter fullscreen mode Exit fullscreen mode
  • After 
models:
  dbt_quickstart:
    +materialized: table
Enter fullscreen mode Exit fullscreen mode

Let's run dbt run in this state to see how the behavior changes. Select Run from the command selection button in the upper right and press the play button.

2025-08-13_09h48_18

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You should see that everything has been created as tables.

2025-08-13_10h23_56

For reference, Materialization settings can be configured at the project or folder level in dbt_project.yml, and also specified within each .sql file. For example, looking at my_first_dbt_model.sql, you'll see a config specification as shown below. When the same config setting exists in both dbt_project.yml and a .sql file, the .sql file takes precedence.

2025-08-13_10h25_37

As additional reference, Materialization includes not only tables and views but also incremental for incremental updates and ephemeral which doesn't create objects but is only used as a WITH clause. For details on these, please refer to the official documentation below:

https://docs.getdbt.com/docs/build/materializations

9. Delete the example models

https://docs.getdbt.com/guides/snowflake?step=9

In this chapter, we'll delete the sample .sql files that were added by default.

From the models folder, click the "..." next to my_first_dbt_model.sql and my_second_dbt_model.sql, and click Delete for each.

2025-08-13_10h32_14

2025-08-13_10h32_36

This completes what we need to do in this chapter, but it's important to note that dbt doesn't automatically delete the corresponding tables/views when .sql files are deleted. After deleting .sql files, you need to manually delete the tables/views through SQL commands or other methods.

10. Build models on top of other models

https://docs.getdbt.com/guides/snowflake?step=10

In this chapter, following dbt best practices, we'll break down the data cleanup logic and create separate .sql files (corresponding to the Staging layer).

First, create the following two new .sql files directly under the models folder for the Staging layer:

  • stg_customers.sql 
select
    id as customer_id,
    first_name,
    last_name

from raw.jaffle_shop.customers
Enter fullscreen mode Exit fullscreen mode
  • stg_orders.sql 
select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from raw.jaffle_shop.orders
Enter fullscreen mode Exit fullscreen mode

Next, update the previously created customers.sql with the following content. The key point is using {{ ref() }} notation to specify existing .sql file names, which builds processing dependencies ensuring that this .sql file executes after the specified .sql files have completed.

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
Enter fullscreen mode Exit fullscreen mode

To view these model dependencies in the Workspace, run the dbt compile command which compiles each SQL file.

2025-08-13_10h52_07

After this, click DAG in the bottom right of the Workspace to see the lineage showing the dependencies.

2025-08-13_10h53_14

Finally for this chapter, let's run dbt run to output to the development schema. Select Run from the command selection button in the upper right and press the play button.

2025-08-13_09h48_18

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You can see that the two newly created .sql files have been output as tables.

2025-08-13_10h55_51

11. Build models on top of sources

https://docs.getdbt.com/guides/snowflake?step=11

In this chapter, we'll configure "sources" which define the source data for dbt transformations. By configuring sources, you can display source data in the lineage and run tests on source data before performing dbt transformations (covered in Chapter 12).

First, create sources.yml under the models folder with the following content:

version: 2

sources:
    - name: jaffle_shop
      description: This is a replica of the Postgres database used by our app
      database: raw
      schema: jaffle_shop
      tables:
          - name: customers
            description: One record per customer.
          - name: orders
            description: One record per order. Includes cancelled and deleted orders.
Enter fullscreen mode Exit fullscreen mode

Next, modify the two .sql files we created earlier for the Staging layer to reference sources. When referencing sources, use notation like {{ source('jaffle_shop', 'customers') }}.

  • stg_customers.sql 
select
    id as customer_id,
    first_name,
    last_name

from {{ source('jaffle_shop', 'customers') }}
Enter fullscreen mode Exit fullscreen mode
  • stg_orders.sql 
select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle_shop', 'orders') }}
Enter fullscreen mode Exit fullscreen mode

To view the dependencies including sources in the Workspace, run the dbt compile command to compile each SQL file.

2025-08-13_10h52_07

After this, click DAG in the bottom right of the Workspace to see the lineage including sources.

2025-08-13_11h07_49

Also, by running dbt compile, you can check the compiled .sql files in the compiled folder within the target folder. As shown below, you can see that the source references have been replaced with the appropriate table names.

2025-08-13_11h10_48

12. Add tests to your models

https://docs.getdbt.com/guides/snowflake?step=12

In this chapter, we'll try the data testing feature, which is unique to dbt.

Data tests are defined in YAML files, so create schema.yml in the models folder with the following content:

version: 2

models:
  - name: customers
    description: One record per customer
    columns:
      - name: customer_id
        description: Primary key
        data_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
        data_tests:
          - unique
          - not_null

  - name: stg_orders
    description: This model cleans up order data
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
Enter fullscreen mode Exit fullscreen mode

In the above schema.yml, the key point for data test definitions is the data_tests section, where you define necessary tests for each column from the following four test types:

  • unique
    • Tests whether the column values are unique across all records
  • not_null
    • Tests whether the column values are not null across all records
  • accepted_values
    • Tests whether the column values are one of the values specified in the list
  • relationships
    • Technically, this checks referential integrity
    • In the above code, it confirms that "the customer_id column in stg_orders consists of values from the customer_id column in stg_customers"

After creating schema.yml, run dbt test to actually perform the data tests.

2025-08-13_11h23_08

After execution completes, check the logs from the bottom right of the Workspace. When each test is executed and shows PASS, it means the test has passed.

2025-08-13_11h25_25

13. Document your models

https://docs.getdbt.com/guides/snowflake?step=13

This chapter covers trying dbt's documentation generation feature. However, as of August 13, 2025, dbt Projects on Snowflake cannot execute dbt docs generate as a standard feature, so we'll skip this.

14. Commit your changes

https://docs.getdbt.com/guides/snowflake?step=14

This chapter covers Git commit and push, assuming the use of dbt Cloud's Managed Repository. However, since we're excluding Git integration for this tutorial, we'll skip this chapter.

For reference, once again, you can learn about Git integration in dbt Projects on Snowflake in the following blog post:

https://dev.classmethod.jp/articles/try-dbt-projects-on-snowflake-with-github/

15. Deploy dbt

https://docs.getdbt.com/guides/snowflake?step=15

In this chapter, we'll deploy the developed content to the production environment. Note that dbt Projects on Snowflake requires completely different operations from dbt Cloud.

Adding production environment information to profiles.yml

First, add information about the schema where you want to output to production to profiles.yml.

Open profiles.yml and modify it as follows. The key point is adding a new target called prod with schema: PROD:

dbt_quickstart:
  target: dev
  outputs:
    dev:
      type: snowflake
      role: ACCOUNTADMIN
      warehouse: TRANSFORMING
      database: ANALYTICS
      schema: DBT_SSAGARA
      account: ''
      user: ''
    prod:
      type: snowflake
      role: ACCOUNTADMIN
      warehouse: TRANSFORMING
      database: ANALYTICS
      schema: PROD
      account: ''
      user: ''
Enter fullscreen mode Exit fullscreen mode

Deploying the dbt Project to the specified schema

dbt Projects on Snowflake can be scheduled to run as tasks, but this requires deploying the dbt Project to a specified schema.

Click Connect in the upper right of the Workspace, then click Deploy dbt project.

2025-08-13_11h41_26

Enter the following information and click Deploy:

  • Select location
    • Database: ANALYTICS
    • Schema: PROD *Note: We're using the same schema as the data output destination, but a different schema is also fine.
  • Select or create dbt project
    • Click + Create dbt project
    • Enter Name: dbt_quickstart

2025-08-13_11h43_30

2025-08-13_11h45_11

If deployment is successful, it will display as shown below. Looking at the prod schema, you can see that the dbt Project has been deployed.

2025-08-13_11h46_10

2025-08-13_11h47_42

Next, go to the worksheet and run the following query to define a task using the dbt Project. The key point is args='build --target prod', where build executes the dbt build command which alternately runs dbt run and dbt test for each model, and --target prod executes the dbt command against the production target prod we created earlier.

use role accountadmin;

create or alter task analytics.prod.dbt_execute
  warehouse = transforming
  schedule = 'using cron 0 9 1 1 * Asia/Tokyo' -- Runs at 9 AM on January 1st every year
as
execute dbt project analytics.prod.dbt_quickstart args='build --target prod';
Enter fullscreen mode Exit fullscreen mode

After this, run the following command to manually execute the task:

execute task analytics.prod.dbt_execute;
Enter fullscreen mode Exit fullscreen mode

For reference, you can check the execution status of the dbt project itself by clicking dbt projects from Monitoring on the left side of the screen.

2025-08-13_11h57_07

2025-08-13_11h57_47

After confirming the task succeeded, looking inside the prod schema, we can see that the tables have been successfully created!

2025-08-13_11h59_39

Conclusion

I tried running the "Quickstart for dbt and Snowflake" tutorial with dbt Projects on Snowflake.

I think this is perfect content for learning about dbt itself while trying out dbt Projects on Snowflake! Please give it a try.

Comments 0 total

    Add comment