Data Quality and Profiling

Lesson Overview

This lesson covers data quality dimensions and profiling techniques to ensure reliable data analysis.

What You'll Learn:

• Data quality dimensions
• Data profiling methods
• Quality assessment techniques
• Data quality metrics
• Improvement strategies

Key Concepts:

• Data Quality: Fitness of data for intended use
• Data Profiling: Examining data for quality and structure
• Completeness: Presence of required data
• Accuracy: Correctness of data values
• Consistency: Uniformity across data sources

Data Profiling Techniques

What is Data Profiling

Data Profiling is the systematic examination of data to understand its structure, content, and quality. It involves analyzing data to identify patterns, anomalies, and quality issues.

Profiling Objectives

• Discover data structure and format
• Identify data quality issues
• Understand data relationships
• Document metadata
• Support data cleansing efforts

Duplicate Detection

Types of Duplicates

• Exact Duplicates: Identical records across all fields
• Partial Duplicates: Similar records with some variations
• Fuzzy Duplicates: Records that are approximately the same
• Logical Duplicates: Different representations of same entity

Duplicate Detection Methods

• Exact Matching: Compare all fields for exact equality
• Key Field Matching: Compare primary identifiers
• Fuzzy Matching: Use similarity algorithms
• Rule-Based Matching: Apply business rules

Short SQL Example: Duplicate Detection

-- Find duplicate records based on key fields
WITH duplicate_candidates AS (
    SELECT 
        customer_id,
        email,
        first_name,
        last_name,
        phone,
        -- Count occurrences of same email
        COUNT(*) OVER (PARTITION BY email) as email_count,
        -- Count occurrences of same name
        COUNT(*) OVER (PARTITION BY first_name, last_name) as name_count,
        -- Count occurrences of same phone
        COUNT(*) OVER (PARTITION BY phone) as phone_count
    FROM customers
    WHERE email IS NOT NULL
)
SELECT 
    'Duplicate Analysis' as analysis_type,
    email,
    first_name,
    last_name,
    phone,
    email_count,
    name_count,
    phone_count,
    -- Duplicate classification
    CASE 
        WHEN email_count > 1 THEN 'Email Duplicate'
        WHEN name_count > 1 THEN 'Name Duplicate'
        WHEN phone_count > 1 THEN 'Phone Duplicate'
        ELSE 'Unique'
    END as duplicate_type,
    -- Risk level
    CASE 
        WHEN email_count > 2 OR name_count > 2 THEN 'High Risk'
        WHEN email_count > 1 OR name_count > 1 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END as risk_level
FROM duplicate_candidates
WHERE email_count > 1 OR name_count > 1 OR phone_count > 1
ORDER BY email_count DESC, name_count DESC;

Missing Data Analysis

Types of Missing Data

• Missing Completely at Random (MCAR): No pattern in missingness
• Missing at Random (MAR): Missingness related to observed data
• Missing Not at Random (MNAR): Missingness related to unobserved data

Missing Data Patterns

• Random Missing: No discernible pattern
• Systematic Missing: Missing in specific conditions
• Structural Missing: Not applicable for certain records
• Collection Missing: Data not collected properly

Short SQL Example: Missing Data Analysis

-- Analyze missing data patterns
SELECT 
    'Missing Data Analysis' as analysis_type,
    table_name,
    column_name,
    data_type,
    COUNT(*) as total_records,
    COUNT(CASE WHEN column_value IS NULL THEN 1 END) as null_count,
    COUNT(CASE WHEN column_value = '' THEN 1 END) as empty_count,
    COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) as total_missing,
    -- Missing percentage
    ROUND(COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*), 2) as missing_percentage,
    -- Missing pattern classification
    CASE 
        WHEN COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*) > 50 THEN 'Severe Missing'
        WHEN COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*) > 20 THEN 'High Missing'
        WHEN COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*) > 5 THEN 'Moderate Missing'
        WHEN COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*) > 0 THEN 'Low Missing'
        ELSE 'Complete'
    END as missing_severity,
    -- Recommended action
    CASE 
        WHEN COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*) > 50 THEN 'Investigate Data Collection'
        WHEN COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*) > 20 THEN 'Impute or Remove'
        WHEN COUNT(CASE WHEN column_value IS NULL OR column_value = '' THEN 1 END) * 100.0 / COUNT(*) > 5 THEN 'Monitor Closely'
        ELSE 'No Action Needed'
    END as recommended_action
FROM data_profile
GROUP BY table_name, column_name, data_type
ORDER BY missing_percentage DESC;

Pattern Analysis

Data Pattern Types

• Format Patterns: Date formats, phone numbers, email formats
• Value Patterns: Ranges, distributions, outliers
• Text Patterns: Capitalization, special characters, length
• Temporal Patterns: Trends, seasonality, gaps

Pattern Detection Methods

• Regular Expressions: Identify format patterns
• Statistical Analysis: Detect value patterns
• Text Analysis: Analyze text patterns
• Time Series Analysis: Identify temporal patterns

Short SQL Example: Pattern Analysis

-- Analyze data patterns in text fields
SELECT 
    'Pattern Analysis' as analysis_type,
    column_name,
    data_type,
    COUNT(*) as total_records,
    -- Email pattern analysis
    COUNT(CASE WHEN column_value LIKE '%@%.%' THEN 1 END) as email_format_count,
    COUNT(CASE WHEN column_value REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN 1 END) as valid_email_count,
    -- Phone pattern analysis
    COUNT(CASE WHEN column_value REGEXP '^\+?[0-9]{10,15}$' THEN 1 END) as phone_format_count,
    -- Text pattern analysis
    AVG(LENGTH(column_value)) as avg_length,
    MIN(LENGTH(column_value)) as min_length,
    MAX(LENGTH(column_value)) as max_length,
    -- Pattern quality
    CASE 
        WHEN data_type = 'email' AND COUNT(CASE WHEN column_value REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN 1 END) = COUNT(*) THEN 'Excellent'
        WHEN data_type = 'email' AND COUNT(CASE WHEN column_value REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN 1 END) > COUNT(*) * 0.9 THEN 'Good'
        WHEN data_type = 'email' AND COUNT(CASE WHEN column_value REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN 1 END) > COUNT(*) * 0.7 THEN 'Fair'
        ELSE 'Poor'
    END as pattern_quality
FROM data_profile
WHERE data_type IN ('email', 'phone', 'text')
GROUP BY column_name, data_type
ORDER BY pattern_quality;

Data Validation and Consistency Checks

Validation Rules

Data Type Validation

• Numeric Validation: Range checks, format validation
• Text Validation: Length limits, character restrictions
• Date Validation: Format checks, range validation
• Boolean Validation: True/false values only

Business Rule Validation

• Referential Integrity: Foreign key relationships
• Business Logic: Domain-specific rules
• Cross-Field Validation: Related field consistency
• Temporal Validation: Date sequence logic

Short SQL Example: Data Validation

-- Comprehensive data validation checks
SELECT 
    'Data Validation' as validation_type,
    table_name,
    column_name,
    validation_rule,
    COUNT(*) as total_records,
    COUNT(CASE WHEN validation_passed = true THEN 1 END) as valid_records,
    COUNT(CASE WHEN validation_passed = false THEN 1 END) as invalid_records,
    -- Validation pass rate
    ROUND(COUNT(CASE WHEN validation_passed = true THEN 1 END) * 100.0 / COUNT(*), 2) as pass_rate,
    -- Validation status
    CASE 
        WHEN COUNT(CASE WHEN validation_passed = true THEN 1 END) * 100.0 / COUNT(*) >= 95 THEN 'Excellent'
        WHEN COUNT(CASE WHEN validation_passed = true THEN 1 END) * 100.0 / COUNT(*) >= 90 THEN 'Good'
        WHEN COUNT(CASE WHEN validation_passed = true THEN 1 END) * 100.0 / COUNT(*) >= 80 THEN 'Fair'
        ELSE 'Poor'
    END as validation_status
FROM validation_results
GROUP BY table_name, column_name, validation_rule
ORDER BY pass_rate ASC;

Consistency Checks

Cross-Table Consistency

• Referential Consistency: Foreign key relationships
• Semantic Consistency: Same meaning across tables
• Temporal Consistency: Date relationships
• Value Consistency: Standardized values

Within-Table Consistency

• Field Consistency: Related field values
• Record Consistency: Logical record structure
• Format Consistency: Standardized formats
• Range Consistency: Valid value ranges

Short SQL Example: Consistency Checks

-- Cross-table consistency checks
WITH consistency_checks AS (
    -- Check referential integrity
    SELECT 
        'Referential Integrity' as check_type,
        'orders.customer_id' as field_checked,
        COUNT(*) as total_orders,
        COUNT(CASE WHEN c.customer_id IS NULL THEN 1 END) as orphaned_orders,
        -- Consistency rate
        ROUND((COUNT(*) - COUNT(CASE WHEN c.customer_id IS NULL THEN 1 END)) * 100.0 / COUNT(*), 2) as consistency_rate
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.customer_id
    
    UNION ALL
    
    -- Check date consistency
    SELECT 
        'Date Consistency' as check_type,
        'orders.order_date vs ship_date' as field_checked,
        COUNT(*) as total_orders,
        COUNT(CASE WHEN ship_date < order_date THEN 1 END) as inconsistent_dates,
        -- Consistency rate
        ROUND((COUNT(*) - COUNT(CASE WHEN ship_date < order_date THEN 1 END)) * 100.0 / COUNT(*), 2) as consistency_rate
    FROM orders
    WHERE ship_date IS NOT NULL
)
SELECT 
    check_type,
    field_checked,
    total_orders,
    orphaned_orders as inconsistent_records,
    consistency_rate,
    -- Consistency status
    CASE 
        WHEN consistency_rate >= 99 THEN 'Excellent'
        WHEN consistency_rate >= 95 THEN 'Good'
        WHEN consistency_rate >= 90 THEN 'Fair'
        ELSE 'Poor'
    END as consistency_status,
    -- Action needed
    CASE 
        WHEN consistency_rate < 90 THEN 'Immediate Action Required'
        WHEN consistency_rate < 95 THEN 'Investigate Issues'
        WHEN consistency_rate < 99 THEN 'Monitor Closely'
        ELSE 'Maintain Standards'
    END as action_needed
FROM consistency_checks
ORDER BY consistency_rate ASC;

Automated Quality Monitoring

Continuous Monitoring

• Real-time Validation: Check data as it enters
• Batch Validation: Periodic quality checks
• Alert Systems: Notify on quality issues
• Trend Analysis: Track quality over time

Quality Metrics

• Completeness Metrics: Percentage of populated fields
• Accuracy Metrics: Validation pass rates
• Consistency Metrics: Cross-reference checks
• Timeliness Metrics: Data freshness indicators

Impact of Poor Data Quality on Analysis

Business Impact

Financial Impact

• Direct Costs: Rework, correction, cleanup
• Opportunity Costs: Missed insights, poor decisions
• Compliance Costs: Fines, penalties, legal issues
• Reputation Costs: Customer dissatisfaction, brand damage

Operational Impact

• Inefficiency: Time spent on data issues
• Delays: Project setbacks, reporting delays
• Errors: Incorrect decisions, failed processes
• Resource Waste: Unnecessary data processing

Analytical Impact

Statistical Impact

• Biased Results: Skewed distributions, incorrect conclusions
• Reduced Power: Lower statistical significance
• Invalid Models: Poor predictive performance
• Misleading Insights: False patterns, wrong trends

Decision Impact

• Poor Decisions: Based on incorrect data
• Missed Opportunities: Overlooked insights
• Risk Exposure: Unidentified threats
• Strategic Errors: Wrong business direction

Short SQL Example: Impact Analysis

-- Analyze impact of data quality issues on analysis
SELECT 
    'Quality Impact Analysis' as analysis_type,
    quality_issue_type,
    affected_table,
    affected_analysis,
    COUNT(*) as affected_records,
    -- Impact metrics
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM analysis_results), 2) as impact_percentage,
    -- Business impact
    CASE 
        WHEN quality_issue_type = 'Missing Key Fields' THEN 'High Impact'
        WHEN quality_issue_type = 'Duplicate Records' THEN 'Medium Impact'
        WHEN quality_issue_type = 'Invalid Formats' THEN 'Medium Impact'
        WHEN quality_issue_type = 'Inconsistent Values' THEN 'Low Impact'
        ELSE 'Unknown Impact'
    END as business_impact,
    -- Recommended action
    CASE 
        WHEN quality_issue_type = 'Missing Key Fields' THEN 'Immediate Data Collection Fix'
        WHEN quality_issue_type = 'Duplicate Records' THEN 'Deduplication Required'
        WHEN quality_issue_type = 'Invalid Formats' THEN 'Validation Rules Needed'
        WHEN quality_issue_type = 'Inconsistent Values' THEN 'Standardization Required'
        ELSE 'Investigate Further'
    END as recommended_action
FROM quality_impact_analysis
GROUP BY quality_issue_type, affected_table, affected_analysis
ORDER BY impact_percentage DESC;

Real-World Examples

Customer Analytics

• Problem: Duplicate customer records
• Impact: Overcounted customers, inaccurate segmentation
• Solution: Deduplication, master data management

Financial Reporting

• Problem: Missing transaction data
• Impact: Incorrect revenue calculations, compliance issues
• Solution: Data validation, automated checks

Marketing Campaigns

• Problem: Invalid email addresses
• Impact: Low deliverability, wasted marketing spend
• Solution: Email validation, regular cleaning

Quality Improvement Strategies

Prevention Strategies

• Input Validation: Check data at entry point
• Standardization: Consistent formats and rules
• Training: Educate data users and collectors
• Process Design: Build quality into processes

Correction Strategies

• Data Cleansing: Remove or correct bad data
• Imputation: Fill missing values appropriately
• Standardization: Convert to consistent formats
• Enrichment: Add missing or correct data

Monitoring Strategies

• Quality Dashboards: Track quality metrics
• Alert Systems: Notify on quality issues
• Regular Audits: Periodic quality assessments
• Continuous Improvement: Ongoing quality enhancement

Key Takeaways

1. Data Profiling: Essential for understanding data structure and quality issues
2. Duplicate Detection: Critical for data accuracy and analysis reliability
3. Missing Data Analysis: Understanding patterns helps determine appropriate handling
4. Validation Rules: Ensure data meets business and technical requirements
5. Consistency Checks: Maintain data integrity across systems
6. Quality Impact: Poor data quality significantly affects business decisions
7. Continuous Monitoring: Data quality requires ongoing attention and improvement

Next Steps

In the next lesson, we'll explore data security and masking to understand how to protect sensitive data while maintaining usability.