dbt-data-transformation_skill

Overview

This skill is a complete guide to dbt data transformation covering models, tests, documentation, incremental builds, macros, packages, and production workflows. It provides practical patterns for developing modular SQL, enforcing data quality, documenting lineage, and operating dbt at scale. Use it to standardize ELT development and deploy reliable analytics pipelines.

How this skill works

The skill inspects and explains core dbt concepts: models (SELECT-only SQL files), sources, seeds, snapshots, macros, and materializations (view, table, incremental, ephemeral). It describes ref() and source() usage, testing strategies (schema and custom tests), documentation blocks, and incremental build strategies with example configs. It also outlines production workflows including CI/CD, orchestration, and package management.

When to use it

• Building modular, testable data transformation pipelines for analytics
• Implementing ELT workflows and migrating legacy ETL to dbt
• Creating incremental models for large or time-series datasets
• Enforcing data quality and data contracts with automated tests
• Documenting models, lineage, and business metrics for consumers
• Orchestrating dbt runs with Airflow, Dagster, or dbt Cloud

Best practices

• Organize models by staging, intermediate, and marts using clear prefixes (stg_, int_, fct_, dim_)
• Use ref() for model dependencies and source() to document raw tables
• Prefer incremental materializations for large datasets and ephemeral for reusable CTE logic
• Define schema tests and severity levels; add custom SQL tests for business rules
• Write column-level docs and docs blocks; generate and serve dbt docs for visibility
• Adopt CI/CD, run tests on pull requests, and schedule incremental builds in production

Example use cases

• Create staging models that mirror raw tables, then build cleaned marts and facts for BI
• Implement an incremental fct_orders table with merge or delete+insert strategies to handle late-arriving updates
• Use dbt_utils package tests to validate complex constraints like unique combinations and sequential values
• Document metrics and exposures for downstream dashboards and ML features, and publish docs site
• Run dbt in CI to compile models, execute tests, and deploy artifacts to a production schema