跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
其他技能

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-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
阅读完整来源 ↗