Data Cleansing

Deduplication identifies and merges exact duplicate records. Entity resolution is a more advanced process that disambiguates and links records referring to the same real-world entity despite variations in representation (e.g., 'IBM', 'International Business Machines Corp.', 'I.B.M.').

• Techniques include: Rule-based matching, fuzzy string matching, and machine learning models that compare multiple attributes.
• Critical for: Building a Single Source of Truth (SSOT) in master data management and ensuring accurate node identity in knowledge graphs.

Standardization enforces consistent formats and units across a dataset (e.g., converting 'USA', 'U.S.A.', 'United States' to a single canonical form 'US'). Normalization scales numerical data to a standard range (like 0 to 1) or transforms it to follow a normal distribution.

• Examples: Date formats (MM/DD/YYYY), phone numbers, currency symbols, and categorical value encoding.
• Purpose: Eliminates syntactic heterogeneity, enabling reliable joins, filters, and algorithmic processing. This is a prerequisite for effective semantic integration.

This technique enforces data quality rules and business logic constraints at the point of entry or during pipeline processing. It ensures data adheres to defined schemas and logical boundaries.

• Rule types: Format validation (email regex), range checks (age > 0), referential integrity (foreign key exists), and cross-field logic (end_date > start_date).
• Implementation: Often codified in data contracts between producers and consumers. Failed validations trigger alerts for data observability platforms.

Addresses gaps (NULLs) and logical contradictions in data. Strategies must be chosen based on the data's nature and the downstream use case.

• For missing values: Deletion (if sparse), imputation (mean/median/mode), or prediction using ML models.
• For inconsistencies: Rule-based correction (e.g., if 'City=New York' and 'Country=UK', correct country to 'US') or flagging for manual review.
• Impact: Directly affects model performance in machine learning; missing relationships cripple graph query completeness.

Decomposes complex, unstructured, or semi-structured fields into atomic, machine-readable components. This is essential for extracting value from raw text logs, JSON blobs, or composite fields.

• Common operations: Splitting full names into first/last, extracting key-value pairs from JSON, parsing timestamps from log strings, and tokenizing text.
• Downstream benefit: Creates clean, discrete attributes ready for indexing in vector databases or for use as properties on knowledge graph entities.

Identifies data points that deviate significantly from the majority of the dataset. The goal is to distinguish between valid extremes (a legitimate $10M transaction) and errors (a temperature of 200°C for a human).

• Methods: Statistical methods (Z-score, IQR), proximity-based models, or unsupervised ML like Isolation Forests.
• Action: True anomalies may be corrected, capped, or removed. This process is a core component of maintaining a robust data quality posture and is critical for training reliable predictive models.

Missing values are empty or null fields in a dataset, which can cause failures in computations, bias in machine learning models, and incomplete query results. Cleansing strategies must be chosen based on the cause of the missingness.

• Deletion: Remove records or columns if missing data is random and minimal.
• Imputation: Fill missing values using statistical methods (mean, median, mode) or predictive models (k-nearest neighbors, regression).
• Flagging: Add a new indicator column (e.g., salary_was_missing) to preserve the information that imputation occurred, which is critical for auditability.

Inconsistent formatting occurs when the same semantic data is represented in different structural forms, preventing reliable grouping, sorting, and joining. This is a primary target for data standardization.

• Date Formats: MM/DD/YYYY, DD-MM-YYYY, YYYYMMDD.
• Text Casing: New York, NEW YORK, new york.
• Categorical Values: USA, U.S.A., United States, America.
• Process: Apply transformation rules (parsers, regular expressions, lookup tables) to enforce a single, canonical format across the entire dataset.

Syntax errors are structural violations that prevent data from being correctly parsed or loaded, often originating from manual entry, file corruption, or incorrect export settings.

• Malformed Data: Extra delimiters in a CSV file, mismatched quotes, or invalid JSON/XML.
• Data Type Mismatches: A numeric field containing the text "N/A" or "123-456".
• Cleansing Action: Use schema validation tools to detect violations. Correct by repairing the file structure, applying data type casting with error handling, or rejecting the offending records with detailed logs for reprocessing.

These are values that fall outside predefined logical, business, or physical boundaries, indicating measurement error, entry mistakes, or system faults.

• Logical Violations: A person's age of -5 or 150.
• Business Rule Violations: A sale date that occurs before the product launch date.
• Referential Integrity Violations: An order.customer_id that does not exist in the customer table.
• Cleansing: Enforce data validation rules using range checks, whitelists of permissible values, and foreign key constraints. Invalid records are typically quarantined for manual review.

Semantic errors occur when data is syntactically valid but its meaning is contradictory, ambiguous, or misaligned with business context. Resolving these often requires domain expertise and linkage to a knowledge graph.

• Contradictions: One source lists a product's category as "Electronics" while another lists it as "Home Appliance".
• Ambiguity: The term "Java" could refer to a programming language, an island, or coffee.
• Cleansing Process: Map values to a controlled vocabulary or ontology. Use knowledge graph entities to disambiguate terms by context and establish authoritative, canonical meanings.

A data quality rule is a formal, testable assertion that defines a constraint or condition data must satisfy to be considered fit for its intended purpose. These rules are the executable specifications against which data cleansing processes are run.

• Examples: A rule might enforce that a customer_id field is never null, that a purchase_date is not in the future, or that an email field matches a valid regex pattern.
• Implementation: Rules are often codified in data quality tools or pipeline code (e.g., using frameworks like Great Expectations or dbt tests) to automatically flag violations for cleansing.

Data validation is the process of ensuring data meets predefined quality rules and constraints at the point of entry or during processing. It is a proactive, front-line defense against dirty data, often integrated directly into application forms or ingestion pipelines.

• Syntax Validation: Checks format (e.g., date YYYY-MM-DD).
• Semantic Validation: Checks business logic (e.g., ship_date must be after order_date).
• Referential Integrity: Ensures foreign key relationships are valid. While cleansing corrects existing bad data, validation aims to prevent it from entering the system in the first place.

Entity resolution (also known as record linkage or deduplication) is the process of disambiguating, linking, and merging records that refer to the same real-world entity across different data sources. It is a critical, complex form of data cleansing.

• Challenge: The same customer may appear as "J. Smith, NYC" in one system and "John Smith, New York" in another.
• Process: Uses algorithms to compute similarity scores based on names, addresses, and other attributes to identify matches.
• Outcome: Creates a golden record—a single, cleansed, authoritative view of each entity—which is fundamental for accurate analytics and customer relationship management.

Data harmonization is the process of bringing together data from disparate sources, transforming it into a consistent format, and resolving semantic conflicts to create a unified view. It extends cleansing by addressing structural and meaning-based inconsistencies.

• Schema Mapping: Aligning field names and data types (e.g., mapping TotalPrice to grand_total).
• Semantic Alignment: Resolving differing business definitions (e.g., one system's "Active Customer" vs. another's "Subscribed User").
• Standardization: Applying consistent units, currencies, and code sets (e.g., converting all country references to ISO 3166-1 alpha-2 codes). This process is essential before data can be loaded into a unified repository like a data warehouse or knowledge graph.

Master Data Management (MDM) is a comprehensive method of defining and managing an organization's critical data entities (master data) to provide a single, trusted point of reference. Data cleansing is a core, ongoing operational process within any MDM program.

• Master Data: Includes key entities like Customer, Product, Supplier, and Employee.
• MDM Hub: The central system that stores the golden record for each entity, which is the product of continuous cleansing, matching, and merging from source systems.
• Governance Link: MDM establishes the data stewardship roles and data quality rules that govern how cleansing is performed and who is accountable for the results.

Data observability is the automated monitoring of data pipelines and assets to detect anomalies, lineage breaks, and quality degradation in near real-time. It provides the telemetry that triggers and prioritizes data cleansing efforts.

• Five Pillars: Freshness, Distribution, Volume, Schema, and Lineage.
• Proactive Cleansing: An observability platform might alert that a key column's null rate has spiked from 1% to 40%, triggering an immediate investigation and cleansing workflow.
• Shift-Left: Moves quality detection from downstream reporting failures (days later) to the moment data enters the pipeline, making cleansing more efficient and less costly.