Duplicate Column Identification via Statistical and Semantic Analysis

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Current systems face challenges in identifying and managing duplicate columns in data sets, which affects the quality, cost, time, and resources required for data processing and analysis, as existing tools are inefficient in comparing values across columns.

Innovation Solution

A system and method utilizing statistical, semantic, and machine learning techniques to preprocess data, derive scores for column pairs, perform row-level analysis using fuzzy logic, semantic analysis, and lookup tables to identify duplicate columns, reducing the need for manual comparison and improving data quality.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If duplicate data is stored on arrival without identification, then data velocity is maintained and no overhead is added, but the quality of insights deteriorates due to increased errors from processing duplicate data

Engineering Contradiction:

Improvedata velocityVSAvoidquality of insights

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

The system performs preliminary identification of duplicate columns before data processing begins. By calculating statistical scores and performing row-level analysis upfront, the system prepares a deduplication plan that eliminates duplicates before they can affect insight generation, thus maintaining both data velocity and insight quality

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent introduces an intermediary deduplication module that sits between data ingestion and processing. This module uses statistical analysis and machine learning to identify duplicate columns without blocking data flow, acting as a mediator that filters duplicates while maintaining data velocity

Inventive Principle:

Principle #24Intermediary (Mediator)

2Reliability

If duplicate data is identified and eliminated before storing, then data quality is improved by removing redundancy, but data velocity deteriorates due to additional overhead in identifying and eliminating duplicates

Engineering Contradiction:

Improvedata qualityVSAvoiddata velocity

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system applies partial deduplication by focusing only on identifying duplicate columns rather than eliminating all duplicate data. By calculating statistical scores and selecting only columns with scores above a threshold, the system performs a targeted partial analysis that improves data quality without the full overhead of complete deduplication

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The patent transforms the deduplication problem by changing parameters from exact matching to statistical scoring. By using correlation coefficients and similarity scores with configurable thresholds, the system adapts the deduplication intensity to balance quality improvement with velocity maintenance

Inventive Principle:

Principle #35Parameter changes

3Device complexity

If manual comparison of column values is used, then simplicity is maintained, but time consumption and resource usage increase significantly

Engineering Contradiction:

Improvemethod simplicityVSAvoidtime consumption

Core Design Contradiction:

Device complexityVSLoss of time

Solution Approach 1:

The patent replaces manual mechanical comparison with automated computational methods. By substituting human column-by-column inspection with statistical score calculation and machine learning-based row-level analysis, the system eliminates time consumption while maintaining the conceptual simplicity of comparison-based deduplication

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

4Measurement precision

If statistical score threshold is set low to capture more potential duplicates, then measurement precision improves, but the quantity of false positives increases requiring more manual verification

Engineering Contradiction:

Improveduplicate detection accuracyVSAvoidnumber of false positive pairs

Core Design Contradiction:

Measurement precisionVSQuantity of substance

Solution Approach 1:

The system transitions from a single-dimensional threshold approach to a multi-dimensional verification process. By combining statistical scores with semantic analysis, data type matching, and row-level similarity checks, the patent adds multiple dimensions of verification that maintain high detection accuracy while filtering out false positives through layered validation

Inventive Principle:

Principle #17Another dimension (Dimensionality change)

Data Source

PatentUS11561944B2Method and system for identifying duplicate columns using statistical, semantics and machine learning techniques

Publication Date: 2023.01.24 TATA CONSULTANCY SERVICES LTD

AI summary

With the availability of huge amount of data, it has becoming difficult to identify and manage duplicate data, especially when the data is in a plurality of columns. A method and system for identifying duplicate columns using statistical, semantics and machine learning techniques have been provided. The system provides a design framework to compare huge datasets at column level and identify potential duplicate columns, not based on the column title, but based on all of its values. The disclosure has ability to compare values in multiple columns and identify potential duplicate columns wherein comparison of values is not only for the exact match, but for semantic match, smart match, fuzzy match, and match after UOM conversion etc. using Statistical, semantics and machine learning techniques.