Data Profiling Using Python

Introduction

Data profiling is a critical step in any data project, especially when it comes to data migration. As organizations transition from legacy systems to modern platforms, ensuring data quality and integrity becomes essential to avoid inconsistencies and errors in the new system. Before defining the business rules and transformations required for data migration, it is crucial to conduct data profiling. This process provides in-depth insights into the structure, completeness, and overall health of the data, identifying issues early and informing the migration strategy for a smooth and accurate transition. In this blog, we explore how Python can be leveraged for data profiling to help teams generate a comprehensive report that informs data mapping rules.

What is Data Profiling?

Data profiling is the process of analyzing data to collect statistics and metadata. It helps uncover:

• Data types (numeric, text, date, etc.)

• Missing values and their distribution

• Duplicate records

• Outliers and anomalies

• Patterns and format inconsistencies

• Correlations between fields

Understanding these characteristics allows teams to define data mapping rules such as field transformations, validation checks, and cleansing steps before migration.

Automating Data Profiling with ydata-profiling

Python offers several libraries for data profiling. One of the most powerful and widely used tools is ydata-profiling (formerly pandas-profiling). It automatically generates an HTML report that summarizes key dataset statistics.

What the Report Contains

• Overview: Dataset structure, number of rows and columns, memory usage.

• Variables: Summary statistics, unique and missing values.

• Interactions: Correlation between fields.

• Missing Values: Visual heatmaps showing null data distribution.

• Duplicate Rows: Identifies redundant records.

• Text Analysis: Detects common patterns and inconsistencies.

Data Profiling Report: Overview of dataset quality, highlighting key issues and suggested transformations.

Enhancing Mapping Logic Using Profiling Insights

During a data migration project, teams often use a mapping sheet (typically in Excel or CSV format) to define how fields from the legacy system map to the new system. However, simply listing column names is not enough. You must understand the quality and nature of each field to apply the right transformation logic.

Our Approach

We automate the profiling-to-mapping process in three steps:

1. Input the Source

The source data can be either:

• A CSV file (e.g., legacy_data.csv)

• A SQL query result (e.g., SELECT * FROM legacy_table)

2. Reference a Mapping Sheet

This mapping sheet should include:

• Source Field Name

• Target Field Name

• Data Type

• Transformation Rule (to be populated)

3. Profile Only the Mapped Columns

Using Python, we:

• Subset the dataset to include only columns listed in the mapping sheet

• Run profiling with ydata-profiling on those columns

• Update the mapping sheet with findings such as:

  • Suggested Transformations: .upper(), .strip(), or pd.to_datetime()

  • Null Handling: fill with default values, drop rows, or flag for review

  • Formatting Needs: regex patterns for phone numbers (^\+61\d{9}$), ABNs, emails

  • Duplicate Checks: flag fields with repeated values needing deduplication

  • Inconsistencies: mixed types or inconsistent date formats

Monitoring Data Quality with Power BI

Another effective application of data profiling is using Power BI to track data quality over time. By setting up incremental refresh in Power BI, teams can regularly monitor trends and improvements in data quality. As data profiling provides insights into missing values, duplicates, and inconsistencies, these can be visualized in Power BI dashboards to track changes. This allows for ongoing monitoring and ensures that data quality improves with each migration phase, helping teams stay on top of critical data integrity issues.

Conclusion

Data profiling is a vital pre-migration step. It enables teams to understand the current state of their data, define proper transformation rules, and minimize migration risks. By using Python and tools like ydata-profiling, you can automate this process and produce detailed insights with minimal effort.

Incorporating data profiling into your migration workflow improves data quality and lays a solid foundation for a successful transition to the new system.