| license | Apache-2.0 |
| name | dbt-analytics-engineer |
| description | dbt Core/Cloud data transformations, testing, documentation, and CI/CD. Activate on: dbt, data transformation, analytics engineering, ref, source, staging model, mart, dbt test. NOT for: orchestration/scheduling (use airflow-dag-orchestrator), data warehouse tuning (use data-warehouse-optimizer). |
| allowed-tools | Read,Write,Edit,Bash(npm:*,npx:*,dbt:*,python:*) |
| category | Data & Analytics |
| tags | ["dbt","analytics-engineering","data-transformation","sql","data-modeling"] |
| pairs-with | [{"skill":"data-warehouse-optimizer","reason":"dbt models run on warehouses that need optimization"},{"skill":"data-quality-guardian","reason":"dbt tests are a core data quality enforcement mechanism"},{"skill":"dimensional-modeler","reason":"dbt marts implement dimensional models"}] |
dbt Analytics Engineer
Build, test, and document data transformations using dbt Core/Cloud with modern analytics engineering practices.
Decision Points
Materialization Selection Strategy
Model Size & Query Pattern → Materialization Choice
├── < 1M rows, rarely queried
│ └── VIEW (ephemeral if only intermediate)
├── 1M-10M rows, daily queries
│ └── TABLE (full refresh nightly)
├── > 10M rows, frequent queries
│ ├── Append-only data → INCREMENTAL (append strategy)
│ ├── Updates/deletes → INCREMENTAL (merge strategy)
│ └── Complex joins/aggregations → TABLE with incremental source prep
└── Dev/staging environment
└── Always VIEW (cost optimization)
Model Layer Assignment
Data Characteristics → Layer Placement
├── Raw source mapping (1:1)
│ └── staging/ (stg_ prefix, light cleaning only)
├── Business logic, joins, calculations
│ └── intermediate/ (int_ prefix, reusable components)
├── Final consumption ready
│ ├── Analytics/BI → marts/ (fct_, dim_ prefixes)
│ └── ML features → features/ (fea_ prefix)
└── One-off analysis
└── analysis/ (not materialized)
Testing Strategy Selection
Model Criticality & Data Patterns → Test Coverage
├── Core business metrics (revenue, customers)
│ └── COMPREHENSIVE: unique, not_null, relationships, custom business rules
├── Supporting dimensions
│ └── STANDARD: unique, not_null, accepted_values
├── Intermediate models
│ └── MINIMAL: not_null on join keys, row count > 0
└── Development models
└── BASIC: not_null on primary key only
Failure Modes
1. Circular Reference Death Spiral
Detection Rule: dbt compile fails with "Cycle detected" error
Symptoms: Model A refs Model B, which refs Model C, which refs Model A
Fix: Break cycle by moving shared logic to new intermediate model that both reference
2. Incremental Model State Corruption
Detection Rule: dbt run succeeds but row counts decrease unexpectedly on incremental models
Symptoms: Late-arriving data missed, duplicates created, or filter logic excludes existing records
Fix: dbt run --full-refresh to rebuild, then fix filter conditions and unique_key configuration
3. Test Suite Performance Collapse
Detection Rule: dbt test takes >30min or times out on warehouse
Symptoms: Tests query entire fact tables without limits, complex join tests on unindexed columns
Fix: Add limit: 100000 to expensive tests, use for CI
