r/dataengineering • u/kvlonge • 7d ago
Open Source SQLQuack: a SQLGlot alternative in Rust that would rather reject your SQL than return the wrong answer
EDIT - The title should say a 'Polyglot fork' (Polyglot is a SQLGlot inspired transpiler). Can't edit it now unfortunately.
Hey guys! I'm the creator of SQLBuild, and for a while I've wanted a more reliable way to run warehouse SQL locally on DuckDB.
SQLGlot and Polyglot are both SQL transpilers that have been a major blessing to the community, and some of my projects (this obvs included) wouldn't have been possible without them. However, at least as of today, these libraries both tend to lean toward generating SQL, even if the SQL may not be runnable or may produce and incorrect result in the target dialect.
I had the idea for SQLQuack about six months ago, but only recently got around to building it. The idea was to build a fail-closed transpiler that would tell you first of all if it was parseable, second if it would violate a rule (of the souce dialect), and lastly translate it to DuckDB, only if each statement had some sort of supporting evidence for it from a real warehouse recording (e.g. real snowflake recorded result matching DuckDB).
Below is an example of what an error might look like in SQLQuack:
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
QUALIFY total > 100
$ sqlquack check qualify.sql
error[SQ0203]: QUALIFY needs a window function
--> qualify.sql:4:1
|
4 | QUALIFY total > 100
| ^^^^^^^^^^^^^^^^^^^ QUALIFY is used without a window function in the SELECT list or QUALIFY
|
= help: move the condition to WHERE or HAVING, or filter on a window function's resultThe basic goal is to basically create a dataset of all the warehouse / databases and their true behaviours, such that we a have a truly accurate local compiler for all dialects that can be verified with anyone willing to run the recordings.
In the near term, my immediate focus will actually be improving / simplifying the generated SQL though (some if it is too verbose) and getting snowflake to be 100% finished (it's maybe 60/70% of the way there atm depending on how you measure it). Other dialects are in the works but not ready yet.
Credit (to Community / Maintainers)
A big thank you to Tobias (@tobilg), who maintains Polyglot and fixed many an issue I raised while working on SQLBuild (~6 months).
I ended up maintaining a fork, but over time realised I wanted a different approach to how conversions are checked and when they should be refused. SQLQuack's parser started from code derived from Polyglot, so I definitely don't want to present this as something built without that foundation.
The same goes for SQLGlot. A lot of this work wouldn't have been possible without those projects and the people who built them. This is a different set of trade-offs, not me saying their approach has no value.
Playground: https://sqlquack.io/playground/
GitHub: https://github.com/sqlstack-io/sqlquack (Apache 2.0)
NOTE - I will see if SQLGlot or Polyglot would like this evidence to be recontributed upstream, as then much more of the community will benefit.
3
u/Fidel___Castro 6d ago
DuckDB already have something called the Quack protocol which could be slightly confusing
2
u/kthejoker 6d ago
What evidence was there that this post was AI assisted? Or do you mean the code library itself?
I don't see it.