Data Profiling

This analysis reveals the physical schema and format of the data. It answers fundamental questions about the dataset's organization.

• Column Analysis: Identifies data types (e.g., INTEGER, VARCHAR, TIMESTAMP), lengths, and nullability constraints.
• Key Discovery: Proposes potential primary and foreign keys by analyzing uniqueness and referential integrity between tables.
• Pattern Recognition: Detects and classifies recurring formats within string columns, such as email addresses (*@*.*), phone numbers, or postal codes.

Example: Profiling a customer table might reveal that a customer_id column is unique (a candidate primary key) and that a postal_code column contains both 5-digit and ZIP+4 patterns.

This characteristic involves calculating descriptive statistics for each column to understand the distribution and central tendency of the data.

• Numeric Columns: Computes minimum, maximum, mean, median, standard deviation, and quantiles.
• Categorical Columns: Calculates the frequency and distribution of distinct values, identifying the most common (mode) and least common entries.
• Cardinality: Measures the number of distinct values in a column, distinguishing between low-cardinality (e.g., status with values 'ACTIVE', 'INACTIVE') and high-cardinality columns (e.g., user_id).

Example: Profiling a transaction_amount column might show a mean of $150, a median of $75 (indicating a right-skewed distribution), and a maximum value of $1,000,000, which could be an outlier warranting investigation.

Data profiling quantifies the health and cleanliness of a dataset by identifying anomalies and rule violations. This forms the basis for data quality metrics.

• Completeness: Measures the percentage of non-null values in a column.
• Uniqueness: Assesses the percentage of distinct values, highlighting potential duplicate records.
• Validity: Checks values against defined rules or patterns (e.g., all dates must be in YYYY-MM-DD format).
• Accuracy (Inferred): While true accuracy requires cross-referencing with a source of truth, profiling can flag values that are statistically improbable or outside expected domains.

Example: A profile might reveal that 15% of email fields are null (completeness issue) and that 5% of records have duplicate social_security_number values (uniqueness issue).

Beyond single-table analysis, profiling explores how datasets interconnect, uncovering functional and referential dependencies.

• Foreign Key Discovery: Identifies potential relationships between tables by matching column values and data types.
• Functional Dependency Detection: Finds columns where values in one set determine values in another (e.g., zip_code often determines city).
• Cross-Table Value Analysis: Compares value domains and overlaps between columns in different tables to suggest relationships.

This analysis provides the raw material for building data lineage maps and understanding the impact of changes upstream. For instance, profiling might reveal that product_id in an orders table perfectly correlates with id in a products table, suggesting a strong foreign key relationship.

Modern data profiling is not a one-time manual audit. It is an automated, scheduled activity integrated into data pipelines and catalogs.

• Pipeline Integration: Runs automatically on new data arrivals or as part of CI/CD checks for schema changes.
• Change Detection: By comparing profiles over time, it can detect schema drift (e.g., a column type changing from STRING to INTEGER) or data drift (e.g., the statistical distribution of a key metric shifting).
• Catalog Enrichment: Results are published to a metadata catalog or data dictionary, making findings discoverable for data consumers.

Tools like OpenMetadata, DataHub, and Great Expectations automate this process, turning profiling from a project into a continuous practice of data observability.

The outputs of data profiling are essential inputs for broader data management disciplines. It provides the empirical evidence needed for informed decision-making.

• Informs Data Classification: Profiling can automatically tag columns containing PII (e.g., emails, SSN patterns) for governance policies.
• Populates Business Glossaries: Statistical summaries and value frequencies help data stewards create accurate business definitions.
• Enables Data Discovery: Profile metrics (like column names, data types, and sample values) become searchable indexes in a data catalog, helping users find relevant datasets.
• Sets Data Quality Baselines: The initial profile establishes a benchmark against which future data quality metrics are measured, enabling the calculation of quality scores and trends.

In essence, profiling transforms raw data into documented, understood assets ready for production use.

Data profiling is the primary method for establishing a quantitative baseline of data health. It automatically scans datasets to calculate key quality metrics such as:

• Completeness: Percentage of non-null values per column.
• Uniqueness: Count of distinct values to identify potential primary keys or duplicates.
• Validity: The proportion of values conforming to defined patterns or formats (e.g., email addresses, phone numbers).
• Consistency: Cross-column or cross-table validation of logical rules (e.g., end_date should be after start_date).

This statistical snapshot allows data engineers to prioritize remediation efforts and track quality improvements over time.

Before integrating a new data source, profiling reveals its actual structure, which often differs from documented expectations. It automatically infers:

• Data Types: Detects the true storage type (e.g., INTEGER, VARCHAR, TIMESTAMP) and suggests optimal types.
• Patterns & Formats: Identifies recurring formats within string columns (e.g., YYYY-MM-DD, ###-##-#### for SSN).
• Schema Drift Detection: By comparing current profile results against a historical baseline, teams can detect unauthorized or accidental schema changes in production pipelines, such as a column changing from INT to STRING.

For data scientists, profiling is the essential first step in the model development lifecycle. It directly informs feature engineering and preprocessing by highlighting:

• Cardinality: Identifying high-cardinality categorical columns that may require encoding or bundling.
• Value Distributions: Revealing skewness, multimodality, or the presence of long tails that may require transformation (e.g., log, Box-Cox).
• Missingness Patterns: Determining if missing data is random or follows a systematic pattern, guiding imputation strategy selection.
• Potential Identifiers: Flagging columns like email or user ID that must be excluded from training data to prevent data leakage and model bias.

Profiling engines are the primary source of technical metadata for modern data catalogs like DataHub or OpenMetadata. They automatically populate catalog entries with discovered metadata, including:

• Table & Column Statistics: Row counts, distinct value counts, min/max values, and data type summaries.
• Data Dependencies: Inferred foreign key relationships based on value overlap between columns across tables.
• Data Classifications: Suggesting sensitivity tags (e.g., PII, PCI) based on column names, patterns, and sample values.

This automation turns static documentation into a living, searchable inventory, powering data discovery and self-service analytics.

When migrating data to a new platform (e.g., from an on-premise warehouse to a cloud data lakehouse), profiling is critical for scoping and design. It helps:

• Assess Complexity: Quantify the volume of unstructured data, legacy encoding issues, or non-standard date formats that require special handling.
• Design Target Schemas: Inform the design of efficient, typed schemas in the destination system based on actual data content, not assumptions.
• Estimate Effort: Provide concrete metrics on data quality issues that must be cleansed during the Extract, Transform, Load (ETL) process, allowing for accurate project planning and resource allocation.

Profiling tools scan data at scale to support compliance with regulations like GDPR and HIPAA. Using pattern matching, keyword search, and machine learning classifiers, they:

• Discover Sensitive Data: Automatically locate columns containing Personally Identifiable Information (PII) such as credit card numbers, national IDs, or health codes, even if column names are obfuscated.
• Map Data Flows: By profiling data across systems, organizations can trace where sensitive data resides and flows, a key requirement for creating data lineage maps for compliance audits.
• Support Data Subject Requests: Enable efficient searching and retrieval of all records pertaining to an individual by understanding the structure and content of identity-linked tables.

A centralized repository that defines the structure, meaning, and relationships of data elements within a specific database or system. It focuses on technical attributes like data types, constraints, and field definitions, serving as a key reference for engineers and analysts. Data profiling is the primary method for populating and validating a data dictionary's contents.

• Core Function: Technical documentation of database schemas.
• Input from Profiling: Discovered data types, null counts, unique value counts, and pattern distributions.

The overarching process of finding, identifying, and understanding relevant data assets across an organization. It utilizes tools like search, browsing, and recommendations within a data catalog. Data profiling is the analytical engine of discovery, providing the statistical summaries and quality assessments that make raw data understandable and searchable.

• Key Activities: Searching metadata, browsing lineage, understanding data content.
• Profiling's Role: Generates the descriptive statistics (e.g., min/max values, sample values) that populate discovery interfaces.

Quantitative and qualitative measures used to assess the health and usability of data. Profiling calculates the foundational metrics that feed into quality dashboards and monitoring systems. These metrics are often defined as rules validated during the profiling scan.

• Common Profiling-Generated Metrics:
  • Completeness: Percentage of non-null values in a column.
  • Uniqueness: Count of distinct values versus total records.
  • Validity: Percentage of values conforming to a defined pattern or format (e.g., email, date).
  • Accuracy: (When a gold source is available) Percentage of values matching the verified source.

The process of verifying that data conforms to expected formats, types, and structural rules. While validation is a rule-based check, data profiling is the exploratory precursor that informs what those rules should be. Profiling reveals the actual schema present in the data, which is then compared against the expected schema for validation.

• Profiling Informs Validation: Discovers actual data types, string lengths, and allowable values to define constraints.
• Example: Profiling a "postal_code" column might reveal a mix of 5-digit and ZIP+4 formats, guiding the creation of a more accurate validation rule.

Metadata that describes the structural and technical properties of data. This is the primary output category of automated data profiling. It is stored in catalogs and used for impact analysis, governance, and pipeline design.

• Key Technical Metadata Generated by Profiling:
  • Structural: Table/column names, inferred data types, primary/foreign key candidates.
  • Statistical: Row counts, value distributions, min/max/mean/median for numeric columns.
  • Content-Based: Pattern frequency (e.g., 80% of strings match 'XXX-XX-XXXX'), sample values.

A granular form of data lineage that tracks the flow and transformation of data at the individual column level, from source to destination. Data profiling supports lineage in two key ways:

1. Identifying Join Keys: Profiling reveals primary key candidates and foreign key relationships by analyzing uniqueness and value overlaps between columns, which are critical for mapping lineage.
2. Understanding Transformations: By profiling data before and after a transformation step, the specific change in data characteristics (e.g., data type cast, value mapping) can be documented as part of the lineage record.