Lab · dbt (Snowsight, optional)

Module 4 stretch — same models in Snowsight dbt Projects (Snowflake-only)

title: “Lab · dbt (Snowsight, optional)” subtitle: “Module 4 stretch — same models in Snowsight dbt Projects (Snowflake-only)” —

Snowsight “dbt project” vs this workshop

Snowflake dbt Projects workspaces run dbt inside Snowsight — a different runtime from the main lab. Module 4 does not require it.

The required lab is Exercise: dbt: dbt Core in Codespaces with Snowflake and Databricks targets (dbt run --target snowflake and optional --target databricks). SQL and Snowpark setup stay in your Snowflake Git workspace.

This optional page is for trainees who want the same dbt_project/ in a Snowsight dbt project workspace instead (product docs · getting-started tutorial).

Snowflake-only — no Databricks target, no dbt docs serve, different dbt runtime version than Codespaces.

Module: 4 — dbt Pipeline · Required lab: Exercise: dbt · Setup: Snowflake · dbt

What you will do

Same outcome as Exercise: dbt — Steps 2–3 on Snowflake only:

dbt deps → dbt seed → dbt run --exclude tag:ml tag:streaming → dbt test --exclude tag:ml tag:streaming
PASS=16 (2 staging + 2 silver + 12 gold)
View lineage in the workspace DAG tab (instead of dbt docs serve)

Prerequisites

Snowflake setup complete through 04_external_stage.sql (Bronze tables loaded from Module 3)
Role DE_WORKSHOP_ROLE, warehouse DE_WORKSHOP_WH Started
Git API integration via + API Integration in the Git dialog (same GITHUB_WORKSHOP_INTEGRATION / OAuth as Module 3 — alternative: 01_git_api_integration.sql)
Bronze tables exist in {your_id}_BRONZE (from Snowflake pipeline exercise)

Step 1: External access for `dbt deps` (one-time, ACCOUNTADMIN)

Snowsight dbt deps downloads dbt-labs/dbt_utils from the internet. Snowflake requires an external access integration (tutorial § optional external access).

Open snowflake/setup/reference/dbt_external_access_integration.sql (from your existing Git workspace or paste from Codespace)
Run as ACCOUNTADMIN
Note the integration name: DBT_PACKAGES_EXT_ACCESS

Step 2: Create a dbt workspace from your fork

Per Workspaces + Git and the dbt getting-started tutorial:

Open the create dialog (either path):

Path	Steps
A — global create	Left sidebar → + (top) → Git repository
B — from Workspaces	Projects → Workspaces → + in the Workspaces pane (or workspace dropdown above the files pane) → Git workspace / From Git repository under Create workspace

Then in the dialog:

If offered workspace types, choose dbt project → From Git repository — not a plain Git repository workspace for SQL-only work (workspaces for dbt)
(If you only see From Git repository, use that — then set Project to dbt_project in Step 3.)
Repository URL: https://github.com/<YOUR-USERNAME>/MHPDataEngineerWorkshop.git (your fork)
API integration: select GITHUB_WORKSHOP_INTEGRATION (from + API Integration in Module 3) — if empty, see setup troubleshooting
Authentication: OAuth2 → GitHub OAuth (snowflakedb app) — grant Repository access to your fork before Create
Create — wait for sync; default branch is usually main (change later on Changes → branch menu)

Our repo layout: dbt_project/dbt_project.yml is not at the repository root. In the workspace toolbar, set Project to dbt_project (subfolder). Snowflake passes this as PROJECT_ROOT when executing dbt (EXECUTE DBT PROJECT · PROJECT_ROOT = 'dbt_project').

Separate from the Module 3 SQL Git workspace

You may already have a Git repository workspace for snowflake/setup/ and snowflake/sql/. A dbt project workspace is a second workspace (or a new one focused on dbt_project/). SQL setup stays in the first workspace; dbt runs here.

Step 3: Add `profiles.yml` inside `dbt_project/`

Snowsight reads profiles.yml from the dbt project folder, not ~/.dbt/profiles.yml (tutorial § Verify profiles.yml).

In the workspace, open dbt_project/profiles.snowsight.yml.example
Save as dbt_project/profiles.yml (same folder as dbt_project.yml)
Set schema: to {YOUR_ATTENDEE_ID}_DBT (uppercase on Snowflake), e.g. de_XX_yourname_DBT after you replace the placeholder
Keep role: DE_WORKSHOP_ROLE and warehouse: DE_WORKSHOP_WH

account and user can be placeholders — dbt runs under your Snowsight session (tutorial example).

Attendee ID and `env_var`

Codespaces uses export ATTENDEE_ID=... for sources and gold schemas. Snowsight does not load your Codespace .env.

Replace de_XX_yourname with your assigned ID (same as Snowflake pipeline scripts and .env).

Item	What to do
`profiles.yml` `schema:`	`"de_XX_yourname_DBT"` → your ID, e.g. `"DE_01_ALICE_DBT"`
Bronze sources (`_staging.yml`)	Snowsight has no `.env` — change the `env_var('ATTENDEE_ID', 'tr_01_trainer')` default to your ID, or temporarily set account setup to match

Gold models land in {ID}_GOLD via generate_schema_name — same _staging.yml / attendee ID rule.

Step 4: Run dbt commands from the workspace toolbar

Use the bar above the editor: Project → dbt_project · Profile → snowflake · Role → DE_WORKSHOP_ROLE · Warehouse → DE_WORKSHOP_WH.

Open the Output tab below the editor to see stdout (tutorial § Execute dbt commands).

Step	Command	Extra settings
1	Deps	Click ▼ next to Run → set External access integration to `DBT_PACKAGES_EXT_ACCESS`
2	Seed	Run with defaults
3	Run	Additional flags: `--exclude tag:ml tag:streaming`
4	Test	Additional flags: `--exclude tag:ml tag:streaming`

Expected: PASS=16 on run (same as Exercise: dbt § First run).

Supported commands: Snowflake supported dbt commands (run, test, seed, build, compile, deps, docs generate, …).

Not supported in Snowsight: dbt docs serve — use Compile then the DAG tab for lineage (supported commands note).

Step 5: Verify gold KPIs (same as main exercise)

In a SQL file in the workspace (or your SQL Git workspace):

USE ROLE DE_WORKSHOP_ROLE;
USE WAREHOUSE DE_WORKSHOP_WH;

SET attendee_id = 'de_XX_yourname';  -- your assigned ID
LET kpi_trips_by_hour := $attendee_id || '_GOLD.kpi_trips_by_hour';
SELECT * FROM IDENTIFIER(:kpi_trips_by_hour) ORDER BY pickup_hour;

Optional: Deploy a DBT PROJECT object

After a successful run, you can Deploy dbt project from the workspace Connect menu to create a schema-level DBT PROJECT object (tutorial § Deploy). This is beyond the workshop exercise — useful for scheduling (EXECUTE DBT PROJECT) in production.

Limitations vs Codespaces (read before you start)

Topic	Codespaces (main lab)	Snowsight dbt (this page)
Databricks target	Yes (`--target databricks`)	No
`dbt debug` / network credentials	Yes	Not applicable (in-account)
`dbt docs serve`	Yes	No — use DAG view
dbt version	1.11.8 (workshop pin)	Snowflake-managed (version notes)
`ATTENDEE_ID`	`.env`	`profiles.yml` + possible `_staging.yml` edit
Push to Git	Normal git in Codespace	Workspace Push (private repos)

Troubleshooting

Issue	Fix
`deps` fails / network error	Run `reference/dbt_external_access_integration.sql`; pass `DBT_PACKAGES_EXT_ACCESS` on Deps
Wrong bronze schema / source not found	Replace `de_XX_yourname` with your ID in `profiles.yml` and bronze `schema:` in `_staging.yml`
Wrong gold schema	Check `profiles.yml` `schema` and `generate_schema_name` / attendee ID
No dbt command bar	Create a dbt project workspace, not a plain SQL worksheet
Project not found	Set Project dropdown to `dbt_project`
Permission errors	Role `DE_WORKSHOP_ROLE`, warehouse Started