ibis: There are a few features that make Ibis amazing, but they are not about the syntax ... but rather the practices that it might enable.
- The first feature is that it can help you support multiple backends. If some of your data lives in DuckDB and another lives in BigQuery, you can still use a single tool to tackle both.
- The second feature is that it helps you write code that can run both on your laptop and on the backend. If locally you're using pandas but in production you're using BigQuery, you may need to rewrite a lot of code manually. Not only is that time wasted, but it's also error-prone. Ibis can help you write code that can run on both immediately.
- There's also the matter of vendor lockin. If a vendor suddenly become more expensive for your use-case then it's much easier to switch if you can re-use the same codebase. Ibis can play a big role there.
- Finally, and this may be the best feature, Ibis can make it much easier to write unit tests for your SQL queries. If you have a SQL query that you want to test for a proprietary database, how would you automatically test that in CI? With Ibis you can write all the tests you want, and if they work on a pandas backend then the odds are pretty good that they'll work on the backend too. As long as you're aware of the supported backend features, you might be all set.
Another big feature is that you can keep writing Python code and don't have to worry as much about SQL.