I'm much less worried about that happening to SQL scripts.
So merely writing in a language that we believe will be around forever does not prevent your code from being rewritten.
Also unfamiliarity will make SQL feel inefficient for non-SQL programmers. The experience of rewriting SQL teaches some this lesson. But others find that the familiarity of the language of the rewrite makes it seem better to them, even though it is objectively worse. An objectively worse that only becomes visible when someone needs to optimize it for performance reasons. And it is seldom the programmer who did the inefficient write who has the skills to optimize it back to what SQL did in the first place!
(Yeah, I've had to do those optimization fixes.)
So on seeing one they can sometimes think it's quicker to redo this in a language I know rather than upskill enough to understand this.
If your db is scalable enough (like Snowflake) and you have the money, you can use SQL directly in the same way. But, I've seen data analysts writing bad SQL against Snowflake costing thousands of dollars per query.
It’s really tough to future proof anything. At least with pyspark you will have the code. And, there will always be experts out there. Worse case, if it’s important enough, someone will wrap it in an API and use it until something better comes along (see: 50 year old mainframes still in use).
But, tell me more. I always like a better way.
Is it more efficient to read/write from SSD? Right now, I need everything in memory.
You can choose when to cache the data in memory.
And it's the fact that you can write the code in Python, Scala etc instead of SQL.
Is this hosted databricks ?