3 ms·

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 i

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.

vovavili 29d ago
You should be using dbt instead of string manipulation for serious query building.
- sanderjd 29d 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 29d 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.
    - throwaway7783 29d ago
      My issue with DBT is it is a mix of SQL, yaml, jinja2 flow controls (and metrics is whole another thing). SQL with jinja2 if/else can get really unmaintainable quickly. It's perhaps better than homegrown sql based transformers. polars is code and can be version controlled too. Dataframes in my opinion are more elegant, and with the right backends and some lineage enhancements, could serve a much wider set of use cases than what DBT does
      - vovavili 29d ago
        Different tools for different tasks.
    - sanderjd 29d ago
      But is that ... good?
      - vovavili 28d ago
        It's definitely better than raw 500+ lines of SQL composed with 300+ lines of Python for some bespoke business transformation without clean versioning, which is what SQL transformations tend to converge towards without something like dbt.
        geysersam 28d ago
        Versioning seems like a separate problem, no? I mean, you can version control your python program generating SQL queries
        sanderjd 28d ago
        Yeah that's fair. In practice, the projects I've worked on have always seemed to end up with a mix of all of dbt, SQL strings in python, and dataframe manipulation. I've always felt like we should be able to minimize one of these in favor of the others, but in practice it seems like people find each of these things useful for different things.
- geysersam 29d 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.
  - vovavili 29d ago
    Embedded databases like DuckDB are just as suited for dbt work.
  - OoooooooO 27d 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.

vovavili 29d ago

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

sanderjd 29d 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 29d 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.
  - throwaway7783 29d ago
    My issue with DBT is it is a mix of SQL, yaml, jinja2 flow controls (and metrics is whole another thing). SQL with jinja2 if/else can get really unmaintainable quickly. It's perhaps better than homegrown sql based transformers. polars is code and can be version controlled too. Dataframes in my opinion are more elegant, and with the right backends and some lineage enhancements, could serve a much wider set of use cases than what DBT does
    - vovavili 29d ago
      Different tools for different tasks.
  - sanderjd 29d ago
    But is that ... good?
    - vovavili 28d ago
      It's definitely better than raw 500+ lines of SQL composed with 300+ lines of Python for some bespoke business transformation without clean versioning, which is what SQL transformations tend to converge towards without something like dbt.
      - geysersam 28d ago
        Versioning seems like a separate problem, no? I mean, you can version control your python program generating SQL queries
      - sanderjd 28d ago
        Yeah that's fair. In practice, the projects I've worked on have always seemed to end up with a mix of all of dbt, SQL strings in python, and dataframe manipulation. I've always felt like we should be able to minimize one of these in favor of the others, but in practice it seems like people find each of these things useful for different things.
geysersam 29d 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.
- vovavili 29d ago
  Embedded databases like DuckDB are just as suited for dbt work.
- OoooooooO 27d 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.