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`

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

1# Compile only2dbt compile --target dev3
4# Run a single model and everything downstream5dbt run --select my_model+ --target dev6
7# Full refresh (REQUIRED for schema or inner-SELECT changes)8dbt run --full-refresh --select my_model --target dev9
10# Run tests11dbt test --target dev