Data Profiling Using SQL: Understanding the Data Before Cleaning
Whenever we work with a new dataset, it's important to explore the data before making any changes. This helps us identify missing values, duplicates, incorrect data types, and other issues that might affect the analysis.
This process is called Data Profiling.
In simple words, data profiling helps us understand the quality of our data and identify issues that need to be handled before analysis.
In this blog, we will perform data profiling on a sample HR Employee dataset using PostgreSQL and pgAdmin.
Step 1: Creating the Database
Let's start by creating a database in PostgreSQL using pgAdmin.
Open pgAdmin and connect to the PostgreSQL server.
Right-click Databases → Create → Database.
Enter the database name as hr_analysis.
Click Save.
Our database is now ready.
📸Database creation window showing hr_analysis.

📸Newly created hr_analysis database in pgAdmin.

Step 2: Creating the Staging Schema
Next, let's create a staging schema to store the raw HR data.
A staging schema is a separate working area where we can load and examine the original data before cleaning it.
Open the Query Tool for the hr_analysis database and run:
CREATE SCHEMA IF NOT EXISTS staging;

📸staging schema under the database.

Creating the Staging Table
Now, let's create a table called hr_employees inside the staging schema.
All columns are initially stored as TEXT so that we can check the original values before converting them into the required data types.
CREATE TABLE staging.hr_employees (
employee_id TEXT,
employee_name TEXT,
age TEXT,
gender TEXT,
department TEXT,
job_role TEXT,
salary TEXT,
joining_date TEXT,
experience_years TEXT,
employment_status TEXT
);
📸 CREATE TABLE query and successful execution.

📸 hr_employees table under the staging schema.

Step 3: Importing the HR Dataset
Once the staging table is ready, the next step is to load the CSV file.
Right-click staging.hr_employees.
Select Import/Export Data.
Choose Import and select the HR CSV file.
Set Format to CSV.
Enable Header.
Check the column mapping and click OK.
📸 CSV import settings in pgAdmin.

📸 Successful import message.

Step 4: Checking Column Names
Before profiling the data, let's check whether the column names are clear and follow a consistent naming format.
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'staging' AND table_name = 'hr_employees' ORDER BY ordinal_position;
The column names in our HR dataset already follow lowercase naming with underscores, so no renaming is required.
📸 Column names displayed in the query result.

Step 5: Checking Data Types
Next, let's check the data types of all columns.
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'staging' AND table_name = 'hr_employees' ORDER BY ordinal_position;
📸Column Names with datatypes

Since all columns were initially created as TEXT, we need to identify which columns should be converted later.
For example, age should be INTEGER, salary should be NUMERIC, and joining date should be DATE.
Before conversion,
Checking Age (INTEGER)
SELECT employee_id, age FROM staging.hr_employees
WHERE NULLIF(TRIM(age), '') IS NOT NULL AND TRIM(age) !~ '^[0-9]+$';
📸 Invalid age values.

Checking Salary (NUMERIC)
SELECT employee_id, salary FROM staging.hr_employees
WHERE NULLIF(TRIM(salary), '') IS NOT NULL AND TRIM(salary) !~ '^-?[0-9]+(\.[0-9]+)?$';
📸 Invalid salary values.

Checking Joining Date (DATE)
SELECT employee_id, joining_date FROM staging.hr_employees
WHERE NULLIF(TRIM(joining_date), '') IS NOT NULL AND TRIM(joining_date) !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';
📸 Incorrect joining-date formats.

Step 6: Checking Standardization
Standardization means keeping values in a consistent format.
For example, department names may contain extra spaces or different capitalization.
Let's check the department values.
SELECT DISTINCT department FROM staging.hr_employees ORDER BY department;
📸 Different department values.

We can also compare the original values with a standardized format.
SELECT DISTINCT department AS original_value, UPPER(TRIM(department)) AS standardized_value
FROM staging.hr_employees;
Here, TRIM() removes extra spaces and UPPER() converts text to uppercase.
These queries only show how the values would look after standardization. They do not change the original data.
📸 Original and standardized department values.

Step 7: Checking Missing Values
Checking the number of Missing Values
Missing values are common in datasets. Some fields may be empty or contain NULL values.
Let's check missing values in important columns.
SELECT
COUNT(*) FILTER (WHERE NULLIF(TRIM(employee_name), '') IS NULL) AS missing_name,
COUNT(*) FILTER (WHERE NULLIF(TRIM(age), '') IS NULL) AS missing_age,
COUNT(*) FILTER (WHERE NULLIF(TRIM(department), '') IS NULL) AS missing_department,
COUNT(*) FILTER (WHERE NULLIF(TRIM(salary), '') IS NULL) AS missing_salary,
COUNT(*) FILTER (WHERE NULLIF(TRIM(joining_date), '') IS NULL) AS missing_joining_date
FROM staging.hr_employees;
📸 Missing-value counts.

Identifying Employee Records with Missing Values
SELECT employee_id, employee_name, age, department, salary, joining_date
FROM staging.hr_employees
WHERE NULLIF(TRIM(employee_name), '') IS NULL OR NULLIF(TRIM(age), '') IS NULL
OR NULLIF(TRIM(department), '') IS NULL OR NULLIF(TRIM(salary), '') IS NULL
OR NULLIF(TRIM(joining_date), '') IS NULL;
📸 Employee records containing missing values.

Step 8: Checking Duplicate Records
Duplicate records can affect the accuracy of our analysis.
Let's check whether any employee IDs are repeated.
SELECT employee_id, COUNT(*) AS duplicate_count FROM staging.hr_employees
WHERE NULLIF(TRIM(employee_id), '') IS NOT NULL GROUP BY employee_id HAVING COUNT(*) > 1;
This query displays employee IDs that appear more than once.
📸 Duplicate employee IDs.

Next, let's check complete duplicate records.
SELECT * FROM staging.hr_employees
WHERE employee_id IN (SELECT employee_id FROM staging.hr_employees GROUP BY employee_id HAVING COUNT(*) > 1 ) ORDER BY employee_id;
This queries help us identify duplicates for further review.
📸 Complete duplicate records.

Step 9: Checking Outliers
Outliers are values that are unusually high or low compared to other values.
Let's check salary values using the IQR method.
IQR stands for Interquartile Range.
IQR = Q3 − Q1
Values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR are considered potential outliers.
WITH valid_salaries AS (
SELECT employee_id, employee_name, TRIM(salary)::NUMERIC AS salary
FROM staging.hr_employees
WHERE TRIM(salary) ~ '^[0-9]+(\.[0-9]+)?$'
),
quartiles AS (
SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3
FROM valid_salaries
)
SELECT v.employee_id, v.employee_name, v.salary
FROM valid_salaries v CROSS JOIN quartiles q
WHERE v.salary < q.q1 - 1.5 * (q.q3 - q.q1)
OR v.salary > q.q3 + 1.5 * (q.q3 - q.q1);
This query identifies salary values outside the IQR boundaries.
Outliers are not always incorrect, so they should be reviewed before making changes.
📸 Salary outliers identified using IQR.

Step 10: Range Validation
Range validation helps us identify values outside an expected range.
Checking Age
For this example, let's review ages below 18 or above 75.
SELECT employee_id, employee_name, age FROM staging.hr_employees
WHERE TRIM(age) ~ '^[0-9]+$' AND (TRIM(age)::INTEGER < 18 OR TRIM(age)::INTEGER > 75);
📸 Employee ages outside the review range.

Checking Negative Salary
SELECT employee_id, employee_name, salary FROM staging.hr_employees
WHERE TRIM(salary) ~ '^-?[0-9]+(\.[0-9]+)?$' AND TRIM(salary)::NUMERIC < 0;
📸 Negative salary values.

Checking Negative Experience
SELECT employee_id, experience_years FROM staging.hr_employees
WHERE TRIM(experience_years) ~ '^-?[0-9]+$' AND TRIM(experience_years)::INTEGER < 0;
📸Negative experience values.

These are review checks. The actual acceptable ranges depend on business requirements.
Step 11: Checking Inconsistent Values
Inconsistent values occur when the same information is entered in different ways.
For example, a department may be written as HR, hr, or Human Resources.
Let's check the different values and their counts.
SELECT department, COUNT(*) AS employee_count FROM staging.hr_employees
GROUP BY department ORDER BY department;
📸Department values and counts.

We can perform the same check for employment status.
SELECT employment_status, COUNT(*) AS total FROM staging.hr_employees
GROUP BY employment_status ORDER BY employment_status;
These checks help identify values that may need to be standardized during cleaning.
📸 Employment status values and counts.

Step 12: Checking Data Consistency
Consistency checks help us identify values that may not make sense when compared with other columns.
Checking Age and Experience
For example, if an employee is 22 years old but has 20 years of experience, the record may need verification.
SELECT employee_id, age, experience_years FROM staging.hr_employees
WHERE TRIM(age) ~ '^[0-9]+$' AND TRIM(experience_years) ~ '^[0-9]+$' AND
TRIM(experience_years)::INTEGER > TRIM(age)::INTEGER - 16;
This uses age 16 as an example starting point for work experience.
📸 Age and experience consistency results.

Checking Joining-Date Formats
Let's check whether joining dates follow the expected YYYY-MM-DD format.
SELECT employee_id, joining_date FROM staging.hr_employees
WHERE NULLIF(TRIM(joining_date), '') IS NOT NULL AND TRIM(joining_date) !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';
This identifies dates with different formats. It does not verify whether a date is valid on the calendar.
📸 Joining-date format issues.

Step 13: Data Profiling Summary
After completing the checks, we can prepare a summary of the issues identified in the dataset.

Conclusion
Data profiling is an important step before data cleaning.
By checking missing values, duplicates, outliers, incorrect formats, and inconsistencies, we can understand the quality of our dataset.
One important thing to remember is that unusual values are not always incorrect. They should be reviewed before making changes.
Once the profiling is complete, we can move on to cleaning and transforming the data.
Happy Reading...!!


