Data Profiling

Descriptive statistics provide quantitative summaries of a dataset's central tendency, dispersion, and shape. These are the first-order metrics calculated during profiling.

Key metrics include:

• Mean, Median, Mode: Measures of central tendency.
• Standard Deviation & Variance: Indicators of data spread and volatility.
• Min/Max & Range: The bounds of the data.
• Quantiles (25th, 50th, 75th percentile): Understanding data distribution, especially for identifying data skew.

For example, profiling a transaction_amount column would immediately flag potential outliers if the maximum value is orders of magnitude larger than the 99th percentile.

This technique measures the count of distinct values in a column relative to the total number of rows. It is fundamental for primary key detection and understanding attribute granularity.

• High Cardinality: A column with a unique or nearly unique value for each row (e.g., user_id, transaction_id).
• Low Cardinality: A column with few distinct values repeated many times (e.g., status with values 'active', 'inactive').

Uniqueness Percentage is calculated as (COUNT(DISTINCT column_values) / COUNT(*)) * 100. A result of 100% suggests a candidate key. Profiling tools use this to automatically suggest primary keys and detect potential foreign key relationships by matching high-cardinality columns across tables.

Beyond basic storage types (e.g., VARCHAR, INT), profiling infers the semantic data type and format pattern of column values through regular expression matching and heuristic analysis.

This process identifies:

• Standard Patterns: Email addresses, phone numbers, credit card numbers, UUIDs, ISO date formats.
• Custom Patterns: Internal product codes (e.g., PRD-XXXX-YY), license plate formats.
• Semantic Types: Differentiating between a string that is actually a city_name, zip_code, or email. This feeds directly into data domain inference and PII detection.

For instance, a column with values like 2023-12-01 and Jan-15-2023 would be inferred as a date type with multiple format patterns, highlighting a data quality issue for standardization.

This assesses the prevalence of missing, null, or empty values in a dataset, producing the completeness metric.

• Completeness: (Non-Null Count / Total Row Count) * 100. A column with 95,000 non-null values out of 100,000 rows has 95% completeness.
• Sparsity: The inverse, measuring the percentage of missing values. High sparsity can indicate problems with data collection or ETL processes.

Profiling expands this to cross-column analysis, identifying if missing values in one column correlate with specific values in another (e.g., shipping_date is always null when order_status is 'cancelled'). This is a key input for data quality metrics and anomaly detection.

This technique visualizes and analyzes the frequency of values within a column. A histogram bins values to reveal the shape of the distribution.

Key insights include:

• Identifying Skew: Whether data is normally distributed or heavily skewed left/right.
• Detecting Outliers: Values that fall far outside the typical bins.
• Understanding Common Values: The most frequent (mode) entries, which can reveal data entry patterns or default values.
• Spotting Invalid Categories: For categorical data, it lists all distinct values, exposing typos or deprecated codes (e.g., 'USA', 'US', 'United States').

This analysis is crucial for data segmentation, setting expectations for machine learning feature distributions, and informing data transformation discovery.

Advanced profiling techniques automatically uncover structural relationships within and between datasets. This moves beyond single-column analysis to multi-column and cross-dataset analysis.

Core techniques include:

• Functional Dependency Discovery: Determining if column A's value uniquely determines column B's value (e.g., zip_code -> city).
• Foreign Key Detection: Identifying columns in Table B whose values are a subset of a key column in Table A, suggesting a join path.
• Association Rule Mining: Finding frequently co-occurring values across columns.
• Data Relationship Mapping: Building a preliminary graph of how tables connect.

These discoveries are the foundation for data lineage and dependency mapping, ensuring referential integrity, and optimizing query design.

Data profiling provides the quantitative evidence needed to assess data health and prioritize remediation. It automatically surfaces issues like:

• Completeness gaps: Null rates exceeding defined thresholds.
• Validity violations: Values that break format rules (e.g., malformed emails).
• Consistency anomalies: Mismatched data types or units across related tables.
• Uniqueness problems: Duplicate values in columns expected to be primary keys. This statistical baseline allows teams to move from anecdotal complaints to data-driven incident management, focusing engineering effort on the most impactful quality defects.

For undocumented or legacy datasets, profiling automatically reverse-engineers the structural metadata. This includes:

• Data type inference: Determining if a column contains integers, dates, or free-text strings.
• Constraint discovery: Identifying potential primary keys, foreign keys, and unique constraints by analyzing value cardinality and cross-table relationships.
• Pattern recognition: Detecting standard formats (e.g., YYYY-MM-DD) or custom business codes within string values. This creates the foundational documentation required for any subsequent integration, migration, or analysis work, effectively mapping the 'shape' of unknown data.

Profiling informs the logic and architecture of data transformation pipelines. By understanding source data characteristics, engineers can design more robust ETL/ELT processes:

• Informing cleansing rules: Setting appropriate default values for nulls based on value distributions.
• Optimizing transformations: Choosing correct parsing functions for discovered date formats or numeric precision.
• Sizing compute resources: Using volume and sparsity analysis to right-size cluster memory and storage.
• Validating business logic: Ensuring calculated fields (e.g., revenue = quantity * price) align with profiled statistical ranges. This reduces post-deployment surprises and rework.

Profiling automates critical checks for data privacy and regulatory adherence. It is essential for:

• Sensitive Data Discovery (PII Detection): Scanning columns to automatically flag potential personally identifiable information like credit card numbers or national IDs using pattern matching and semantic inference.
• Data Classification: Tagging data with sensitivity labels (e.g., 'public', 'internal', 'restricted') based on profiled content and context.
• Audit Evidence: Generating baseline reports on data state at a point in time for compliance audits (e.g., GDPR, CCPA).
• Access Policy Enforcement: Providing the metadata needed to enforce role-based access controls on sensitive columns.

When moving data between systems (e.g., legacy RDBMS to cloud data warehouse), profiling de-risks the project by identifying compatibility issues and scope. Key activities include:

• Source-to-target mapping: Discovering semantic relationships and structural differences between old and new schemas.
• Anomaly detection in legacy data: Uncovering long-hidden quality issues that could break the new system.
• Volume and growth forecasting: Accurately sizing the target environment and estimating migration runtime.
• Post-migration validation: Using pre-migration profiles as a benchmark to verify data integrity after the cutover, ensuring no loss or corruption occurred during transfer.

For data science and ML workflows, profiling is the first step in feature engineering and model risk assessment. It directly impacts model performance by:

• Identifying training data suitability: Detecting high cardinality, severe skew, or sparsity that could bias a model.
• Guiding feature selection: Highlighting columns with low variance (constants) or high null rates that are poor candidates for features.
• Informing preprocessing: Revealing the need for normalization, outlier capping, or imputation strategies based on value distributions.
• Monitoring data drift: Establishing the statistical baseline of training data to later detect shifts in production data that degrade model accuracy.

The automated process of inferring a dataset's structural metadata without prior documentation. This includes identifying:

• Column names and data types (e.g., integer, string, timestamp)
• Constraints like nullability and uniqueness
• Table relationships and potential keys

It is the first step in profiling, creating a blueprint of the data's organization.

The automated determination of the logical or semantic type of data in a column based on its content and format. It goes beyond basic storage types (e.g., VARCHAR) to identify:

• Semantic types: Email addresses, phone numbers, URLs, IP addresses
• Temporal types: Dates, timestamps, durations
• Numeric types: Currency, percentages, measurement units

This enables accurate validation and appropriate processing logic.

The measurement of the number of distinct values in a dataset column. This key profiling metric helps assess:

• Uniqueness: High cardinality may indicate a candidate primary key (e.g., user_id).
• Data Quality: Unexpectedly low cardinality in a supposedly unique field signals potential duplicates.
• Storage & Indexing: Informs decisions on database indexing strategies and compression.

Cardinality is often expressed as a count or a percentage of total rows.

Summary metrics calculated during profiling to quantitatively describe a dataset's central tendency, dispersion, and shape. Core statistics include:

• Central Tendency: Mean, median, mode
• Dispersion: Standard deviation, variance, range
• Distribution Shape: Skewness, kurtosis
• Quantiles: Minimum, 25th percentile (Q1), median (Q2), 75th percentile (Q3), maximum

These provide a numerical snapshot of data health and distribution.

The automated identification of recurring formats, sequences, or structures within data values. This technique discovers:

• Standard Formats: Date patterns (YYYY-MM-DD), phone numbers, postal codes
• Custom Identifiers: Internal SKU formats, invoice numbers, transaction IDs
• Anomalous Patterns: Values that deviate from the discovered norm, indicating potential errors

Pattern recognition is essential for data validation and cleansing rule generation.

The automated process of identifying relationships between columns where the value of one set (the determinant) uniquely determines the value of another set. For example, in a dataset, country_code might determine currency. This profiling activity reveals:

• Business Rules: Embedded logic within the data.
• Data Redundancy: Opportunities for normalization.
• Data Integrity Issues: Violations of expected dependencies.

It is a critical step for understanding data semantics and optimizing schema design.