altimate-data-engineering-skills

# Altimate Data Engineering Skills > Skill by [ara.so](https://ara.so) — Data Skills collection. Altimate Data Engineering Skills is a collection of Claude Code skills that encode the workflows and best practices of experienced analytics engineers. These skills transform Claude from a code generator into a capable data engineering assistant by teaching **how to approach tasks**, not just what syntax to use. The project demonstrates 53% accuracy on ADE-bench (43 real-world dbt tasks), 3x improvement on model creation tasks, and 84% pass rate on Snowflake query optimization. ## What This Project Does Data Engineering Skills provides: - **7 dbt skills**: Model creation, debugging, testing, documentation, migration, refactoring, incremental models - **3 Snowflake skills**: Cost analysis, query optimization by ID, query optimization by text - **1 delegation skill**: Hand off complex tasks to altimate-code CLI tool - **Workflow automation**: Skills trigger automatically based on user intent - **Best practices**: Encoded patterns from experienced analytics engineers Skills are markdown files with YAML frontmatter that define trigger conditions and step-by-step workflows. ## Installation ### Installing Skills in Claude Code ```bash # Add the marketplace plugin /plugin marketplace add AltimateAI/data-engineering-skills # Install all skills /plugin install dbt-skills@data-engineering-skills /plugin install snowflake-skills@data-engineering-skills /plugin install altimate-code@data-engineering-skills ``` ### Installing Kits Kits bundle skills, MCP servers, and instructions: ```bash # Install altimate-code CLI (required for kits) npm install -g altimate-code # Install kit system altimate-code kit install AltimateAI/data-engineering-skills # Activate the dbt-snowflake kit altimate-code kit activate dbt-snowflake # Check status altimate-code kit status ``` ### Manual Installation Clone and reference skills directly: ```bash git clone https://github.com/AltimateAI/data-engineering-skills.git cd data-engineering-skills ``` ## Available Skills ### dbt Skills **creating-dbt-models**: Creates new dbt models following project conventions - Discovers existing patterns before writing - Runs `dbt build` after creation (not just compile) - Verifies output with `dbt show` - Handles staging, intermediate, and mart models **debugging-dbt-errors**: Troubleshoots dbt compilation and runtime errors - Reads full error messages carefully - Checks upstream dependencies - Applies fixes and rebuilds - Stops after 3 failed attempts to reassess **testing-dbt-models**: Adds schema tests to models - Studies existing test patterns in project - Matches project testing style - Covers uniqueness, not_null, relationships, accepted_values **documenting-dbt-models**: Generates dbt model documentation - Analyzes model logic and columns - Creates descriptions for models and fields - Follows project documentation patterns **migrating-sql-to-dbt**: Converts legacy SQL to dbt models - Parses raw SQL queries - Creates proper dbt model structure - Handles CTEs, refs, and sources **refactoring-dbt-models**: Safely restructures dbt models - Tracks all dependencies before changes - Applies refactoring - Verifies downstream models still work **developing-incremental-models**: Creates incremental dbt models - Selects appropriate strategy (append, merge, delete+insert) - Designs proper unique_key - Handles late-arriving data and edge cases ### Snowflake Skills **finding-expensive-queries**: Identifies costly Snowflake queries - Finds queries by cost, time, or data scanned - Ranks by impact - Provides query IDs for optimization **optimizing-query-by-id**: Optimizes using Snowflake query history ID - Retrieves query profile from ID - Applies optimization patterns - Validates semantic preservation **optimizing-query-text**: Optimizes raw SQL query text - Profiles query execution - Identifies bottlenecks (scans, joins, aggregations) - Applies anti-pattern fixes - Tests performance improvement ### Delegation Skill **altimate-code**: Delegates complex data tasks to altimate-code CLI - Verifies altimate-code installation - Invokes `altimate-code run --yolo` non-interactively - Reads output file and summarizes results - Requires: `npm install -g altimate-code` (Node 20+) ## Skill Structure Skills are markdown files with YAML frontmatter: ```yaml --- name: creating-dbt-models description: | Guide for creating dbt models. ALWAYS use this skill when: (1) Creating ANY new model (staging, intermediate, mart) (2) Task mentions "create", "build", "add" with model/table (3) Modifying model logic or columns --- ``` Followed by workflow instructions: ```markdown # dbt Model Development **Read before you write. Build after you write. Verify your output.** ## Critical Rules 1. ALWAYS run `dbt build` after creating models - compile is NOT enough 2. ALWAYS verify output after build using `dbt show` 3. If build fails 3+ times, stop and reassess your approach ## Workflow 1. Discover Conventions - Check existing models in same layer (staging/intermediate/mart) - Note naming patterns, CTE style, column ordering 2. Write Model - Follow discovered patterns - Use proper refs and sources 3. Build and Verify ```bash dbt build --select model_name dbt show --select model_name --limit 10 ``` ``` ## Usage Examples ### Creating a dbt Model ```yaml # User request: "Create a staging model for raw customers data" # Skill triggers: creating-dbt-models # AI workflow: # 1. Checks models/staging/ for naming patterns # 2. Creates models/staging/stg_customers.sql # 3. Runs: dbt build --select stg_customers # 4. Verifies: dbt show --select stg_customers ``` Example model created: ```sql -- models/staging/stg_customers.sql with source as ( select * from {{ source('jaffle_shop', 'customers') }} ), renamed as ( select id as customer_id, first_name, last_name, email, created_at from source ) select * from renamed ``` ### Debugging dbt Errors ```yaml # User request: "Fix this error: Compilation Error in model customers (models/customers.sql)" # Skill triggers: debugging-dbt-errors # AI workflow: # 1. Reads full error message # 2. Checks upstream model dependencies # 3. Identifies issue (e.g., missing ref) # 4. Applies fix # 5. Rebuilds: dbt build --select customers ``` ### Optimizing Snowflake Query ```yaml # User request: "This query is taking 5 minutes, can you optimize it?" # Skill triggers: optimizing-query-text # AI workflow: # 1. Profiles query (EXPLAIN, execution stats) # 2. Identifies bottlenecks (table scans, inefficient joins) # 3. Applies optimizations (clustering, filters, join order) # 4. Tests and measures improvement ``` Example optimization: ```sql -- Before (slow) SELECT o.order_id, c.customer_name, SUM(oi.amount) as total FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id WHERE o.order_date >= '2024-01-01' GROUP BY 1, 2; -- After (optimized) SELECT o.order_id, c.customer_name, SUM(oi.amount) as total FROM orders o INNER JOIN order_items oi ON o.order_id = oi.order_id LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date >= '2024-01-01' AND o.order_date < '2024-12-31' -- Added upper bound for partition pruning GROUP BY 1, 2; -- Assumes orders table is clustered by order_date ``` ### Using Kits ```bash # Activate dbt-snowflake kit for a project cd /path/to/dbt-project altimate-code kit activate dbt-snowflake # This configures: # - All dbt and Snowflake skills # - MCP server for dbt (live project access) # - Project-specific instructions # Deactivate when done altimate-code kit deactivate ``` Kit configuration (`.altimate/kits/active.yaml`): ```yaml kit: dbt-snowflake version: 1.0.0 skills: - creating-dbt-models - debugging-dbt-errors - testing-dbt-models - documenting-dbt-models - migrating-sql-to-dbt - refactoring-dbt-models - developing-incremental-models - finding-expensive-queries - optimizing-query-by-id - optimizing-query-text mcp_servers: - dbt instructions: | You are working on a dbt + Snowflake project. Always check project conventions before creating models. Run dbt build (not compile) after creating models. ``` ## Configuration ### Skill Configuration Skills are auto-triggered based on user requests. No explicit configuration needed. ### MCP Integration Skills work best with Altimate MCP server for live project access: ```json // claude_desktop_config.json or similar { "mcpServers": { "altimate-dbt": { "command": "npx", "args": ["-y", "@altimate/mcp-server-dbt"], "env": { "DBT_PROJECT_DIR": "/path/to/dbt/project", "DBT_PROFILES_DIR": "/path/to/.dbt" } } } } ``` MCP tools available to skills: - `dbt_project_info`: Project structure, model list, sources - `dbt_model_details`: Column types, dependencies, compiled SQL - `dbt_compile`: Compile models without CLI - `snowflake_query_history`: Recent query executions and stats - `snowflake_table_stats`: Row counts, clustering info ## Common Patterns ### Pattern 1: Multi-Step Model Creation ```yaml # User: "Create a mart model for monthly revenue by customer" # Skills auto-chains: # 1. creating-dbt-models: Creates initial model # 2. testing-dbt-models: Adds schema tests # 3. documenting-dbt-models: Adds documentation ``` ### Pattern 2: Debug-Fix-Verify Loop ```yaml # User: "My model won't compile" # Skill: debugging-dbt-errors # Workflow: # 1. Read error # 2. Check dependencies # 3. Apply fix # 4. Rebuild # 5. If still fails, repeat up to 3x # 6. If 3x fails, stop and reassess approach ``` ### Pattern 3: Safe Refactoring ```yaml # User: "Break this large model into smaller ones" # Skill: refactoring-dbt-models # Workflow: # 1. Map all downstream dependencies # 2. Create new intermediate models # 3. Update refs in downstream models # 4. Run dbt build on entire DAG # 5. Verify no breakage ``` ### Pattern 4: Incremental Model Development ```sql -- Pattern: merge strategy with unique_key {{ config( materialized='incremental', unique_key='event_id', merge_update_columns=['status', 'updated_at'] ) }} select event_id, user_id, event_type, status, created_at, updated_at from {{ source('events', 'raw_events') }} {% if is_incremental() %} where updated_at > (select max(updated_at) from {{ this }}) {% endif %} ``` ### Pattern 5: Query Optimization Workflow ```python # Skill: optimizing-query-text # Steps encoded: # 1. Profile query # 2. Identify bottleneck type: # - Full table scan → Add filters/clustering # - Inefficient join → Reorder, change type # - Large aggregation → Pre-aggregate or partition # 3. Apply pattern-based fix # 4. Validate semantics unchanged # 5. Measure improvement ``` ## Troubleshooting ### Skills Not Triggering **Problem**: Skills don't activate for your request **Solution**: - Be explicit: "Create a dbt model" not "make a model" - Check skill installation: `/plugin list` - Review trigger phrases in skill YAML - Use exact trigger phrases from skill description ### dbt Build Failures **Problem**: Models fail to build after creation **Solution**: - Skill will auto-retry up to 3 times - After 3 failures, skill stops to reassess - Check `dbt debug` for connection issues - Verify refs and sources exist - Check for SQL syntax errors ### MCP Server Not Connected **Problem**: Skills can't access live project data **Solution**: ```bash # Check MCP server config cat claude_desktop_config.json | grep altimate # Verify dbt project path export DBT_PROJECT_DIR=/correct/path export DBT_PROFILES_DIR=/correct/.dbt/path # Restart Claude Code ``` ### altimate-code Delegation Fails **Problem**: "altimate-code not found" error **Solution**: ```bash # Install altimate-code CLI npm install -g altimate-code # Verify installation altimate-code --version # Check Node version (requires 20+) node --version ``` ### Query Optimization No Improvement **Problem**: Optimized query performs the same **Solution**: - Skill checks for anti-patterns but can't fix all issues - May need manual clustering/indexing setup - Check if bottleneck is data volume (consider aggregation) - Review Snowflake warehouse size - Use `finding-expensive-queries` to compare before/after ### Kit Activation Issues **Problem**: Kit won't activate **Solution**: ```bash # Check kit is installed altimate-code kit list # Reinstall if needed altimate-code kit install AltimateAI/data-engineering-skills # Check for conflicting active kits altimate-code kit status # Deactivate others first altimate-code kit deactivate ``` ## Performance Benchmark results (ADE-bench, 43 real-world dbt tasks): - **Baseline Claude**: 46.5% accuracy (20/43 tasks) - **Claude + Skills**: 53.5% accuracy (23/43 tasks) - **Model Creation**: 3x improvement (40% → 65%) Snowflake optimization (TPC-H SF1000, 62 queries): - **Baseline**: 77.4% pass rate, 4.7% avg improvement - **With Skills**: 83.9% pass rate, 16.8% avg improvement (3.6x better) ## Additional Resources - Documentation: https://docs.myaltimate.com/ - ADE-bench Framework: https://github.com/dbt-labs/ade-bench - altimate-code CLI: https://github.com/AltimateAI/altimate-code - dbt Slack: https://getdbt.slack.com/archives/C05KPDGRMDW - Contact: https://app.myaltimate.com/contactus