dbt-workflow

name	dbt-workflow
description	Load at Step 1 before exploring the project. Covers Notion context gathering, output shape inference, incremental model handling, and what to trust in YML.
type	skill

dbt Workflow Skill — Explore and Plan

0. Notion Context — Gather Business Context BEFORE Planning

Check if .claude/notion-config.md exists in the working directory.

If config exists: Load the notion-context skill and run it. The skill searches configured Notion pages (meeting notes, product specs, data dictionaries) for context relevant to the current task. It returns a structured NOTION CONTEXT block with definitions, decisions, and constraints.

After gathering context:

1. Write the full NOTION CONTEXT block to notion_context.md in the working directory (the notion-verify subagent reads this after the build).
2. Keep the context in your working memory — reference it when making decisions about grain, joins, filters, and column logic in later steps.
3. If Notion context conflicts with YML, prefer YML for column names but prefer Notion for business logic (grain, filter rules, metric definitions).

If config does not exist: Skip this step — Notion is optional. Proceed to Section 1. If the user asks about Notion context later, point them to /notion-setup.

1. 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
• RANK CONSTRAINT: "top N" / "ranks the top N" → exactly N output rows. Filter with ROW_NUMBER() ... <= N using a deterministic tiebreaker (add primary key to ORDER BY). Do NOT use DENSE_RANK for filtering — it can return more than N rows.
• 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).
• 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 (e.g. "starting from first position on the grid" → use the grid column, not qualifying position)
• Understand the business meaning of each column
• Pick the right aggregation logic

But do NOT treat descriptions as literal computation instructions. They may describe steady-state behavior that doesn't apply on first build, or use imprecise language. After reading the description, always verify your logic against the actual source data — query the source tables to confirm which columns and values produce the expected result.

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

1b. Snapshot Reference Tables BEFORE Building

The starting database contains pre-computed reference tables with correct output. dbt run will overwrite them. Before your first dbt run, for each target model that already exists as a table in the database:

SELECT COUNT(*) FROM <model_name>

Record the row count in your -- EXPECTED SHAPE comment. If your rebuilt model's row count doesn't match after dbt run, you MUST diff against this reference to find which rows differ.

2. 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. Computing these from raw historical data would produce values that don't match the expected first-run output.

If the model SQL already exists (not a stub):

• Read the {% if is_incremental() %} block to understand the filter logic.
• The code outside that block runs on full refresh.

3. 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).

YML not_null tests on key/dimension columns (IDs, names, dates, categories) imply a WHERE col IS NOT NULL filter on input data. Do NOT implement this as an INNER JOIN — use an explicit WHERE clause. not_null on metric/aggregate columns (counts, averages, totals) just asserts the output shouldn't be NULL — don't filter inputs for those, fix the aggregation instead.

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

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

1. Unique key structure: If the YML defines a unique key or surrogate key column, examine what it's composed of. A key like concat(ticker, timestamp) means the grain is (ticker, timestamp) — not (ticker, date). The key tells you exactly what combination of values identifies one row.

2. Column list: The columns themselves reveal the grain. If a model has both a header-level key AND a detail-level key as separate columns, the grain is at the detail level.

3. Upstream model grain: Check existing upstream models that feed into yours. If bar_executions produces one row per (ticker, timestamp), your model that depends on it likely has the same or coarser grain — not finer.

4. Source cardinality: Before writing SQL, query the source tables to check how many rows your model should produce: SELECT COUNT(DISTINCT key_col) FROM source_table If your model produces dramatically fewer rows than upstream, your GROUP BY is too coarse.

5. Sibling model row counts: Check complete models at the same level.

Do NOT deduplicate with ROW_NUMBER to force a unique test to pass — if the data naturally has multiple rows per key, keep them all.