Skip to content

Search docs and blog

ADR: SQLGlot Use and Non-Use

Status

Accepted (codifies existing practice as of 2026-05-03).

SQLGlot version at acceptance: >=20.0.0,<31.0.0 in pyproject.toml; resolved version 30.6.0 in uv.lock. Decisions were validated against that version; gaps may have changed since.

Maintenance protocol: Re-review when (a) a new SQLGlot major is adopted, (b) the phase taxonomy in the phase-aware pipeline ADR changes, or (c) sqlglot[c] is adopted in production.

Date

2026-05-03

Context

BenchBox runs benchmarks across 36 SQL platforms plus DataFrame engines (Polars, Pandas, DataFusion-Python, PySpark, Dask, Modin, cuDF, LakeSail). Cross-platform portability requires translating canonical benchmark SQL into something each engine accepts and executes correctly. We chose SQLGlot for that translation. The recent mypyc work delivered ~5x parser, ~2.5x generator, ~2x optimizer speedups for our workload.

Over the last 12 months BenchBox accreted a substantial layer of SQL infrastructure on top of SQLGlot, organized into seven categories: dialect normalization, post-generation fixups, three per-platform query transformers, a 19-platform DDL rewrite registry, hand-written query overrides, a SQL-to-DataFrame facade, and the sql_compat rule engine. This ADR documents why we use SQLGlot for some decisions and deliberately do not use it for others; the sibling ADR documents how the rule engine is organized.

Decision

SQLGlot is the canonical translation engine for query-shaped SQL between SQL dialects. Every SQL query travels through SQLGlot at the query_compile phase of the sql_compat pipeline unless an explicit registry decision says otherwise.

SQLGlot is deliberately not used for five categories outside what a syntactic transpiler can structurally do:

  1. Engine-semantic query rewrites (well-formed SQL, wrong results)
  2. DDL physical-design semantics (engines, sort/shard keys, table formats)
  3. Hand-written per-engine query variants (emitter output the engine refuses)
  4. SQL-to-DataFrame translation (Polars, Pandas, etc.)
  5. Compatibility governance (registry, lint, capability matrix)

Non-Goals

  • Replacing or forking SQLGlot.
  • Adopting sqlglot[c] immediately (deferred pending wheel-availability check across our supported Python versions and architectures).
  • Pinning a specific SQLGlot version permanently.
  • Standardizing every dialect-branching call site. Legitimate local rendering (type mapping, identifier quoting, storage layout) remains in adapter code, gated by @compat_local.

Where SQLGlot is Canonical

OperationSiteNotes
Source-to-target SQL translationbenchbox/utils/dialect_utils.py translate_sql_query()Centralized wrapper; pre/post processors plug in here
Identifier quoting, function/operator renaming, type-syntax conversionSQLGlot generatorPer-dialect; type aliasing (e.g., VARCHAR to STRING for Doris) is handled at the DDL layer
AST parsingSQLGlot parserConsumed by query_adapter and query_compile phases
Roundtrip parsing of TPC-base SQLSQLGlotDefault base is netezza; dialect_utils.normalize_dialect_for_sqlglot() maps unsupported targets to nearest peer

SQLGlot handles the long tail of SQL syntax variation (joins, CTEs, window functions, aggregates, basic types, LIMIT to TOP N, EXTRACT to DATE_PART, function renames). This is the 80% case.

Where SQLGlot is Deliberately Not Used

1. Engine-Semantic Query Rewrites

The case: SQL is well-formed in the target dialect’s grammar and accepted by SQLGlot’s generator, but the target engine’s planner executes it incorrectly. User-visible failure mode: “the query ran and returned wrong results.” A syntactic transpiler cannot catch this; the bug lives in the engine planner, not the SQL.

These cases are tagged failure_mode=SILENT_CORRUPTION in the rule registry; see the FailureMode enum in the phase-aware pipeline ADR.

Steelman and rebuttal: One could argue a portability-focused transpiler should ship rewrite recipes for known engine bugs. We do not adopt this position: SQLGlot’s job is dialect-correct emission, and an engine-bug registry is its own artifact (it could live alongside SQLGlot, in a sidecar project, or in project-specific layers). We would happily contribute to such an artifact if one emerges.

Implementation: benchbox/platforms/datafusion_query_transformer.py rewrites four TPC-H queries (Q11, Q16, Q18, Q20). The query_adapter rules register them as REWRITE_QUERY actions with a transformer_id per query. Each rewrite is validated against TPC-H reference answers.

Retirement protocol: A rewrite is a candidate for retirement when (a) the engine release notes claim the bug is fixed, (b) the rewrite-bypass version passes validation against TPC-H references, and (c) the rule is removed and CI re-runs validation. Retirement is opt-in.

2. DDL Physical-Design Semantics

DDL physical design (engines, sort keys, shard keys, table formats, distribution policies) depends on deployment context. SQLGlot is a query transpiler; this is correctly outside its scope.

Examples we ship per platform:

  • ClickHouse: strip Nullable NOT NULL; inject ENGINE=MergeTree() ORDER BY (...)
  • Doris: TIME->VARCHAR(8); STRING->VARCHAR(65533); ARRAY[N]->ARRAY; inject DUPLICATE KEY + DISTRIBUTED BY HASH
  • SingleStore: strip FK; CREATE REFERENCE TABLE for dimensions; inject SHARD KEY + SORT KEY (in order)
  • Databricks: CREATE OR REPLACE TABLE ... USING DELTA TBLPROPERTIES(...)
  • pg_mooncake: preserve heap DDL for COPY, then promote loaded tables into mooncake mirrors

Implementation: 19 platform-specific modules under benchbox/sql_compat/rules/ddl_optimize/, plus the per-adapter _optimize_table_definition() hook documented in the phase-aware pipeline ADR.

Fabric Warehouse occupies a third path between “in the registry” and “not in the registry”: fabric_dw_ddl_rewrites.py registers a governance_only=True rule so compat_lint can audit the platform, but the runtime transformation lives in FabricWarehouseAdapter._optimize_table_definition() because the schema prefix it injects depends on adapter state. The registry rule is the governance marker; the rewrite is operational.

3. Hand-Written Per-Engine Query Variants

When SQLGlot’s emitter produces SQL the engine refuses, we ship hand-written variants. The clearest example is H2O Q9 (PERCENTILE_CONT WITHIN GROUP):

  • ClickHouse needs quantile()
  • StarRocks needs PERCENTILE_APPROX()
  • MySQL/SingleStore accepts the ANSI form, but only without the multi-expression WITHIN GROUP ORDER BY (CASE WHEN ... THEN ... ELSE NULL END) decorator that SQLGlot emits

Function translation between vendor-specific ordered-set aggregates is non-bijective; every choice the emitter could make fails on at least one engine. Variants live under benchbox/sql_compat/rules/query_source/ (h2odb_variants.py, nyctaxi_variants.py, coffeeshop_variants.py, tpcdi_variants.py, vector_search_variants.py). The fallback is a SELECT_VARIANT action at the query_source phase, which short-circuits SQLGlot.

4. SQL-to-DataFrame Translation

DataFrame engines (Polars, Pandas, DataFusion-Python, PySpark, Dask, Modin, cuDF, LakeSail) do not speak SQL; SQLGlot translates SQL into more SQL, so this is structurally outside its scope. benchbox/platforms/dataframe/unified_frame.py is the SQL-shaped facade (approximately 4,200 lines, the largest single file in our cross-platform layer). The sql_compat registry’s dataframe_filter phase carries skip/variant decisions for DataFrame mode.

5. Compatibility Governance

The registry, lint, and capability-matrix layer is BenchBox-specific and exists to govern the four categories above. SQLGlot has no opinion about our compatibility policy.

Alternatives Considered

  • SQLGlot only (no infrastructure on top): rejected. Early BenchBox releases operated this way; produced silent validation failures (DataFusion Q11/Q16/Q18/Q20), table-creation failures (ClickHouse, Doris), and parse errors (QuestDB comma joins, MySQL ordered-set aggregate decorators). Every category in the section above is something we tried not doing.
  • Custom parser/transpiler: rejected. Building a maintained transpiler covering 20+ dialects is years of work; the 20% we add is at the boundary, not the core.
  • sqlglotrs (Rust): rejected for now. Pure-Python compilation via mypyc gets us most of the speedup without a Rust toolchain. SQLGlot performance has not been a BenchBox bottleneck.
  • Adopt sqlglot[c] immediately: deferred. Speedups would benefit our workload, but we want to verify wheel availability across our supported Python versions and architectures, and we have not rigorously measured SQLGlot’s share of the BenchBox runtime budget.
  • Hand-write SQL per platform: rejected. BenchBox ships 22 TPC-H + 99 TPC-DS queries plus several other suites across 36 platforms; manually maintaining ~792 TPC-H query variants alone is operationally infeasible.

Consequences

Positive

  • The boundary between “SQLGlot’s job” and “BenchBox’s job” is documented; contributors can decide where new compatibility work belongs without re-deriving it.
  • SQLGlot upgrades are routine; we do not depend on SQLGlot to do things it does not promise.
  • Each of the five categories has a separate, documented home; a contributor hitting a DataFusion silent-corruption case knows to add a rule in query_adapter, not file a SQLGlot bug.
  • The compatibility surface is auditable via the sql_compat inventory tool.

Negative / Risks

  • The infrastructure on top of SQLGlot covers seven categories and grows with each new platform; contributors evaluating cross-engine support must read this ADR plus the phase-aware pipeline ADR.
  • Engine-version drift can re-open closed semantic-rewrite cases. Stale rewrites accumulate (the retirement protocol is opt-in).
  • Hand-written variants are a maintenance burden when benchmark specs change; mitigated by keeping the variant set small.

Neutral

  • We use SQLGlot’s main release line at our normal cadence. If we adopt sqlglot[c] later, this ADR does not need updating; the boundary is the same regardless of how SQLGlot is compiled.

Upstream Contribution Policy

We are open to contributing patches upstream where they fit SQLGlot’s mission: bug fixes in dialect generators (e.g., GROUP BY ALL quoting in DuckDB), and new tests for dialects we exercise heavily (Doris, QuestDB, DataFusion). We do not expect SQLGlot to absorb engine-semantic rewrites, DDL physical-design, hand-written variants, DataFrame translation, or compatibility governance.

How to Decide Where New Work Goes

  1. Does SQLGlot already handle this when called via dialect_utils.translate_sql_query()? If yes, no work needed.
  2. Syntactically wrong on the target engine, fix is dialect-generic? Add a post-processor in dialect_utils.py; register as a post_translate action.
  3. Specific to one platform’s syntax quirks? Add a query_adapter rule with REWRITE_QUERY and a transformer_id.
  4. SQL is correct but the engine returns wrong results? Add a query_adapter rule with REWRITE_QUERY that produces the same answer through a different shape; validate against reference answers.
    • 4a. If the AST rewrite is structurally unsafe (corrupts queries exercising EXCEPT/INTERSECT, GROUP BY at subquery boundaries, etc.) and the engine offers a session knob, prefer SET_SESSION_POLICY over REWRITE_QUERY. Canonical example: ClickHouse TPC-DS Q23/Q87 joined_subquery_requires_alias=0.
  5. Issue in DDL? Add a ddl_optimize rule and implement the transformation in the platform’s _optimize_table_definition() hook.
  6. Emitter produces SQL the engine refuses with no clean AST fix? Hand-write a variant in sql_compat/rules/query_source/; register a SELECT_VARIANT action.
  7. DataFrame engine? Add to unified_frame.py or the per-engine adapter; use dataframe_filter for skip/variant decisions.

If none fit, open an issue or draft PR; the existing taxonomy may need to extend.

Cross-References

BenchBox is open source under the MIT License.