Build log: testing SQL models on a laptop, forty rows at a time

A build log for running warehouse SQL models on small Parquet fixtures in an in-process engine, plus the four dialect differences that still call for a nightly warehouse run.

Fixture rows input rows, expected rows Model SQL Dialect translator warehouse dialect in In-process engine seconds, on a laptop Result assertions CI check on every commit Nightly run on the real warehouse
Model SQL is translated and run on fixture rows in process on every commit, and a thin line carries on to the nightly warehouse run.
On this page
  1. A suite nobody runs before pushing
  2. One folder per case, one file a person edits
  3. Twenty lines of pytest
  4. The refund that counted twice
  5. Four places the laptop and the warehouse disagree
  6. Three cases that stay on the warehouse

Ten rows will catch a join that doubles refunds: three orders, five shipments, two refunds. None of them has to live in a warehouse.

Most bugs in SQL transformations are logic bugs. A join fans out, or a NULL slips past a filter, and a total comes out wrong by too little for anyone to ask. Reproducing a bug like that takes a few dozen rows chosen on purpose, and last year’s order table adds nothing to them.

So I’d run those tests where the SQL gets written: on a laptop, against an in-process engine that lives inside the test suite. What follows is a build log of that setup for Okapi Rentals, a company that doesn’t exist. I invented it, numbers included, so each step would have a scale. The tools are real: DuckDB as the engine, Parquet files for fixtures, pytest to run them, and SQLGlot to translate the warehouse’s SQL dialect.

A suite nobody runs before pushing

Okapi has 14 SQL models that turn orders into revenue, with shipments and refunds joined in along the way. Their tests run on the warehouse, where every case loads rows into scratch tables and waits on a network round trip per statement. A full run is long enough that people push first and read the result later. By then they’re on something else.

One folder per case, one file a person edits

The layout, with one case filled in:

models/
  order_revenue.sql                # warehouse SQL, one SELECT per file
  daily_revenue.sql
tests/
  test_models.py                   # the helper further down
  cases/
    order_revenue/                 # one folder per model
      two_shipments_one_refund/    # one folder per case
        rows.sql                   # hand-written: input rows and expected rows
        orders.parquet             # written by rows.sql
        shipments.parquet
        refunds.parquet
        expected.parquet           # what the model must return
    daily_revenue/
      customer_local_midnight/
        warehouse_only             # empty file: this case skips the laptop
        rows.sql
        ...

A case is a folder named for what it proves. A person writes one file in it, rows.sql, with the input and expected rows as plain VALUES.

-- tests/cases/order_revenue/two_shipments_one_refund/rows.sql
-- Run from this folder: duckdb -c ".read rows.sql"
COPY (SELECT * FROM (VALUES
  (1001, 120.00),
  (1002, 180.00),
  (1003,  95.00)
) AS t(order_id, amount)) TO 'orders.parquet' (FORMAT PARQUET);

COPY (SELECT * FROM (VALUES
  (1001, 'S-1', DATE '2024-03-05'),
  (1002, 'S-2', DATE '2024-03-06'),   -- 1002 ships in two parcels
  (1002, 'S-3', DATE '2024-03-08'),
  (1003, 'S-4', DATE '2024-03-06'),   -- so does 1003, with no refund
  (1003, 'S-5', DATE '2024-03-07')
) AS t(order_id, shipment_id, shipped_on)) TO 'shipments.parquet' (FORMAT PARQUET);

COPY (SELECT * FROM (VALUES
  (1001, 20.00),
  (1002, 25.00)
) AS t(order_id, amount)) TO 'refunds.parquet' (FORMAT PARQUET);

COPY (SELECT * FROM (VALUES
  (1001, DATE '2024-03-05', 120.00, 20.00, 100.00),
  (1002, DATE '2024-03-06', 180.00, 25.00, 155.00),   -- one refund, counted once
  (1003, DATE '2024-03-06',  95.00,  0.00,  95.00)    -- no refund: zero, not NULL
) AS t(order_id, first_shipped_on, amount, refunded, net_revenue))
TO 'expected.parquet' (FORMAT PARQUET);

Running it writes one Parquet file per table, and both get committed. The tests read the Parquet, which I’d take over CSV because the types travel in the file: a decimal stays a decimal, where a CSV reader has to guess. The nightly job can load the same files into the warehouse untouched. Reviewers read rows.sql, since nobody can review Parquet in a pull request.

Rows get written by hand. I wouldn’t sample them from production, because a sampled row is in the fixture by accident. A fixture row should be there for a reason that fits in a comment, like the ones in the file above. Okapi’s cases run from 8 to 40 rows.

Twenty lines of pytest

DuckDB runs inside the Python process, the way SQLite does, with no server and no credentials. A fresh database per test costs almost nothing.

# tests/test_models.py
from collections import Counter
from pathlib import Path

import duckdb
import pytest
import sqlglot

ROOT = Path(__file__).parent.parent
WAREHOUSE_DIALECT = "snowflake"  # whatever your warehouse speaks


def cases():
    for case in sorted(p for p in (ROOT / "tests" / "cases").glob("*/*") if p.is_dir()):
        marks = [pytest.mark.warehouse_only] if (case / "warehouse_only").exists() else []
        yield pytest.param(case, id=f"{case.parent.name}/{case.name}", marks=marks)


def result(con, sql):
    cur = con.execute(sql)
    return [col[0].lower() for col in cur.description], Counter(cur.fetchall())


@pytest.mark.parametrize("case", list(cases()))
def test_model(case):
    con = duckdb.connect()  # in-memory, gone when the test ends
    con.execute("SET TimeZone = 'UTC'")  # match the warehouse session, not the laptop
    for f in case.glob("*.parquet"):
        if f.stem != "expected":
            con.execute(f"CREATE TABLE {f.stem} AS SELECT * FROM '{f.as_posix()}'")
    model_sql = (ROOT / "models" / f"{case.parent.name}.sql").read_text()
    local_sql = sqlglot.transpile(model_sql, read=WAREHOUSE_DIALECT, write="duckdb")[0]
    expected = result(con, f"SELECT * FROM '{(case / 'expected.parquet').as_posix()}'")
    assert result(con, local_sql) == expected

Under the imports that’s twenty lines. Each test gets an empty in-memory database, loads the case’s input files as tables, translates the model out of the warehouse’s dialect, runs it, and compares its columns and rows with the expected file. The comparison goes through a Counter, so row order is ignored and a duplicated row still fails. A set would hide it.

The helper expects plain SELECT statements. Compile templated models first and point it at the output.

By the end of its first week Okapi has 62 cases across the 14 models. They run in 3.4 seconds on a laptop, and CI runs the same command on every commit. Loaded into the warehouse, the same 62 take 11 minutes.

The refund that counted twice

One of those 62 is the case above. Order 1002 ships in two parcels and has one refund of 25.00. The model reports 50.00.

order_revenue joined orders to shipments for the first ship date, then joined refunds and summed. By the time the refunds arrived, order 1002 was already two rows, so its refund was added once per parcel. Most orders ship in one, which kept the error small and the monthly total believable. The fix is to sum refunds to one row per order before the join. The case stays.

Nobody had written that case while the tests lived on the warehouse, and I blame the wait. When a run costs minutes, people test the orders they expect to work. At 3 seconds they try the awkward one just to see.

Four places the laptop and the warehouse disagree

SQLGlot rewrites syntax and some defaults, but it works from the text alone. It can’t see column types or session settings, and two engines can run the same statement to different answers. I’d keep this list next to the tests.

Difference How it bites What I’d do
Time zones DuckDB’s session zone defaults to the machine’s, so truncating a zoned timestamp to a day can pass on a laptop and fail in CI Pin the zone in the helper. Send zone-conversion functions to the nightly run
Integer division 7 / 2 is 3 on engines that truncate and 3.5 on DuckDB. Decimal results also differ in how many places they keep Cast explicitly and round once, at the end of the model
Null ordering Engines disagree on whether NULLs come first in a descending sort, which changes the row a window function picks Write NULLS FIRST or NULLS LAST in the model
QUALIFY Not standard SQL. DuckDB accepts it, but ROW_NUMBER() ... = 1 over tied sort keys can keep a different row on each engine Add a tiebreaker to the ORDER BY, and a fixture with a tie

DuckDB is also still pre-1.0, and its 0.8 release changed the defaults for both division and null order. Pin the version in the test requirements and upgrade on purpose.

Three cases that stay on the warehouse

Three more Okapi cases report revenue by the customer’s local day, using the warehouse’s zone-conversion functions. I wouldn’t argue with a translation there. Each of those folders gets an empty warehouse_only file, which the helper turns into a pytest marker. pytest.ini registers the marker and puts -m "not warehouse_only" in addopts, so a plain pytest leaves them out.

The nightly job is the thin line at the end of the diagram. It runs all 65 cases on the real warehouse through a sibling of the helper, which loads the same Parquet files into a scratch schema and runs each model untranslated. It takes a little over 11 minutes, at an hour when nobody is waiting.

Fixtures only test logic. Query cost and the odd shapes in real data need checks on the real tables.

A case that’s green on the laptop and red at night is a dialect difference nobody had listed. It gets a row in the table and a fixture that shows it. Whenever the two engines disagree, the warehouse is right.