r/dataengineering • • Aug 27 '23

Discussion Why I don't like SQL

The reason I don't like SQL over something like Pandas is because in Pandas/Python I can break down the complex transformations in small simpler components and they are easy to interact and debug with. In SQL my only option is to write stored procedures and yes in that I can also do the same but I think the ecosystem of interacting and debugging with data step by step is not as good as python. And it is something that we should have. Recently I was exploring snowpark and I felt it is a step in a right direction. It allows to me to write my query in multiple python statements and then it will transpile the whole thing into an SQL query.

Generally in my mind I assume the tables/dataframes as 2 dimensional structures and Pandas API give much more intuitive and wide variety of constructs to explore that structure rather than SQL. What do you guys think?

0 Upvotes

102 comments sorted by

68

u/theleveragedsellout Aug 27 '23

After spending half an hour trying to figure out which hyperparameter is needed to properly join/concatenate two DataFrames because all you're receiving is a cryptic error, you begin to appreciate SQL more.

I use them both on a daily basis and like a lot of things, it's a question of using the right tool for the right job. From my perspective, SQL is a lot better for manipulating data where a lot of joins are involved. Conversely, Python is far better for iteration.

-14

u/__albatross Aug 27 '23

I agree, my discussion is purely from API design standpoint. Consider a table a 2d array and then compare in how many ways you can interact with data in sql and then in pandas. Size of the data or other factors are not the point of discussion. My belief is simple that sql can provide more friendly ways to interact with that 2d structure which are given in pandas. How to handle big size of data and which is better in which I am not thinking about those variables of the equation

15

u/danlsn Data Engineer Aug 27 '23

Pandas could have an API so beautiful that it could be framed behind glass at the Louvre. But I've got a 98 million row table in Redshift I'm working on that would melt my pathetic little Dell work laptop if I even mentioned the word Python.

10

u/bubzyafk Aug 27 '23

Lol exactly. People whack on sql because they might only have 3 tables to join.

Wait until they need to build complex Fact table (transaction table) where comes from 7 tables join, plus 2 sub query inside sub query, and few others sub query to get windows agg func such as rownumber over partition by to get the latest date transaction from other particular table, and some columns need case-when or some other complicated business logic.

This most likely happen when building datamart gathering data from ERP in big companies where they use Oracle or SAP system. Which data is scatter in so many tables.

Use py when it’s needed and sql when it’s needed. Bashing one another is pointless.

2

u/danlsn Data Engineer Aug 29 '23

I'll take "out of memory error" for $100 please

2

u/bubzyafk Aug 29 '23

If you used spark,

Like a dumb people would say on memory issue is “please buy racks full of new servers and increase your memory allocation”

Or if you use cloud, it’s just 1 click away to increase the cluster resource .

Definitely $100 won’t be enough (: /s

2

u/BlackBird-28 Aug 27 '23

98M rows is a test table for me. But there is Spark ⚡️

3

u/Andrew_the_giant Aug 27 '23

I think you'd be interested in pyspark my friend.

1

u/kenfar Aug 28 '23

Hmm, I've had multiple production warehouses with 50+ billion row fact tables - built with python and with vastly lower cost, lower latency (ex: 65 seconds from when an event was created to when it was in the data lake, warehouse and data mart), and higher scalability than is achievable with SQL.

It all depends on your pipeline design and platform.

22

u/evening-emotion-1994 Aug 27 '23

You have never worked with huge volumes of data or required to build systems that needed Optimisation, have you ?

Pandas is okay for some small analysis stuff and lots of automations , but When you have huge chunks of data in DBMS or hive tables - SQL type query is goto method for calling in those data

-1

u/__albatross Aug 27 '23

I have worked with huge amounts of data. In my current project we have a huge pyspark cluster. I agree that there are advantages to both SQL and Pandas. I am talking about from a purely API design standpoint that what constructs and ways sql give you to interact with data and what ways pandas give you.

I am not saying to replace one with other. I am saying that sql can give more friendly ways to interact with data which are provided by pandas. Size of the data is not the point of discussion here

11

u/evening-emotion-1994 Aug 27 '23

The amount of transformations that you can do with python is obviously not available in SQL or it can be syntax heavy and less intuitive.

Python can make data sing in so many ways if volume of data is not a blocker.

Honestly I have worked with Pyspark cluster on Databricks and ,the amount of times toPandas() method breaks due to data being too large for driver node to handle has made me sore

1

u/__albatross Aug 27 '23

Exactly my point, we can have some more friendly ways in which sql can make the data sing and make our lives better

1

u/evening-emotion-1994 Aug 27 '23

That's a hard call , cause SQL is made around ACID and many things are limitations in its design . Just like python has some limitations too

1

u/__albatross Aug 27 '23

Well, After many decades when we redesigned programming languages we did a really good job with Go and Rust. The learnings from all those decades taught us a lot. I am sure if someone amazing like ken thompson or Rob Pike thinks about redesigning SQL API they can do a lot of improvements.

11

u/cutsandplayswithwood Aug 27 '23

The fact that you keep comparing sql to imperative programming languages, as if there’s any parallel, is the root of why this discussion doesn’t make much sense.

1

u/Cheating_Data_Monkey Aug 27 '23

The SQL language is constantly evolving. Each platform provider is dumping lists of new functionality every month. And when one provider does something really new and cool, others follow. Sometimes it's silly things like "group by all", other times, it's more useful things like advanced array functionality through lambda expressions.

But you're comparing GO and Rust to other imperative languages, declarative data don't work that way. The payload is bound to the code.

So when we're looking for efficiency, as we did with Go and Rust, that's not about the language but the compiler behind it. We're making HUGE advances here too.

2

u/d4njah Aug 27 '23

Wouldn’t the pandas api on spark help avoid needing toPandas?

1

u/evening-emotion-1994 Aug 27 '23

What do you mean by that ? Data on Pyspark cluster is of type Spark dataframe. If you need pure python functions to work on it , you need to convert the data to Pandas dataframe. Or else use alternative of Pyspark APIs which do have lots of these functionalities!!

2

u/d4njah Aug 27 '23

Can you give an example? Particular with needing to convert to pandas for pure functions?

1

u/evening-emotion-1994 Aug 27 '23

Udfs vs apply methods in pandas . Syntax is different functionality is similar

2

u/d4njah Aug 27 '23

You can use pandas udf decorator for better performance on udf, without needing toPandas. Try using df.pandas_api to extend for some pandas methods.

2

u/SentinelReborn Aug 27 '23

There is a pandas api on pyspark which results in equivalent spark code under the hood.

4

u/cryptoel Aug 27 '23

Pandas api on pyspark is quite bad. I've tried rebuilding a pandas data pipeline to spark using pandas api on spark.

Long story short, alot is not supported and once you do have it functional, it's unstable. Some tasks will fail randomly one time and other times succeed. Also the execution plan looked like a nightmare.

So I rewrote everything in Polars and then used mapInArrow.

3

u/SentinelReborn Aug 27 '23

Yeah im definitely not advocating the pandas on spark api and wouldn't use it myself. Spark is very easy to learn anyway as it is similar to SQL conceptually so I dont really see the point in avoiding it.

2

u/cryptoel Aug 27 '23

It's easy to learn but in many cases pure spark is slower than spark + arrow udf. My current pipeline runs faster with a Polars UDF on spark, then doing all transformations directly in Spark.

1

u/d4njah Aug 27 '23

Yeh agree with you, it’s annoying though a lot of profiling packages are only based on pandas

1

u/cryptoel Aug 27 '23

That's why UDFs, such as mapInArrow exist

1

u/Gators1992 Aug 29 '23

One thing people seem to be missing is that it's not either/or, it can be and. You can run SQL statements in pyspark for instance and have a dataframe implementation rather than huge CTEs or temp tables. I tend to default to SQL when I can because I have written a ton more of it in my career than python. I wrote a mart recently that was entirely SQL on Spark except for adding one column that required a python library. Most of my data warehouse could be written in SQL as well. Python is a lot more flexible and a DE should know it, but it's not like python is needed in the vast majority of typical transformation cases.

6

u/ZirePhiinix Aug 27 '23

I've broken down complex SQL by using views, temporary tables, and subqueries.

2

u/__albatross Aug 27 '23

And how your journey has been like when you need to debug data if some calculations aren’t correct or you need to create complex data pipelines around them and tie them to modern orchestrators

7

u/ZirePhiinix Aug 27 '23

You split up the SQL. This last pipeline I made had about a dozen intermittent views as the data goes through different transformations and picks up new data. It isn't very optimized, but I'll optimize it using query planner once I get the calculation correct.

3

u/CesiumSalami Aug 27 '23

Use CTE’s - just select from the chunk of logic associated with a CTE.

7

u/[deleted] Aug 27 '23

I've built some fairly complex data applications that are mostly SQL for spark, chained to together with snippets of python, and creating temporary views that downstream tables reference.

So much more maintainable than trying to understand someone's pandas code.

-9

u/__albatross Aug 27 '23

You’re saying that you’re using snippets of python but you can’t understand pandas code? I doubt you know python then

5

u/Inferno2602 Aug 27 '23

That's an unfair characterisation.

Python is a language and Pandas is a library. You can be proficient in one and not the other.

I've worked with some great analysts who've known a big chunk of the Pandas API inside and out, but couldn't tell you what a class was or how to use a decorator.

You can produce spaghetti in anything. As the features of a domain-specific language like Sql are fewer and more focused, it can be easier to untangle than a general purpose one. Especially when you are maintaining code that someone else wrote.

2

u/mikeupsidedown Aug 27 '23

Super weird take.

Can you know Python and not know Pydantic, SQL Alchemy or SciKitLearn. Libraries have their own learning curve beyond base python.

Pandas in particular has an API that takes time to get used to.

1

u/[deleted] Aug 27 '23

I've been writing python code for 20 years, have embedded python interpreters in C++ applications, and use to write optimised modules for Python in C/Rust/cython.

So while there's always more I could learn, I think I know it pretty well.

But regardless of how well I know Python, some libraries are still easier to write clean and easily understood code with than others. My job often involves deciphering crazy pandas code that some data scientist wrote and converting into something performant that can run in production. Usually the easiest way is using an SQL engine, and that often reduces the total amount of code by up to 50%.

32

u/-crucible- Aug 27 '23

I think sql is under appreciated. It’s a different approach, around batching things into fewer steps for performance, and it can take approaches like temp tables and understanding the engine to get good performance, but you can get much better performance in some cases than a more general programming approach.

That said, there are many ways it could be improved, for example, I’d love to see what would happen if more gpu acceleration was included in some of these engines.

It really depends what you’re trying to achieve, but as a micro dsl, I think the sql language is very powerful.

1

u/Awkward-Cupcake6219 Aug 27 '23

Definitely. I thought sql as a poor way of manipulating data (compared to others) until I really learned it.

-14

u/ProfessorPetulant Aug 27 '23

SQL was created for transactions. Its use for full table manipulations is stretching it, and anything but simple analysis quickly becomes overly complex.

6

u/cutsandplayswithwood Aug 27 '23

That’s… deeply incorrect.

The set-based nature of sql makes it ideal for expressing analytical workloads over tabular data.

The vast, overwhelming majority of analytical workload is performed with SQL.

1

u/__albatross Aug 27 '23

Are you sure? I see a huge trend in the industry as I have been working with a big asset Manager is US and most financial companies have started projects of moving thousands of stored procedures to python data pipelines and business processes around them.

We keep creating data manipulation libraries in general purpose programming languages like pandas polars and many more

If SQL is so perfect, why do we need them?

What is the debug time if you write complex business transformations in SQL compared to python

3

u/Ok_Raspberry5383 Aug 28 '23

I think you're focusing on the python element too much. It's not really python, it's pandas, Polaris, spark etc. Python is just a facade for easy access.

The real reason for this shift is the changing architectures that separate storage and compute. This means an org can manage a single repository and then different teams/business units can bring their own compute to the party to query the data. This alleviates bottlenecks and resource constraints that plague traditional data warehouses.

In this context python is a natural choice due to the wide array of libraries and frameworks supported that allow you to 'opt in' with SQL (good for semi technical business folk) but also supports more advanced use cases that are not covered with SQL, e.g. DS/ML use cases as well as software engineering use cases.

It's not really a contest between SQL and python IMO

EDIT: Polars* not Polaris

2

u/Andrew_the_giant Aug 27 '23

I work as a data engineer for a top 100 company. We use SQL DAILY for querying and data manipulation / transformation.

2

u/Ok_Raspberry5383 Aug 28 '23

What's a 'top 100' company?

1

u/cutsandplayswithwood Aug 28 '23

A huge trend is not the one company you’re working with 🤣

Do people like porting old stores procedures? Yes, often. But stored procedures aren’t sql, they’re sql with programming language additions. Porting them often makes sense.

But that’s not “all sql goes away”, by any stretch of the imagination.

0

u/ProfessorPetulant Aug 27 '23

Basic things like calculating the transaction amounts from timestamped balances, or sum the 3 largest values for each row in a list of columns. Simple simple stuff requires really convoluted queries. Now if you want statistics like principal components or anova, forget it.

I don't use python so wouldn't know.

25

u/[deleted] Aug 27 '23

Oh boy.

37

u/FunDirt541 Aug 27 '23 edited Aug 27 '23

I was the same but that's because I started learning python/pandas before SQL. And the writing paradigm is very different for the two or them. As you get better in SQL, you'll notice that these things wouldn't matter much. And data in SQL is persistent compared to data inside a csv or an excel file, also in SQL you write the query and the database engine decide how to run/optimize the query, that's totally different than python. So keep practicing, use CTE, if you have never heard of them google it up, it was a game changer for me, and made SQL much easier to write.

2

u/NickSinghTechCareers Aug 28 '23

Exactly right! Learn to use & love CTEs!

p.s. Wrote an interactive CTE tutorial if ya wanna check it out!

1

u/Ashamed_Wheel6930 Aug 29 '23

mostly agree here, but i loathe CTEs. I’ve had to decipher to many ridiculous CTEs and they’ve contributed to tech debt a ton in my org. i’ll always prefer pyspark if it’s getting overly complicated with CTEs

29

u/[deleted] Aug 27 '23

If you think one of the other is better then you don’t understand what they do. They are Different paradigms, you could write the exact same pandas code than what a sql query would do. But you wont because in your mind you are only thinking in the Python/Pandas paradigm.

-2

u/dukeofgonzo Data Engineer Aug 27 '23

Would you at least say SQL has clumsier language for breaking queries into smaller tables or arrays?

1

u/[deleted] Aug 27 '23

No, you can break it into smaller steps rather than nested queries. There’s certain functions SQL doesn’t do well, row by row processing, retention type stuff, not otherwise fairly comparable.

0

u/yo_sup_dude Aug 28 '23

not really, both are set based implementations

3

u/[deleted] Aug 27 '23

SQL is great at reading data from a database and performing simple joins and transformations. It's incredibly simple, so much that it has barely changed in the last 30 years. You can usually break down complex transformations into multiple simple queries.

But the more I deal with complex transformations, the more I appreciate working with Python and the PySpark API. I can just give transformations names that describe what they do and chain them together. Being able to use all the cool features of a mature programming language is amazing and can simplify many things that are tedious to do in SQL.

My only gripe is that the level of entry is orders of magnitude higher than SQL. You can teach someone SQL in like a week and with a few months of practice and good feedback processes, they can write good and readable SQL scripts.

4

u/valorallure01 Aug 27 '23

SQL is not incredibly simple in my opinion. Yes, some simple select statements are easy to grasp. However, things get complicated and complex when you start to really learn it. Dynamic SQL, Cursors, Temp Tables, Windowed Functions, CLR Assemblies etc.

2

u/[deleted] Aug 27 '23

This is probably an 80-20 thing. You can solve 80% of your problems without ever touching the more complex features of SQL.

And if you find yourself in a situation where you constantly have to use these complex features, there's a chance that SQL is simply not the best solution to your problem.

1

u/Gators1992 Aug 29 '23

I wouldn't say window functions or cursors are genius level. If anything cursors are an annoying implementation, but not hard to understand. Window functions are pretty straightforward and even data analysts can understand the concepts as they use the same functionality daily. There are some more complex aspects of SQL that are difficult, but for the majority of transforms you just use selects, CTEs, aggregations and window functions.

-3

u/__albatross Aug 27 '23

1000% agreed. I work for a big asset manager in US and we have thousands of stored procedures which we migrated to python and created data pipelines and processes around them and it was a huge project. All I imagined was that is it impossible to imagine a language which can have both : constructs for handling complex data transformations and at the same create scalable pipelines around them. Imagine if sql had all the bells and whistles of python and you can organize your code in modules and packages and can create processes around them. I am no expert in creating programming languages but I am just curious to think what that world would look like. We wouldn't need dataframe libraries at all.

We used to think that a general purpose programming language can't exist with a performance close to C with automatic garbage collection until Go came along.

I don't get it why people are getting sentimental about CTEs. I have been working with them for quite some time but still they can't replace the necessity of python to write complex data pipelines.

I just had a thought why can't the standard data processing language have all the bells and whistles of a general purpose programming language. Its an idea which I just want to explore, I dont know if its good or bad

5

u/Max_Americana Aug 27 '23

They had SQL in Star Trek… it’s here to stay.

5

u/MikeDoesEverything mod | Shitty Data Engineer Aug 27 '23

What do you guys think?

SQL has it's place. Python has it's place. Sticking to only one of these and refusing to learn both leads to pure frustration.

4

u/SwimmingHelicopter15 Aug 27 '23

Wait, but in SQL you can also write small statements in functions and store procedures. And you can call them inside a master sp. Is no different. Just because people prefered to wrote 1000 lines of one sp in the past does not mean this is your only option. The only thing different is that Panda is used in newer projects which are easier to handle than legacy programs, you have less code and more cleaner.

3

u/[deleted] Aug 27 '23

SQL is declarative/ you tell the engine what you need not how to do it. Honestly this is why so many people “know” SQL but never get good at it. That shift is a hard one to make.

1

u/Cheating_Data_Monkey Aug 27 '23

Exactly, making the leap from functional programming to declarative programming is hard. It's a paradigm shift for the brain. It's not that you have to code differently, you have to THINK differently.

Comparing SQL to any of these languages is silly. Now, if we want to compare it to REL, that might be an interesting discussion.

1

u/bingbong_sempai Aug 27 '23

SQL is great for filtering and extracting data.
But you're right about its limitations when used interactively.
Check out the ibis project, it lets you interact with SQL databases as if they're in memory dataframes.

5

u/__albatross Aug 27 '23

Damn ibis looks nice. Thanks for sharing that. I don't know why people are getting sentimental about CTEs and can't admit limitations of sql and there is a scope of improvement

2

u/[deleted] Aug 27 '23 edited Nov 02 '23

[removed] — view removed comment

-1

u/__albatross Aug 27 '23

I am acknowledging CTEs and I understand SQL provide ways but improvements done by ANSI SQL are not very prominent. The mere fact that if I write a simple SQL query than the sequence of transformations is not intuitive and I have to read a separate article on the order of execution of operations shows the fact there are things which are not intuitive. That’s why projects like ibis or EdgeQL exists.

Check out EdgeQL and you’ll know what I am talking about.

Yes SQL has served well for decades in the industry but people are so set in their ways that they can’t see that you can have easier ways to interact with database.

Yes SQL will be here for a long time like many other languages will be. But people still move to better things. From Java to GO, From C to Rust

2

u/ramenAtMidnight Aug 27 '23

Yeah this is a hot take. In my experience, analysts love SQL because it’s easy, intuitive, independent of whatever interface the DEs give them. Funnily enough engineers also love SQL because of very similar reasons, just that they work with various databases and provide interfaces for DAs. Only the academics really make use of pandas for whatever reason? Personally I also find pandas API to be strange. Even spark offers a SQL-like interface.

2

u/FloggingTheHorses Aug 27 '23

Generally speaking I like to use Python to clean/transform the raw data right after extraction for normalisation of tables. Then bring the data to staging tables and then SQL joins it all for the final tables/views.

This way the conversion from business logic to data output is very clear, and has always helped me with CI/CD where the rules change or we start adding datasets etc.

2

u/[deleted] Aug 27 '23

I agree with you. I hate having 600 lines of SQL. I much prefer to break up my code into different files. It’s much more manageable.

2

u/JPyoris Aug 27 '23 edited Aug 27 '23

One reason why I prefer SQL over Pandas for simple transformations is that it has much less weird behavior and footguns, which for me is important for use in production. For example, there's the horrible behavior to convert an int column silently to float when there is a None in in it (preferably happening the first time long after you wrote the code) and then to pretend that None and nan are the same (which they aren't). Nullable dtypes were introduced but came with new Bugs. Why does casting np.nan to a nullable type result in <NA> even though it can represent NaN? Why does Series.dt.date return a object dtype? Oh maybe because Pandas doesn't even have a native Date dtype. Also, Pandas thinks that a date and a datetime at midnight are the same thing.

The whole typing is messed up. SQL on the other hand is mature and makes sense. So does Polars btw.

2

u/CoolingCool56 Aug 27 '23

That is why I use temp tables and each temp table should do a specific thing and be testable.

1

u/wiki702 Aug 27 '23

This is the way, especially if your work place has poor code standards or lack of comments for existing scripts.

2

u/Eze-Wong Aug 27 '23

My biggest beef with SQL is that the order of operations/execution is structured DIFFERENTLy from the typed order which is confusing to beginners.

You can always figure out from pandas that the sequence based on the logical operators and parentheses.

But in SQL, "where" is before group by and having then select and then order by. This inability to rearrange this order makes it so you need nested subqueries to get what you want. Also it's just not easy for beginners to know what is happening.

5

u/[deleted] Aug 27 '23

Have you ever heard of common table expressions. Those allow for "breaking down the problem in subproblems". People who seriously think python is a better language for data transformation than SQL, are completely lost.

5

u/ntdoyfanboy Aug 27 '23

Do you know what a CTE is, bud?

2

u/kenfar Aug 28 '23

When you find that you need to make significant changes to a 600 line query with 20 CTE steps - and there's no documentation or unit tests - how do you procede?

Do you write unit tests for the intermediate CTEs? or just manually test them? And is that what everyone else in your shop will down over the next 10 years repeatedly when they encounter this query?

And does that sound like it may be a problem?

-1

u/FearMyBlades Aug 28 '23

So your problem is a lack of documentation and unit tests.... and your answer is python? Are you trolling?

1

u/kenfar Aug 28 '23

Are you unfamiliar with unit-testing frameworks like pytest?

Or how to bring up 600 lines of code into multiple functions - each independently documented, say with a docstring, and a unit test class?

I say this because I've done exactly this many times, and at no point have I ever contemplated rewriting it in SQL.

1

u/FearMyBlades Aug 29 '23 edited Aug 29 '23

I don't python

Edit: I should clarify I do a fair amount of C# development. My comment was kind of tongue in cheek but from my experience python developers tend to ignore unit tests. Im sure there are people who follow some standards but most people i know who use python are data scientists. Personally i lost interest in python once i learned about the GIL

1

u/kenfar Aug 29 '23

Yeah, I find that data scientists are hit or miss when it comes to unit testing. Some of that because of tooling they're using, some of that because of expectations, training, etc.

But when it comes to software developers, unit testing in python is extremely popular. As much as as with Ruby - another dynamically-typed language that's risky to productionalize without unit testing.

Regarding the GIL: it's definitely a limiter. There are some initiatives going on right now to remove the GIL over the next year. However, I do multi-threading & multi-processing all the time with the GIL and still get a ton of parallelism. I'll use threading when the bottleneck is IO, and multiprocessing when the bottleneck is CPU. And I've had excellent results with both.

Good luck!

1

u/FearMyBlades Aug 29 '23

You said the magic word, productionalize. There just isn't a reason to use Python, imo.I don't develop in Python, but i did spend a good amount of time troubleshooting it and trying to understand asynchronous and parallel processimg in python. I know it is better now, but it feels a bit like the unsafe keyword in c#. You can get access to some useful features, but you better know what you're doing.

I know it can be religious in nature, but there are just so many advantages to c#. The analyzers these days are just incredible. I saw an article about removing the GIL and i think it would be a mistake.I see concurrency causing problems for python developers.

1

u/kenfar Aug 29 '23

Yeah, I've seen a few folks have problems with threading on python - they were surprised that it was limited by the GIL and then gave up.

However, I've found it easy to use multiprocessing instead - which is unaffected by the GIL. And when using concurrent.futures and the map function it only takes about 2 lines of code, and you can switch between threading & multiprocessing by changing 1 word. I've used this to run 128 massive file transforms at a time in parallel when I had billions of records to transform.

1

u/FearMyBlades Aug 30 '23

Well the problem is that multi processing is serializing the instance and cloning it. On large data sets you're going to be spending a lot of resources to that effort. Threading should be more efficient but seems needlessly complex. Well it might be more accurate to say it seems inelegant.

1

u/kenfar Aug 30 '23

The way that I'll typically use multiprocessing is, for example, to transform 12+ large files - with each process reading a file, transforming it, and writing the results out. On occasion I've also processed a massive file this way - if I can seek to different parts of the file. That works really well.

And threading with python can also work really well. I just recently used threading to speed up the copying of thousands of files from one bucket to another. I probably got a 20x speed-up through 2-5 lines of code.

So, I probably use either threading or multiprocessing every 1-3 months. It's rare that a few lines of code won't make a massive difference.

→ More replies (0)

2

u/m1nkeh Data Engineer Aug 27 '23

ok, thanks I guess 🤷

You do you..

2

u/DesperateForAnalysex Aug 27 '23

DBT largely addresses all of these issues so I suggest checking that out if you need code modularity.

SQL is also incredibly readable even for less technical folks. I don’t want to dive into python code to understand a simple join

1

u/TheCamerlengo Aug 27 '23

If you are querying a relational database, hard to beat SQL. They are probably best used together, instead of either or. SQL is very good for exploratory data querying. Python with pandas is good for writing programs or pipelines.

Also don’t forget that SQL that goes thru a database gets a query optimization step. You can even look at the generated plan and improve the query. With Python and pandas and you are kind of on your own to tune your code.

1

u/billysacco Aug 28 '23

I started with SQL so I am very partial to it and can usually accomplish what I need to with it. However I don’t feel it’s better or worse then pandas. They are both tools and have their strengths and weaknesses. You can debug SQL too so I’m not sure what you mean on that. SQL if done correctly is very powerful to parse information but done correctly is the caveat.

1

u/CautiousSpell8165 Aug 28 '23

Nah, sql is fine. Try using CTE to break your processing logic into smaller steps. And don't you dare telling us you don't know what CTE are!!

1

u/Outrageous-Heat-6353 Aug 27 '23

SQL is universal more or less. Pandas, not everybody uses/understands it, and analysts won’t understand it for example.

0

u/valorallure01 Aug 27 '23

Your comment is stating you prefer Pandas over SQL for transforming data. I agree. Pandas does a great job of, for example, transforming a json file to a table. I wouldn't say I don't like SQL because of it though.

8

u/[deleted] Aug 27 '23 edited Nov 02 '23

[removed] — view removed comment

-3

u/__albatross Aug 27 '23

Sure C is a good language, but Rust is better. And now it will be integrated in Linux kernel. I am not saying SQL is bad, i am only saying it has a scope of improvements. It was introduced in 1974. In these 50 years are we sure that we haven’t encountered any limitations, that’s hard to believe. Even cobol code base might be there in next 50 years. Doesn’t mean its good

-8

u/__albatross Aug 27 '23

Every mainstream programming languages has versions in which they introduce new features. I don’t see that in SQL

2

u/Cheating_Data_Monkey Aug 27 '23

The following query would be impossible on any platform 5 years ago. It performed terribly 2 years ago.

Today it runs in .3s

Obviously some new features were developed :)

with arraycte as (

select partitionfield1, partitionfield2, nest(timefield) as timearray, nest(valuefield) as valuarray

from table

group by all) --I love "group by all", somehow it feels inclusive :)

, manipulatedcte as (

select *, array_concat(

slice(

array_reverse(

ARRAY_FILL(x -> x > 0, array_reverse(valuarray)))

), 2, length(cs_array)), [null]) AS nextvalues

from arraycte)

SELECT

ts,

partitionfield1,

partitionfield2,

val,

nextval

FROM

manipulatedcte

unnest(timearray AS ts, valuarray AS val, nextvalues AS nextval);