I built a small open-source tool called sqltesting to test SQL transformations using mock tables + assertions. It works with BigQuery, Snowflake, Redshift, Athena, Trino, and DuckDB. Also made mocksmith library to generate realistic test data by simply adding mockable annotation on existing classes

Curious how others test SQL logic today. Do you write tests, or just rely on warehouse runs?

Co-author here. happy to answer questions.

On how this compares to alternatives - the way we think about it is theres kind of 3 layers. DQ monitors check if your data is healthy after its already in prod (anomalies, freshness, etc). dbt tests verify your models are sound during deployment (constraints, relationships, schema).

What was missing for us was testing the actual business logic before you deploy. for example, does this calculation actually return what you expect for these inputs.

This framework lets you define mock tables as dataclasses, inject via CTEs so its fast, and validate your logic in development before it ever hits prod. same test works across warehouses too which was useful when we were evaluating migrations. also its not tied to dbt or any specific framework

FWIW I used to use https://pgtap.org for a short while but then switched to 'invariant testing' (my word for it) where I basically write DDL for views that collect rows that don't look like I expect them to look like.

I since moved to SQLite in NodeJS and one of the things I just implemented is views that look at strategic points in the DB—where data is ingested—and tests whether all text fields are Unicode-normalized; for this I can use the JS `String::normalize()` method I previously didn't even know of. Stuff like this makes me easily forget the many advanced feautures of PostgreSQL and forgive SQLite its quirks because the overall experience is that much more enjoyable. And, to answer directly: I can write unit tests for SQL in the same file and using the same code that I use for all my unit tests. Yay!