Profiling helps analysts understand a dataset before analysis, identify quality issues, and determine what cleansing or transformation is needed. Start by understanding the source, volume, structure, keys, relationships, counts, and expected totals.
RAW DATASET
NameAgeStatus
Alya21Active
HafizNULLActive
Siti22Active
Siti22Active
Kumar 999Active
PROFILE → INVESTIGATE → CLEAN
CheckFindingAction
MissingAgeReview
DuplicateSitiVerify
Range999Validate
SpacesKumarTrim
Understand the data → find quality issues → cleanse responsibly → verify the result
02
Steps of Data Profiling
Document the source and its integrity; identify field names and data types; inspect important report columns; confirm expected primary, natural, and foreign keys; establish expected totals; and count observations. These baselines help verify data after transfers and transformations.
1
SOURCEConfirm origin and integrity
2
FIELDSInspect names and data types
3
COLUMNSIdentify important report fields
4
KEYSCheck primary, natural, and foreign keys
5
TOTALSEstablish expected aggregates
6
COUNTSCount observations and records
PROFILE BASELINE: source + structure + keys + totals + counts → reference point for later verification
03
Profiling Tools and Column Insights
Profiling may be manual or automated. Numeric columns can be explored with counts, minimums, maximums, averages, and distributions. Text columns can be examined for distinct or unique values. Profiling tools can also surface valid, error, empty, and null-related measures. Be aware when a preview profiles only a sample of rows.
DATA PREVIEW
StudentScoreProgram
Alya72CS
Hafiz85DA
Siti91Business
Kumar68Engineering
Nur88DA
COLUMN PROFILE · SCORE
5COUNT
68MINIMUM
91MAXIMUM
80.8AVERAGE
PROFILE: inspect a column → calculate statistics → examine distribution → spot quality issues
04
Redundant, Duplicated, and Unnecessary Data
Redundant data is identical data stored in multiple places, so analysts need to determine the appropriate source of truth. Duplicated data repeats within the same dataset and can inflate results when its grain is misunderstood. Unnecessary fields add noise and processing overhead and can be removed or excluded from queries.
WORKING DATASET
IDProductTotalExtra
101Monitor$300Flag A
102Keyboard$80Flag B
102Keyboard$80Flag B
103Mouse$40Unused
PROFILE FINDINGS
REDUNDANTSame business data may exist in multiple systems → identify the appropriate source.
DUPLICATEDID 102 repeats → verify whether repetition is expected before calculating totals.
UNNECESSARYUnused field adds noise → exclude it when it is not needed for analysis.
PROFILE FIRST: identify the issue → understand why it exists → choose the appropriate cleansing action
SELECT DISTINCT ProductID, Name, ProductNumber FROM Products;
05
Missing and NULL Values
NULL means that a field has no value. A null may mean a value is not applicable, has not yet been created, has no matching record, or was not supplied. A null Ship Date, for example, can meaningfully indicate that an order has not yet shipped. Determine why the value is absent before choosing a treatment.
DATASET WITH MISSING VALUES
OrderColorShip Date
501Blue2026-09-10
502NULL2026-09-11
503RedNULL
504BlackNULL
ASK: WHY IS IT NULL?
NOT APPLICABLEThe item may not have a color characteristic.
NOT YET KNOWNA missing Ship Date can mean the order has not shipped.
NO MATCHA lookup or join may not find the expected related record.
NULL ≠ AUTOMATIC ERROR: find the cause → interpret the meaning → choose the appropriate treatment
06
Joins, Filtering, and Troubleshooting NULLs
Missing values may originate in source data or appear because of joins, report models, broken connections, or permissions. An inner join intentionally retains matching records, so it can exclude customers with no orders. SQL can filter missing values with IS NULL. Troubleshoot backward from the report or verify the source first when appropriate.
TROUBLESHOOT: a NULL may come from the source, a missing match, filtering, the report model, or access conditions
SELECT * FROM ShippingOrders WHERE ShipDate IS NULL;
07
Applying Logic to Missing Values
Functions can replace or interpret missing values when the replacement accurately reflects their meaning. A blank Ship Date can support a derived status such as Not Shipped. Handling decisions should be documented. Do not manufacture an answer—for example, an unanswered yes/no survey item should not automatically become No.
MISSING VALUES
OrderShip DateColor
701Sep 12Blue
702NULLRed
703Sep 13NULL
IF?
MEANINGFUL OUTPUT
SHIP DATEIf genuinely not shipped yet → derive status Not Shipped.
COLORIf color does not apply → use a meaningful label such as No Color.
DOCUMENTRecord the rule so others understand how missing values were handled.
Do not turn an unanswered Yes/No field into “No” unless the business meaning supports that interpretation.
LOGIC FLOW: investigate the NULL → understand its meaning → apply a justified rule → document the decision
08
Identifying Invalid Data
Invalid data is incorrect data. Causes include values becoming outdated, flawed survey questions, impossible dates or codes, technical issues, and extreme values. Outliers are not automatically invalid; they should be investigated and validated before removal.
PROFILE THE DATA
IDAgeScoreStatus
8012488Active
802-776Active
80331250Active
8042992Legacy?
?
INVESTIGATE FINDINGS
IMPOSSIBLEAge −7 violates an expected valid range.
OUTLIERScore 250 is suspicious; validate it before deciding what to do.
OLD RULEA legacy status may reflect an outdated hard-coded assumption.
A suspicious value is a signal to investigate. An outlier is not automatically an error.
IDENTIFY: profile → compare with valid rules and ranges → flag suspicious values → verify before cleansing
09
Invisible Characters and Outliers
Leading spaces, trailing spaces, tabs, and other non-printable characters can make values that look identical to people fail a computer match. Functions such as TRIM or CLEAN can address character-related problems. Profiling, quartile methods, or z-scores can help surface unusual values for investigation.
TEXT THAT LOOKS IDENTICAL
RowCustomerLength
1Acme4
2Acme 5
3Acme4
4Acme5
NUMERIC DISTRIBUTION
TYPICAL VALUESOUTLIER?
Most values cluster together; one unusually large value should be investigated rather than automatically removed.
VIEW RAW DATAREVEAL CHARACTERSFLAG OUTLIERINVESTIGATECLEAN + VERIFY
PROFILE CAREFULLY: TRIM/CLEAN can address hidden text characters; statistical checks can flag outliers for validation
10
Remove, Replace, or Correct?
After confirming that data is invalid, analysts may remove it, replace it with an appropriate indicator, or correct it when the true value can be verified. Important changes should follow business requirements, involve relevant stakeholders when needed, and be documented.
SUSPICIOUS DATA
IDFieldValue
901Unused fieldInvalid
902StatusNULL
903CityKula Lumpur
WHAT NOW?
CHOOSE BY CONTEXT
REMOVEExclude an invalid field or record when it should not be retained and removal is justified.
REPLACEUse a meaningful indicator when the business process calls for an explicit value.
CORRECTFix the value only when the true value can be verified from a trustworthy source.
KEY IDEA: do not change suspicious data automatically — understand the cause and preserve business meaning
11
Convert Data to Meet Specifications
Data moved between systems must meet destination specifications. Analysis also depends on appropriate types: numeric calculations need numeric data and date calculations need date-compatible data. If a load fails because a field type is incompatible, identify the field and convert it to the required type.
1 · SOURCE VALUE
"09/15/2026"
TEXT
→
2 · REQUIREMENT
Date calculations
NEEDS DATE
→
3 · CONVERT
TEXT → DATE
TRANSFORM
→
4 · VERIFY
2026-09-15
DATE ✓
NUMBER → TEXT for display/exportTEXT → DATE for date operationsTEXT → NUMBER for calculations or numeric keys
CAST(SalesQuota AS varchar(24)) AS Sales_Quota
CONCEPT: compare the current data type with the required specification → convert only when needed → verify the converted value
CAST(SalesQuota AS varchar(24)) AS Sales_Quota
12
End-to-End Cleansing Workflow
A practical sequence is: profile the data, investigate redundancy and duplicates, understand missing values, validate suspicious or invalid values, clean text issues, convert data types, and then verify counts, totals, and meaning. Profiling remains iterative throughout the project.
1
PROFILEUnderstand source, structure, counts, keys, and expected totals.
2
DEDUPLICATEInvestigate redundant, duplicated, and unnecessary data.
3
HANDLE NULLSFind the cause of missing values and apply justified logic.
4
VALIDATEInvestigate invalid values, hidden characters, and outliers.
5
CONVERTMatch data types and formats to required specifications.
6
VERIFYRecheck counts, totals, meaning, and expected results.
✓ CLEANSING COMPLETE ONLY AFTER VERIFICATION — confirm the result still represents the intended business meaning.
END-TO-END: profile → investigate → cleanse responsibly → convert where needed → verify the final dataset
13
Knowledge Check — 20 Questions
Test the key concepts from profiling and cleansing data.
1. What is the main purpose of data profiling?
Answer: A. Understand the data and identify quality issues
2. Why establish record counts before a transfer?
Answer: A. To verify that the intended records reached the destination
3. What can numeric column profiling reveal?
Answer: A. Minimum, maximum, average, counts, and distributions
4. What is redundant data?
Answer: A. Identical data stored in multiple places
5. What is duplicated data?
Answer: A. Data repeated within the same dataset
6. What risk can unexpected duplicates create?
Answer: A. Inflated calculations or invalid reports
7. What are unnecessary fields often considered?
Answer: A. Noise in the data
8. What does NULL mean?
Answer: A. There is no value in the field
9. What can a null Ship Date indicate?
Answer: A. The order has not yet shipped
10. Why investigate the cause of a null?
Answer: A. The cause determines the appropriate handling
11. What can an inner join do to unmatched records?
Answer: A. Exclude them
12. Which SQL condition filters missing values?
Answer: A. IS NULL
13. What is imputation?
Answer: A. Replacing or interpreting missing values with an appropriate value
14. Should an unanswered yes/no survey item automatically become No?
Answer: A. No
15. Are outliers automatically invalid?
Answer: A. No; they require validation
16. What can leading or trailing spaces cause?
Answer: A. Values that look alike can fail to match
17. Which functions can help with extra or non-printable characters?
Answer: A. TRIM or CLEAN
18. When can invalid data be corrected?
Answer: A. When the correct value can be verified
19. Why convert a data type?
Answer: A. To meet system or analytical specifications
20. What should happen after cleansing?
Answer: A. Verify counts, totals, types, and meaning