Dbt Development

dbt Development

Implement transformations in the conventions and dbt version already used by the project. Inspect the adapter, project configuration, packages, and neighboring models before changing code.

Boundary

Use data-pipeline-design to decide source extraction, checkpointing, file publication, and non-dbt pipeline behavior. Use data-pipeline-testing for fixture design, multi-run incremental scenarios, and reconciliation oracles. dbt implements transformation logic and warehouse contracts; it does not replace source-change analysis.

Workflow

1. Inspect the project

Read:

• dbt_project.yml
• dependency or package files
• source definitions
• neighboring model SQL and YAML
• adapter and warehouse configuration
• CI commands
• installed dbt version when available

Follow existing naming and configuration unless they are the problem.

2. State the model contract

Before writing SQL, define:

• model purpose
• grain
• primary or unique key
• upstream sources and models
• downstream consumers
• materialization
• update and delete behavior
• required tests and documentation

Every model should have one explainable grain.

3. Place logic in the correct layer

Use the project's layering conventions. A common flow is:

• Staging: source-conformed renaming, casting, and light cleanup
• Intermediate: purpose-built joins or transformations
• Marts: business-conformed entities and facts for consumers

Do not force an intermediate layer when it adds no clarity. Avoid embedding final business metrics in source-conformed staging models.

4. Write dependency-aware SQL

• Use source() for declared raw sources.
• Use ref() for dbt-managed dependencies.
• Select explicit columns once the model contract matters.
• Name transformation CTEs by purpose.
• Make join cardinality and deduplication tie-breakers visible.
• Keep warehouse-specific SQL intentional and documented.

5. Choose materialization deliberately

Select view, table, incremental, ephemeral, snapshot, or project-specific materialization based on data volume, update behavior, cost, and consumer needs.

For incremental models, use dbt-patterns.md and define:

• reliable filter or change signal
• unique key
• adapter-supported incremental strategy
• overlap and late-arrival behavior
• delete handling
• full-refresh and backfill procedure

A max(timestamp) filter without a tie-breaker, overlap, and late-data analysis is not a complete incremental design.

6. Add tests at the right level

Use:

• source freshness for arrival expectations
• data tests for dataset invariants
• unit tests for model SQL logic on static inputs when supported by the project version
• singular tests for cross-row or cross-model rules
• reconciliation queries for critical totals

Current dbt documentation calls schema assertions data tests; tests may remain a supported alias in some versions. Follow the installed version and project convention.

7. Document ownership and meaning

Document model purpose, grain, important columns, owners, and material caveats. Do not write descriptions that merely restate column names.

8. Verify narrowly, then broadly

Use the project's commands. Typical progression:

dbt parse
dbt compile --select <model>
dbt build --select +<model>+
dbt source freshness

Scope builds during iteration, then run the required downstream or CI surface. Inspect compiled SQL when Jinja, dispatch, or adapter behavior matters.

9. Review generated warehouse behavior

Check:

• query plan or warehouse cost where material
• row count and grain
• duplicates and nulls
• incremental merge behavior
• downstream compatibility
• full-refresh safety

Output

For implementation, return changed models, YAML, tests, and verification results. For review, return:

1. Contract and grain findings
2. Layering and dependency issues
3. Materialization and incrementality risks
4. Test and documentation gaps
5. Smallest correct changes
6. Commands needed to verify

Quality Gate

• Model grain and key are explicit.
• source() and ref() preserve the DAG.
• Layer placement has a clear purpose.
• Incremental behavior covers late data, deletes, and full refresh.
• Tests protect meaningful contracts.
• Verification matches the installed dbt version and adapter.