top of page

Welcome
to NumpyNinja Blogs

NumpyNinja: Blogs. Demystifying Tech,

One Blog at a Time.
Millions of views. 

Data Profiling Using SQL: Understanding the Data Before Cleaning

Oct 21
6 min read

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.

  1. Open pgAdmin and connect to the PostgreSQL server.

  2. Right-click Databases → Create → Database.

  3. Enter the database name as hr_analysis.

  4. 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.

  1. Right-click staging.hr_employees.

  2. Select Import/Export Data.

  3. Choose Import and select the HR CSV file.

  4. Set Format to CSV.

  5. Enable Header.

  6. 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...!!

 
 

+1 (302) 200-8320

NumPy_Ninja_Logo (1).png

Numpy Ninja Inc. 8 The Grn Ste A Dover, DE 19901

© Copyright 2025 by Numpy Ninja Inc.

  • Twitter
  • LinkedIn
bottom of page