Testing SQL queries and data quality

A query can run without an error and still return the wrong numbers, so SQL needs tests like any other code. Testing SQL queries means running each query on a fixture, a few rows small enough that you know the right answer by hand, and comparing the rows it returns with the rows you expect. Checks of data quality do the opposite job: they run on real data, and each one is a query that lists the rows breaking one rule, such as a missing value, a duplicate or an order whose customer does not exist. A check that returns no rows passes. Both are cheap to write, and both are only worth something on the engine that runs in production: a test that passes on SQLite says little about the same query on PostgreSQL.

Two kinds of check

• A query test checks that a query computes the right answer on rows whose answer you know.
• A data-quality check is a query that lists the rows of real data breaking one rule.
• A constraint such as NOT NULL, UNIQUE, CHECK or a foreign key stops a bad row before it is written.

Testing SQL queries protects the logic; checking data quality protects the input. A correct query over duplicated or orphaned rows still gives wrong totals, so data quality needs its own checks.

Constraints are the cheapest tests of all, because they run on every write and cost nothing to maintain. Checks catch what constraints cannot: data loaded from an export without its constraints, rules across several rows such as “no two customers share an e-mail address, ignoring case”, and rules that are true most of the time, such as “a delivered order has at least one item”.

A query test, run

The test below needs nothing but Python’s sqlite3. check.py builds a fresh database from a fixture, runs the query and compares its rows with the expected rows. Without ORDER BY a result has no defined order, so the rows are compared as a multiset: each row must appear as often as expected, in any order.

from check import report, run, same_rows

# A fixture small enough to work out the answer by hand: Farah has no orders at all.
FIXTURE = """
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL);
INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Farah', 'Pune'), (3, 'Lakshmi', 'Pune'), (4, 'Ravi', 'Kochi');
INSERT INTO orders VALUES (101, 1, 'delivered'), (102, 1, 'placed'), (103, 3, 'delivered'), (104, 4, 'delivered');
"""
EXPECTED = [("Asha", 2), ("Farah", 0), ("Lakshmi", 1)]

# Two versions of "orders per customer of a city", the customers without orders included.
VERSIONS = {
    "count(*)": """SELECT c.name, count(*) FROM customers c
                   LEFT JOIN orders o ON o.customer_id = c.customer_id
                   WHERE c.city = ? GROUP BY c.customer_id""",
    "count(o.order_id)": """SELECT c.name, count(o.order_id) FROM customers c
                            LEFT JOIN orders o ON o.customer_id = c.customer_id
                            WHERE c.city = ? GROUP BY c.customer_id""",
}
for name, sql in VERSIONS.items():
    report(name, same_rows(run(FIXTURE, sql, ("Pune",)), EXPECTED))

count(*): FAIL, missing [('Farah', 0)], unexpected [('Farah', 1)]
count(o.order_id): pass

"""A small helper for testing queries: run a query on a fixture and compare its rows with the rows you expect."""
import sqlite3
from collections import Counter


def run(fixture, sql, params=()):
    """Builds a fresh in-memory database from the fixture's SQL and returns the query's rows."""
    conn = sqlite3.connect(":memory:")
    conn.executescript(fixture)
    rows = conn.execute(sql, params).fetchall()
    conn.close()
    return rows


def same_rows(actual, expected, ordered=False):
    """None when the rows match, else what differs. Without ORDER BY a result has no order, so rows are compared as
    a multiset: every row must appear as often as expected, in any order."""
    if ordered:
        return None if actual == expected else f"expected {expected}, got {actual}"
    missing = Counter(expected) - Counter(actual)
    extra = Counter(actual) - Counter(expected)
    if not missing and not extra:
        return None
    return f"missing {sorted(missing.elements())}, unexpected {sorted(extra.elements())}"


def report(name, problem):
    print(f"{name}: {'pass' if problem is None else 'FAIL, ' + problem}")

The fixture has one customer who matters more than the others: Farah, who has no orders. Both versions keep her with a LEFT JOIN, but count(*) counts the joined row whose order columns are NULL, so it reports 1 order where there are none. count(o.order_id) counts only real orders and passes. A fixture of customers who all have orders would have passed both versions, which is the general lesson: a test is only as good as its awkward rows. Write the expected rows by hand, from the fixture, never by copying what the query printed.

The same comparison works in any test runner. With pytest, a test function builds the fixture, runs the query and asserts that Counter(actual) == Counter(expected). For a query whose order is part of the answer, such as “the top 3 products”, compare lists instead, and give the query a tie-breaker so that the order is fully defined.

Which queries to test first

Not every query needs its own test. Start with the queries whose mistakes cost the most and are least likely to be noticed: reports that feed decisions or invoices, anything that adds up money, queries with outer joins, grouping or window functions, and queries over columns that may hold NULL. A wrong total on a dashboard can stay unnoticed for months, while a wrong lookup on a page is usually reported the same day. Give each such query one test with an ordinary fixture and one with the awkward rows, and run them in the same pipeline as the rest of the code, so that a change to the query, the schema or the engine version runs them again.

Edge-case fixtures that break reports, reproduced Pro: opens with a Pro, Premium or Ultimate pass

A data-quality pack for mart, run on a dirty copy Pro: opens with a Pro, Premium or Ultimate pass

Tests that pass on SQLite and fail on PostgreSQL, reproduced Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Test a query on a fixture whose answer you know, and compare rows as a multiset unless the order is part of the answer.
• A data-quality check lists the rows that break one rule; constraints stop most of them at write time.
• Test on the engine that runs in production.