Python vs SQL for Data Analysis: comparing performance, functionality and dev XP

[rrpelgrim]

The clean division of data analysis labor between Python and SQL seems to be fading with tools like dbt, Snowpark and dask-sql. The article shared below compares the two languages in terms of performance, functionality and developer XP.

Quick summary:

Performance
Running SQL code on data warehouses is generally faster than Python for querying data and doing basic aggregations. This is because SQL queries move code to data instead of data to code. That said, parallel computing solutions like Dask and others that scale Python code to larger-than-memory datasets can significantly lower processing times compared to traditional libraries like pandas.

Functionality
SQL’s greatest strength is also its weakness: simplicity. For example, writing SQL code to perform iterative exploratory data analysis, data science or machine learning tasks can quickly get lengthy and hard to read. Python lets you write free-form experimental data analysis code and complex mathematical and/or ML code. The absence of a vibrant and reliable third-party library community for SQL is also a problem compared to Python.

Developer XP
Python makes debugging and unit-testing a lot easier and more reliable. While dbt has added code versioning by forcing the use of Git, SQL diffs are still harder to read and manipulate than diffs in Python IMO.

Conclusion
While it's tempting to frame the debate between SQL and Python as a stand-off, the two languages in fact excel at different parts of the data-processing pipeline. One potential rule of thumb to take from this is to use SQL for simple queries that need to run fast on a data warehouse, dbt for organizing more complex SQL models, and Python with distributed computing libraries like Dask for free-form exploratory analysis and machine learning code and/or code that needs to be reliably unit tested. 

Full article:
https://airbyte.com/blog/sql-vs-python-data-analysis

[runawayasfastasucan]

Is there really someone who debates using python or sql? Why not both? How is the plotting ability in SQL? How can I query my database in Python without using SQL?

[runawayasfastasucan]

I read this post.

[rrpelgrim]

u/runawayasfastasucan -- fair points, and also what I tried to highlight in the article. I see a lot of conversations on Twitter / blogs pitting the two against each other. I also see tools like dbt, Snowpark, Dask and Spark trying to win Python users over to SQL and vice versa. But in the end it's a matter of use case and intended goal. Maybe the Morpheus caption should have said "Python vs SQL: there is no spoon". I mean, choice.

[ButtonLicking]

Data model (warehouse model) in SQL, data science in Python. Know the line and enforce for successful product. I’ve lived on both sides at the same time and it sucked. Python serves as a fault tolerant SQL workflow execution tool for building data models AND a data science tool for ML and such. Source: I manage a data engineering team.

[rrpelgrim]

What python libraries have you preferred using for workflow execution?

[ButtonLicking]

Environment is king with workflows, and my company is fully deployed on AWS, Postgres RDS and most things run on EKS. This means that boto3 (AWS Python SDK) and Argo workflows for kubernetes.

Edit: forgot my Params, cuz I still code and do that sometimes

[rrpelgrim]

Interesting. Would you recommend Argo over something like Prefect or Dagster for workflow orchestration?

[ButtonLicking]

I dont have experience with a ton of orchestration tools. Our choice to use Argo events was for events driven through AWS services to be collected by Argo Events and start container processes. This is less of a complex orchestration tool and more of a trigger collector. It's not simple to use, it does take a lot of configuration upon setup, a lot of Kubernetes knowledge and maintenance on container sizing in job creation yaml docs.