How to Unit Test in Snowflake

What is unit testing in Snowflake?

Unit testing in Snowflake is the practice of running small, isolated checks that confirm a query, view, or stored procedure produces the expected result when fed deterministic data. It prevents silent logic errors from reaching production.

Why should data teams unit-test Snowflake objects?

Tests catch broken transformations early, document intent, and enable safe refactoring. They also help CI/CD pipelines block faulty SQL before deployment, keeping dashboards and downstream services accurate.

How do I set up a Snowflake unit-test framework?

Step 1 – Create a dedicated test schema

Use an isolated schema (e.g., TESTING) to store fixtures and avoid polluting production tables.

Step 2 – Insert deterministic fixture data

Load minimal but representative rows into Customers, Orders, Products, and OrderItems. Keep IDs hard-coded for repeatability.

Step 3 – Write Snowflake Scripting test blocks

Wrap assertions in anonymous blocks or stored procedures so they can be invoked by CI tools.

What syntax does Snowflake Scripting use for assertions?

Snowflake supports ASSERT <condition>. When the condition is FALSE, execution stops with an error that CI can detect.

BEGIN
LET calc_total NUMBER := (
SELECT SUM(oi.quantity * p.price)
FROM OrderItems oi
JOIN Products p ON p.id = oi.product_id
WHERE oi.order_id = 100
);
ASSERT calc_total = 199.98;
END;


Example: Verify order total calculation

The block below tests that the view vw_order_totals returns the correct sum for order 100.

BEGIN
LET expected NUMBER := 199.98;
LET actual NUMBER := (
SELECT total_amount
FROM vw_order_totals
WHERE id = 100
);
ASSERT actual = expected;
END;


Best practices for reliable Snowflake tests

Reset fixtures inside each test, keep tests idempotent, and test one assumption per block. Automate execution via Snowflake Tasks or external CI runners.

Common mistakes and how to avoid them

Using production data in tests causes flaky results. Always isolate fixtures in a test schema.

Relying on row order makes assertions brittle. Compare scalar results or ordered sets explicitly.