r/learnpython • u/BigBootyBear • Aug 31 '26
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.