TE
SQL Testing
SQL testing conventions using pgTAP, dbt tests, and query result validation
Details
Language / Topic
SQLCategory
Testing
Rules
balanced
- Use `pgTAP` for PostgreSQL unit tests: define test functions with `ok()`, `is()`, `isnt()`, and `results_eq()` assertions inside `SELECT * FROM runtests()`.
- Write dbt schema tests (`not_null`, `unique`, `accepted_values`, `relationships`) in `schema.yml` files alongside every model to validate data contracts.
- Test every view and stored procedure with at least one positive case (valid input, expected output) and one negative case (invalid input, expected error or empty result).
- Use transaction wrapping in test scripts: `BEGIN; /* test code */ ROLLBACK;` so tests leave no permanent state in the database.
- Seed test data into temporary tables or a dedicated test schema rather than using production data — test isolation prevents environment coupling.
- Use `EXPLAIN ANALYZE` in test assertions to verify that critical queries use expected index scans rather than sequential scans after data volume grows.
- Run dbt tests with `dbt test --select tag:nightly` to separate fast schema tests from slow row-count or distribution tests in CI.