Data Profiling Techniques

Section 3: Data Assessment and Preparation. Lesson 12.

This section moves from planning to the data itself: profiling, quality issues, cleansing, missing and duplicate data, and standardisation. Profiling comes first, because you cannot fix data you have not looked at.

What is data profiling?

Data profiling is the process of examining, analysing and understanding data to discover its structure, content, quality, relationships and patterns. It is one of the most important activities in a migration because it shows the actual condition of the source data before the migration begins, rather than what people assume it contains.

Think of it as a medical diagnosis: a doctor runs tests before prescribing treatment, and a data engineer profiles data before designing the migration. The results answer three questions:

Is the source data suitable for the planned migration?
What corrections or cleansing does it need?
How complex will the migration effort be?

The five things to profile

Structure: tables, columns, data types, lengths, keys. Is the data really in the format the schema claims?
Content: row counts, value ranges, distinct values, min and max, the most common values.
Quality: nulls, blanks, invalid values, duplicates, inconsistent formats.
Relationships: keys between tables, dependencies and correlations between columns.
Patterns: formats (emails, phone numbers, dates), recurring structures and outliers.

Hands-on example: video game sales dataset

The lecture profiles an open dataset from Kaggle: video game sales (about 9 MB, around 50,000 rows), opened in Excel. Kaggle is a good source of free practice datasets, such as gaming statistics or student exam performance.

Structure: data is in rows and columns, with columns such as game_id, title, platform, platform_type, platform_maker, platform_generation, genre, year, publisher and developer.
Content: about 50,000 rows of historical sales records.
Quality: checked with validation, for example whether year is always a valid year and publisher is never blank.
Relationships: for example user score against publisher tier, or Metacritic score against critic review count.
Patterns: which genres, platforms and eras cluster together, and which values stand out as outliers.

Core profiling techniques

Column profiling: for each column, the data type, null count and percentage, distinct count, min, max, mean, most frequent values and length distribution.
Pattern profiling: the shapes values take, such as how many phone numbers match a standard format and how many don't.
Uniqueness and key analysis: whether a supposed primary key is actually unique, and how many duplicates exist.
Cross-column (dependency) profiling: rules between columns, such as city always matching postcode, or an end date after a start date.
Cross-table (referential) profiling: orphaned rows, such as orders whose customer_id doesn't exist in customers.
Distribution and outlier analysis: histograms, and unusual values such as a game released in the year 2099.

Profiling with SQL

sql

-- Column profile: volume, nulls, distinct values, range
SELECT
  COUNT(*)                                AS total_rows,
  COUNT(*) - COUNT(publisher)             AS null_publishers,
  COUNT(DISTINCT publisher)               AS distinct_publishers,
  MIN(year)                               AS min_year,
  MAX(year)                               AS max_year
FROM video_games;

-- Key uniqueness: duplicated game_id values
SELECT game_id, COUNT(*) AS copies
FROM video_games
GROUP BY game_id
HAVING COUNT(*) > 1;

-- Top values for a category column
SELECT genre, COUNT(*) AS games
FROM video_games
GROUP BY genre
ORDER BY games DESC
LIMIT 10;

Profiling with Python (pandas)

python

import pandas as pd

df = pd.read_csv("video_games.csv")

df.shape                       # rows, columns
df.dtypes                      # data type per column
df.isna().sum()                # nulls per column
df.nunique()                   # distinct values per column
df.describe(include="all")     # min, max, mean, top values
df["game_id"].duplicated().sum()   # duplicate keys
df[["user_score", "critic_score"]].corr()  # relationships

Dedicated tools: ydata-profiling (one-line HTML profile reports), Great Expectations, Informatica Data Quality, Talend Data Quality, and the profiling features built into AWS Glue DataBrew and Azure Purview.

When to profile

Profiling is not a one-time activity. It runs at three points:

Initial assessment: understand the source and size the cleansing effort.
Testing: re-profile after each trial migration to confirm the transformations behave.
After migration: profile the target and compare it with the source profile (row counts, null rates, distinct counts should match or differ only as expected).

Benefits

Improves migration accuracy
Reduces operational risk by surfacing problems early
Enhances overall data quality
Increases confidence in the migrated data
Sets a baseline, so new data arriving later can be checked against the same patterns and rules

Key takeaways

Profiling examines structure, content, quality, relationships and patterns before migration.
Like a diagnosis, it decides whether data is suitable, what needs fixing and how complex the work is.
Use column, pattern, key, dependency, referential and distribution profiling, with SQL, pandas or dedicated tools.
Profile at assessment, during testing and after migration.

Next: 13. Identifying Data Quality Issues, which covers how to spot and pinpoint the problems profiling reveals.