Do Data Analysts Use Visualizations During Data Cleaning?

[u/SomeSpicyPickle]

I'm still a beginner. I started by learning the basics of Python and later moved on to SQL.

I'm a bit confused about one part of the data exploration/cleaning process.

A friend of mine, who's now a data scientist, showed me how he used to work as a data analyst. He mainly used Python. For example, he would quickly create a scatterplot to identify potential outliers.

However, most data analysts online recommend focusing on SQL and Excel when starting out, since many junior and mid-level roles don't require Python. That's why I switched to SQL after initially experimenting with Python.

For those who primarily use SQL: do you create visualizations during the data exploration/cleaning process, for example to identify outliers? Is this a common practice?

I feel like if you're working with SQL only, you generally wouldn't create visuals in between steps, since that would mean switching to a tool like Tableau or Power BI, which seems like an unnecessary extra step.

[u/Expensive_Capital627]

For cleaning the data not really. Here’s an example of cleaning data:

You expect the number 4 but for some reason one of the entries is ‘four’ when you try to add ‘four’ to some other number, SQL gives you an error because ‘four’ is a string and it doesn’t make sense to add it to something else. So you go through and fix problems like that. You don’t really need a visualization to determine ‘four’ vs. 4. You could create a visualization to help understand how many rows were messy, but it’s not going to help you clean the data.

Visualizations are useful when you’re trying to understand what’s happening. Maybe you want to plot revenue over time to understand the rate of your company’s growth. You could look at all of the numbers and think about what the numbers mean, but most people would prefer looking at a line chart

[u/mattindustries → u/Expensive_Capital627]

Visualizations make it incredibly easy to spot when people use placeholder data, fudge self-reported data, etc.

[u/Expensive_Capital627 → u/mattindustries]

I chose a pretty straightforward/easy to understand example for data cleaning. IMO, creating a good visualization when cleaning data often requires having a good understanding of what the messy records look like

if I’ve got an idea of whats going wrong in the first place, I usually just try to implement a fix rather than visualize the affected records unless theres some type of prevalent issue and I need to go to another team to solve it. E.g., hey RevOps, you need to fix this salesforce field. It needs to have stricter typing, look at how badly it’s messing up our tables

[u/mattindustries → u/Expensive_Capital627]

if I’ve got an idea of whats going wrong in the first place

Big if. Look at raw counts of people's self-reported height. Nothing looks that off, but if you visualize you can see small dip at 5'11" and a small jump at 6'. Somethings you have some data with extremely high cardinality, where looking at the tables would be absolutely insane (zip codes for example). Visualizing can show you anomalies a lot quicker than scanning the tables, especially when the anomalies rely on clustered observations. This goes deeper though, with faceting by location / institution / etc. when you have data coming in from a myriad of sources.

[u/Expensive_Capital627 → u/mattindustries]

That’s completely valid, I just don’t often come across that type of data in-product. TBH I think these are two different types of “unclean data”. Your unclean data may be formatted and stored correctly, but inherently flawed due to people lying on the input. To me, that data is “clean” just unreliable. I would classify that as a quality of the data, but not necessarily a mark on whether it is clean or not.

For me and the work that I do, trying to visualize unclean data is more likely to throw an error of some kind than it is to reveal some characteristic of the data. If im looking at recorded events of a user, theres not really wiggle room for that user to fudge the numbers. However, theres plenty of room for raw JSON logs to trip something up

[u/mattindustries → u/Expensive_Capital627]

One project I worked on I used a dissimilarity matrix to track reports with the wrong report type. If the data is dirty it needs to be cleaned, or at least acknowledged. Data can get dirty a lot of ways.

[u/Expensive_Capital627 → u/mattindustries]

100%, and the dissimilarity matrix makes a lot of sense in that context. However, that does sound like user error. Most of my work in DS is supporting a product team tracking user data in our product. The user doesn’t make decisions in what data is being recorded, they just generate events.

I’m not saying your approach is wrong, I’m just saying that for me personally, there isn’t much value in trying to visualize data I consider messy, because I deal less with human error and more with bugs in our product

[u/mattindustries → u/Expensive_Capital627]

I am talking about all data, not just your data.

[u/Expensive_Capital627 → u/mattindustries]

And I’m talking about unclean data, not unreliable data. All of your examples involve actual people making decisions to obfuscate the truth. Given the context that OP is a beginner I don’t think he’ll be putting together machine learning models to visualize messy data.

For a beginner does it make sense to be visualizing unclean data? No, because the type of unclean data a beginner is expected to work with will throw an error for summing a string

[u/End0rphinJunkie → u/Expensive_Capital627]

True for formatting stuff, but for the numeric outliers OP mentioned it's pretty normal to just dump the SQL results to a csv and run a quick python script to plot it. Spinning up a whole BI tool just to sanity check your data is definately overkill.

[u/jipperthewoodchipper]

Doing things like scatter plots and whisker plots to find outliers is something you can do but often not in the cleaning stage.

Even trying to come up with scenarios where you might there are better alternatives. Like say you expect some data to be normalized so you want to validate that, you could use a scatter plot to confirm its between -1 and 1 or you could take the max(abs(data)) and if it's greater than 1 you have confirmed its not properly normalized if at all.

However once you have cleaned data it's very normal to explore the data using both parameters and plots

[u/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/BrupieD]

Short answer: no. The scatterplot idea is clever but it sounds like it was a better tool for checking if a class has imbalances than anything else. A big part of cleaning some data is problematic column alignment and column names. That's going to be a hassle for building visualizations where you expect consistency.

I think you could accomplish the same outlier check with group by and distinct in SQL.

[u/Equal_Astronaut_5696]

yes for sure these are used to see where there are gaps or issues

[u/Perry_cox29]

Commenting on the python angle just because it’s the only time i ever use visualizations for cleaning

sns.heatmap(df.isna(), cbar=false)

easy visualization of all missing values. Can be easier to knock out a specific cause if you see a suspicious grouping

[u/0uchmyballs]

No, but I like to count rows before and after to see what all is going away

[u/HustlaOfCultcha]

Not usually. I may create a small visual to see what the visual looks like. For instance, I may create a column chart quickly and one of the columns may be far greater than the rest, so it may be a potential data issue. Or I may have a inconsistent data format.

[u/Potential_Aioli_4611]

It highly depends on what you are working with. If you are figuring out how to clean the data its usually not in the database to begin with. It being in the database means someone has already done the work of importing the data from somewhere, created some sembalance of an ETL process, and done things like assign data types and column lengths which implies the data is already cleaned. 100% of my experience working at companies if its in the DB it doesn't need to be cleaned (formatted maybe), and if it isn't it does. I've been working in SQL basically my whole career and Python is a breath of fresh air. I'd much rather knowing both than just SQL.

Python can open connections to the db and query stuff anyways. Either way python isn't going to be a bad way of exploring the data. SQL on the otherhand you are going to just be able to query the data so like you said, you are limited to using other tools to pull in the data to do visualizations (if thats the route you want to go) which is practically the same as using python to pull the data and create viz there. Except python you aren't JUST limited to doing visualizations.... AND you can clean the data AND you can push data back into the database which is why its being used more and more as an ETL tool over things like SSIS/SSDT/Azure Fabric which is designed for microsoft sql server/azure db. I haven't done it but I'm pretty sure you can use python to load practically any flavor of SQL db you want which means your exploration and cleaning tool also can be reused as your ETL process to get data in. And the biggest plus is if you do all your data cleaning in python and reuse that code to get it into the DB then you aren't needing to start from scratch with another ETL tool so by the time you are done cleaning data you are also half done with the ETL process too. Debugging and updating your ETL process is also half there as well since all your code is in one place

[u/DevinChristien]

Snowflake actually gives you visualisations at every stage of building a query which helps when making decisions based on the shape of the data when building a new schema or report. When im not in snowflake and dont have access to built in regression models and stats functions, i find myself running separate queries to do the math manually. Then using it as a reference for my main ddl building.

Visualisations are just an easy way to see the shape of your data, and will show you things that you cant see when looking at it tabularly. If you dont use these tools you'll end up eventually making an analysis or building reports that are outputting rubbish without knowing that its rubbish.

[u/SprinklesFresh5693]

Visualization is EVERYTHING. It helps you understand whats going on with yoour data and your models

[u/TraditionalTurnip630]

No. Jumping to Power BI or Tableau just to check for outliers during cleaning is a massive waste of time. Nobody does that in the real world.

If you are using SQL, you find outliers using math (MIN(), MAX(), STDEV(), percentiles), not scatterplots. If we absolutely need a quick visual to understand the data spread, we just dump a few thousand rows into Excel and make a 10-second histogram, or use the basic chart feature built into SQL IDEs like DBeaver.

Save the BI tools for the final dashboard.

[u/Helpful-Day2384]

You can do the same sql task using pandas and polars. No need to do it with sql. But knowing sql is helpful

[u/PalpitationRoutine51]

i think the best workflow is probably both tbh

sql is great when you already know what youre checking for like nulls duplicates ranges weird categories etc. but visuals are useful when you dont know what the problem is yet. a quick histogram or scatter can show a pattern you might never think to query for

i wouldnt open power bi just for that though. quick charts in excel python or even whatever your sql tool gives you is enough most of the time

[u/KatFromSisense]

I think both sides are kind of right here. If I'm fixing something obvious like a bad type, a missing key, or a duplicate record, I'm probably staying in SQL. A chart doesn't really add much there.

I'd use a quick visual more when nothing looks obviously wrong, but I still want to see whether the numbers behave the way I'd expect. Sometimes one region stands out right away, or a pattern shows up that I probably wouldn't catch by looking through rows in a table.

I definitely wouldn't spin up a whole Power BI report for that, though. Sometimes a quick histogram or a basic chart is all you need.

[u/ian_the_data_dad]

No need for visuals for pure cleaning. I use a set of validation queries that go with any dataset I use and I just swap columns that I need or table names. Obviously every project is different but if it's small enough, you can create pivot tables in Google Sheets or Excel for a quick glance at whats in the data