Real Life Data Cleansing Challenges

The following questions were posed to me recently around data cleansing, data profiling and favourite toolsets.

• What was the most challenging data cleansing task? How did you handle it?

• Do you have a favourite tool for data cleansing?

• Where have you used data profiling tools and what was the biggest benefit to you?

Most challenging Data Cleansing Task

Performing Due Diligence on vast amounts of data for a Finance Company that I previously worked for. This was debt data that was put up for auction, and the quality of the data would be risk scored to determine the price paid for the data at auction and how serviceable the data was.

The tools used were SQL, MS Access (back in the day) and Excel.

Favourite tools

More recently, Data Quality and Data Cleansing was achieved using T-SQL and SSIS or ADF, with a bit of scripting to manipulate and validate the data.

Data Profiling

Again, using ETL tools such as SSIS and ADF and creating data flows to eventually expose erroneous data by way of end-user reports (exception reporting) and building in tolerance levels for the data quality. SSIS has a great Data Profiler task that can produce reports on data patterns. In the Azure, Fabric and Power BI world the data profiling tools provided by Power Query are excellent, along with the ability to implement Notebooks (Databricks) taking advantage of Python data profiling libraries and SQL coding and functions .

Biggest Benefit of Data Profiling

• Improved data input and data quality

• Improved training for system users

• Improved business performance

• Avoid GIGO - Garbage In, Garbage Out!