dbt-analytics-engineer

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