SQL tables with no assertion

For SQL projects without dbt, this rule flags each table or view the SQL writes that has no assertion file beside it. An assertion file is a checks.sql, tests.sql or assertions.sql, or any file under a tests/ folder. A view that only passes a table through is covered by that table's assertions.

Without an assertion, a duplicate row or a missing day reaches every reader before anyone notices. When no relation in the project has one, we report it once for the whole project rather than once per table.

Add assertions to the relations people read. Start with the relations people read most. Add a checks.sql beside the query that asserts the grain and the keys, in the same style your tested relations already use.

Before:

-- orders/query.sql
create or replace table analytics.orders as
select order_id, customer_id, ordered_at
from raw.shop_orders;

After:

-- orders/checks.sql: each query should return no rows
select order_id
from analytics.orders
group by order_id
having count(*) > 1;

select order_id
from analytics.orders
where order_id is null or customer_id is null;

A scratch table nobody reads needs no assertion. A relation checked somewhere we cannot see, such as warehouse constraints or a separate test repository, is covered in practice. Tell us, and the finding comes out of your report.

The .sql files, read from your repository with read-only access or uploaded. We match assertion files to relations by name and path, so the finding is medium confidence.