# dbt & Build Pipeline

dbt defines, tests, and deploys the transformed tables and views in `CLINICOGENOMICS`. Every transformed table — staging views through cohort deliverables — is a dbt model in the `cgdb` repo. To add a column, create a cohort, or fix a data issue, you edit a `.sql` file, not the database.

## What is dbt?

Think of dbt as a SQL project manager. You write SQL models (`SELECT` statements); dbt turns them into tables, manages dependencies, runs data quality tests, and deploys changes across environments. Raw source data arrives via Fivetran into schemas like `DATA_SHARE`; dbt treats those as **sources** (monitored, not created) and builds everything on top.

## Where dbt lives

- Repository: `cgdb` (single master branch)
- Models: `cgdb/snowflake/dbt/models/`
- Seeds: `cgdb/snowflake/dbt/seeds/`
- Runner scripts (non-dbt SQL): `cgdb/snowflake/dbt/scripts/`

## Repository structure

| Folder | Purpose |
| --- | --- |
| `models/` | SQL files defining staging views, Dynamic Tables, and other materializations. |
| `macros/` | Reusable SQL/Jinja snippets injected into models at compile time. |
| `seeds/` | Small CSV files for static reference data, loaded into the SEEDS schema. |
| `tests/` | Custom SQL data quality tests, run during dbt test / build. |

Materializations: `dynamic_table` (preferred default, auto-refreshing), `view` (staging, computed on read), `table` (rare), and `incremental` (very large tables).

## Non-dbt scripts & infrastructure

Not everything is built by dbt. Three layers:

| What | Where |
| --- | --- |
| Staging views, Dynamic Tables, tests, seeds, semantic views | `cgdb/snowflake/dbt/` |
| Storage Integrations, Snowpipe, CDC Tasks, SOS, one-time DDL | `cgdb/snowflake/dbt/scripts/` |
| Users, roles, warehouses, databases, schemas, RBAC | `ndp-account-request` |

> **Scripts use runner scripts, not dbt run**
>
> The `scripts/` directory holds SQL deployed via SnowSQL runner scripts (e.g. `run_cohort_cdc.sh <env>`), which handle environment-specific variable substitution. Never deploy these via ad-hoc SnowSQL with manual variable replacement.

## Maturity tags

Models use maturity tags to control which environment they deploy to. New models start at `maturity:sbx` and are promoted through `qa` then `prod` as they are validated. CI/CD selects models by tag per environment.

## Common commands

```bash
# Compile only
dbt compile --target dev

# Run a single model and everything downstream
dbt run --select my_model+ --target dev

# Full refresh (REQUIRED for schema or inner-SELECT changes)
dbt run --full-refresh --select my_model --target dev

# Run tests
dbt test --target dev
```

> **Structural changes need a full refresh**
>
> Adding columns or changing a model’s inner `SELECT` requires `--full-refresh`; a plain `dbt run` can silently no-op. Structural changes may also need `--no-partial-parse` to avoid reusing stale SQL.