Summary
sqlmesh currently pins sqlglot~=30.4.2 (v0.234.1) / ~=30.8.0 (v0.236.1), which caps the compatible minor version. Several BigQuery→DuckDB transpilation fixes that landed in sqlglot v30.14.0 are therefore unreachable for users running sqlmesh unit tests against DuckDB.
Upstream sqlglot PRs (all merged)
| PR |
Title |
Merged |
| tobymao/sqlglot#7891 |
feat(duckdb): transpile BigQuery ARRAY_CONCAT_AGG to DuckDB |
2026-07-21 |
| tobymao/sqlglot#7892 |
feat(duckdb): transpile BigQuery IN UNNEST(array) to ARRAY_CONTAINS |
2026-07-23 |
| tobymao/sqlglot#7893 |
feat(duckdb): convert ARRAY_AGG IGNORE NULLS to FILTER clause |
2026-07-21 |
These were first released in sqlglot v30.14.0 (2026-07-27).
Impact
Without these fixes, BigQuery models that use ARRAY_CONCAT_AGG, value IN UNNEST(array), or ARRAY_AGG(… IGNORE NULLS) cannot be tested with sqlmesh test (DuckDB engine) because:
ARRAY_CONCAT_AGG passes through untranspiled → DuckDB rejects itIN UNNEST(array) transpiles to IN (SELECT UNNEST(…)) instead of ARRAY_CONTAINSARRAY_AGG IGNORE NULLS silently drops the IGNORE NULLS clause → wrong results
Workaround
We currently maintain a sqlglot fork that backports these fixes onto the 30.4.x base, and install it over the locked sqlglot in CI. We'd like to drop this workaround once sqlmesh's constraint allows >=30.14.0.
Compatibility note
Installing sqlglot >=30.14.0 with sqlmesh 0.234.1 or 0.236.1 fails at import time:
TypeError: _parse_types() got an unexpected keyword argument 'with_collation'
(in sqlmesh.core.engine_adapter.spark.SparkEngineAdapter)
So this likely requires a code change in sqlmesh as well, not just a constraint bump.
Request
Please bump the sqlglot dependency to >=30.14.0 (or broader) in a future sqlmesh release so that these upstream transpilation improvements are available to users running BigQuery models in DuckDB test environments.
Summary
sqlmesh currently pins
sqlglot~=30.4.2(v0.234.1) /~=30.8.0(v0.236.1), which caps the compatible minor version. Several BigQuery→DuckDB transpilation fixes that landed in sqlglot v30.14.0 are therefore unreachable for users running sqlmesh unit tests against DuckDB.Upstream sqlglot PRs (all merged)
ARRAY_CONCAT_AGGto DuckDBIN UNNEST(array)toARRAY_CONTAINSARRAY_AGG IGNORE NULLStoFILTERclauseThese were first released in sqlglot v30.14.0 (2026-07-27).
Impact
Without these fixes, BigQuery models that use
ARRAY_CONCAT_AGG,value IN UNNEST(array), orARRAY_AGG(… IGNORE NULLS)cannot be tested withsqlmesh test(DuckDB engine) because:ARRAY_CONCAT_AGGpasses through untranspiled → DuckDB rejects itIN UNNEST(array)transpiles toIN (SELECT UNNEST(…))instead ofARRAY_CONTAINSARRAY_AGG IGNORE NULLSsilently drops theIGNORE NULLSclause → wrong resultsWorkaround
We currently maintain a sqlglot fork that backports these fixes onto the 30.4.x base, and install it over the locked sqlglot in CI. We'd like to drop this workaround once sqlmesh's constraint allows
>=30.14.0.Compatibility note
Installing sqlglot
>=30.14.0with sqlmesh 0.234.1 or 0.236.1 fails at import time:(in
sqlmesh.core.engine_adapter.spark.SparkEngineAdapter)So this likely requires a code change in sqlmesh as well, not just a constraint bump.
Request
Please bump the sqlglot dependency to
>=30.14.0(or broader) in a future sqlmesh release so that these upstream transpilation improvements are available to users running BigQuery models in DuckDB test environments.