snowflake-dbt-model-refactoring
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.
AltimateAI · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add AltimateAI/data-engineering-skills --skill 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-analyzelookups withdbt ls --select +<target>+ --output json(downstream nodes) plusdbt docs generate+ readingtarget/catalog.jsonandtarget/manifest.jsonfor 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-paritywith the SQL in Phase 4 run directly against the warehouse (via the dbt adapter,snowsql, or any Snowflake client). - Replace
sql-reviewwithsqlfluff lintor project-standard linter. - Replace
cost-report/query-optimizewith direct queries againstSNOWFLAKE.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).
- Target model name (required). If missing, ask.
- 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:
- Static code metrics (single bash pass; do not use one call per metric):
- Total lines,
joincount, 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.
- Total lines,
- 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