How do you clean data with multiple languages, formats, and standards?

I think this article needs to distinguish between data cleaning for an ad-hoc workflow and a production workflow. For an ad-hoc workflow, the suggested practices of dealing with missingness, inconsistencies, and duplicates will suffice. You should be wary of any removal of outliers, imputations, or transformations as it can impact how results are interpreted. Production workflows is a different beast, as you need to move away from resolving data issues to consistently detecting data issues and having the appropriate alerts and processes to resolve them. Why are values all of a sudden becoming missing? Why are they shifting over time? The paper "Learning under Concept Drift: A Review" by Dr. Jie Lu et al. is a great source to learn more!

For ad-hoc cleaning these tools might be ok, but for production workflows you really want to look in to specialised tools like Informatica.com, SAS, or Boomi and Dataiku. Not only will it help you automate these flows, but also detects errors, take care of signals to data owners and monitor lineage and data quality throughout the workflow.

We are now at the stage where we are cleaning data more and more in SQL. We used to clean data using R or Python immediately prior to analysis, which for our stage of development made more sense, but now as our infrastructure has matured a bit it makes more sense to clean and provision it using SQL into a form ready for analysis.

Most users provide raw data in Excel Format to us. I use Python to clean the data like correcting the data type (numbers to string, date format, etc), analyzing the need to remove duplicate entries, and deciding how to treat null values. Then I convert it to CSV format and load it to the SQL database for further analysis.

It really depends on the size of the data, the tools and infrastructure being used, as well as what needs to be done with the data. In many advanced distributed data systems, such as Palantir's Foundry, or on a platform like DataBricks, a user can choose to clean the data with whatever language the user feels most comfortable, and in some environments it's even possible to do the cleaning in a low code/no code environment (though this is not yet optimal). However, in my experience, if the data is large enough and housed in a distributed platform, the most optimal approach is to use pySpark, as it provides certain data processing advantages in a distributed environment over and above what one gets with SQL.

Tools mentioned in the previous step Informatica.com, SAS, or Boomi and Dataiku) will automate this for you. Help with analysing the data quality, have listening agents to detect and alert when necessary.

I kept my daily plan and to-do list for all my ongoing projects in the Markdown file. It is like a work diary to me. And for the complex steps, I write comments in the code for both Python and SQL scripts. I also kept the old script files by their version in the project archive folders which sync with my company Google Drive account.

With automated workflows use things like GitHub to keep track of all automation. It also tracks who has made the change, you can have different branches to look for solutions and keep track of your production lines.