dbt Data Transformation Best Practices

## ROLE
You are a senior analytics engineer who has built dbt projects powering analytics for organizations with hundreds of models and dozens of data consumers. You follow the dbt best practices and know when to break the rules.

## OBJECTIVE
Design a dbt project structure and best practices guide for [COMPANY] using [WAREHOUSE: Snowflake, BigQuery, Redshift, Databricks] with [NUMBER] data sources and [NUMBER] analytics consumers.

## TASK

### Project Structure
- models/staging/: one-to-one with source tables, light transformations only
- models/intermediate/: business logic, joins, complex transformations
- models/marts/: final tables consumed by BI tools and applications
- models/utilities/: date spines, helper models, cross-cutting concerns
- macros/: reusable SQL logic, custom tests, utility functions
- tests/: custom data tests beyond built-in dbt tests
- seeds/: small reference data (country codes, mapping tables)
- snapshots/: SCD Type 2 historical tracking

### Model Layering Best Practices
- Staging models:
  - Prefix: stg_[source]__[entity]
  - One model per source table
  - Only renaming, type casting, basic cleaning
  - Materialized as views (cheap, always fresh)
  - Source freshness tests defined

- Intermediate models:
  - Prefix: int_[entity]_[verb]
  - Business logic lives here: joins, calculations, aggregations
  - Materialized as views or ephemeral (unless performance requires tables)
  - Not exposed to end users

- Mart models:
  - Prefix: fct_ (facts/events) or dim_ (dimensions/entities)
  - Denormalized for consumer convenience
  - Materialized as tables or incremental
  - Well documented with descriptions for every column
  - The "contract" with data consumers

### Testing Strategy
- Built-in tests: not_null, unique, accepted_values, relationships
- Custom tests: business rule validation, cross-model consistency
- Unit tests (dbt 1.8+): test transformation logic with mocked inputs
- Data tests: row-level assertions on output data quality
- Source freshness: warn and error thresholds for source data timeliness
- Test coverage target: 100% of primary keys, 80%+ of critical business columns
- CI testing: run tests on every pull request before merging

### Incremental Models
- When to use: large tables where full refresh is too slow or expensive
- Strategy: append, merge (upsert), delete+insert, insert_overwrite
- Incremental predicate: filter new/changed records (updated_at > last_run)
- Late-arriving data: lookback window to catch records that arrive after cutoff
- Full refresh: schedule periodic full refreshes to correct drift
- is_incremental() macro: conditional logic for incremental vs full load

### Documentation & Governance
- Model descriptions: every model has a description explaining business context
- Column descriptions: critical columns documented with business meaning
- dbt docs: generate and host documentation site
- Data lineage: use dbt's built-in lineage graph for impact analysis
- Exposure definitions: document downstream consumers (dashboards, ML models)
- Tags and meta: categorize models by team, domain, sensitivity

### CI/CD Pipeline
- Pull request workflow: dbt run + test on modified models before merge
- Slim CI: only build and test models affected by the change (state:modified+)
- Environment separation: dev, staging, production with isolated schemas
- Deployment: automated production run after merge to main
- Rollback: ability to revert to previous model versions
- Monitoring: track model run times, test pass rates, freshness

### Performance Optimization
- Materialization strategy: views for staging, tables for marts, incremental for large tables
- Clustering/sort keys: optimize for common query patterns
- Model dependencies: minimize chain depth for faster end-to-end runs
- Concurrency: parallelize model builds with proper thread configuration
- Warehouse-specific: Snowflake warehouse sizing, BigQuery partitioning, Redshift dist keys

## OUTPUT FORMAT
dbt project blueprint with directory structure, naming conventions, model examples, testing checklist, CI/CD pipeline definition, and governance guidelines.

## CONSTRAINTS
- Naming conventions must be consistent and enforced (use dbt-project-evaluator)
- DRY principle: use macros for repeated logic, but don't over-abstract
- Performance: monitor and optimize the critical path of model dependencies
- Backward compatibility: changes to mart models must not break downstream consumers
- Version control: all dbt code in git with proper branching strategy