dbt Core is an open-source data transformation tool and framework that you can use to define, test, and deploy SQL transformations. dbt on Snowflake allows you to use familiar Snowflake features to create, edit, test, run, and manage your dbt Core projects. Snowflake integrates with Git repositories and offers Snowflake CLI commands to support continuous integration and development (CI/CD) workflows for data pipelines.

In this lab, we will go through everything you need to know to get started with dbt Projects on Snowflake!

Workspaces provides a developer environment where you can edit, test, and deploy your dbt projects, all within Snowflake. Your personal workspace is your own area where you can create and edit files.

Navigate to Projects > Workspaces to get started.

Snowflake needs an API integration to create a workspace from a GitHub repository. In your personal workspace, create a new SQL file, then run the following command:

Let's now clone the example dbt project we will use in the rest of this lab.

1. Select the + button next to Workspaces and Databases, and then select Git workspace.
2. In the popup, enter the following fields:
   1. Repository URL: https://github.com/Snowflake-Labs/getting-started-with-dbt-on-snowflake.git
   2. Workspace name: tasty_bytes
   3. API Integration: TB_DBT_GIT_API_INTEGRATION
   4. Select Public Repository. Note: Private repos can be authenticated with personal access tokens and GitHub users can authenticate with OAuth.
3. Click Create!

The repository includes a setup script, tasty_bytes_dbt_demo/setup/tasty_bytes_setup.sql, that creates the warehouse, database, schemas, and Tasty Bytes source data for this lab.

1. In your new workspace, open tasty_bytes_dbt_demo/setup/tasty_bytes_setup.sql.
2. Comment out STEP 4 (the GitHub secret and API integration). You already created the API integration, and this lab uses a public repository, so you don't need a secret.
3. Run all of the uncommented commands.

We will work with Tasty Bytes data in this lab. The raw data includes sales data, truck metadata, and customer information. This will all be transformed with dbt and saved as a table in Snowflake.

We will start by adding a new model to show how you can create using workspaces. Create a SQL file called sales_data_by_truck.sql in tasty_bytes_dbt_demo/models/marts. Copy the following code.

Each dbt project folder in your Snowflake workspace must contain a dbt_projects_profiles.yml or profiles.yml file that specifies a target warehouse, database, schema, and role in Snowflake for the project. The type must be set to snowflake.

dbt requires an account and user, but these can be left empty because the dbt project runs in Snowflake under the current account and user context.

This project uses dbt_projects_profiles.yml. It works the same way as profiles.yml but is specifically for dbt Projects on Snowflake. Standard dbt doesn't recognize dbt_projects_profiles.yml. The local dbt CLI reads only profiles.yml, so adding dbt_projects_profiles.yml never disrupts anyone's existing local dbt workflow, including a personal ~/.dbt/profiles.yml. If both files are present, Snowflake uses dbt_projects_profiles.yml.

When you run dbt commands, your workspace reads dbt_projects_profiles.yml if it's present. Otherwise, it reads profiles.yml. When you have at least one valid target specified in the file, the selections for the targets defined in that project and the dbt commands available to run are available in the workspace menu bar.

We will keep the values in dbt_projects_profiles.yml unchanged.

We will now run dbt compile. From the command list, select Compile, and then select the execute button. To set additional flags or an external access integration before you run a command, select the down arrow next to the execute button.

Select Advanced in that window to see more options for the run:

Once you have compiled the project, you can view the compiled SQL by clicking the View Compiled SQL button from an individual model. This shows the true tables and views the model will reference.

You can also view the DAG to see how models interact with one another. In the panel below the editor, select the DAG tab to open the DAG.

Let's now run the project to pull data through our pipeline. From the dbt toolbar, select run from the dropdown, and click the play button. Once this completes, we have data in our marts tables!

Note: dbt deps requires an External Access Integration which is not supported in Snowflake trial accounts.

First we need to create the External Access Integration to download the dbt_utils package we will use for testing. Run the following in a new SQL file in your workspace.

Next, return to your dbt project uncomment out packages.yml. This will pull in two packages, dbt_utils and dbt_semantic_view.

Now we'll run dbt deps to pull in the dbt_utils package. The dbt_utils package contains several usefull test that we have built into the project.

From the dbt toolbar, you get dropdowns for the project, target, and command. Clicking the play button will run the relevant command. You can also click the down arrow to override the arguments.

1. From the toolbar, select dev and deps.
2. Click the dropdown arrow, and under External Access Integration, select DBT_ACCESS_INTEGRATION, the external access integration you created above.
3. Click Execute.

Note: this step requires dbt deps (above).

Let's now open tasty_bytes_dbt_demo/models/staging/__sources.yml to view data tests we have created. There are several common tests included, including ones from dbt_utils. On line 219, we have defined a maximum allowed value for order_amount. This is to flag abnormally large orders. Uncomment this code to run the test.

Finally, let's run dbt tests to confirm the data meets our requirements. From the toolbar, select test, and click the play button.

Oh no! There's an error with one of our tests. It looks like there is a test that doesn't quite look right. Let's update line 219 of `tasty_bytes_dbt_demo/models/staging/__sources.yml to be 10000 instead of 10. That should fix it.

Note: this step requires dbt deps (above).

The dbt_semantic_view package allows you to build semantic views from dbt models! Semantic views allow you to define business metrics and model business entities and their relationships. By adding business meaning to physical data, the semantic view enhances data-driven decisions and provides consistent business definitions across enterprise applications. Building semantic views in dbt allows you reference them from downstream dbt models or other places in Snowflake like Cortex Analyst.

To get started, save the following code in a new model saved to models/semantic_views/order_analytics.sql in your project. Then run dbt run to build the view.

Now that we have updated and validated our dbt project, let's deploy it so we can build automation. Deploying a dbt project creates a Snowflake object and allows us to create tasks based on the object.

1. On the right side of the workspace editor, select Connect > Deploy dbt project
2. Ensure your role is accountadmin
3. Under Select location, select database tasty_bytes_dbt_db and schema public
4. Under Select or create dbt project, select Create dbt project
5. Under Enter Name, enter tasty_bytes
6. (Optional) Set a Default Target and choose a dbt Version
7. (Optional) Under External Access Integration, select DBT_ACCESS_INTEGRATION, the integration you created earlier. This is only needed if your project runs dbt deps

8. Scroll down and leave Auto Compile and Default Writeback set to true (their defaults)

9. Select Deploy. After you deploy, the Connect menu shows the name of your dbt project object, with options to redeploy, disconnect, or view the project.

Workspaces are fully git backed. To view changes and commit, click changes from the file selector. In this case, we have pulled from a public repository, so we will skip committing.

Every dbt project object in your account is listed under Transformation > dbt Projects. The All Projects tab is an inventory of each project with its location, dbt version, and owner. Filter by Location TASTY_BYTES_DBT_DB to find the TASTY_BYTES project you just deployed.

Navigate to Catalog > Database Explorer > TASTY_BYTES_DBT_DB > PUBLIC > dbt Projects > TASTY_BYTES to view the project details. You can also select View project from the Connect menu in your workspace. From the Run History tab, you can view all runs associated with the project. To run the project once without a schedule, select Execute, choose an operation and profile, and then select Run.

To browse what's in the deployed project, click the View Overview dropdown and select Models. The pane lists every model with its folder path, such as /models/marts and /models/staging. Use the same dropdown to view Tests, Snapshots, Seeds, Sources, and Macros.

Now that the project is deployed, you can schedule it with tasks, either from the project details page or with SQL.

Let's create tasks to regularly run and test our dbt project.

1. Navigate to the Project Details tab
2. Open the Schedules dropdown and click + Create new
3. Under Task name, enter tasty_bytes_hourly_run
4. Keep Role set to ACCOUNTADMIN and select warehouse TASTY_BYTES_DBT_WH
5. Keep Trigger set to Schedule, and set Frequency to Hourly
6. Set Operation to Run and keep Profile set to Project default
7. Click Create

You can also schedule from your workspace: select the calendar icon next to the dbt project menu to see existing schedules, and then select + Create new.

We can create more complex task structure with the script below. It creates a task DAG and alerts us when there is a test failure. Copy the script below into a new SQL file and run the commands one by one. Note the alert will fail unless you have verified your email. To verify your email, click on the user icon in the bottom left of the screen > profile > enter your email > click the link in your email.

Note: This script assumes you have added dbt tests.

To see how runs are going across your projects, go to Transformation > dbt Projects and select the Run History tab. It summarizes runs across your projects, showing each project's last command, last run status, when it last ran, and how many previous runs succeeded or failed. Filter by Database TASTY_BYTES_DBT_DB to focus on this lab.

To see every run of a single project, open TASTY_BYTES in Catalog and select the Run History tab. It charts runs over time by status and lists each run's command, status, start time, dbt version, and writeback setting. Use the Command and Run status filters to narrow the list. You can also view the status of running tasks in Transformation > Task History.

Each dbt run is a single EXECUTE DBT PROJECT query, so you can inspect any individual run in Query History. From the project's Run History tab, select a run to open its query details.

The Query details tab shows the SQL text that ran, followed by a dbt Run table listing every model with its start time, duration, and status. Sort by Duration to find the slowest models. The Details pane on the right shows the run's warehouse, duration, and the task that triggered it, in this case tasty_bytes_hourly_run.

Scroll down to see the dbt Output, the full dbt log for the run. Select Download Build Artifacts to get the run's artifacts, such as manifest.json, for local debugging.

dbt projects in Snowflake integrate with Tracing and Logging, allowing you to easily monitor and debug dbt projects. Tracing follows the OpenTelemetry standard and allows you to keep logs within a single platform.

To view the trace for a run, select the Query telemetry tab on the same query. Each span shows how long it took, from the task graph and task that triggered the run, down to each dbt model and the individual SQL statements it ran. Expand a model to see where its time went, and use the Span Type filter or the legend to focus on a specific type. The Event Table at the top shows your account's active event table.

Snowflake makes it easy to monitor usage across your account. To view consumption, go to Admin > Cost Management > Consumption Tab. Depending on your setup, you can create a dedicated warehouse to run dbt jobs to allow easy monitoring. Filter by Service Type Warehouse and select TASTY_BYTES_DBT_WH to see the credits your dbt runs used and the queries behind them.

Additionally, you are able to build resource monitors and alerts based on warehouse consumption.

Congratulations! You've successfully completed the "Getting Started with dbt Projects on Snowflake" lab. You now understand how dbt Core on Snowflake enables you to define, test, and deploy SQL transformations using familiar Snowflake features. You've learned how Snowflake's native integration with Git repositories and CLI commands support CI/CD workflows for your data pipelines.

If you want to learn more about dbt Projects on Snowflake, check out the official documentation.