Comment by geysersam

3 days ago

I agree sql is more elegant. The problems arise when you have to add logic on top of sql. Often I end up constructing queries via string manipulation and that is not very ergonomic. Polars api is more verbose and complex than sql but at least it's not meta-programming.

The duckdb python api is okay, but it is a bit limited, no ctes, no as of join, and it can be slow at bind/interpretation time when you do stuff like unioning multiple relations in a loop (I think that becomes O(N^2), but I might be wrong). Most issues can be worked around, but Polars is designed from the ground up to be used from python.

12 comments

geysersam

Reply
vovavili  3 days ago

You should be using dbt instead of string manipulation for serious query building.

  • sanderjd  3 days ago

    It's never quite been clear to me what the advantage of dbt over a python program using sqlalchemy / duckdb / polars to transform data is. Can you enlighten me?

    • vovavili  3 days ago

      At the minimum, it's just Jinja2 templates in your SQL queries - meaning, you can do pure SQL transformations with conditional logic in your templates. In addition to being able to run tests, specify custom macros, having version control and having some constrained way to organize your tables, you're turning SQL into a proper programming language with just one library.

      6 replies →

  • geysersam  3 days ago

    I've been looking at dbt for exactly this reason but don't quite get the advantages if you're not interacting with a data warehouse of some sort.

    • OoooooooO  2 days ago

      Spark/PySpark/polars/dbt/sqlmesh are all data engineering frameworks for data transformation (and some data scientists).

      If you don't have a data warehouse / OLAP system you are generally not in the niche for those tools.