What Is a SQL Unit Test? Examples, Benefits, and How to Write One

Quick answer: a SQL unit test checks whether a SQL query does the thing you think it does. You give the query a tiny, controlled set of input rows, define the result you expect, and let the test compare the two.

Say you have a revenue model that should ignore refunded orders. A useful SQL unit test gives it a couple of paid orders and one refunded order, then asserts that only the paid amount appears in the total. If someone later removes the refund filter, the test fails before the change gets to production.

That is the whole idea. You are testing the logic, not hoping a dashboard happens to reveal the mistake next week.

What a SQL unit test actually tests

Most useful SQL is more than a select * from table. It joins entities, fills in missing values, classifies rows, applies date rules, calculates metrics, and turns a business definition into a table somebody will rely on.

A SQL unit test isolates one of those rules.

controlled input rows -> SQL query -> expected output rows

The input is deliberately small. You want it to be easy to see why the result should be correct, and easy to see what broke when it is not.

For example, this query calculates revenue for paid orders:

select
  status,
  sum(amount) as revenue
from orders
where status = 'paid'
group by 1

The test case can be just three rows:

The expected result is one row: paid revenue equals 150.

That looks almost too simple. Good. Simple test cases are what make the rule obvious. The 999 refunded order makes the test fail loudly if somebody changes or removes the where status = 'paid' condition.

SQL unit tests vs data quality checks

SQL unit tests and data quality checks are related, but they answer different questions.

Quality checks catch problems in the data that reached your model. A source may deliver duplicates, an ID may become null, or yesterday's load may never arrive. Those are production-data problems.

Unit tests catch a different kind of problem: the source data can be completely fine, while the query handling it is wrong. You used an inner join instead of a left join, a case condition is in the wrong order, or a date boundary excludes a day it should include.

You want both. A clean result from a broken query is still broken, and a correct query pointed at bad source data does not save anyone either.

What SQL logic is worth unit testing?

You do not need a test for every select statement. Start with logic that encodes a decision or tends to get changed by people who have not been living in the model for months.

Good candidates include:

• business rules in case when expressions, such as customer tiers, funnel stages, or churn flags
• filters that include or exclude important records, like refunds, test accounts, cancelled subscriptions, or internal users
• aggregations where double counting is possible
• joins that must preserve rows or choose the latest related record
• date logic around time zones, month boundaries, trial periods, and rolling windows
• null handling, defaults, and fallback values
• incremental logic that decides which records should be updated

The test should describe behaviour, not copy the query line by line. "A refunded order does not count as revenue" is a contract. "The query has a where clause" is not.

A practical SQL unit test example

Imagine a model that gives each account a lifecycle stage. An account is active when it has logged in within the last 30 days and has a paid subscription. Everything else is inactive.

select
  account_id,
  case
    when plan = 'paid'
      and last_login_at >= current_date - interval '30 days'
      then 'active'
    else 'inactive'
  end as lifecycle_stage
from accounts

The happy path needs a test, but it is not the interesting one. The useful tests sit at the edges:

• a paid account that logged in yesterday is active
• a free account that logged in yesterday is still inactive
• a paid account that last logged in 31 days ago is inactive
• an account on the 30-day boundary gets the outcome your definition calls for

That last one is why SQL tests pay for themselves. Date logic has a habit of looking correct in a pull request and being one day off in production. Same thing with time zones, null values, and joins that appear to work until a customer has two records.

How to write SQL unit tests with Bruin

Most SQL testing frameworks follow the same pattern: define fixtures, run a query, compare the output. Bruin's unit test feature keeps that test next to the SQL asset it covers.

In this example, the asset calculates revenue from analytics.orders. The test supplies the three mock orders, runs the query as a read-only select, and checks the output.

/* @bruin
name: analytics.revenue_summary
type: duckdb.sql

unit_tests:
  - name: refunds_are_excluded
    inputs:
      - asset: analytics.orders
        rows:
          - { id: 1, status: paid, amount: 100 }
          - { id: 2, status: refunded, amount: 999 }
          - { id: 3, status: paid, amount: 50 }
    expected:
      match: exact
      rows:
        - { status: paid, revenue: 150 }
@bruin */

select
  status,
  sum(amount) as revenue
from analytics.orders
where status = 'paid'
group by 1

Then run it with:

bruin unit-test assets/revenue_summary.sql

Bruin replaces the upstream table named in inputs with the rows in the test. It does not create a production table or write the result to the warehouse. The test runs the query logic and compares its result with expected.

There are a few small details here that are useful in larger models:

• The mock rows can be sparse. Columns you leave out are null, which makes it easy to test null handling without writing a huge fixture.
• By default, an expected result can be a subset. match: exact is worth using when you want to protect the full shape of a small result.
• You can assert a row count, specific output columns, or both.
• If an asset has several CTEs, Bruin can assert a named intermediate CTE as well as the final query result.
• Time-sensitive queries can set execution_time in the test, so current_date, timestamps, and date macros do not make a test flaky.

Those are implementation details, but they matter. A SQL test should be deterministic enough to run on every pull request without making someone wonder whether it failed because the clock moved.

Keep fixtures small and specific

The temptation is to copy a bunch of production rows into a test. I would not.

Big fixtures are difficult to read, difficult to maintain, and they often hide the actual rule being tested. Five carefully chosen rows are usually better than 500 anonymized ones.

Use rows that make the behaviour visible:

one row that should pass
one row that should fail
one awkward boundary case

That is not a hard rule. A join test may need two orders and three customer records. A deduplication test may need repeated events. But every row should earn its place in the fixture.

When several tests need the same reference data, such as country mappings or currency rates, Bruin also supports shared pipeline fixtures. Define them once in pipeline.yml, then name the fixture in each test. The individual test can still override a fixture's rows when it needs a different case.

Common mistakes when testing SQL

The first mistake is treating a row-count check as a unit test. A query returning 10 rows does not tell you whether those are the right 10 rows. Assert the fields that carry the business rule.

The second is only testing the happy path. If all your input records are valid paid orders, a revenue test can pass even if the query accidentally includes refunds. Put a row in the fixture that should be excluded.

Another common one is leaving time unpinned. Tests around current_date, now(), or Jinja dates can pass today and fail tomorrow. Use a fixed execution time where the date is part of the rule.

And finally, do not let the test merely mirror a mistake in the SQL. If the query says a user is active after 30 days and the test repeats that same expression in another form, both can be wrong together. Phrase the expected outcome in business language first, then build the rows that prove it.

Where SQL unit tests fit in a data pipeline

SQL unit tests work best as part of normal development, not as a cleanup project after an incident.

When you add a new rule, write one or two tests with it. When a bug slips through, turn the failing production case into a small fixture before fixing the query. That fixture becomes a guardrail for the next person who touches the model, which might be future you three months from now trying to remember why that weird condition exists.

For Bruin projects, the workflow is pleasantly direct: SQL, asset definition, and the unit test can live in the same file, run locally with the CLI, and run in CI before a pipeline is deployed. Bruin's regular quality checks then cover the other half of the job once the asset runs against real data.

If a model decides who gets counted, contacted, billed, alerted, or excluded, it is probably worth a test. What is the first business rule in your SQL that you would rather prove than hope is still right?

FAQ

What is a SQL unit test?

A SQL unit test runs a query against a small set of known input rows and checks the result against an expected output. It verifies query logic such as joins, filters, calculations, date rules, and case statements without depending on production data.

What is the difference between a SQL unit test and a data quality test?

A SQL unit test tells you whether the query logic is correct. A data quality test tells you whether the actual table or view produced by a pipeline meets expectations, for example whether an ID is unique, a column is not null, or a table is fresh. They catch different failures, so mature pipelines use both.

Can you unit test SQL without production data?

Yes. The test uses mock input rows, also called fixtures, instead of real production rows. That makes tests repeatable and safe to run locally or in CI before a query touches a production table.

How do you run a SQL unit test in Bruin?

Add a unit_tests block to a SQL asset's @bruin definition, name the upstream input assets and their mock rows, then declare the expected output. Run bruin unit-test against an asset, pipeline, or repository. See the Bruin unit test documentation for the full schema and options.