How to Unit Test in Redshift

How do I write and automate SQL unit tests in Amazon Redshift?

Unit testing in Amazon Redshift executes small, repeatable SQL checks that confirm each query or transformation returns expected results.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is unit testing in Redshift?

Unit testing in Redshift runs focused SQL assertions that compare actual query output to an expected result. Each test isolates one business rule, helping you catch regressions early.

Why perform SQL unit tests?

Tests document logic, shorten debugging, and keep dashboards trustworthy after schema changes.Automating them in CI/CD prevents broken queries from reaching production.

Which tools can I use?

Redshift lacks native TAP support, but you can script tests with plain SQL inside transactions, use open-source packages like redshift-assert, or run dbt tests against Redshift.

How do I create a test schema?

Create a dedicated schema (e.g., test_schema) and load minimal fixture rows into Customers, Orders, Products, and OrderItems.Wrap each test case inside a transaction and roll back when finished.

How do I write an assertion query?

Use a SELECT that returns 0 rows when the rule passes, otherwise raises an error.Example: compare expected and actual aggregates with EXCEPT.

Example: verify order totals

The query checks that each Orders.total_amount equals the SUM of its OrderItems.

How can I automate tests?

Store tests in SQL files, run them with psql or the Redshift Data API, and abort the CI job on the first non-zero exit code.

Best practices

Keep fixtures tiny, use transactions for isolation, name tests descriptively, and include edge-case rows like NULLs or zero quantities.

Common mistakes to avoid

Skipping rollback leaves test data in production.Hard-coding IDs makes tests brittle when fixtures change.

Why How to Unit Test in Redshift is important

How to Unit Test in Redshift Example Usage


--Run inside CI
\i tests/order_total_assertion.sql
--Shell exit code is 0 when the SELECT returns 0 rows

How to Unit Test in Redshift Syntax


--1. Create test schema and load fixtures
BEGIN;
CREATE SCHEMA IF NOT EXISTS test_schema;
SET search_path TO test_schema;

INSERT INTO Customers(id, name, email, created_at) VALUES (1,'Ada','ada@example.com',CURRENT_DATE);
INSERT INTO Products(id, name, price, stock) VALUES (10,'Keyboard',50,100);
INSERT INTO Orders(id, customer_id, order_date, total_amount) VALUES (100,1,CURRENT_DATE,100);
INSERT INTO OrderItems(id, order_id, product_id, quantity) VALUES(500,100,10,2);

--2. Assertion: order total equals line-item sum
WITH expected AS (
    SELECT oi.order_id, SUM(oi.quantity*p.price) AS calc_total
    FROM OrderItems oi
    JOIN Products p ON p.id = oi.product_id
    GROUP BY oi.order_id
), failures AS (
    SELECT o.id AS order_id, o.total_amount, e.calc_total
    FROM Orders o
    JOIN expected e ON e.order_id = o.id
    WHERE o.total_amount <> e.calc_total
)
SELECT * FROM failures; -- returns 0 rows when test passes
ROLLBACK;

Common Mistakes

Leaving test data committed: Always wrap fixture inserts in a transaction and ROLLBACK, or you will pollute production tables.
Hard-coding primary keys: Use stable business keys or CTE-generated IDs so tests stay valid when fixture rows shift.