The Ibis project blog covers Ibis, a Python dataframe library that runs on backends like DuckDB, with benchmarks and usage posts.
Commenters recommend Ibis as a Python dataframe API over DuckDB and other backends, praising lazy execution, larger-than-memory data, and speed, though one says it gets little discussion.
1. I would argue there are no "real alternatives". The two most proximate alternatives in feature space are Ibis and Snowpark.
- Ibis because while it can target multiple engines (as we state in our docs, we are built on and heavily reliant on Ibis), it aims to be "single engine, single session" in its execution in that nothing is expected to persist beyond the current session and an Ibis expression can only have a single engine. We want to be multi-engine and have some artifacts durable across sessions (by way of caching)
- Snowpark because it is sort of "multi-engine" by way of external functions or python stages, but locked to Snowflake. In some sense, we want to be Anypark: Snowpark like functionality but centered on whatever engine of choice is desired and performant interop with any other engines.
2. We don't have anything I would hold out as benchmarks yet. We don't aim to be "best in class" / the "fastest engine", we aim to be "in class" for as many operations as possible (we use the word performant). Our goal is to make it easy for an org to choose whichever engine(s) they feel most performant in when they consider the full space of {developer,computation} x {time,cost}. However, Hussain has demonstrated how having information from the "whole pipeline" available but execution deferred can allow for specialized optimization by way of predicate pushdowns (https://ibis-project.org/posts/udf-rewriting/)
Thanks for your interest and please feel free challenge any of…
Look into [Ibis](https://ibis-project.org/). It's a dataframe library built on duckdb. It supports lazy execution, greater than memory datastructures, remote s3 data and is insanely fast. Also works with basically any backend (postgres, mysql, parquet/csv files, etc) though there are some implementation gaps in places.
I previously had a pandas+sklearn transformation stack that would take up to 8 hours. Converted it to ibis and it executes in about 4 minutes now and doesn't fill up RAM.
It's not a perfect apples to apples pandas replacement but really a nice layer on top of sql. after learning it, I'm almost as fast as I was on pandas with expressions.
I felt the same - have to relearn/lookup everything every time I went back to a project or wanted to do some operations that are simple to describe in SQL but I couldn't wrap my mind around e.g. using multi-indexed dataframes & aggregations properly. These days, I always jump to Polars instead of Pandas - much more intuitive and consistent API. Tons of props to Pandas for all that they did (and continue to do) in the data space, but their API did not evolve very well IMO.
I've also been wanting to play with Ibis[1] recently, but Polars has been sufficient for me.
[1] https://ibis-project.org/
You can compose SQL with https://ibis-project.org/tutorials/ibis-for-sql-users, which is using https://github.com/tobymao/sqlglot to parse the SQL under the hood.
As an alternative to parsing the SQL yourself, DuckDB's https://duckdb.org/docs/api/python/relational_api allows you to compose SQL expressions efficiently and lazily, which I've used when playing around with things like https://gist.github.com/ajfriend/eea0795546c7c44f1c24ab0560a846b9
Not a big fan of SQL dsl's personally. If you feel you'd need a better query syntax and plotting for interactive use, I'd suggest looking at the dataframe libraries that have a larger community to discover how to solve things and have ironed out issues.
Specifically dataframe logic is easily mappable to dbplyr https://dbplyr.tidyverse.org/articles/sql-translation.html for those that know R and ibis https://ibis-project.org/ for Python/Pandas
Ive found polars quite intuitive, though for python, I lean more towards [ibis](https://ibis-project.org/). The interface is nearly identical, but ibis has the benefit if building sql queries before pulling any actual data (like dbplyr) — whereas polars requires the data to be in-memory (at least for rdb’s, though correct me if Im wrong)
this to me seems like a good argument for only using ibis, but Im happy to be convinced otherwise