/snowflake-dbt-model-refactoring

name: snowflake-dbt-model-refactoring
description: >
  Refactor a bloated dbt model on Snowflake safely and produce a quantified
  before/after report that explains **which qualities improved and why** —
  not just raw deltas. Extends the standard refactor flow with (1) downstream
  impact analysis via `dbt-analyze`, (2) row-count + column-hash parity
  validation via `data-parity`, (3) a report that lands a Bottleneck Summary
  and an "Outcome axes improved" table (readability, maintainability,
  performance, cost, governance, best-practices), and (4) an optional
  post-refactor warehouse benchmark against real Snowflake query history
  offered at the end. Use when the user says "refactor this model", "clean
  up this model", or points at a model that is oversized / over-joined /
  duplicated across the project — and the warehouse is Snowflake. Delegates
  to Altimate skills — does not reinvent them.

snowflake-dbt-model-refactoring

When to invoke

Trigger this skill when any of the following is true for a target dbt model **on a Snowflake-backed dbt project**:

• More than ~200 lines
• More than 5 joins in a single query block
• A CTE longer than ~50 lines
• Logic duplicated across 3+ models (payment pivots, RFM buckets, etc.)
• Hardcoded lists / thresholds repeated inline
• Downstream consumers exist and would be affected by structural changes

If the project targets BigQuery, Redshift, or Databricks, use the generic `refactoring-dbt-models` skill instead — Phase 7's warehouse benchmark is Snowflake-specific.

Non-goals

• Do **not** rewrite for performance alone — that is the job of `query-optimize`
• Do **not** change model outputs — refactoring must preserve row count and per-row values
• Do **not** touch models the user did not point at, even if they look messy

Prerequisites & invocation

This skill delegates specialized sub-tasks (lineage, parity, SQL review, cost attribution, etc.) that are not themselves packaged with `snowflake-skills`. Two valid invocation paths — pick the one that matches the user's environment:

**Preferred — `altimate-code`** (bundled in the `dbt-snowflake` kit). Before using any delegated-task line below, verify it is available:

command -v altimate-code

If present, delegate each sub-task via its non-interactive runner:

altimate-code run "<task description>" --yolo \
  --output /tmp/altimate-result.md --dir "$(pwd)"

Then read `/tmp/altimate-result.md` and use the result. See the `altimate-code` skill for the full flag reference and install guidance.

**Fallback — plain dbt + direct SQL** when `altimate-code` is not installed and the user does not want to install it. In that case:

• Replace `dbt-analyze` lookups with `dbt ls --select +<target>+ --output json`

(downstream nodes) plus `dbt docs generate` + reading `target/catalog.json` and `target/manifest.json` for the column-usage map. Note: this is coarser than altimate-code's column-level lineage.

• Replace build/compile delegation with plain `dbt build` / `dbt compile`.
• Replace `data-parity` with the SQL in Phase 4 run directly against the

warehouse (via the dbt adapter, `snowsql`, or any Snowflake client).

• Replace `sql-review` with `sqlfluff lint` or project-standard linter.
• Replace `cost-report` / `query-optimize` with direct queries against

`SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` + `QUERY_ATTRIBUTION_HISTORY` (sample SQL in Phase 7).

If **neither** `altimate-code` nor plain `dbt` is available, stop and tell the user — do not fabricate results from file reads alone.

Throughout the procedure below, every reference to a delegated tool (`dbt-analyze`, `data-parity`, `sql-review`, `cost-report`, `query-optimize`, `lineage-diff`) is a logical sub-task name. Execute it via `altimate-code run "..."` on the preferred path or the plain-dbt equivalent on the fallback path. Every reference to `altimate-dbt build --model X` on the preferred path translates to `altimate-code run "dbt build --select X ..."` or plain `dbt build --select X` on the fallback path.

Time budget

Target end-to-end runtime: **5–6 minutes**, hard ceiling **8 minutes**. The individual phases below have explicit budgets: Phase 1 ≤90s · Phase 2 ≤60s · Phase 3 ≤90s · Phase 4 ≤90s · Phase 5 ≤30s · Phase 6 ≤60s. If a phase exceeds its budget, stop the extra work and move on — the report is the deliverable, not a comprehensive audit.

Inputs to collect from the user (ask once, up front)

Ask **only** these two questions before proceeding. Do NOT ask about warehouse benchmarking here — that comes at the end (see Phase 7).

1. **Target model name** (required). If missing, ask. 2. **Parity strictness.** Ask: *"For the before/after parity check, do you want row-count parity only (fast) or full column-hash parity (slower, slightly higher warehouse cost)?"* Default: row-count + spot-check hash on the grain column and one numeric column.

Do not ask further questions until these two are answered. Then proceed end-to-end.

**Do NOT ask about saving the report.** The report is always displayed inline at the end. It is not saved to a file. Persistence is not part of this skill.

Procedure

Phase 1 — Baseline capture (target: ≤3 min)

Before touching anything, capture the baseline. Every number below feeds the "Before" column of the final report.

Run these steps **in parallel where possible** — most are independent:

1. **Static code metrics** (single bash pass; do not use one call per metric):

• Total lines, `join` count, CTE count and largest CTE size, count of

hardcoded literals repeated ≥3 times.

• Combine into one shell pipeline; do not re-read the file for each

metric. 2. **Downstream + column-usage map** — delegate (see tool-delegation cheatsheet):

• One call to fetch the downstream dbt subtree and the columns each

downstream dbt model consumes. Save the result. Columns produced by the target but not referenced by any downstream