r/learnpython • u/BigBootyBear • 1d ago
How do you deal with garbage data when building an ETL pipeline?
We have secretaries creating reservations in a SAP form. Those reservations get exported as CSV files into some shared directory each VM has to mount. Then a cronjob fires an automated python script that drops all the tables in the application, and shoves the data from the CSV into those rows.
The form fields in the SAP forms don't match the CSV row fields. The CSV row fields don't match the applications MySQL schema in type or name. Properties such as datetime, address, client, guests, room_number etc are split across 8-12 CSV files so you have to load all of them into memory and perform black magic to construct a proper reservation object.
No unique IDs are given (so I have to fingerprint based on time, client, address etc), dates are in non-standard string format, 50% of the data is redundant and even the damn encoding is not utf8.
It's been a week or two that I have been working on this and Its driving me crazy. The schema is so complex that it takes a good hour just to load up this convoluted mess into my mental RAM so I can start working on it at the start of the day.
I assume if I were a full-time ETL/PowerBI guy I'd already finish this nightmare but I'm a devops/fullstack guy. I need some guidance on how to think about this problem in an abstract sense (i.e how to organize garbage data) so I can handle it effectively.
3
u/SpecialLengthiness29 1d ago
It maybe that Python isn't the best tool for the job (or at least the whole workflow). Sed and other Bash CLI utilities might have a useful role in cleaning up data.
1
u/BigBootyBear 12h ago
I don't think the language matters it's that the business domain is really complex.
2
u/Diapolo10 I write code for a living -- https://github.com/Diapolo10 1d ago
My condolences, that sounds like hell to deal with.
I don't know if this helps, but if the CSV and database structures are that different, maybe it would help to have an intermediary format you can more easily reason about. Like, you could try to first design some kind of an "adapter" format you can more easily work with that you then map the CSV data into, and then you just need to bridge the gap on the other side to the database format. Yes, it's an extra step, but if it ultimately reduces your mental load it should still help, if you don't need to worry about software performance loss at least.
2
u/Ni_Peng_NeeeWom 1d ago
exho that that sounds like an awful workflow from the very start. is it possible to do away with the csv export and imports? maybe pitch them on just writing a front end for the admin staff to input reservations directly into in a guided format so you get regular data from the start?
1
u/JoeB_Utah 1d ago
I agree. I spent a large part of my career basically creating ETL workflows that more often than not were based on taking $hit data and honing into something useable. It was challenging, frustrating, and even maddening at times but when it was all said and done it was rewarding too. That said, it’s 2026 and there is no rhyme or reason for data entry to be so error prone. A solid front end input form seems like an easy sell.
1
u/BigBootyBear 12h ago
The org is sold on the idea that everything has to inlcude SAP at some point. I could try to pitch this idea. But better solutions to simpler problems have been rejected by non-technical execs, who I can only assume have their vooddo dolls in a safe somewhere in Germany.
1
u/FatDog69 1d ago
Ideally you have 1 place in the pipeline where all your data hygiene lives.
I suspect the python script that shoves the data into the tables is where you should put error & sanity checking.
I think when it opens each file on the shared mount it should open a ".error" version of the file. As each row is read in - you sanity check the number of columns, sanity check the columns that should be dates or only numbers, etc.
Any row that does not satisfy the rules should be written to the .error file and a error_count bumpped.
At the end if there are 0 errors the .error file should be closed & deleted.
If there are errors - the .error file should be closed and an email or announcement sent to the secretaries so the person responsible can read the problem rows and re-submit for the next round.
BONUS
As the python script examines each row it should build up a list of errors, not just that the row is mysteriously bad or stop at the first error. These error messages should look like this:
data,data,data,data,data,....
# Row appears to have 18 columns but we counted 22
# Date string in column 2 does not appear in yyyy.mm.dd format: (print the funny data)
# Dollar string in column 12 contains non numeric characters: (print the funny data)
# etc
If you dont print a message for every error - you will be constantly bugged "Why did the system not accept this row?" all day long. It's not enough to flag a row as a problem - you need to say why. And you need to document all the errors each time so 'someone' does not have to edit the same row 5 times.
ERROR LIMIT
Lets say a file normally has about 50 rows. Change the script to accept a 'error_limit = 6' command line argument. If the file contains 6 or more rows with errors - you abort loading that entire file.
1
1
u/Capable_Fig 1d ago
> even the damn encoding is not utf8.
that sucks.
how much data is going through the pipe? If its under a few gigs a day, python will be a fine tool. Having dealt with similar issues (in healthcare) I eventually swapped to C# for the speed and memory cleanup.
Basic approach is build a class for each CSV, that handles them particularly, then combine. I'd definitely use polars over pandas for this. then you can use a hash for the UID/fingerprinting on shared columns.
I don't think python is the correct language beyond scoping for this though. You may want to look into lower level languages once you have the project pseudo-coded
1
u/BigBootyBear 12h ago
My shit code loaded all 150K rows into memory and it consumed 400MB. I can easily drop that to 40M with batching, and shave off a dozen more MBs by dropping Pydantic usage. Fortunately performance is not the bottleneck. It's my mental RAM thats capped lol.
1
1
u/OppositeFisherman506 13h ago
Dropping tables inside cron scripts is straight out of a developer horror film.
-1
u/jeffrey_f 1d ago
Extract - Bring the data rows into a program
Transform - Make the data match your destination
Load - Load the data into your application
You can have the vendor of the data match your data source, but ETL is really what this is all about.
6
u/NewbornMuse 1d ago
This is not a very python-centric answer and potentially an ignorant one, but is there any chance at all to influence the format of the generated CSVs? I know this kind of stuff often has a lot of organizational overhead and inertia behind it, but if the alternative is coding the ETL script from hell, suddenly I'd be much more motivated to go bother some people.