dbt

DP dbt — compile / run

Two thin wrappers around dbt compile and dbt run that encode the IL/DP team's conventions: which DBT_SNOWFLAKE_DB to use for each, which warehouse, which target, and the rule that personal profiles.yml entries must never be committed to the repo.

The motivation: the actual shell incantation is two ~200-character commands with ten env vars and four flags. Every DP engineer remembers them imperfectly and types them slightly differently. The wrappers turn those into /dp:dbt-compile <model> and /dp:dbt-run <model>.

When to use

• You want to verify a dbt model compiles against the prod schema before pushing — /dp:dbt-compile <model>. Read-only against WELD_NORTH_PROD (no Snowflake writes; just resolves ref()/source() against prod metadata).
• You want to materialize a model into your personal sandbox for testing — /dp:dbt-run <model>. Writes to your WELD_NORTH_TEST.<your_schema> target.

When NOT to use

• Running against prod (WELD_NORTH_PROD) for materialization — never. The skill blocks this. Production materialization happens via the deploy pipeline (Snowchange + scheduled DAGs), not from an engineer's machine.
• Generic dbt usage on non-IL projects — use dbt directly.
• Running dbt test, dbt seed, dbt snapshot — out of scope for v1. Add new commands if the team needs them.

---

⛔ Hard Rules

1. dbt-run only ever writes to WELD_NORTH_TEST. The skill hardcodes DBT_SNOWFLAKE_DB=WELD_NORTH_TEST for run mode. Cannot be overridden — this is a guardrail, not a default.
2. dbt-compile is read-only. It calls dbt compile, never dbt run. The skill hardcodes the action; the user can't trigger a write from a compile invocation.
3. profiles.yml is per-engineer and must not be committed. When the engineer's DBT_TARGET block is missing from profiles.yml, the skill prints a template to add and reminds them not to commit. After running, the skill checks git status and warns if profiles.yml looks staged or modified.
4. Read-only on the repo for everything else. Do not modify model files, dbt_project.yml, packages, or anything else in DPS_DBT_ROOT.
5. No row-level data access by this skill itself. The dbt run does materialize into Snowflake — that's the engineer's intended action, not something the skill initiates beyond that.
6. Windows-safe output: ASCII only ([OK], [WARN], [FAIL]).

---

Required environment variables

Read from the user's shell environment. All must be exported in ~/.bashrc / ~/.zshrc / PowerShell $PROFILE / Windows User env vars:

Var	Purpose	Example
DPS_DBT_ROOT	Path to the dw_dbt/app/snowflake/ directory inside the local DataPlatformSnowflake clone. The skill cds here before invoking dbt.	/path/to/DataPlatformSnowflake/dw_dbt/app/snowflake
DBT_SNOWFLAKE_USER	The engineer's IL email (used by externalbrowser SSO to identify the IdP redirect).	[email protected]
DBT_TARGET	The engineer's personal target name in profiles.yml. Convention: <firstname>_test.	vishal_test
DBT_DATA_SOURCE	The dbt data-source vars value the team passes through. Most commonly events.	events
DBT_SNOWFLAKE_WH	Warehouse to run against. Convention: ENGINEERING_SANDBOX_WH (the team's shared dev warehouse — never a prod warehouse).	ENGINEERING_SANDBOX_WH

If any required var is missing, the skill stops with a [FAIL] message naming the missing var and where to export it. The skill does NOT prompt for an interactive input.

---

Required profiles.yml block (per engineer)

Every DP engineer must have their own block in <DPS_DBT_ROOT>/profiles.yml. The block is local-only — profiles.yml should be in .gitignore for the engineer's working tree. Never commit this block.

Template (replace <firstname> with the engineer's first name):

<firstname>_test:
  type: snowflake
  account: wne

  # User/password
  user: "{{ env_var('DBT_SNOWFLAKE_USER') }}"
  authenticator: externalbrowser
  role: DATA_ENGINEER

  database: WELD_NORTH_TEST
  warehouse: ENGINEERING_SANDBOX_WH
  schema: WNE_DIM_MODEL
  threads: 4
  client_session_keep_alive: False
  query_tag: dbt_testing
  reuse_connections: True

If the engineer's <DBT_TARGET> block is missing from profiles.yml, the skill prints this template with the right name substituted and stops.

---

Workflow (both commands)

Phase 1 — Validate environment

1. Resolve required env vars (see table above). Stop with [FAIL] listing any missing vars.
2. Resolve DPS_DBT_ROOT. If it doesn't exist on disk, stop.
3. Read <DPS_DBT_ROOT>/profiles.yml. Confirm a block matching <DBT_TARGET>: exists at top-level. If not, print the template (above) with <firstname>_test replaced by <DBT_TARGET> and stop.

Phase 2 — Resolve model path

The user passes a bare model name (e.g. lesson_attempt_fact), not a path.

1. Glob <DPS_DBT_ROOT>/models/**/<model_name>.sql (case-sensitive).
2. 0 matches — stop with [FAIL] Model '<name>' not found under <DPS_DBT_ROOT>/models/. Did you mean: <closest-match-via-substring>? Use ls models/**/*.sql for the suggestion.
3. 1 match — proceed. Show the resolved relative path: [OK] Resolved: ./models/<rel-path>.
4. >1 match — list all matches and ask the user which one. Use AskUserQuestion.

Phase 3 — Build the dbt invocation

Build the env vars dict and argv. Both commands share these env vars:

DBT_SNOWFLAKE_USER=<from env>
DBT_SNOWFLAKE_WH=<from env or default ENGINEERING_SANDBOX_WH>
DBT_DATA_SOURCE=<from env or default 'events'>
DBT_PROFILES_DIR=.
DBT_TARGET=<from env>

Mode-specific overrides:

Mode	DBT_SNOWFLAKE_DB	DBT_ENV	dbt action
compile	WELD_NORTH_PROD	prod	dbt compile -t <DBT_TARGET> -s ./models/<rel-path>
run	WELD_NORTH_TEST	test	dbt run -t <DBT_TARGET> -s ./models/<rel-path>

These are not user-configurable. The compile/run distinction IS the database distinction.

Phase 4 — Invoke dbt

cd "<DPS_DBT_ROOT>" && <env-vars> dbt <action> -t <target> -s <rel-path>

Stream the output live (don't capture-and-replay — long compiles/runs feel broken without progress). On Windows, ensure PYTHONUTF8=1 is set in the env so dbt's progress lines render correctly.

Phase 5 — Summarize

After dbt exits, parse and print a short summary block:

On success:

============================================================
  [OK] dbt <action> succeeded
  Model:    <rel-path>
  Target:   <DBT_TARGET>  (database: <DB>)
  Result:   PASS=N WARN=N ERROR=0 SKIP=N TOTAL=N
  Time:     <elapsed>
============================================================

For dbt-run, also print: Materialized: <DBT_SNOWFLAKE_DB>.<schema>.<model> (extract schema from profiles.yml).

On failure:

============================================================
  [FAIL] dbt <action> errored
  Model:    <rel-path>
  Result:   PASS=N ERROR>0 (see error blocks above)

  Error(s):
    - <model-name>: <one-line error summary>
    - ...

  Compiled SQL: <DPS_DBT_ROOT>/target/compiled/dw_dbt/<rel-path>
  Inspect the compiled SQL to debug. Common patterns:
    - Stray ';' inside a Jinja block -> 001003 (42000)
    - Source object missing in the target env -> 002003 (42S02)
    - SSO session expired -> run `aws sso login` (the SSO is for the IdP, not Snowflake itself)
============================================================

Phase 6 — profiles.yml hygiene check

After successful invocation, run:

cd <DPS_DBT_ROOT> && git status --porcelain profiles.yml

If the output is non-empty (uncommitted changes to profiles.yml), print:

[WARN] profiles.yml has uncommitted changes. Per DP team policy, do NOT commit
       this file - it contains your personal target block. Add `profiles.yml`
       to a local exclude (`.git/info/exclude`) if it's not already gitignored.

This is a soft check — it doesn't block, just warns.

---

Common failure modes

Symptom	Likely cause	Fix
Could not find profile <DBT_TARGET>	Engineer hasn't added their block to profiles.yml	Phase 1 prints the template; add it
OperationalError: 250001 ... could not connect	Snowflake SSO browser flow timed out or was blocked	Re-run; ensure browser opens
Compilation Error: 'env_var' returned 'None'	A required DBT_* var is unset	Phase 1 catches this; restart Claude Code if you just exported a new var
Snowchange-related compile error	The model references a column added by a Snowchange migration that hasn't been applied to WELD_NORTH_TEST yet	Apply the migration to your test env, or use WELD_NORTH_PROD (compile only)
packages.yml parsing failure	dbt deps was never run, or package-lock.yml is stale	cd <DPS_DBT_ROOT> && dbt deps
Model materialized but downstream refs fail	The downstream model is materialized as a view and your run materialized only the parent. Views resolve at query time	Run the downstream model too, or materialize as a table for testing

---

What this skill encodes that's IL-specific

• The two-database split — compile-against-prod / run-against-test. dbt has no built-in concept of this; it's an IL convention because production source tables only exist in WELD_NORTH_PROD, and engineer sandboxes only exist in WELD_NORTH_TEST.
• The personal-target naming — <firstname>_test. Not a dbt convention; an IL DP team one.
• The ENGINEERING_SANDBOX_WH warehouse — distinct from the prod warehouse; teams must not point local dev runs at production warehouses.
• The DBT_DATA_SOURCE=events default — most DP work is on the events pipeline; this matches the team's most-used invocation.
• The "never commit profiles.yml" rule — IL-specific operational policy. dbt itself is fine with committing profiles.yml; the team has chosen not to.

---

Future scope (not v1)

• /dp:dbt-test <model> — run dbt tests for a model. Same env-var shape as dbt-run.
• /dp:dbt-snapshot <name> — for the few SCD2 snapshots that aren't in models/. Lower priority.
• Pre-flight check that the model's upstream sources exist in the target env (would require an INFORMATION_SCHEMA query — not implemented in v1 to keep the skill out of the data plane).
• Auto-running dbt deps if packages.yml is newer than package-lock.yml. Possible quality-of-life if engineers hit it often.

---

Related Skills

Need	Skill
Triage a pipeline incident (DAG / ECS task → logs → fix)	dp:incident
Publish a fix summary or postmortem to Confluence	dp:publish-ticket-confluence
AWS SSO setup (DBT_SNOWFLAKE_USER is your IL email)	il:configure-aws-cli-multi-account