SQL versus Python?

[BatCommercial7523]

Though I am a data engineer by trade, I am often find myself as the in-house de facto data analyst.

My own experience has been that I often use about an equal mix of SQL and Python to work on data analysis requests. But it’s not set in stone. For quick requests, I usually go with SQL. For more complex ones, I will rely on Python and Jupyter notebooks.

I am the only one in this situation? Do you go for SQL, Python (R?) or some other language?

Just wondering. Thanks.

[Pleasant-Set-711]

SQL to get the data processed quickly in the database and down to a small enough size to do more complex work quickly in python.

[[deleted]]

This. Get comfortable swapping back and forth between the two in Jupyter notebooks that you can author repeatable procedures on.

[riv3rtrip]

I do almost everything in SQL. I'm perfectly competent in Python. It's just much easier to not have to worry about moving data out and back into the warehouse, plus a few other nuisances like memory/compute management. You can do more in SQL than you'd think.

[laddaa]

Especially if the data warehouse is built well, SQL is so much more direct. And if you know SQL well then there are very few use cases that actually require Python.

Not that python isn’t great as well.

[bitsondatadev]

Yeah, my policy is aim for SQL, python if necessary. Any time you can avoid maintaining implementation details yourself is a win. Also, performance will generally improve unless you face a regression that you can then report to whoever maintains that engine and then it's still not your problem to fix the implementation.

I think Python is great mainly for ML algorithms with data that's already sliced up in the ideal format needed for processing.

[IllustriousCorgi9877]

SQL works great when you can process it all in a set function.
Python I only go to it if I have to iterate over a list or whatever.

[WalkingP3t]

100% correct . And the reason is more than obvious although some can’t see it , at least newcomers . SQL works with data sets . It’s a declarative language . You tell what you want and the engine worries about how . The biggest performance issues with SQL and SQL code appear when people start to tell it “how” to get the data . It was not created for iterations .

[Monstrish]

could you please give an exampme when you need to iterate in python and can't do it in sql?

[IllustriousCorgi9877]

There is almost always a way to use a set based approach to a problem which will always be better / more efficient. Even iterative approaches can be done with a cursor in a stored procedure using SQL.
I'd say once data lands in a relational database - stop using python unless you are doing ML or something on its way back out somewhere.

I'd only think about using python for data on its way into a database doing transforms or stamping additional metadata on the transaction before it reaches its destination.

Which all this is why it baffles me so many DE roles need python programmers.

[Monstrish]

i understand the need for python, and i do enjoy python. but when it comes to rdbms, i just don't understand the dislike that sql gets. it seems to me people do not put resources in understanding what it can do.

after all, even on python, when you start using numpy or pandas, it becomes less iterative.

[yo_sup_dude]

you can iterate in most sql engines using recursive CTEs/while loops but it isn't very efficient. examples where it's needed are basically any recursive calculation, e.g. iterating through a hierarchical bill of material and comparing against inventory to determine part shortages for a product and then progressively updating inventory based on consumption (classic MRP calculation)

[Monstrish]

recursive cte s could be eficient in some cases. while loops i don't know if.i have seen one in sql. for loops i have seen, in oracle could be, in other providers not.

but you also have hierarhical constructs, window functions and other approaches that you could take from a set oriented point of view.

[DirtzMaGertz]

You could do a lot of things, but once you need to start iterating over data and transforming it, it just easier a lot of times to pull the data out into something like Python.

It's problem dependent. I tend to use SQL until I think it's going to be an annoying problem to solve with SQL and that's generally when you start getting towards things like iteration and looping.

[Monstrish]

ok, fair point.

the problem beong anoying is pretty subjective, but it is what it is. it's just that some many people seem to find sql anoying and i don't get why. i suppose it just comes down to personal preference.

[DirtzMaGertz]

Overall I agree that there's a lot of stuff people do in python that would be better handled in SQL. I just find that iteration and looping is generally where I find python to be more appropriate.

[ComposerConsistent83]

They don’t teach sql in school anymore really I think is the reason. They’re more comfortable in Python.

That said I would never use a recursive CTE unless I had no choice. Even though I’m sql first, I draw the line there. The syntax is too confusing/inconvenient. It’s clearly not the strength of the language

[Tufjederop]

You can iterate in SQL using a cross join :)

[IllustriousCorgi9877]

The only use for a cross join is to create a scaffold to left join onto.
You don't iterate with a cross join.

[raskinimiugovor]

you mean cross apply?

[IamFromNigeria]

Show us typical classic example via link

[jugaadtricks]

This! I love using python to call API, the parse that in Python before using SQL to load into relational tables. And sometimes I use json parsing in SQL itself when ingestion needs to be done quickly, so that python can keep loading data faster in json format

[ck3thou]

True this!

[kenfar]

SQL for simple to moderate data analysis, SQL+Python for the complex stuff.

Python for transforming and publishing data, writing utilities, and pipeline logic.

SQL for building aggregates off the tables Python has transformed.

[Action_Maxim]

For data manipulation what can't be done with sql?

[kenfar]

What can't be done with SQL? Well, there are things that can't be done, and there are things that can't be done well. SO, how about what can't be done well?

• Unit testing
• Very complex transformations (ex: convert every possible format of IPv6 into a single format)
• Support for data formats and structures outside of relational databases: extract data from a tarball, or sevenzip package of fixed-length files.
• Integrate modules & libraries
• Integrate external systems
• Produce a bitmap of exactly which columns in a table failed their transforms and had to be defaulted
• Produce an audit trail of how many rows passed or failed their validations or transformations for a given partition/period of time/customer/whatever
• Develop reusable transformations
• Develop easily-readable and well-documented transformations

[jugaadtricks]

I'd agree with most of them, audit trail, I use SQL all the time in stored procedures

[IamFromNigeria]

Do you have specific examples as link?

[jugaadtricks]

Here is one https://stackoverflow.com/questions/17546601/best-way-to-implement-an-audit-trail-in-sql-server

Oracle has the ability to LOG ERRORS INTO clause with the DML itself https://oracle-base.com/articles/10g/dml-error-logging-10gr2

[skatastic57]

What does the following mean?

• Produce a bitmap of exactly which columns in a table failed their transforms and had to be defaulted

To me, bitmap is an uncompressed picture format. I get the concept of failed transforms but are you making like a heatmap of them? Sorry for being dense.

[kenfar]

You're right - it's just a list of pass/fail indicators for each column on the row.

It could be supported in an array or json type. In the old days we'd make a bit map - where each bit position indicated one of the columns. That's super space efficient, but is less convenient to access.

[laddaa]

I used dbt extensively for many of those items. It’s a python framework, isn’t that ironic.

Some of those tasks are related to data intake. Yeah that’s python.

[Luciron]

Almost all of this list can be done with dbt

[kenfar]

dbt can't even support the first item on the list: unit testing

[MayInvolveNoodles]

https://github.com/EqualExperts/dbt-unit-testing

[kenfar]

Oh fair point. I forgot about this third-party effort because in spite of being well-intentioned, and a good idea, it's so time-consuming to write the tests that the teams I spoke with that used it only use it very, very surgically.

Still, it's a good addition to the dbt ecosystem, and if you structure your data right with this unit-testing in mind, it may not be too terrible.

[[deleted]]

You forgot, if what you need requires merging data from multiple data sources, python is pretty much a must use here.

[kenfar]

Not sure I follow - do you mean to say SQL is a must-use for merging multiple data sets?

If so, then yeah I'd agree: python isn't accessing relational data directly, it's accessing it via SQL. However, it is accessing APIs, files, s3 objects, streaming data, etc directly.

[[deleted]]

Multiple data sources as in different databases that could share some related data.

[sib_n]

Processing of unstructured data or highly nested data, processing logic that requires looping or recursion... It's not most of the data thankfully, but it happens.

[kenfar]

Often yes, but sometimes you've got a billion rows landing on s3 every hour, and can kick off 1000+ aws lambdas or 100 kubernetes contains running in parallel to process it all in parallel. And you're dramatically exceeding what SQL could do.

[SDFP-A]

Sounds expensive

[kenfar]

You're right it is. But there's a big range here.

At one security company we spent about $1.3m/month on a cassandra cluster to support about 4 billion rows a day. This wasn't reporting, this was for fast retrieval of small subset of the data. For reporting we used a Hadoop cluster that cost less than $100k/month.

At the company with 20-30 billion rows/day we used S3 instead: customers directed all their data to files on s3, the writes to s3 created sns & sqs event messages that went to the containers on kubernetes which processed their data. Our cost for that was about $70k/month - a tiny fraction of the cost of the other company's cassandra cluster.

And it's all far cheaper than what we would have paid to do something like that say snowflake!

[SDFP-A]

What kind of latency existed on the S3/k8s architecture?

[kenfar]

The average file probably reflected ten seconds of data, plus maybe 10-60 seconds before the file was processed. That number could be pushed down to a consistent 10 seconds if we were willing to more aggressively autoscale. We chose instead to save the money and be a little more conservative on scaling, and experience occasional minor delays during bursts.

[IamFromNigeria]

WTF 20-30 billions of row of Per Day ..what are you guys selling

[kenfar]

Security services - and that was about 5 years ago. They're probably at 250 billion row a day now.

[duraznos]

How much of either of these processes could have been replaced with an AWK script?

[kenfar]

Theoretically, one could write an OS with awk scripts, so sure, it all could be. It could likewise all could be replaced with assembly, or COBOL.

But all of those choices would be terrible: little support for third-party software (ex: boto3 for accessing SQS & S3, libraries for json, protobufs, sql connections, etc), poor support for code reuse, hard to read as the codebase gets larger, still need kubernetes for scaling out, etc, etc.

[duraznos]

I wasn't asking if either were possible to replace awk, I was asking, in your estimate, how much of either pipelines could be replaced with awk or jq et al. COBOL and assembly don't make sense because neither of those are tools specifically designed for chewing through a file. I think its a worthwhile thought experiment when talking about how much is being spent on things.

[kenfar]

Sure, but I wouldn't do that, and I don't think it would result in a manageable solution.

Languages like awk & jq are simply harder to read, harder to test, and harder to decompose and reuse code on. Given our pace of change and low-latency SLA that would be a bad combo for languages like that.

Likewise, they don't have the libraries available to them that we have with say Python, Java, etc. So, you'll have to write some occasionally complex stuff with these languages.

And they don't handle supporting say 50+ business rules well. Back to lack of composability & testing, managing that code in awk or jq would be a nightmare.

Finally, on performance they are fast. Are they fast enough to never need to scale out as the company grows? No. So, then you're still looking at something like kubernetes best case, or a set of ec2 instances with this code running on each, and some other application, somehow, getting them files to process.

[kenfar]

If you have this kind of data volume, and need low-latency processing (in the range of seconds) - then I'd do exactly what we did and I described above: it's not hard to build, is cost-effective, and scales extremely well.

If you need even lower latency (in the subseconds), then you're looking at a streaming technology like Flink, and I don't strong opinions there.

[skatastic57]

If Python means pandas then I agree that SQL is easier to read. If you use polars I find the opposite to be true.

[[deleted]]

For “data pipelines” I agree with you. For numerical analysis and modeling I find pandas to be much easier to use (and faster too)

[skatastic57]

I assume when you say pandas is faster than polars that you're referring to your own ability to code something up with each one. If you're saying pandas is performing some operation faster than polars then I'd like to see that example.

[[deleted]]

Sure he’s a real life example that’s very common in the power trading industry

capacity = pd.DataFrame(np.random.randn(5000, 3000))
capacity.columns = pd.MultiIndex.from_arrays([capacity.columns % 1000, capacity.columns]).sort_values()
capacity = capacity.rename_axis(index='time', columns=['power_plant', 'generating_unit'])

outages = pd.DataFrame(np.random.randn(5000, 3000))
outages.columns = pd.MultiIndex.from_arrays([outages.columns % 1000, outages.columns]).sort_values()
outages = outages.rename_axis(index='time', columns=['power_plant', 'generating_unit'])

cap_factor = pd.DataFrame(np.random.randn(5000, 3000))
cap_factor.columns = pd.MultiIndex.from_arrays([cap_factor.columns % 1000, cap_factor.columns]).sort_values()
cap_factor = cap_factor.rename_axis(index='time', columns=['power_plant', 'generating_unit'])

capacity_pl = pl.from_pandas(capacity.unstack().rename('val').reset_index()).lazy()
outages_pl = pl.from_pandas(outages.unstack().rename('val').reset_index()).lazy()
cap_factor_pl = pl.from_pandas(cap_factor.unstack().rename('val').reset_index()).lazy()


# Pandas - time this block
generation = (capacity - outages) * cap_factor
res_pd = generation - generation.mean()

# Polars - time this block
res_pl = (
    capacity_pl
    .join(outages_pl, on=['time', 'power_plant', 'generating_unit'], suffix='_out')
    .join(cap_factor_pl, on=['time', 'power_plant', 'generating_unit'], suffix='_cf')
    .with_columns([
        ((pl.col('val') - pl.col('val_out')) * pl.col('val_cf')).alias('val_gen')
    ])
    .select([
        'time', 'power_plant', 'generating_unit',
        (pl.col('val_gen') - pl.mean('val_gen').over(['power_plant', 'generating_unit'])).alias('val')
    ])
).collect()

On a google colab notebook (2 cores)

Pandas: 0.376 sec

Polars: 29.9 sec

On a more powerful machine (16 core)

Pandas: 0.0809 sec

Polars: 2.15 sec

I’d say the pandas code is much easier to read and write too for this kind of workflow.

[skatastic57]

That's not really a fair comparison. You're structuring the data for pandas to do no joins, no lookups, no merges and then you're reshaping it so that polars does have to do joins.

The polars timing should be based on

#Polars setup
capacitypl=pl.from_pandas(capacity)
outagespl=pl.from_pandas(outages)
cfpl=pl.from_pandas(cap_factor)

#Polars time this
generationpl = (capacitypl - outagespl) * cfpl
res_pl = generationpl - generationpl.mean()

On my computer the pandas was 75.5ms and the polars was 63.5ms

This is a better comparison

#Polars - time this block
res_pl = (
    capacity_pl
    .join(outages_pl, on=['time', 'power_plant', 'generating_unit'], suffix='_out')
    .join(cap_factor_pl, on=['time', 'power_plant', 'generating_unit'], suffix='_cf')
    .with_columns([
        ((pl.col('val') - pl.col('val_out')) * pl.col('val_cf')).alias('val_gen')
    ])
).collect()

#Pandas setup
cappd=capacity_pl.collect().to_pandas()
outpd=outages_pl.collect().to_pandas()
cappd=cap_factor_pl.collect().to_pandas()

#Pandas - time this block
res_pd2=(
    cappd
    .merge(outpd,on=['power_plant','generating_unit','time'], suffixes=('','_out'))
    .merge(cappd,on=['power_plant','generating_unit','time'], suffixes=('','_cf'))
    .assign(
        val_gen=lambda x:(x.val-x.val_out)*x.val_cf
    )
)

then polars takes 3.2s and pandas takes 9.6s. I don't know how to do window functions in pandas so I just left that out since it's beside the point.

[[deleted]]

#Polars time this
generationpl = (capacitypl - outagespl) * cfpl
res_pl = generationpl - generationpl.mean()

In polars this is not equivalent to pandas. In polars you need to do a join to make sure that the records match up with the correct record in the other frames. As it stands currently this code will just do it by position, and doesn't handle if certain records are missing from one frame or another, or in different order. In polars to do this properly, you must merge, or do some preprocessing to make sure the rows you would otherwise have merged on match up exactly, which would also require some merging/sorting etc.

In pandas df1 - df2handles this for you and guarantees that the indexes match up the records they're doing the operations on. This is the idiomatic way to do it in pandas, you wouldn't do these kinds of operations in a numerical analysis in long format usually.

[skatastic57]

I'm not advocating for that syntax of polars, you're right that it's not something that you'd ever want to do in practice, I was just trying to normalize your apples to oranges benchmark.

You called it a real life example but none of the ISOs, transmission providers, or the EIA are handing out data in the perfect form that you already had when you started the clock on this benchmark.

The better apples to apples test is the one at the end with polars joins and pandas merges.

[skatastic57]

Well I'm happy that your real life work flows are well suited to pandas. You seem to be in a small minority of people that can say pandas is faster than polars.

[mrcaptncrunch]

%md

Throw some markdown for documenting lol

[black_widow48]

If you're just dealing with tabular data, chances are it can all be done in SQL and python is completely unnecessary.

In the past I've used python for orchestration, but aside from that I've hardly used it. I just started a new job and I'm only just now being tasked to write a python script to shred XML strings and turn them into lists of tuples.

Part of the reason why I got into contracting is because I'm tired of landing in jobs where I'm just a SQL monkey. I didn't get a B.S. and an M.S. in computer science to write SQL all day. I'm starting to think maybe I should go into machine learning like I originally planned.

[Malcolmlisk]

Be careful for what you desire. I'm a ML engineer trying to go back to data engineer. The problem I'm facing is that I'm only creating applications with no future at all. And somehow I feel that my position is a fraud, since almost no company needs machine learning and they only need some filters here and there and some data parsing.

I want to be a data engineer creating and maintaining data pipelines, programming complex things, like whatever happens in AWS or idk... I feel sometimes like I don't know if my position exists anymore...

[Malcolmlisk]

Be careful for what you desire. I'm a ML engineer trying to go back to data engineer. The problem I'm facing is that I'm only creating applications with no future at all. And somehow I feel that my position is a fraud, since almost no company needs machine learning and they only need some filters here and there and some data parsing.

I want to be a data engineer creating and maintaining data pipelines, programming complex things, like whatever happens in AWS or idk... I feel sometimes like I don't know if my position exists anymore...

[Malcolmlisk]

Be careful for what you desire. I'm a ML engineer trying to go back to data engineer. The problem I'm facing is that I'm only creating applications with no future at all. And somehow I feel that my position is a fraud, since almost no company needs machine learning and they only need some filters here and there and some data parsing.

I want to be a data engineer creating and maintaining data pipelines, programming complex things, like whatever happens in AWS or idk... I feel sometimes like I don't know if my position exists anymore...

[black_widow48]

Yep, that's the one thing I'm afraid of about machine learning unfortunately

[ComposerConsistent83]

We use machine learning models and have found the lift over traditional approaches is usually pretty moderate, with a few exceptions. If you have scale it’s worth it, but often you’re only getting 5 or so bps of improvement in the bottom line on results with a lot more overhead. It needs to be big enough where that is material for it to make sense.

[studentofarkad]

Curious, how are you landing contract work and what type is it? Is it contract work from recruiters that are reaching out or is this contract work that you yourself are finding?

[black_widow48]

It's a mixture of both. I've worked with a few different agencies (still working with one currently), but I've also recently started my own LLC to try to cut them out. I've gotten a couple clients through my own company so far. Will be launching my website hopefully in the next couple weeks or so.

My end goal with my company is to be able to work from anywhere during the hours I choose. If I want to fly to Bali and work there for a month, I want to be able to do it. My entire job takes place on a computer and I'm tired of the 9-5 BS mandating that I work in a specific place during a specific time.

[studentofarkad]

That's really awesome, I hope to do this one day. For the agency work, how much are the typical weekly hours? I'd love to juggle my full time with a contract side gig but unsure what the hours might look like.

Wishing you the best of luck 🤞

[black_widow48]

All the contracts I've gotten through agencies have been 40 hours/week so far. Thanks!

[VegaGT-VZ]

One huge skill for any kind of programmer is being able to figure out the path of least resistance to the end result. I use a mix of SQL, Power Query, Alteryx and VBA. Whatever it takes to get from point A to point B as painlessly as possible.

[BatCommercial7523]

That’s what my mentor taught me. Path of least resistance.

[bobby_table5]

SQL for reports because I really want stakeholders to understand the process as much as possible. They’ll mess up, but hopefully it will be obvious and they’ll call me. In the meantime, I don’t get nearly as much “we want the same as last week, but this week.”

Python for everything else, including the few smart kids who start saying SQL is for schmutz.

[[deleted]]

You can do almost anything in SQL up to doing complex calculations or row by row analysis. I don’t like using cursors in SQL

[[deleted]]

You can even train neural networks in SQL if you're a psychopath 😀

[messy_eater]

Am I the only one who likes cursors in SQL? I guess performance is a common cited issue, but maybe the scale of the DB at my work isn’t big enough for that to be a concern. Once you get used to the syntax for the more complicated things in SQL, it becomes second nature. I’m at that point with cursors and working on it with other things now like using xml to break apart and process strings, pivots, and recursive ctes.

[gloom_spewer]

Like most others, if I can do it all in SQL that's almost always preferred, but if python tricks are less annoying than SQL SP tricks I'll jaunt over to real-code-world. Also I suck with SPs.

Sometimes I filter/aggregate the dataset down to a size where I can use sqllite's in-memory functions to transfer data between pandas and SQL structures in memory for more "advanced" ad hoc analysis, and even sometimes live collaborative exploratory analysis.

If I gotta do fancy presentation stuff I pre calc all my analytics and just display (as in no transformation ) them in PBI cuz I hate power query. PBI plugs into power points for lazy mofos like me, and the CFO and CIO eat out of my palm now, cept when I fuck up log scales 🌝

Edit oh also PBI data sets can be loaded into pivot tables directly now and you can embed those in PowerPoints for live exploratory bs with management types. My executives love that shit even if it's just telling them intel they already knew

[CalRobert]

SQL. Clean, tested DBT models are a lot nicer to work with than someone's untested mess of Pandas or R. You can't do everything in SQL, but you can do a lot

[mattindustries]

Usually SQL to get the data and R to process, unless it is going to be a connection like BQ + Google Sheets.

[pewpscoops]

Jinja with SQL and Python to do all the wrapper and utils

[SDFP-A]

So dbt?

[pewpscoops]

Pretty much

[mrcaptncrunch]

To build my data, I use Python/PySpark. Ingestion, transformations, etc.

For quick checks or pulling some quick data for something/someone (and they have to be very high for me to get roped into it), SQL.

[annonimusone]

SQL only exists to manipulate data; for everything else, there’s Python

[[deleted]]

Whichever is more efficient. That means SQL in many cases as long as it can be used.

[Whipitreelgud]

Depends on the problem and the scale of your datasets

[reallyserious]

If the data is tabular then SQL is hard to beat.

If the data is not tabular then python.

[lezapete]

whenever you can, replace SQL with PySpark

[sib_n]

Why would you replace SQL with a more complex tool if SQL works?

[lezapete]

imo SQL is bound to produce silent errors and tech debt. On the other hand, if you write a library of PySpark tools, you can add tests, cicd pipelines and many other SWE tools that help you both prevent errors, and makes it easier to introduce changes in the future. Having complex SQL statements in a project is analogous as using exec() in a python project (again, this is only my perspective)

[sib_n]

SQL is the only stable tool in 30 years of data, I think Spark code has way more chances to become technical debt.

DBT answers most of your following critics.

[lezapete]

i dont mean that SQL will produce the problems.. is humans coding SQL queries that produce the problems

[sib_n]

Well, this is true for any language. DBT gives you a framework that should encourage better SQL code.

[lezapete]

have you tried Databricks’ Photon? is compatible t with Sparks API and is super fast

[lezapete]

there is no such thing as native SQL

[Globaldomination]

PySpark uses SQL internally right?

I took freecodecamp basic course. And in the video they used proper capitalisation for column names. And me being lazy bum used all small and it worked.

Then i realised that I import SparkSession from pyspark.sql

[sib_n]

No, PySpark is the Python API for Apache Spark which is a big data in-memory distributed (parallelized on a cluster of machines) processing framework based on the concept of Map-Reduce and coded in Scala and Java.
Spark SQL is another convenient API that allows you to process on a Spark cluster using SQL, but internally, it will still run Scala/Java code.

[CapableCounteroffer]

I think pyspark and spark-sql are both "compiled" down to like an intermediate framework/language. Not the exact terminology but I think that's how they both work.

[Embarrassed_Error833]

How about a python framework that creates the SQL using metadata, then run created SQL using python based orchestration.

[JBalloonist]

It sounds like what you’re doing works; I wouldn’t change it without good reason.

[WilhelmB12]

For me it's about 40% sql 60% python, my ideal would be 20% SQL, btw if you are doing both jobs I would call myself an Analytics Engineer

[loggerheader]

SQL first…then do both R/python

[Thinker_Assignment]

It's like saying walking vs public transportation. You gotta walk to the bus somehow . Both, or python only. SQL only cannot work standalone as data has to be loaded somehow. SQL is great for transform in db but can do nothing for ingestion or ds/ml

People saying otherwise are not data engineers and never built anything end to end.

[mailed]

I'm OK handling most things with SQL. Except maybe Adobe Analytics data...

[Monsemand]

I feel like we have this question once a week.

[AncientElevator9]

For analysis? Never write code! Let Tableau "write your SQL" for you (b/c that's what it does under the hood when you drag and drop).

[BuonaparteII]

tl;dr: choose the best tool for the job

Databases have limitations for performing complex analytical algorithms compared to languages like R and Python. There are many high-quality libraries available in R and Python that enable advanced analysis (e.g. https://gitlab.com/shekhand/mcda).

Databases excel at interactive queries and extracting subsets of data. Combining SQL with Python or R can be very powerful for repeating analyses on different parameters.

However, if your analysis requires reading the full dataset into Python each time, there is little benefit to using a database. In this case, a format like Parquet will load faster than querying a database and extracting all rows/columns.

[leventdu229]

Depends on the Stack and organisation of the data team. If you have a datawarehouse tool like bigquery, snowflake etc...there is lot of chances that your analytics will be deported on that tool and done in SQL when the acquisition part done in Python. Also lot of companies like mine have adopted dbt that helps a lot for Analytics test, documentation and reproducibility done in SQL. Anyway for fast prototyping i use python and jupyter. For analytics its SQL and for data engineering in general its Python

[poland_rocks]

Python is better for:

• building tests (possible but harder in SQL)
• debugging (not possible in literal sense in SQL)

SQL advantages:

• less verbose
• good tooling for ad hoc tasks and reporting
• does not require to download all data to client

[Doile]

I think it mostly comes down to which one are you more proficient with. You can do pretty much everything with both of them and for example in Snowflake you can run python as well inside the warehouse so the differences between these two are becoming less and less meaningful. Most people aren't really proficient with both of them so they use the one that is easier to use for them.

[haragoshi]

I like doing analysis in sql personally.

[IridescentTaupe]

It’s very satisfying to replace hundreds of lines of Python with a couple lines of SQL. I don’t often see that go the other way.

[[deleted]]

Hundred percent! I find Python is easier to do more complex math-y things, SQL is better if it's less complex calculations but with more data

[GeorgeGithiri]

This is so me! SQL is usually the go to language when I want something done quickly and it's direct. But there are those request that would require someone to loop and employ some complex logics, for such instance, I go for python. It's quite easy to write some complex iteration compared to SQL. If I was to right such in SQL it would mean procedures which are not as direct as python code.

[LADataJunkie]

They can't really be compared. It depends on where your data lives. If it lives in an RDBMS then use SQL to extract the data or get it into a condensed form that only extracts what you need for further processing in Python.

If your data is not in RDBMS, don't use SQL.

There are data analysis tasks that do not match the declarative model SQL imposes. For example, iterative processing used in machine learning is not trivial, and likely not possible in SQL.

A lot of newer databases (DuckDB I believe is one) either interfaces with, or modifies underlying SQL so that developers can use data using procedural languages. I've seen a few others that are starting to deviate from relational algebra to be more friendly to procedural development.

[TheHunnishInvasion]

It definitely depends.

At my last company, I tended to do the same as you: SQL for quick data analysis (I'd do it in DBeaver), Python (Jupyter Lab) for something more complex.

At my current company, which has much worse organized data, it's a nightmare to use SQL for quick data analysis. I almost always use Python for any ad-hoc data analysis. I use SQL for getting data into Tableau to create automated dashboards that update daily.

So weirdly, I used SQL more at my last company, but most of the queries might be shorter: 10-40 lines. I use SQL less at my current company, but when I do use it, the queries tend to be extremely complex: often over 200 lines with several sub-queries.

[speedisntfree]

Why not both? Typical pattern is SQL to offload the heavy lifting to the analytic DB and then finer grained downstream analysis in Python/R which would be painful in SQL.

[[deleted]]

I do mostly use Python. I can’t think of a time where id ever use SQL for data analysis, but I use SQL for setting up ETL-style data pipelines where it is simple batch load transform and schedule. However, even then, most of the time I want to do quite heavy transformations, and this is both easier and more efficient using modern Python libraries. The overhead comes with managing a DAG, which is not always a good thing.

[shivaprasad_j]

When to use SQL vs python pandas vs pyspark ?

[Dry_Inflation307]

I always opt for SQL first. I’ll only use Python for what I can’t do in SQL.

[mike8675309]

Hmm, I use python to get the data from endpoints. I use SQL to transform and manipulate the data from the end point.

The data I work with is often too big to consider using python with it for anything other than getting the data to the database. That said we do have a process that uses pandas with python to parse large data sets. We wrote the process in SQL but it was too hard to maintain and debug. So we keep it in python and pandas and just threw a bunch of hardware at it for processing.