Benchmark Suite

SQE ships with sqe-bench, a Rust CLI tool that generates benchmark data, loads it into SQE via the read_parquet() TVF, and runs query suites to validate SQL correctness and measure query performance.

For the longitudinal view (every benchmark JSON in benchmarks/results/ plotted across time, per-suite, per-scale, per-query heatmaps), see getsqe.com/performance. Charts auto-regenerate from the committed JSONs via make benchmark-charts.

Available Benchmarks

Why these benchmarks? Each covers a different slice of SQL correctness:

• TPC-H and SSB validate the analytical core: joins, aggregates, GROUP BY, ORDER BY, date arithmetic. TPC-H is the standard first check for any SQL engine.
• TPC-DS is the hardest. Its 99 queries exercise correlated subqueries, CTEs, window functions, GROUPING SETS, and complex multi-table joins. Passing TPC-DS well means the engine handles real analytical workloads.
• TPC-C and TPC-E cover OLTP patterns: point lookups, small aggregates, indexed access by key ranges, plus write operations (DELETE, UPDATE) exercised via Copy-on-Write.
• TPC-BB exercises semi-structured data alongside the TPC-DS schema, useful for validating string functions and JSON handling.

Results (SF1, vs Trino 465)

The numbers below are the latest SF1 run, as of 2026-06-12, against Trino 465 on identical Iceberg tables and S3 storage. All 222 queries pass (222/222). SQE wins six of seven suites at SF1.

Run-to-run variance is real, so treat each figure as approximate. The rank order is stable across the last month of runs.

Where SQE trails

SSB is the one suite SQE loses at SF1: 8.3s against Trino’s 5.8s, a 0.70x result. SSB is a denormalized star schema built for fast star-join filtering. Trino ships build-side key sets (bloom filters) into its scans, which prunes the lineorder fact table before it is read. SQE’s equivalent, shipping build-side key sets to distributed workers, is in progress. The lineorder fact has a uniform foreign-key distribution that defeats row-group min/max pruning, so the runtime filter only helps at row level today.

TPC-DS has the most misses at SF1 (93/99). The six gaps are GROUPING SETS edge cases around grand-total row presence; they are the same six since March, not new regressions. TPC-E passes 11/11 but is the suite that historically needed the most work: it joins across 33 tables and uses IN-subquery patterns that DataFusion cannot always decorrelate, so deep-join queries dominate its run time.

The SF1 wins are decisive. At SF10 the picture narrows. On the level rig (Trino 481, totals across runs, single-node / distributed-2-worker / Trino range):

At SF10, TPC-H distributed (95.5s) lands inside Trino’s range, roughly par to ahead. TPC-DS distributed (338.3s) sits inside Trino’s range, close. SSB still trails at SF10, the same pattern as SF1. These are SF10 figures on a single rig, not the canonical SF1 results above.

How it is validated

Timing data is only as good as the result data behind it. Two layers of validation run before any number is trusted.

The first layer is differential validation against Trino. sqe-bench compare <suite> runs every query against SQE (Flight SQL) and Trino (HTTP) on the same Iceberg tables and diffs the result rows. A row-count or value mismatch fails the query. A query that returns zero rows on both engines is reported as vacuous, not a match: agreement on nothing validates nothing.

The second layer is an independent data oracle. DuckDB’s official dsdgen output loads side by side with the generated parquet, with per-table row counts and per-column null fractions checked, and all queries run against both datasets inside DuckDB. A query that returns rows on official data and none on ours is a generator-fidelity bug, found without either SQL engine in the loop.

The oracle earned its keep. It flagged 16 vacuous TPC-DS queries as generator gaps rather than engine bugs, and it settled the one genuine engine disagreement in SQE’s favor: TPC-DS q75 differs by two rows because Trino’s DECIMAL(17,2) division rounds two ratios up past a < 0.9 filter and drops them. DuckDB matches SQE exactly. The benchmark that looked like an SQE failure was a Trino rounding bug.

For the longitudinal view across every committed run, see the benchmark history on getsqe.com.

Generating Data

The generate command produces Parquet files on local disk or S3. Data is deterministic (seeded) so results are reproducible.

# Generate TPC-H at scale factor 1 (~1 GB, 8 tables)
cargo run -p sqe-bench -- generate tpch --scale 1 --output ./data

# Scale factor 10 (~10 GB)
cargo run -p sqe-bench -- generate tpch --scale 10 --output ./data

# Write directly to S3
cargo run -p sqe-bench -- generate tpch --scale 1 \
  --output s3://bench-data/ \
  --s3-access-key AKIA... \
  --s3-secret-key ... \
  --s3-endpoint http://localhost:9000 \
  --s3-region us-east-1

# Generate all benchmarks at once
./scripts/benchmark-generate-all.sh

Scale factors explained

The scale factor controls dataset size. Scale factor 1 produces roughly 1 GB for TPC-H and TPC-DS; SSB is ~600 MB at SF1.

Files are split at 128 MB for parallelism. Output is structured as:

./data/
└── tpch/
    └── sf1/
        ├── lineitem/
        │   ├── part-0000.parquet
        │   └── part-0001.parquet
        ├── orders/
        │   └── part-0000.parquet
        └── ... (8 tables total)

Direct-to-Iceberg Sink

generate --sink iceberg skips the staging Parquet step entirely. Instead of writing files to --output for a later load run, it connects straight to the Iceberg REST catalog, creates the tables, and commits data files with one fast_append per table. The sink works for every generator benchmark (TPC-H, TPC-DS, SSB, TPC-C, TPC-E, TPC-BB, ClickBench, and bank), not just the bank demo schema that introduced the sink.

cargo run -p sqe-bench -- generate tpch --scale 10 --sink iceberg \
  --catalog-uri http://localhost:8181/api/catalog \
  --warehouse bench --namespace tpch \
  --client-id ... --client-secret ...

The command creates the namespace if it does not exist, then generates and commits each table in turn. Data is written in parallel shards for TPC-H and bank; the other generators write one shard per table.

The sink buffers one full generation shard in memory before writing it, so peak memory scales with shard size: per-table for the serial generators, per rows / --threads for TPC-H and bank. At large scales, raise --threads to shrink each shard, or fall back to the parquet staging path (generate then load) if memory stays tight.

Two flags control repeated runs against the same namespace:

• --resume skips a table that already carries a sqe-bench.table.<name>=done property, so an interrupted or repeated generate run does not redo work or duplicate rows. The marker is set on the table itself, not derived from snapshot history, because some catalogs serve a trimmed snapshot list.
• --clean drops and recreates every table first, so the run is idempotent regardless of what state the namespace was in. --clean and --resume are mutually exclusive.

Without --resume or --clean, re-running generate against a table that already holds data commits another fast_append on top of it, duplicating every row. The done marker is only consulted when you pass --resume.

Catalog and storage settings match the load command’s --s3-* flags, plus the catalog’s OAuth2 client-credentials pair (--client-id/--client-secret) or a pre-acquired --bearer-token. See sqe-bench generate --help for the full flag list.

Loading Data

The load command connects to SQE and creates Iceberg tables using read_parquet() + CTAS. No intermediate format conversion is needed. Parquet files are read directly and written as Iceberg.

# Load TPC-H from local disk
cargo run -p sqe-bench -- load tpch \
  --scale 1 \
  --data ./data \
  --host localhost \
  --port 60051 \
  --username root \
  --password ""

# Load from S3
cargo run -p sqe-bench -- load tpch \
  --scale 1 \
  --data s3://bench-data/ \
  --s3-access-key AKIA... \
  --s3-secret-key ... \
  --s3-endpoint http://localhost:9000 \
  --s3-region us-east-1 \
  --host localhost \
  --port 60051 \
  --username root \
  --password ""

# Drop and recreate tables before loading
cargo run -p sqe-bench -- load tpch --scale 1 --data ./data --clean \
  --host localhost --port 60051 --username root --password ""

# Use the Trino HTTP protocol instead of Flight SQL
cargo run -p sqe-bench -- load tpch --scale 1 --data ./data \
  --protocol trino \
  --host localhost \
  --port 8080 \
  --username root \
  --password ""

The loader creates a namespace named <benchmark>_sf<N> (e.g., tpch_sf1) and sends one CTAS statement per table:

CREATE TABLE tpch_sf1.lineitem AS
SELECT * FROM read_parquet('/data/tpch/sf1/lineitem/*.parquet');

For S3 sources, inline credentials are injected:

CREATE TABLE tpch_sf1.lineitem AS
SELECT * FROM read_parquet(
  's3://bench-data/tpch/sf1/lineitem/*.parquet',
  access_key => 'AKIA...',
  secret_key => '...',
  endpoint => 'http://localhost:9000',
  region => 'us-east-1'
);

See read_parquet TVF for full syntax documentation.

Fast benchmark runs via attached golden tables

For the read-only suites (tpch, ssb, tpcds, clickbench), the load step dominates wall-clock time and adds nothing to the query measurement: every run rewrites the same parquet into fresh Iceberg tables before a single query runs. Publish those tables once into a persistent Polaris, then attach them read-only on every subsequent run instead of reloading.

Publish once:

BENCH_GOLDEN_POLARIS_URL=https://polaris.example.com/api/catalog \
BENCH_GOLDEN_WAREHOUSE=golden_warehouse \
BENCH_GOLDEN_TOKEN=<bearer> \
BENCH_S3_ENDPOINT=https://s3.example.com \
BENCH_GOLDEN_S3_ACCESS_KEY=... BENCH_GOLDEN_S3_SECRET_KEY=... \
BENCH_SCALE=1 ./scripts/benchmark-publish-iceberg.sh

Then run any read-only suite with BENCH_DATA_SOURCE=attach:

BENCH_GOLDEN_POLARIS_URL=https://polaris.example.com/api/catalog \
BENCH_GOLDEN_WAREHOUSE=golden_warehouse \
BENCH_GOLDEN_TOKEN=<bearer> \
BENCH_S3_ENDPOINT=https://s3.example.com \
BENCH_GOLDEN_S3_ACCESS_KEY=... BENCH_GOLDEN_S3_SECRET_KEY=... \
BENCH_DATA_SOURCE=attach BENCH_SCALE=1 ./scripts/benchmark-test.sh tpch

benchmark-test.sh attaches the golden catalog once (scripts/benchmark-attach-golden.sh) and queries each table as golden.<namespace>.<table> instead of generating and loading data. tpcc and tpce are write suites (their queries include DELETE/UPDATE via CoW), so they still generate and load normally even in attach mode; a shallow-clone path that would let them run against golden tables too, without mutating the shared copy, is planned but not yet built. bank and tpcbb also still generate and load normally in attach mode: bank is published to golden by benchmark-publish-iceberg.sh, but attach mode does not yet query it from there, and tpcbb’s own tables (web_clickstreams, product_reviews) are not published at all, since tpcbb shares tpcds’s namespace and only needs the two tables it adds. Wiring bank/tpcbb into attach mode is the deferred follow-up alongside the tpcc/tpce shallow-clone path. Attach is loud by design: a missing or unreachable golden catalog fails the run with the ATTACH error rather than silently falling back to a full load, which would mask the exact cost this path removes.

Unified harness (benchmark.sh + sqe-bench run)

For the read-only suites (tpch, ssb, tpcds, clickbench), the unified harness combines data attachment with query execution in one command:

BENCH_PROFILE=local BENCH_SCALE=1 scripts/benchmark.sh tpch ssb tpcds clickbench

The scripts/benchmark.sh orchestrates two steps. First, it attaches the golden Iceberg catalog (published once via benchmark-publish-iceberg.sh). Second, it runs the suites through sqe-bench run, which executes all queries against the attached tables and emits JSON reports to benchmarks/results/.

Configuration profiles live in benchmarks/profiles/<name>.toml. Profiles define the coordinator config, catalog credentials, and suite-specific settings. Credentials come from environment variables or AWS profiles, never stored in the TOML file itself. The profile loader redacts secrets on error and checks for committed keys at startup.

Each read-only suite attaches golden (zero load); write suites (tpcc, tpce) are a follow-up plan. The old benchmark-*.sh scripts remain: the migration to unified is a later commit after parity is confirmed.

Running Tests

The test command executes all queries in the benchmark suite against the loaded data and reports correctness and timing.

# Run all TPC-H queries (Flight SQL, default)
cargo run -p sqe-bench -- test tpch \
  --scale 1 \
  --host localhost \
  --port 60051 \
  --username root \
  --password ""

# Run a single query
cargo run -p sqe-bench -- test tpch --scale 1 --query q03 \
  --host localhost --port 60051 --username root --password ""

# Use Trino HTTP protocol
cargo run -p sqe-bench -- test tpch --scale 1 \
  --protocol trino \
  --host localhost \
  --port 8080 \
  --username root \
  --password ""

# Run all benchmarks end-to-end
./scripts/benchmark-test.sh tpch
./scripts/benchmark-test.sh tpcds
./scripts/benchmark-test.sh ssb

Query result statuses

DIFF is not treated as a failure in CI. It is a signal for investigation. Decimal precision differences are expected when comparing float-heavy aggregates across different engines.

Queries can declare their requirements in a header comment:

-- name: Revenue by nation
-- requires: delete, merge
-- timeout: 30s
SELECT ...

Any query with -- requires: will be SKIPped if SQE does not support that feature, rather than FAILing the suite.

Understanding Results

Terminal output

TPC-H SF1 - Flight SQL (localhost:50051)
─────────────────────────────────────────
q01  PASS   1.23s   6001215 rows
q02  PASS   0.45s       460 rows
q03  PASS   0.89s     11620 rows
...
q17  DIFF   2.10s         1 rows  (decimal precision)
q22  PASS   0.33s         7 rows

Results: 20/22 PASS, 1 DIFF, 1 SKIP
Total time: 28.4s
Report: benchmarks/results/tpch-sf1-flight-2026-03-24T14:30:00.json

JSON report format

Reports are written to benchmarks/results/<benchmark>-sf<N>-<protocol>-<timestamp>.json:

{
  "benchmark": "tpch",
  "scale_factor": 1,
  "protocol": "flight",
  "host": "localhost:50051",
  "timestamp": "2026-03-24T14:30:00Z",
  "summary": {
    "total": 22,
    "pass": 20,
    "fail": 0,
    "diff": 1,
    "skip": 1,
    "error": 0,
    "total_duration_ms": 28400
  },
  "queries": [
    {
      "id": "q01",
      "status": "pass",
      "duration_ms": 1230,
      "rows": 6001215
    },
    {
      "id": "q17",
      "status": "diff",
      "duration_ms": 2100,
      "rows": 1,
      "diff_detail": "decimal precision mismatch: expected 1.0000, got 0.9999"
    }
  ]
}

JSON reports are machine-readable and suitable for tracking regressions over time in CI.

Historical Performance Tracking

Benchmark JSON results are committed to benchmarks/results/ for historical comparison. This enables tracking performance regressions and improvements across releases. The committed JSONs feed the per-suite, per-scale, per-query timeline on getsqe.com/performance; refer there for the longitudinal view of how each suite moved across the optimization work.

Comparing against Trino

The benchmark harness can run the same suite against a real Trino on the same data, so you can compare SQE and Trino directly. The Results section above is the output of exactly this run at SF1. There are two modes:

• Correctness parity: --compare-trino diffs SQE’s results against Trino’s row-for-row. This is how SQL correctness is validated at scale, not just timing. A row-count or value mismatch fails the query. A query that returns zero rows on both engines is reported as vacuous, not a match, because agreement on nothing validates nothing. Small decimal differences on float-heavy aggregates are flagged for investigation rather than treated as failures.
• Timing: the same run records per-query wall-clock for both engines, so a head-to-head speed comparison falls out of the parity run.

Run a comparison yourself and see the captured numbers in the benchmark quickstart: Benchmarks: TPC-H / TPC-DS / SSB, or in the repo under benchmarks/.

CI/CD Integration

All three scripts support automated use without a TTY:

# Generate data once (idempotent - skip if files exist)
./scripts/benchmark-generate-all.sh

# Load all benchmarks
./scripts/benchmark-load.sh

# Run and report
./scripts/benchmark-test.sh tpch
./scripts/benchmark-test.sh tpcds
./scripts/benchmark-test.sh ssb

# Exit code is 0 if all queries are PASS or SKIP
# Exit code is 1 if any query is FAIL or ERROR

A typical CI pipeline runs TPC-H at SF1 as a smoke test on every PR, and the full suite (TPC-H + TPC-DS + SSB) nightly.

Query Files

Query SQL files are stored under benchmarks/queries/<benchmark>/:

benchmarks/
├── queries/
│   ├── tpch/     q01.sql ... q22.sql
│   ├── tpcds/    q01.sql ... q99.sql
│   ├── ssb/      q1.1.sql ... q4.3.sql
│   ├── tpcc/     order_status.sql, stock_level.sql, ...
│   ├── tpce/     trade_lookup.sql, customer_position.sql, ...
│   └── tpcbb/    q01.sql ... q10.sql
├── expected/
│   ├── tpch/sf1/    q01.csv ... q22.csv
│   └── ...
└── schemas/
    ├── tpch.sql
    ├── tpcds.sql
    └── ...

Expected results under benchmarks/expected/ are CSV files containing the correct output at the given scale factor. They are committed to the repository and used for regression checking.

Adding New Benchmarks

Implement the BenchmarkGenerator trait in sqe-bench/src/generate/:

#![allow(unused)]
fn main() {
pub trait BenchmarkGenerator {
    fn name(&self) -> &str;
    fn tables(&self) -> Vec<TableDef>;
    fn generate_table(
        &self,
        table: &str,
        scale: f64,
        output: &dyn ObjectStore,
        prefix: &str,
    ) -> Result<GenerateStats>;
}

pub struct TableDef {
    pub name: String,
    pub schema: Arc<Schema>,             // Arrow schema
    pub row_count_fn: fn(f64) -> usize,  // scale factor to row count
}
}

Steps to add a benchmark:

1. Create sqe-bench/src/generate/<name>.rs implementing BenchmarkGenerator.
2. Add SQL query files under benchmarks/queries/<name>/.
3. Add expected result CSVs under benchmarks/expected/<name>/sf1/.
4. Register the generator in sqe-bench/src/generate/mod.rs.
5. Add the benchmark name to the CLI subcommand list in sqe-bench/src/cli.rs.
6. Add a schema DDL file under benchmarks/schemas/<name>.sql for reference.