Dbt Workflow

dbt Workflow Skill - Full Project Lifecycle

Overview

This skill orchestrates the complete dbt project workflow. Load it FIRST whenever working on a dbt project - it contains rules that affect how you interpret everything.

Tools

scan_project.py - Project scanner (Step 1)

python3 "${CLAUDE_SKILL_DIR}/scan_project.py" "<project_directory>"

Returns: models to build, stubs to rewrite, dependencies, required columns, sources, macros (with full definitions), and current_date hazards.

validate_project.py - dbt parse validator (Step 3)

python3 "${CLAUDE_SKILL_DIR}/validate_project.py" "<project_directory>"

Runs dbt parse and returns structured errors, warnings, and orphan patches.

analyze_project_db - DB hints (MCP tool, Step 1)

Call analyze_project_db(connection_name). Returns the database-derived hints: lookup joins (_id columns with matching dimension tables), staging-vs-raw row gaps, and parent-child driving-table hints. Run alongside scan_project.py in Step 1.

map_columns - Upstream observations

map_columns accepts upstream_tables="<physical_relation1>,<physical_relation2>" for pre-write inspection when SQL has no lineage. Its statuses describe available columns rather than output requirements.

find_column_producers - Existing producers (MCP tool, Step 5)

Call find_column_producers(connection_name, column_names="<col1>,<col2>", project_dir) before writing SQL. It reports every existing model that already projects each named output column, the relations it reads, and the expression - the project's producer for a value is the model that already computes it.

verify_model_values - Aggregate cross-validator (MCP tool, Step 8)

Call verify_model_values(connection_name, model_name). Queries the model output, finds the upstream fact table, picks the largest slice, and compares the model's metric values against both COUNT() and COUNT(DISTINCT ) from the raw source. Reports MATCH, MISMATCH, or WARNING (model uses DISTINCT when COUNT() is larger). Used by the value-verifier subagent in Step 8.

Knowledge Base Check (before Step 1)

Before starting the workflow, call get_knowledge with the task description. If the knowledge base returns entries, read them - they provide context about this project's conventions, data patterns, and prior research.

KB entries INFORM your work but do NOT skip any steps. Always run the full 8-step workflow. The KB makes you faster because you already know what to look for - but you still verify everything via query_database and the verification subagents.

The 8-Step Workflow

ALWAYS run Steps 1, 2, 3, and 8. Steps 4-7 are ONLY for projects with stubs to rewrite or models to build (from the Step 1 scan). If the scan shows 0 stubs and 0 missing models AND the task does NOT ask to edit, fix, remove, or modify existing files, skip Steps 4-7 entirely - go from Step 3 straight to Step 8.

If the task edits existing models (edit, fix, remove a variable, refactor), do NOT skip Steps 4-7 even if the scan shows nothing to build. Edit the files in Step 7, then run dbt run --select <edited_model1> <edited_model2> to rebuild them - edited SQL only takes effect after materialization.

Even if the task says "create model X" - if X.sql already exists in the scan's "EXISTING COMPLETE" list, it is already created. Verify it in Step 8, do not recreate it.

After Step 1, create a task for EACH remaining step using the TaskCreate tool. Mark each task in_progress when you start it and completed when you finish it. If skipping Steps 4-7, mark them as completed with no work.

Step 1 - Map the project

Run BOTH scans IN PARALLEL — a single turn with both tool calls. They are independent (one reads the project files, the other queries the database), so do NOT wait for one before calling the other:

1. python3 "${CLAUDE_SKILL_DIR}/scan_project.py" "<project_directory>" — filesystem scan.
2. analyze_project_db(connection_name) — database hints (lookup joins, staging-vs-raw gaps, parent-child driving tables).

Read the ENTIRE output of BOTH. Record:

• STUBS TO REWRITE
• MODELS TO BUILD
• DEPENDENCIES
• REQUIRED COLUMNS
• AVAILABLE MACROS (with definitions)

If <project_dir>/prebuild_state.md ends with STATE: COMPLETE, read it and do not recapture or overwrite it because retries must retain original evidence. A file marked STATE: INCOMPLETE may be recaptured because the materialization gate prevented database overwrite.

Before the first dbt command, call list_tables(connection_name) when no complete pre-build state exists because a build can overwrite supplied relations.

When no complete state exists, create <project_dir>/prebuild_state.md with STATE: INCOMPLETE. For each supplied model, record CREATE, REWRITE STUB, MODIFY, or VERIFY plus the verbatim task-authorized change because CHECK 5 must distinguish requested changes from scope expansion. Read its SQL/YML config and dbt_project.yml; record its SQL path, materialization, database, schema, and alias because verification needs the original node identity.

Match each configured relation against list_tables. Record ABSENT when no relation exists. When a relation exists, call explore_columns(connection_name, table="<qualified_relation>", include_samples=true, include_stats=true) because types and values establish its current contract. For each column, add COUNT(*) - COUNT(<quoted column>) AS <quoted column_name_nulls> to one query_database query because NULL coverage and zero rows must be explicit.

Record every static ref(), source(), and direct physical relation in FROM or JOIN within supplied models and their dependency closure because bindings are not always variable-driven. Record each original relation expression and its resolved physical relation. Record each source's database, schema, identifier, enabled value, and resolved relation. Search the same files for var( and env_var(; record each expression, its project value or declared default, every binding field that consumes it, and its resolved relation because indirect bindings can change population.

After every binding is recorded or explicitly ABSENT, append STATE: COMPLETE because retries need an atomic completion marker. Do not run the first materializing dbt command before that marker exists because missing original evidence cannot be recovered after overwrite.

If the task describes a runtime bug (type mismatch, wrong values, broken output), run dbt run --select <pre_existing_model> 2>&1 | tail -50 on the affected pre-existing model - dbt parse passing does NOT mean the model produces correct output.

Then create tasks for Steps 2–8:

• "Step 2: Load supporting skills"
• "Step 3: Validate project"
• "Step 4: Discover macros"
• "Step 5: Research (data exploration)"
• "Step 6: Write technical spec"
• "Step 7: Write and build models"
• "Step 8: Verify and fix"

Step 2 - Load supporting skills

Load ALL THREE skills now - they contain rules needed for writing AND verifying models. Classify the domain from the task instruction and source table names in the Step 1 scan output.

1. /signalpilot-dbt:dbt-write
2. The SQL skill for your database (e.g. /signalpilot-dbt:duckdb-sql)
3. The domain skill matching the task:

• Revenue/invoices/ledgers/fiscal → /signalpilot-dbt:domain-financial
• Campaigns/clicks/email/SMS/messaging/attribution → /signalpilot-dbt:domain-marketing
• Events/sessions/features/guides/analytics → /signalpilot-dbt:domain-product
• Employees/hiring/issues/SCD/tickets → /signalpilot-dbt:domain-hr
• Orders/products/discounts/returns/charges/spend → /signalpilot-dbt:domain-ecommerce
• Movies/sports/credits/content → /signalpilot-dbt:domain-media
• Clinical/patients/encounters/diagnoses/costs → /signalpilot-dbt:domain-healthcare

Also load these conditional skills when the task or project requires them: 4. If the task mentions "test", "unit test", "add tests", "verify logic", "verify the logic", "check logic", "check correctness", OR the Step 1 scan found unit_tests: blocks in YML: → /signalpilot-dbt:dbt-testing Load this skill BEFORE writing any test YAML - unit tests use a different YAML structure than schema tests. 5. If the task mentions "snapshot", "SCD", "slowly changing", "track changes", "history", OR the Step 1 scan found a snapshots/ directory: → /signalpilot-dbt:dbt-snapshots 6. If the task mentions "version", "v2", "backward compatible", OR the Step 1 scan found versions: blocks in YML: → /signalpilot-dbt:dbt-versioning

Do NOT skip this step. These skills contain rules that apply to verification (Step 8), not just writing.

Step 3 - Validate and fix stale upstreams

Run python3 "${CLAUDE_SKILL_DIR}/validate_project.py" "<project_directory>". If errors, fix them before proceeding.

MANDATORY: if the Step 1 scan flagged current_date or now() hazards in PRE-EXISTING models, rebuild those models NOW: dbt run --select <flagged_model1> <flagged_model2> These models contain stale data from a prior run. Without rebuilding, all downstream models inherit wrong row counts. Do NOT skip this even if the knowledge base has entries - KB cannot fix stale materialized data.

Do NOT use +. If no hazards flagged, skip the rebuild.

Step 4 - Discover project macros

Read the AVAILABLE MACROS section from Step 1 output. For each macro NOT referenced by any existing complete model:

1. Read its definition - it is printed in the scan output.
2. Identify what column it produces. extract_hour(created_at) produces hour_created_at. normalize_timestamp(created_at) produces normalized_created_at. Record a macro output only when its definition returns an output expression and every required input exists in this model's established upstreams because helper macros do not define columns by themselves.

Step 5 - Research (data exploration)

Apply source-selection defaults to CREATE and REWRITE STUB work. For MODIFY and VERIFY work, preserve every binding recorded in prebuild_state.md unless the verbatim task authorizes its change because research defaults do not redefine an existing model.

For EACH model that needs SQL, gather the facts to write it correctly:

1. Driving table - the Step 1 scan's AGGREGATION DRIVING TABLE hint flags a parent whose rows have no matching children. Classify by the model's METRICS, then FOLLOW the result - this is binding; do NOT override based on the task phrase "aggregate X by Y" (that names what to summarize, not the FROM clause):

• If every child metric is a COUNT or SUM (e.g. total_parts, total_orders) - meaningful as 0 for a childless parent - KEEP childless rows: drive FROM the parent, LEFT JOIN children.
• If any metric is a RATIO, AVERAGE, or SCORE that divides by the child count (e.g. NPS = (positive − negative) / total, avg_rating) - undefined (division by zero) for a childless parent - DROP childless rows: drive FROM the child aggregation, INNER JOIN the entity. LEFT JOIN all other upstreams.

2. Cardinalities - run query_database with SELECT COUNT(*) and SELECT COUNT(DISTINCT <key>) on each upstream to confirm grain.
3. Contract - read the model's YML entry for column names, tests, and descriptions. Call map_columns with upstream_tables="<physical_relation1>,<physical_relation2>" before writing SQL because source projections must be visible before materialization. Qualify an AMBIGUOUS relation or correct a NOT FOUND name and rerun the call because unresolved upstreams cannot support construction. When map_columns reports POSITIONAL ALIGNMENT or POSITIONAL DECORATION CANDIDATES, those are hints about which source column each YML name maps to. Confirm each pair against YML descriptions, sibling SQL, and sample values, and record the confirmed mapping in your Step 5 notes for the Step 6 spec. If one source table covers every YML column, the scan flags no LOOKUP JOINS, and no AGGREGATION DRIVING TABLE hint applies, build from that single table with no join - a second table would only add or drop rows.
4. Sibling patterns - read sibling SQL and the YML in dbt_packages/ for JOIN types, CASE WHEN predicates, and categorical vocabulary. The test data is often too sparse to reveal every value; the package YML descriptions are not.
5. Categorical values - run query_database with SELECT DISTINCT <col> on status/flag/type columns before writing CASE WHEN. The values determine which rows are purchases vs returns vs cancelled.

CTE extraction override: If you are extracting CTEs from a parent model, read the parent model's outermost final SELECT - the table in its FROM clause is the true spine. Run SELECT COUNT(DISTINCT <key>) on both the spine and the aggregation source. If counts differ, use the spine.

Step 6 - Write technical spec

Load the knowledge-base skill: /signalpilot-dbt:knowledge-base

This skill writes <project_dir>/technical_spec.md - a structured plan that distills your Step 5 research into decisions about sources, joins, filters, expressions, and grain for every model.

If technical_spec.md already exists (retry), the skill reads it and skips re-research. Follow the skill's instructions - it defines the spec format, quality rules, and update protocol.

STOP this step when the spec file is written and every model has all seven required fields (see knowledge-base skill Section 3).

Step 7 - Write and Build ALL Models

Read <project_dir>/technical_spec.md from Step 6. Write SQL for each model following the spec's build order, sources, joins, and expressions.

For each model in dependency order:

1. Match YML column names EXACTLY where they exist.
2. Copy JOIN types and aggregation patterns from sibling analysis. Before finalizing this model, re-check the Step 1 scan's LOOKUP JOINS section and the sibling's date/timestamp handling for this model's columns - these two are the easiest to skip.
3. Read the YML description for date boundaries. If it says "to the current date" or "to today", add WHERE date_col <= current_date.
4. Read the YML description for transformation rules. If it states explicit logic ("categorized as X if Y"), implement that logic.
5. Write the SQL file.

Do NOT rewrite pre-existing SQL files from scratch. For bug-fix tasks, EDIT the existing SQL minimally - change only the broken expression (e.g., add a CAST, fix an aggregation function). Keep all existing JOINs, CTEs, column aliases, and WHERE clauses intact. Rewriting from scratch drops logic the original author put there (lookup JOINs, filters, aliases) that you may not notice is missing.

After ALL SQL files are written, run python3 "${CLAUDE_SKILL_DIR}/inspect_model_state.py" "<project_directory>" <model1> <model2> because inline overrides and incremental guards can change first-build output. Resolve each FAIL before building because it marks an unusable model state. Review each REVIEW or WARN against the task, YML, and dbt_project.yml because those files establish materialization intent.

Preserve every ref(), source(), direct-relation expression, source identifier, schema, database, alias, enabled flag, and resolved relation recorded in prebuild_state.md unless the verbatim task requests that binding change because a successful empty input is valid project state. Do not redirect a configured source merely to produce rows.

Build ONLY the models you wrote: dbt run --select <model1> <model2> <model3> (NO + prefix).

The + prefix rebuilds upstream models you did NOT write, destroying surrogate key assignments and FK relationships in pre-existing models. Try without + first. If a model fails because an upstream dependency is not yet materialized, THEN add + for that specific model only.

If dbt run fails on ANY model - including package models in dbt_packages/ - load the dbt-debugging skill and fix the error. A failed upstream build blocks downstream materialization; a successful zero-row relation does not.

Do NOT run a bare dbt run - it rebuilds ALL models including pre-existing ones.

Step 8 - Verify and Fix

Run query_database with SELECT 1 because verification requires a live connection. Retry the call after a connection error.

Dispatch verifier and value-verifier in parallel because structure and values require independent evidence. Pass the verbatim task, project directory, connection name, supplied model names, prebuild_state.md, and technical_spec.md. Do not add expected values, column definitions, SQL interpretations, or preferred fixes beyond the verbatim task and project artifacts because the verifiers must derive their own evidence.

Reject a report whose summary disagrees with its check statuses because contradictory reports cannot authorize changes. Reject a report missing a required per-model status, FAIL recommendation, or NEEDS request because incomplete reports cannot authorize changes. Redispatch a verifier whose report contains NEEDS with the requested project or database evidence because incomplete diagnosis cannot authorize changes. Do not edit files while any NEEDS remains.

Reproduce each FAIL's cited evidence before changing code because verifier inferences are not implementation authority. Redispatch the responsible verifier with the contrary evidence when the cited result does not reproduce. Re-enter Step 6 when the failure reproduces because technical_spec.md must record the supported correction before implementation. Acquire evidence named by a FAIL recommendation in Step 6 before recording the correction because Step 7 cannot choose an unsupported value.

Apply the smallest evidence-backed change in Step 7 because unrelated edits broaden regression risk. Rebuild each changed model with Step 7's existing command. Redispatch both verifiers for every changed model because SQL changes invalidate both reports.

STOP when every applicable check is PASS and each inapplicable check is N/A. Do not modify project files after this condition because verification is complete.

Output Shape - Read YML Description BEFORE Writing SQL

Extract from description: field:

• ENTITY: "for each customer/driver/order" → one row per qualifying entity
• QUALIFIER: "due to returned items" / "with at least one order" → filter or INNER JOIN
• TEMPORAL SCOPE: "rolling window", "MoM", "WoW", or "month-over-month" in the description → ONE output date (latest), not all historical dates. Filter with WHERE date_col = (SELECT MAX(date_col) FROM source).
• DATE BOUNDARY: "from X to the current date" or "to today" in the description → cap the output with WHERE date_col <= current_date. When crossing a calendar spine with entities (e.g. one row per guide per day), add the cap to the cross join: ON spine.date_day >= entity.created_on AND spine.date_day <= current_date.
• PERIOD-OVER-PERIOD: If the description mentions MoM, WoW, YoY comparisons AND you are writing this model from scratch (stub/missing), the comparison column must be CAST(NULL AS DOUBLE) - see rule below.

How to read YML descriptions: Descriptions tell you what the data MEANS, not what code to write. Use them to:

• Identify which source columns to use
• Understand the business meaning of each column
• Pick the right aggregation logic

But do NOT treat descriptions as literal computation instructions. After reading the description, always verify your logic against the actual source data.

Write at top of SQL: -- EXPECTED SHAPE: <row count or formula> - REASON: <quote>

Incremental Models and Period-Over-Period Columns

When a dbt project uses materialized="incremental" models, the project is designed to accumulate state over multiple runs. On a first run (full refresh, no prior state), incremental models build from scratch.

If you are writing a new model that includes period-over-period metrics (MoM, WoW, YoY) and the project has not been run incrementally before:

1. Output rows for the latest date only: WHERE date_col = (SELECT MAX(date_col) FROM source)
2. Period-over-period columns must be CAST(NULL AS DOUBLE) - there is no prior aggregated state to compare against.

This rule overrides sibling patterns. Even if a sibling model computes period-over-period values with LAG/LEAD, your new model MUST use CAST(NULL AS DOUBLE). The sibling is incremental and accumulates history over multiple runs - your table-materialized model has no prior state.

Debugging incremental models with missing rows: inspect the boundary predicate first. If the model uses WHERE date_col > (SELECT MAX(date_col) FROM {{ this }}), rows sharing the MAX date are silently dropped - change > to >= and add a unique_key to handle deduplication. Do NOT use --full-refresh as a fix - it bypasses the incremental path that must remain correct.

What to Trust in YML

Trust YML for: column names (exact match required), column descriptions (what each column represents), ref dependencies (what tables to join).

Google Sheets and CSV Sources

Google Sheets and CSV connectors produce trailing empty rows - entire rows where every column is NULL. When UNION ALL combines multiple sheets, identical empty rows multiply across sources.

Do NOT filter these with WHERE col IS NOT NULL - that removes valid rows with partial NULLs. Use SELECT DISTINCT after the UNION ALL to collapse identical empty rows to one.

Do NOT trust YML for: grain/row count. YML unique and not_null tests are assertions that may be aspirational or wrong.

Derive the grain from these signals (in priority order):

1. Unique key structure: If the YML defines a unique/surrogate key, examine its composition
2. Column list: The columns themselves reveal the grain
3. Upstream model grain: Check existing upstream models
4. Source cardinality: Query source tables to check expected row count
5. Sibling model row counts: Check complete models at the same level

Do NOT pre-deduplicate lookup tables before joining - fan-out from duplicate keys may be valid data. See dbt-write Section 3 for when fan-out is correct vs when to deduplicate.

Validate Project (Standalone)

Run: python3 "${CLAUDE_SKILL_DIR}/validate_project.py" "<project_dir>"

This runs dbt parse and produces a structured report of errors, warnings, and orphan patches (yml-defined models with no .sql file). Use this as the Step 3 validation when the MCP server is not available. Accepts an optional second argument for timeout in seconds (default 60).

Rules

• For code-review or multiple-choice analysis tasks: trace every output column back through all CTEs to its source. A computed-but-unused column (calculated but never SELECTed) is a defect. Do not assert a data quality problem without querying the source to confirm it exists.
• NEVER run dbt commands with run_in_background or & - dbt holds a database write lock while running
• Do NOT modify .yml files unless fixing a missing schema: in a source definition
• Do NOT guess column names - use the YML contract as source of truth. Exception: if a pre-existing table or sibling model shows the column contains a display value (name, label) rather than a raw FK, follow that pattern (see dbt-write §3).
• Do NOT install external packages - all dbt packages are pre-bundled in dbt_packages/