curiositech/dbt-analytics-engineer

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 dbt test --select config.severity:error for CI

4. Documentation Debt Explosion

Detection Rule: >50% of models/columns lack descriptions in dbt docs generate output Symptoms: New team members can't understand model purpose, business users avoid self-service Fix: Require description in CI checks, template model YAML generation, quarterly doc reviews

5. Macro Spaghetti Anti-Pattern

Detection Rule: Macros calling other macros >3 levels deep, single macro >100 lines Symptoms: Impossible to debug Jinja errors, changes break unexpected downstream models Fix: Flatten macro hierarchies, split complex macros, add macro unit tests with dbt-unit-testing

Worked Examples

Complete Source-to-Mart Workflow

Scenario: E-commerce orders data from Shopify API to revenue analytics

Step 1: Source Configuration

# models/staging/shopify/_sources.yml
sources:
  - name: shopify_raw
    freshness:
      warn_after: {count: 6, period: hour}
    tables:
      - name: orders
        description: "Raw orders from Shopify API"

Step 2: Staging Model (Decision: VIEW for <1M rows)

-- models/staging/shopify/stg_shopify__orders.sql
SELECT
  order_id::varchar as order_id,
  customer_id::varchar as customer_id,
  order_date::date as order_date,
  total_amount::decimal(10,2) as total_amount,
  status::varchar as status,
  _loaded_at::timestamp as _loaded_at
FROM {{ source('shopify_raw', 'orders') }}
WHERE status != 'cancelled'  -- Business rule: exclude cancelled

Step 3: Mart Model (Decision: INCREMENTAL for >10M rows, daily queries)

-- models/marts/finance/fct_revenue.sql
{{
  config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'  -- Handle late updates
  )
}}

SELECT
  order_id,
  customer_id,
  order_date,
  total_amount,
  status,
  _loaded_at
FROM {{ ref('stg_shopify__orders') }}

{% if is_incremental() %}
  -- Only process recent data
  WHERE _loaded_at > (SELECT max(_loaded_at) FROM {{ this }})
{% endif %}

Trade-off Analysis Made:

• Staging as VIEW: Cost-efficient, <1M rows, simple transformation
• Mart as INCREMENTAL: 50M+ order rows, saves $200/day vs full refresh
• Merge strategy: Handles order status updates (shipped→delivered)

What novice misses: Using append strategy for updatable data, forgetting late-arriving data filters

Quality Gates

• [ ] All models follow naming convention: stg_, int_, fct_, dim_ prefixes
• [ ] Every model has primary key with unique and not_null tests
• [ ] Incremental models have unique_key and appropriate strategy (merge/append)
• [ ] All {{ ref() }} and {{ source() }} calls resolve without hardcoded table names
• [ ] Models >10M rows use incremental materialization with proper filter logic
• [ ] Every model and column has description in YAML schema file
• [ ] dbt test passes with zero failures on all error-level tests
• [ ] dbt docs generate produces complete lineage graph with no broken references
• [ ] CI runs dbt build --select state:modified+ and completes in <15 minutes
• [ ] Source freshness checks configured with appropriate warn/error thresholds

NOT-FOR Boundaries

Do NOT use this skill for:

• Orchestration/Scheduling: DAG dependencies, cron jobs → Use airflow-dag-orchestrator instead
• Data Ingestion: API extraction, CDC, streaming → Use streaming-pipeline-architect instead
• Warehouse Optimization: Query performance, indexing, partitioning → Use data-warehouse-optimizer instead
• Data Catalog Management: Business glossary, data governance → Use data-governance-steward instead
• Real-time Analytics: Sub-second latency, event streaming → Use realtime-analytics-architect instead

Delegation Rules:

• For warehouse-specific performance issues → data-warehouse-optimizer
• For data quality monitoring/alerting beyond dbt tests → data-quality-guardian
• For complex dimensional modeling decisions → dimensional-modeler