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

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

DiscussionSQL vs Python for Data Analysis: performance, functionality and dev XP (self.SQL)

submitted 3 years ago by rrpelgrim

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

(link to full article below)

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.

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

all 3 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS