A pytest plugin for testing SQL pipelines. Drop CSVs in a folder, write SQL, assert on results. Optional Parquet snapshot testing for regression detection.
No Docker. No dbt. No ORM.
pip install pytest-duckdb- Place CSV files in
tests/fixtures/— each becomes a DuckDB table (filename = table name) - Write tests using the
duckdb_sessionfixture — a fresh in-memory DuckDB per test - Optionally snapshot results to Parquet for regression testing
A fresh :memory: DuckDB connection with all CSVs from the fixtures directory auto-loaded as tables.
def test_orders_exist(duckdb_session):
count = duckdb_session.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
assert count == 10Each test gets complete isolation — tables created in one test don't leak to another.
Callable fixture for Parquet-based snapshot testing:
def test_revenue(duckdb_session, sql_snapshot):
result = duckdb_session.execute("""
SELECT customer_id, SUM(amount) as total
FROM orders GROUP BY customer_id
""").fetchdf()
sql_snapshot(result)- First run: saves result to
tests/__snapshots__/{module}_{test}.parquet - Subsequent runs: compares against stored snapshot, raises
AssertionErrorwith a clear diff on mismatch (shows which rows/columns differ) - Regenerate:
pytest --snapshot-update
Executes a .sql file referenced by the @pytest.mark.sql marker:
@pytest.mark.sql("queries/revenue_by_customer.sql")
def test_revenue_query(sql_result):
assert len(sql_result) > 0The SQL file runs against duckdb_session, so it has access to all loaded CSV tables.
- pandas installed: fixtures return
DataFrame - pandas not installed: fixtures return
QueryResult(list-of-tuples with a.columnsattribute)
In pyproject.toml or pytest.ini:
[tool.pytest.ini_options]
duckdb_fixtures_dir = "tests/fixtures" # default
duckdb_snapshots_dir = "tests/__snapshots__" # default--snapshot-update Regenerate all Parquet snapshots
--duckdb-fixtures-dir=PATH Override fixture directory
tests/
├── fixtures/
│ ├── orders.csv → becomes `orders` table
│ └── customers.csv → becomes `customers` table
├── queries/
│ ├── simple.sql
│ └── join.sql
├── __snapshots__/ → auto-created on first snapshot run
├── test_queries.py
└── test_transforms.py
@pytest.mark.sql("queries/monthly_revenue.sql")
def test_monthly_revenue(sql_result, sql_snapshot):
# sql_result runs the SQL file against loaded CSVs
# sql_snapshot saves/compares the result
sql_snapshot(sql_result)- Python 3.9+
- pytest ≥ 7.0
- duckdb ≥ 0.9.0
- pandas (optional — for DataFrame returns)
You can configure DuckDB behaviour via pyproject.toml or pytest.ini:
| Option | Type | Default | Description |
|---|---|---|---|
duckdb_fixtures_dir |
string |
tests/fixtures |
CSV fixture directory |
duckdb_snapshots_dir |
string |
tests/__snapshots__ |
Parquet snapshot directory |
duckdb_config |
string (multiline) |
"" |
DuckDB session config (see below) |
duckdb_fixture_schemas |
string (multiline) |
"" |
Explicit CSV column types |
Pass DuckDB configuration options at connection time:
# pyproject.toml or pytest.ini
[tool.pytest.ini_options]
duckdb_config = """
default_order = desc
threads = 4
"""Or via the CLI:
pytest --duckdb-config default_order=descPrevent non-deterministic read_csv_auto type inference by declaring column types explicitly:
[tool.pytest.ini_options]
duckdb_fixture_schemas = """
orders: order_id INTEGER, amount DOUBLE, customer_id INTEGER
customers: id INTEGER, name VARCHAR
"""When a schema is provided, the specified types are enforced at table creation time.
Columns not listed in the schema are silently dropped. Types with parameters
(e.g., VARCHAR(255), DECIMAL(10,2)) are fully supported.
If you're migrating SQL queries to DuckDB, watch for these common gotchas:
DuckDB follows the SQL standard: "text" is a column identifier, not a string.
-- ❌ DuckDB: "hello" looks for a column called hello
SELECT "hello" AS greeting;
-- ✅ Use single quotes
SELECT 'hello' AS greeting;Enable strict_mysql_compatibility mode to allow double-quoted strings:
[tool.pytest.ini_options]
duckdb_config = """
strict_mysql_compatibility = true
"""DuckDB infers CSV column types from sampling. Results can differ between
DuckDB versions or platforms — integers sampled as VARCHAR on one machine,
BIGINT on another. This breaks snapshot tests.
Fix: declare explicit types with duckdb_fixture_schemas (see Config Reference above).
DuckDB raises an error where other databases silently coerce:
-- ❌ DuckDB: Binder Error — no implicit cast from VARCHAR to INTEGER
SELECT '1' + 1;
-- ✅ Cast explicitly
SELECT '1'::INTEGER + 1;This affects snapshot-based tests: a column typed as BIGINT on one run might
be VARCHAR on another, causing query failures. Another reason to use
duckdb_fixture_schemas.
DuckDB's DATEADD, DATEDIFF, and DATE_TRUNC use a different argument
convention than PostgreSQL/Redshift:
-- PostgreSQL: DATEDIFF(unit, start, end)
-- DuckDB: DATEDIFF(unit, start, end) ✓ same convention
-- But DATE_TRUNC in DuckDB expects the timestamp first, not the part:
-- DuckDB: DATE_TRUNC(timestamp, part) ← swapped!Write queries with named parameters or alias functions for portability.
DuckDB uses json_extract_* functions, not PostgreSQL's ->>, #>>
operators:
-- ❌ PostgreSQL operators (not supported in DuckDB)
SELECT data ->> 'name' FROM events;
SELECT data #>> '{user, id}' FROM events;
-- ✅ DuckDB equivalents
SELECT json_extract_string(data, '$.name') FROM events;
SELECT json_extract_string(data, '$.user.id') FROM events;| Gotcha | Symptom | Fix |
|---|---|---|
| Double-quoted strings | Binder/column "X" not found |
Single quotes or strict_mysql_compatibility=true |
read_csv_auto inference |
Flaky types across runs | duckdb_fixture_schemas with explicit types |
| Strict casting | Binder Error: no implicit cast |
Explicit ::TYPE casts |
| Date function args | Wrong results or errors | Verify args against DuckDB docs |
| JSON extraction | Parser Error: syntax error |
Use json_extract_* functions |
MIT