dbt

What this skill does

## Purpose

dbt (data build tool) is a command-line tool for transforming data in warehouses using SQL-based models. It enables developers to write, test, and document data transformations, ensuring reliable ETL processes.

## When to Use

Use dbt when building SQL data models for warehouses like Snowflake, BigQuery, or Redshift. Apply it for incremental data loading, schema evolution, or automated testing in data pipelines. Avoid it for real-time processing or non-SQL data sources; opt for dbt when you need version-controlled SQL code with built-in validation.

## Key Capabilities

- Define reusable SQL models in .sql files with Jinja templating for dynamic queries (e.g., `{{ var('date') }}` for parameter injection).
- Run automated tests like schema checks or custom assertions via YAML configs (e.g., `not_null` or `unique` tests).
- Generate documentation automatically from models using `dbt docs generate`, outputting HTML with model dependencies and descriptions.
- Handle incremental models with the `is_incremental()` macro to process only new data, reducing warehouse load.
- Support for macros and packages via dbt hub for extending functionality, like adding utility functions.

## Usage Patterns

Follow this workflow: 1) Initialize a project with `dbt init`. 2) Write models in the `models/` directory as SQL files. 3) Configure connections in `profiles.yml`. 4) Run and test models iteratively. 5) Use seeds for static data and snapshots for slowly changing dimensions. For CI/CD, integrate dbt into scripts: run `dbt run` in a Docker container with mounted volumes. Always specify targets like `--target dev` to switch environments.

## Common Commands/API

dbt is primarily CLI-based; use it directly or via scripts. Key commands:

- `dbt init --name project_name`: Create a new project; specify a directory with `--project-dir /path`.
  
  Example:
  ```
  dbt init --name sales_dbt --project-dir ./sales_project
  ```

- `dbt run --models model_name --select tag:nightly`: Execute models; use `--full-refresh` to rebuild all data.

  Example:
  ```
  dbt run --models orders --target prod --threads 8
  ```

- `dbt test --models model_name`: Run tests; add flags like `--store-failures` to log errors.

  Example:
  ```
  dbt test --select source:raw_data
  ```

- `dbt docs generate && dbt docs serve`: Build and serve documentation; integrate with CI for auto-deployment.
- For API-like usage, wrap dbt in Python scripts using subprocess: `subprocess.run(['dbt', 'run', '--models', 'my_model'])`. Use environment variables for profiles, e.g., set `$DBT_PROFILES_DIR` to `/path/to/profiles.yml`.

## Integration Notes

Integrate dbt with warehouses by configuring `profiles.yml`. For Snowflake, use:

```
your_profile:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: your_account
      user: your_user
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      database: your_db
      schema: your_schema
```

For BigQuery, specify:

```
bigquery_profile:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: your-gcp-project
      dataset: your_dataset
      keyfile: "{{ env_var('GOOGLE_KEYFILE') }}"
```

Use env vars for secrets (e.g., `export SNOWFLAKE_PASSWORD=your_key`). Integrate with Git for version control, and tools like Airflow for orchestration: call `dbt run` as a task. For VS Code, install the dbt extension for syntax highlighting.

## Error Handling

Handle errors by first running `dbt debug` to validate connections and profiles. For SQL compilation errors, check model files for syntax (e.g., missing semicolons) and use `dbt compile` to preview. If tests fail, inspect output logs for details like "column not found"; fix by updating schemas in `.yml` files. Use `--fail-fast` in `dbt run` to halt on first error. Common patterns: wrap commands in try-catch for scripts (e.g., in Python: `try: subprocess.run(['dbt', 'run']) except Exception as e: log_error(e)`). For authentication failures, ensure env vars like `$SNOWFLAKE_PASSWORD` are set; test with `dbt run --target dev --log-level debug`.

## Concrete Usage Examples

1. Building a simple incremental model: Create `models/orders.sql` with:
   ```
   {{ config(materialized='incremental') }}
   select order_id, customer_id from source_orders
   where order_date > (select max(order_date) from {{ this }})
   ```
   Then run: `dbt run --models orders --target dev`. This processes only new orders.

2. Running and testing a data model: Define a test in `models/schema.yml`:
   ```
   models:
     - name: customers
       columns:
         - name: customer_id
           tests:
             - unique
             - not_null
   ```
   Execute: `dbt run --models customers && dbt test --models customers`. This builds the model and verifies no duplicates or nulls.

## Graph Relationships

- Cluster: data-engineering (connects to skills like SQL and ETL tools).
- Tags: dbt, sql, data-modeling (links to related tags in other skills, e.g., SQL for query optimization).
- Relationships: Depends on warehouse connectors; integrates with data pipelines in data-engineering cluster.