This is infuriating at times. We ingest a lot of data from 3rd party systems of our clients for things like KPI analysis, the devs¹ provided various APIs for submitting data and we can read from various others, but good old reliable dumping-CSV-files-to-us-via-SFTP is still what most clients want to do.
I have no problem with this², BUT the amount of times I end up talking to clients (sometimes large banking organisations) that don't have anyone available who actually understands SFTP/SSH amazes me. We often resort to password based auth because they simply don't get private/public keys (or claim their other infrastructure just doesn't work with them).
----
[1] used to be a full-stacker myself, but I've let large parts of my skillset rot so except for when working on legacy issues I'm a more specialised database/data-processing/infrastructure fellow these days
[2] other than, of course, dealing with bad CSV output implementations⁴ or just all the combinations of how to encode problems like quotes, eols in long stings, and so forth. JSON³ is starting to get some traction as an alternative which is handy because being more modern it is more rigidly designed compared to the mess that CSV is due to decades of implementations being slapped together in an it'll-do-until-it-doesn't manner.
[3] Don't expect structured data though: the clients & 3rd parties I deal with are sending the same simple table format data, with multiple files for us to join together later when there is more complex structure, that they would send as CSV, just using the more bloated format⁵ for the gain in reduced encoding confusions.
[4] not quoting values containing the separator character or otherwise escaping it, not dealing with existing quotes when values do get quoted, etc. - if you'd be surprised to find CSV is far from a solved problem today, then prepare to be very surprised!
[5] Don't get me wrong when I mention the format bloat: this _is_ a good trade for easing the other issues. Bandwidth & storage (or compute resource for compression) are more than cheap & fast enough to make it so.
There seems to be a dearth of people for the ops jobs that need doing, hence OP's “ops are an almost extinct species”, so the tasks are thrown around and have to be done by the first person who doesn't duck fast enough. Those of us on the other side of the company interaction boundary end up having to help them with what should be the basics. I'm not their infrastructure team, I don't have access to be able to answer some of the questions they ask about their own infrastructure. I shouldn't even be client facing never mind teaching them how to do their jobs.
The infuriating part (perhaps “frightening” is a better word here) is that there are people managing the secure transmission of data (sometimes sensitive personal data) who do not understand what is going on.
Honestly, I kinda feel sorry for the poor DevOps people in hospitals. It's got to be torture.
I guess I was missing the "why" where your post focused on the "what".
Of course when you are working with sensitive data like that it makes a lot of sense to be really careful about security.
I am well aware about the problems with the csv format. But a lot of times you can not really expect clients to be able to download and decode a parquet file, when all they want is to submit a password, download the data and look at it in excel.
Oh, that is a whole extra rant! As much as I actually like Excel as a tool for numerous tasks, it gets things annoyingly wrong too.
We've had clients who manually merge or otherwise tinker with data in Excel to feed to us¹ which can cause numerous problems if leading zeros get cut off identifiers that aren't actually numbers but get interpreted as such, and the age-old joke about Excel being a bit of an inv-cel that mistakes things for dates is still very relevant.
Also it requires (as do a number of MS tools) UTF8 files to have the initial BOM, which is actually not recommended as per the relevant standards², or it will assume Win1252 with all the text corruption that implies if your data contains accented characters, currency symbols, or anything else not in the 7-bit ASCII character set. Sometimes we see data where this has happened at some point to some of it, but not all, so the file has a mix of current UTF8 and corrupted UTF8 then encoded as UTF8. Funfunfun.
----
[1] in one case because their requirements changed, and they didn't want to pay anyone (us, the company responsible for the system the data was output from, or their own internal IT/processing/other teams) to automate the new data manipulation…
[2] https://en.wikipedia.org/wiki/Byte_order_mark#cite_note-5:~:...
> [1] am currently heading to a similar path
Oh man, I've lost track of the number of times I was infuriated by this. I especially love the guys pretending to take security seriously only to show up with some obsolete ssh config that doesn't work out of the box on modern systems.
Just have them upload blobs to S3 or Azure Storage.
The security is better and both are serverless solutions that cost basically nothing at normal scale.
I seriously don’t understand why people in 2024 insist in reproducing these core services but badly and at great expense.
PS: Azure Storage is multi-protocol and supports SFTP! https://learn.microsoft.com/en-us/azure/storage/blobs/secure...
Not everyone can store their data in cloud services, most likely.
That said, self-hosted S3 compatible solutions like MinIO might be a good choice: https://github.com/minio/minio or maybe SeaweedFS for something with a more permissive license: https://github.com/seaweedfs/seaweedfs
But then you would have people who can't figure out SSH trying to figure out private S3-compatible infrastructure.
This is a nearly verbatim conversation I’ve had three or four times in the last twelve months:
“Why not just use blob storage for the data ingest?”
“We have security standards for sensitive data; we have to use private links and validated protocols and systems.”
“Okay, what’s step two? Where does the data end up? Where do end users consume it from?”
“Power BI!”
“The local app?”
“No, the online version.”
“You just said you can’t use cloud services like blob storage!”
“We can’t!”
“You’re using Power BI!”
“That’s different!”
“How?”
“Err…”
At least the data doesn't rest (as far as we know…) in PowerBI.
> I seriously don’t understand why people in 2024 insist in reproducing these core services but badly and at great expense.
Because those users have a flow that they like and don't want to change it regardless of all the pitfalls in that flow. "It works, there is just this one problem. Why don't you invent a solution for this one problem for us. I don't want to use S3, or Azure or whatever. Just give me an sftp endpoint I can upload files to". Oh CSV doesn't have a standardized way of parsing escapes? why don't you handle a `# escape=\` or `# doublequote` or `# singlequote` or `# ignore-new-lines-in-the-middle-of-a-string` and we will use that.
Heck, it's 2024, and we have a fully automated, source control (github, gitlab, bitbucket, etc) integrated, S3/Azure Storage/GCP Buckets integrated, Dockerfile and Makefile aware, application deployment service and we still have a non-trivial number of users asking "Why can't I just drag and drop my .php or .py files over fileZilla to deploy my code? I don't want to use git or docker or S3". Oh, you can't version our code updates when we just drag 100 files over sftp? why don't we update a `version.txt` file when we're done updating then you count that as a version?
https://www.bbc.com/news/articles/cx82407j1v3o
Some of these solutions were never good, but build a momentum of “we’ve always done it this way”.
Because that is the infrastructure they have, what their current arrangements support. We can offer a number of other methods, but if their other systems don't support than and they don't want to pay for upgrades, SFTP it is. Not that I have anything against gobs of data arriving via SFTP for cleaning and importing¹ – it is tried & tested, reliable, and if both sides have a clue² it is easy & pain free. Much the same set of reasons they still want to send us CSV formatted data, often Win1252 encoded rather than UTF8, not JSON or something else better defined.
> Just have them upload blobs to S3 or Azure Storage.
The data is ending up in Azure storage in our case. But any transfer method still has identification and authentication matters to deal with, and they need to understand and their other software needs to support, the method chosen. This is why SFTP often wins by default: it is a relatively proven technique that people see as secure, and it is commonly supported.
> The security is better and both are serverless solutions that cost basically nothing at normal scale.
The cost to the client is only zero if their other systems already support it. They are very much against anything that implies extra development costs or the cost & effort of assessing and switching to newer systems.
> PS: Azure Storage is multi-protocol and supports SFTP!
SFTP support in Azure is a pretty recent addition, some time in this last year IIRC. We (well, I) had to put together our own relay arrangement some years ago: OpenSSH, appropriately locked down, with blob containers in storage accounts mounted via blobfuse). It works well, in nice cheap VMs/containers.
SFTP support in Azure works out rather expensive for our use case, or any reasonable use case IMO. It needs the hierarchical namespace enabled though that is “only” an additional $27/month per storage account, but then you need to pay for SFTP support by the hour!³⁴ Unless we constrain the clients to sending data at specific times that comes to >$200 per month, per account. We can't get away with charging our smaller clients, or the bigger ones for that matter, an extra $2,400/yr nor can we afford to eat that ourselves. See https://azure.microsoft.com/en-gb/pricing/calculator/ for reference.
We could work around the per-account part by stuffing all the data into multiple containers in one account – but that would not fly with some of the requirements financial sector companies understandably have for data at rest, so we would never pass an audit if we did that. Many even want their live and UAT/training/other environments to have completely separate storage accounts so that is potentially more than one lot of up to $2,400 per year per client needing SFTP. And even on one account, $2,400/year to enable 24/7 availability of SFTP transfers is absolutely ridiculous.
----
[1] I'd rather it be cleaned first, but that is a separate issue
[2] This part is, of course, the problem…
[3] $0.30/hour: https://azure.microsoft.com/en-gb/pricing/details/storage/bl...
[4] No shit… https://learn.microsoft.com/en-gb/azure/storage/blobs/secure....
It is a primarily knowledge-intense profession where you can't just hire some untrained professionals and let them hack together an app. Or cannibaliize data-analystis and make them build some backend.
In all seriousness, this is what I am seeing already. Often I see a platform team providing an Openshift for developers to rule as they want. There is cooperation on some levels and a technical architect who is setting standards.
And most companies don't want to pay the price tag that comes with an actually good DevOps guy anyway.