dbt-data-transformation

What this skill does

# dbt Data Transformation

A comprehensive skill for mastering dbt (data build tool) for analytics engineering. This skill covers model development, testing strategies, documentation practices, incremental builds, Jinja templating, macro development, package management, and production deployment workflows.

## When to Use This Skill

Use this skill when:

- Building data transformation pipelines for analytics and business intelligence
- Creating a data warehouse with modular, testable SQL transformations
- Implementing ELT (Extract, Load, Transform) workflows
- Developing dimensional models (facts, dimensions) for analytics
- Managing complex SQL dependencies and data lineage
- Creating reusable data transformation logic across projects
- Testing data quality and implementing data contracts
- Documenting data models and business logic
- Building incremental models for large datasets
- Orchestrating dbt with tools like Airflow, Dagster, or dbt Cloud
- Migrating legacy ETL processes to modern ELT architecture
- Implementing DataOps practices for analytics teams

## Core Concepts

### What is dbt?

dbt (data build tool) enables analytics engineers to transform data in their warehouse more effectively. It's a development framework that brings software engineering best practices to data transformation:

- **Version Control**: SQL transformations as code in Git
- **Testing**: Built-in data quality testing framework
- **Documentation**: Auto-generated, searchable data dictionary
- **Modularity**: Reusable SQL through refs and macros
- **Lineage**: Automatic dependency resolution and visualization
- **Deployment**: CI/CD for data transformations

### The dbt Workflow

```
1. Develop: Write SQL SELECT statements as models
2. Test: Define data quality tests
3. Document: Add descriptions and metadata
4. Build: dbt run compiles and executes models
5. Test: dbt test validates data quality
6. Deploy: CI/CD pipelines deploy to production
```

### Key dbt Entities

1. **Models**: SQL SELECT statements that define data transformations
2. **Sources**: Raw data tables in your warehouse
3. **Seeds**: CSV files loaded into your warehouse
4. **Tests**: Data quality assertions
5. **Macros**: Reusable Jinja-SQL functions
6. **Snapshots**: Type 2 slowly changing dimension captures
7. **Exposures**: Downstream uses of dbt models (dashboards, ML models)
8. **Metrics**: Business metric definitions

## Model Development

### Basic Model Structure

A dbt model is a SELECT statement saved as a `.sql` file:

```sql
-- models/staging/stg_orders.sql

with source as (
    select * from {{ source('jaffle_shop', 'orders') }}
),

renamed as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status,
        _etl_loaded_at
    from source
)

select * from renamed
```

**Key Points:**
- Models are SELECT statements only (no DDL)
- Use CTEs (Common Table Expressions) for readability
- Reference sources with `{{ source() }}`
- dbt handles CREATE/INSERT logic based on materialization

### The ref() Function

Reference other models using `{{ ref() }}`:

```sql
-- models/marts/fct_orders.sql

with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('stg_customers') }}
),

joined as (
    select
        orders.order_id,
        orders.order_date,
        customers.customer_name,
        orders.status
    from orders
    left join customers
        on orders.customer_id = customers.customer_id
)

select * from joined
```

**Benefits of ref():**
- Builds dependency graph automatically
- Resolves to correct schema/database
- Enables testing in dev without affecting prod
- Powers lineage visualization

### The source() Function

Define and reference raw data sources:

```yaml
# models/staging/sources.yml

version: 2

sources:
  - name: jaffle_shop
    description: Raw data from the Jaffle Shop application
    database: raw
    schema: jaffle_shop
    tables:
      - name: orders
        description: One record per order
        columns:
          - name: id
            description: Primary key for orders
            tests:
              - unique
              - not_null
          - name: user_id
            description: Foreign key to customers
          - name: order_date
            description: Date order was placed
          - name: status
            description: Order status (completed, pending, cancelled)
```

```sql
-- Reference the source
select * from {{ source('jaffle_shop', 'orders') }}
```

**Source Features:**
- Document raw data tables
- Test source data quality
- Track freshness with `freshness` config
- Separate source definitions from transformations

### Model Organization

Recommended project structure:

```
models/
├── staging/           # One-to-one with source tables
│   ├── jaffle_shop/
│   │   ├── _jaffle_shop__sources.yml
│   │   ├── _jaffle_shop__models.yml
│   │   ├── stg_jaffle_shop__orders.sql
│   │   └── stg_jaffle_shop__customers.sql
│   └── stripe/
│       ├── _stripe__sources.yml
│       ├── _stripe__models.yml
│       └── stg_stripe__payments.sql
├── intermediate/      # Purpose-built transformations
│   └── int_orders_joined.sql
└── marts/            # Business-defined entities
    ├── core/
    │   ├── _core__models.yml
    │   ├── dim_customers.sql
    │   └── fct_orders.sql
    └── marketing/
        └── fct_customer_sessions.sql
```

**Naming Conventions:**
- `stg_`: Staging models (one-to-one with sources)
- `int_`: Intermediate models (not exposed to end users)
- `fct_`: Fact tables
- `dim_`: Dimension tables

## Materializations

Materializations determine how dbt builds models in your warehouse:

### 1. View (Default)

```sql
{{ config(materialized='view') }}

select * from {{ ref('base_model') }}
```

**Characteristics:**
- Lightweight, no data stored
- Query runs each time view is accessed
- Best for: Small datasets, models queried infrequently
- Fast to build, slower to query

### 2. Table

```sql
{{ config(materialized='table') }}

select * from {{ ref('base_model') }}
```

**Characteristics:**
- Full table rebuild on each run
- Data physically stored
- Best for: Small to medium datasets, heavily queried models
- Slower to build, faster to query

### 3. Incremental

```sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='fail'
) }}

select * from {{ source('jaffle_shop', 'orders') }}

{% if is_incremental() %}
    -- Only process new/updated records
    where order_date > (select max(order_date) from {{ this }})
{% endif %}
```

**Characteristics:**
- Only processes new data on subsequent runs
- First run builds full table
- Best for: Large datasets, event/time-series data
- Fast incremental builds, maintains historical data

**Incremental Strategies:**

```sql
-- Append (default): Add new rows only
{{ config(
    materialized='incremental',
    incremental_strategy='append'
) }}

-- Merge: Upsert based on unique_key
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

-- Delete+Insert: Delete matching records, insert new
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert'
) }}
```

### 4. Ephemeral

```sql
{{ config(materialized='ephemeral') }}

select * from {{ ref('base_model') }}
```

**Characteristics:**
- Not built in warehouse
- Interpolated as CTE in dependent models
- Best for: Lightweight transformations, avoiding view proliferation
- No storage, compiled into downstream models

### Configuration Comparison

| Materialization | Build Speed | Query Speed | Storage | Use Case |
|----------------|-------------|-------------|---------|----------|
| View | Fast | Slow | None | Small datasets, infrequent queries |
| Table | Slow | Fast | High | Medium datasets, frequent queries |
| Incremental | Fast* | Fast | High | Large datasets, time-series data |
| Ephemeral | N/A | Varies | None | Intermediate l