101 ) DATA PROFILING

Complete End-to-End Data Profiling Guide with Real-World Fixes (Insurance Project)

What is Data Profiling?

Data Profiling is the systematic process of examining, analyzing, and reviewing source data (from databases, files, or lakehouses) to collect structural statistics and metadata—such as null counts, distinct value counts, data types, and pattern formats. It helps data engineers and data modelers understand data quality, discover anomalies, and design accurate table structures before building data pipelines.

When and At What Stage of Data Modeling Do We Do Data Profiling?

• When we do it: Data profiling is performed early in the data lifecycle, right after data is ingested into your lakehouse storage, but before building final reporting models.

• At what stage of data modeling: It is done during the Logical Data Modeling stage. Profiling your raw insurance data ensures you catch structural defects (like empty names or invalid formats) before you design your final Star Schema dimensions and facts.

Widely Used Data Profiling Tool

===============================================

 using PYTHON

===============================================

In modern cloud-native data lakehouses, the industry standard open-source tool used for data profiling is Python with YData-Profiling (formerly Pandas-Profiling) or Great Expectations running inside Databricks (our primary Lakehouse platform). For enterprise catalog automation, tools like Collibra Data Quality or Atlan are widely used.

Complete Step-by-Step Data Profiling & Remediation Process

Step 1: Connect and Run the Profiling Script

• What you do: Load your raw insurance dataset (e.g., customer policy files) into your notebook environment and generate the profiling report.

• Tool & Script:

  Python

  import pandas as pd
  from ydata_profiling import ProfileReport
  
  # Load raw insurance customer data from Lakehouse storage
  df_customers = spark.read.format("parquet").load("dbfs:/mnt/insurance-lakehouse/raw/customers/").toPandas()
  
  # Generate profiling report
  profile = ProfileReport(df_customers, title="Customer Data Profiling Report", explorative=True)
  profile.to_file("customer_profiling_report.html")
  

Step 2: Analyze the Report and Identify Anomalies

• What you do: Open the generated HTML report and inspect columns for quality issues. For example, the report highlights that the customer_name column has 5% missing (empty) cells, and phone_number contains invalid text formats.

Step 3: Apply Step-by-Step Remediation for Specific Issues (e.g., Empty Cells)

• What you do: Write and execute data cleaning scripts in your Lakehouse tool (Databricks/PySpark) to fix the anomalies discovered during profiling.

Example A: Handling Empty Cells in the customer_name Column

• Problem found by profiler: 5% of rows have NULL or empty space strings in customer_name.

• Resolution Step & Script:

  Replace missing names with a placeholder or filter them out depending on business rules, and drop duplicate records.

  Python

  import pyspark.sql.functions as F
  
  # Fill empty or null customer names with a default fallback and remove complete duplicates
  df_cleaned = df_customers \
      .na.fill({"customer_name": "Unknown Policyholder"}) \
      .withColumn("customer_name", F.trim(F.col("customer_name"))) \
      .dropDuplicates(["customer_id"])
  

Example B: Handling Invalid Formats in the phone_number Column

• Problem found by profiler: Text mismatch where phone numbers contain letters or incorrect digit counts.

• Resolution Step & Script:

  Filter out or sanitize rows that do not match the expected regex phone pattern.

  Python

  # Filter out rows with invalid phone number patterns
  phone_pattern = r'^\+?[1-9]\d{1,14}$'
  df_cleaned = df_cleaned.filter(F.col("phone_number").rlike(phone_pattern))
  

Step 4: Write Cleaned Data Back to the Lakehouse Storage Table

• What you do: Save the remediated dataframe into your transactional lakehouse table format (Apache Iceberg or Delta Lake) so downstream data models can consume clean data.

• Tool & Script:

  Python

  # Write cleaned data to an ACID-compliant Iceberg table in the Lakehouse
  df_cleaned.writeTo("iceberg_catalog.insurance_db.cleaned_customers") \
      .using("iceberg") \
      .createOrReplace()
  

When Can We Say Data Profiling Is Complete?

Data profiling is officially complete when all of the following exit criteria are met:

1. 100% Asset Coverage: Every column across your ingested source files has been scanned by the profiler tool.

2. Anomalies Addressed: All critical data defects identified in the report (such as empty cells in mandatory name columns, duplicate records, or invalid format strings) have been cleaned using remediation code.

3. Sign-Off: Data stewards and data modelers have verified that the profiling metrics fall within acceptable quality thresholds, giving the green light to proceed with building final dimensional data models.

===============================================

 using COLLIBRA

===============================================

Steps to Perform Data Profiling Using Collibra

In the Collibra Data Intelligence Platform, data profiling is integrated directly into the Data Catalog using Collibra Edge. It automatically scans database assets or lakehouse connections to extract statistics, data types, and quality metrics like null counts and value distributions.

Step 1: Set Up Collibra Edge and Capabilities

• What you do: Ensure your local or cloud environment has a Collibra Edge site configured with a JDBC connection capability and a JDBC profiling capability so Collibra can securely connect to your source database or lakehouse.

Step 2: Register the Data Source and Synchronize Schemas

• What you do: Register your data source (such as your insurance database or Databricks Unity Catalog) through the Edge site and run a synchronization to pull in your table and column metadata.

Step 3: Configure Profiling Options

• What you do: Navigate to the database asset page in Collibra, open the Configuration tab, and set up your profiling preferences.

• You can choose whether to profile all table rows or a representative random subset, and define formatting patterns for dates and times.

Step 4: Run the Profiling Job

• What you do: Trigger the profiling job manually from the configuration menu, set up an automated schedule, or configure it to run immediately after a schema synchronization.

• Collibra Edge executes the profiling query securely within your environment and sends anonymized summary statistics back to the Collibra Platform.

Step 5: Review Profiling Results and Statistics in the Catalog

• What you do: Navigate to the specific Table or Column asset page in Collibra Data Catalog to inspect the results:

  1. Open the Summary tab on a Table asset to view column lists, row counts, empty value counts, and distinct value metrics.

  2. Click on the Descriptive Statistics icon next to any column to open visualization charts showing data distributions, min/max values, mean/median stats, and null percentages.

===============================================

 using DATABRICKS

===============================================

When executing data profiling inside a Databricks lakehouse environment, you can use Databricks Data Quality Monitoring (native lakehouse profiling) or open-source programmatic tools like ydata-profiling.

The step-by-step implementation guide using Databricks to profile data, fix data quality issues (such as empty cells), and verify completion is outlined below:

Step-by-Step Guide: Data Profiling and Remediation in Databricks

Step 1: Run Data Profiling Using Databricks Lakehouse Monitoring

• What you do: Create a system-managed monitor attached to your Unity Catalog insurance table to automatically analyze data integrity, summary statistics, and null counts.

• Configuration Code (Python / Databricks SDK):

  Python

  from databricks import lakehouse_monitoring as lm
  
  # Create a profile monitor on your raw insurance customers table
  lm.create_monitor(
      table_name="insurance_catalog.insurance_schema.raw_customers",
      profile_type="snapshot", # Analyzes all data in the table
      output_schema_name="insurance_catalog.insurance_schema",
      inference_config=None
  )
  

Step 2: Inspect the Auto-Generated Profile Metrics and Dashboard

• What you do: Navigate to the Quality tab of your table in the Databricks Catalog Explorer or open the auto-generated Databricks SQL dashboard.

• What you find: The profiler flags structural defects, showing that the customer_name column has 5% missing (empty/null) cells and the phone_number column contains formatting anomalies.

Step 3: Write and Execute Remediation Code for Identified Issues

• What you do: Open a Databricks Notebook and write PySpark code to clean and fix the anomalies discovered by the profiler.

Fixing Empty Cells and Duplicates:

Python

import pyspark.sql.functions as F

# Load the raw table into a Spark DataFrame
df_raw = spark.read.table("insurance_catalog.insurance_schema.raw_customers")

# Clean data: fill empty/null names with a fallback placeholder, trim whitespace, and drop duplicate IDs
df_cleaned = df_raw \
    .na.fill({"customer_name": "Unknown Policyholder"}) \
    .withColumn("customer_name", F.trim(F.col("customer_name"))) \
    .dropDuplicates(["customer_id"])

Handling Invalid Text Formats (e.g., Phone Numbers):

Python

# Filter rows matching a valid phone regex pattern
phone_pattern = r'^\+?[1-9]\d{1,14}$'
df_cleaned = df_cleaned.filter(F.col("phone_number").rlike(phone_pattern))

Step 4: Save Cleaned Data Back to the Lakehouse

• What you do: Write the remediated dataframe into a trusted, ACID-compliant table format (Delta Lake or Apache Iceberg) within Databricks so downstream models consume clean data.

• Configuration Code:

  Python

  # Write the cleaned data back into a production Unity Catalog table
  df_cleaned.write.format("delta") \
      .mode("overwrite") \
      .saveAsTable("insurance_catalog.insurance_schema.trusted_customers")
  

When Can We Say Data Profiling Is Complete?

Data profiling and its cleanup cycle are officially complete when:

1. Zero Critical Defects: Re-running the Databricks Lakehouse monitor confirms that null percentages in mandatory fields (like name and ID) drop to 0% and pattern validation passes successfully.

2. Metric Stability: The auto-generated profile metrics table shows consistent data volume and distribution without unexpected behavioral drift.

3. Sign-Off: Data engineers and modelers verify that the trusted lakehouse table meets all quality criteria, permitting the transition to final data modeling (Star Schema creation).