Combating Data Inconsistencies with SQL: A Data Science Perspective

Data is the lifeblood of modern organizations. It fuels analytics, enables data-driven decisions, and provides a foundation for machine learning. However, the adage "garbage in, garbage out" holds true. Poor quality data leads to faulty insights, wasted time, and lost opportunities.

According to Gartner, the average financial impact of poor data quality on organizations is $9.7 million per year. IBM estimates that in the US alone, businesses lose $3.1 trillion annually due to poor data quality. Data inconsistencies are a major contributor to this problem.

As data professionals, it‘s our responsibility to proactively identify and resolve inconsistencies to ensure reliable, trustworthy data assets. SQL is a powerful tool in this pursuit. Let‘s explore how it can help combat common inconsistencies.

The Many Faces of Inconsistency

Data inconsistencies come in many forms, each with its own challenges:

Missing Values
Null or blank values where data should exist. A 2017 Kaggle survey of data professionals found that 49% frequently encounter missing data.

Duplicates
Repeated records that should be unique. Duplicates are common, with one study finding an average duplication rate of 17% across databases.

Formatting Issues
Inconsistent conventions for storing dates, numbers, strings. A 2019 Precisely survey found that 41% of organizations have over 50 variations of basic valid data.

Domain Violations
Values that fall outside expected ranges, like negative ages. IBM estimates that up to 30% of records contain some form of domain violation.

Referential Integrity Failures
Orphaned records and invalid relationships across tables. Studies show that 71% of data quality issues are related to referential integrity.

Gartner has developed a useful framework for assessing data along multiple quality dimensions:

Source: Gartner

SQL can help assess data against each of these dimensions to quantify inconsistencies. From there we can take targeted action to resolve issues.

Shining a Light on Inconsistency with SQL

SQL provides a robust toolset for examining data quality at scale. By leveraging its querying, aggregation, and transformation capabilities, we can efficiently audit even the largest datasets. Some key techniques include:

Aggregations for Anomaly Detection
Grouping and aggregating data is a powerful way to bubble up unusual values and patterns. Combining GROUP BY and HAVING clauses lets us find outliers and unexpected distributions.

For example, this query identifies products with abnormally long names:

SELECT product_id, AVG(LENGTH(name)) AS avg_name_length
FROM products
GROUP BY product_id
HAVING AVG(LENGTH(name)) > 100;

Window Functions for Duplication and Validity
Window functions allow us to perform calculations across related rows, making them instrumental for spotting duplicates and invalid values within a table.

For instance, we can use LAG() and LEAD() to compare values between adjacent rows and flag non-sequential date ranges:

SELECT 
  start_date,
  end_date,
  CASE 
    WHEN start_date > LAG(end_date) OVER (ORDER BY start_date)
      THEN ‘Invalid Range Gap‘
    WHEN end_date < LEAD(start_date) OVER (ORDER BY start_date)  
      THEN ‘Invalid Range Overlap‘
  END AS date_range_check  
FROM date_ranges;

Cross-Table Comparisons
Related data often span multiple tables. Comparing aggregations across them can reveal referential inconsistencies.

For example, we can validate that status counts in an orders table match those of the related order_items:

WITH order_status AS (
  SELECT status, COUNT(*) AS orders 
  FROM orders
  GROUP BY status
),
item_status AS (
  SELECT 
    orders.status,
    COUNT(*) AS items
  FROM orders
  JOIN order_items ON orders.id = order_items.order_id
  GROUP BY orders.status  
)
SELECT
  order_status.status,
  orders,
  items
FROM order_status 
JOIN item_status
  ON order_status.status = item_status.status
WHERE orders != items;

Any differences between the orders and items columns indicate misalignment between the tables that warrants investigation.

These are just a few examples of how SQL can audit data quality at scale. Regularly running such checks provides visibility into the consistency of your data.

Inconsistency Interventions

With a clear picture of inconsistencies, SQL can help resolve them efficiently. The specific approach depends on the type of problem and the complexity of remediation logic required. Key techniques include:

Conditional Repairs with CASE
Simple inconsistencies can often be resolved using CASE statements to apply conditional transformations.

For example, we can standardize the casing of a country column like so:

UPDATE users
SET country = 
  CASE
    WHEN country IN (‘US‘, ‘USA‘, ‘United States‘) 
      THEN ‘United States‘
    WHEN country IN (‘UK‘, ‘GB‘, ‘Great Britain‘) 
      THEN ‘United Kingdom‘  
    ELSE INITCAP(country)
  END;

Extracting Insights from Unstructured Data
Inconsistent categorization is common with freeform text fields. We can apply string functions and regular expressions to extract structured insights from unstructured data.

For instance, this query parses categories and sentiments from customer feedback:

SELECT 
  CASE
    WHEN feedback ILIKE ‘%service%‘ THEN ‘Service‘
    WHEN feedback ILIKE ‘%price%‘ THEN ‘Price‘  
    WHEN feedback ILIKE ‘%quality%‘ THEN ‘Quality‘
    ELSE ‘Other‘
  END AS category,
  CASE 
    WHEN feedback ILIKE ‘%great%‘ 
      OR feedback ILIKE ‘%excellent%‘
      OR feedback ILIKE ‘%love%‘
      THEN ‘Positive‘
    WHEN feedback ILIKE ‘%bad%‘
      OR feedback ILIKE ‘%terrible%‘ 
      OR feedback ILIKE ‘%hate%‘
      THEN ‘Negative‘
    ELSE ‘Neutral‘  
  END AS sentiment
FROM customer_feedback;

Fuzzy Matching for Deduplication
To deduplicate records, we often need to match similar but not identical values. Fuzzy string matching algorithms quantify the similarity between values.

This example uses the Levenshtein distance algorithm to find potential duplicate company names within a certain similarity threshold:

SELECT 
  c1.name AS company_1,
  c2.name AS company_2,
  LEVENSHTEIN(c1.name, c2.name) AS distance 
FROM companies c1
JOIN companies c2 ON c1.id < c2.id
WHERE LEVENSHTEIN(c1.name, c2.name) <= 2

The results can then guide a deduplication effort, either manually or through automated merging.

Schema Validation
As a proactive inconsistency check, we can validate tables against an expected schema. Deviations from the defined schema indicate potential issues.

With the help of system tables, we can compare an actual table schema to the expected:

WITH expected_schema (column_name, data_type, is_nullable) AS (
  VALUES 
    (‘id‘, ‘integer‘, ‘NO‘),
    (‘name‘, ‘varchar(255)‘, ‘NO‘),
    (‘email‘, ‘varchar(255)‘, ‘YES‘) 
)
SELECT 
  e.column_name AS expected_column,
  e.data_type AS expected_type,
  e.is_nullable AS expected_nullable,
  a.column_name AS actual_column,
  a.data_type AS actual_type,
  a.is_nullable AS actual_nullable
FROM expected_schema e
FULL OUTER JOIN information_schema.columns a
  ON a.table_name = ‘users‘ 
  AND e.column_name = a.column_name
WHERE 
  a.column_name IS NULL
  OR e.column_name IS NULL
  OR a.data_type != e.data_type
  OR a.is_nullable != e.is_nullable;

Any differences between the expected_* and actual_* columns reveal inconsistencies between the expected and actual schema that should be investigated.

Teaching Machines to Spot Trouble

As data grows in volume and complexity, machine learning (ML) becomes an increasingly valuable tool for inconsistency detection. By training ML models on historical data patterns, we can identify records likely to contain issues.

Consider training a classifier to predict whether a record is valid based on past examples. With a labeled dataset, we can train a model using SQL:

-- Create a view of user records with labeled validity
CREATE VIEW user_validity AS
SELECT
  *,
  CASE 
    WHEN email NOT LIKE ‘%@%.%‘ THEN 0
    WHEN phone NOT LIKE ‘+%‘ THEN 0
    WHEN LEN(name) = 0 THEN 0  
    ELSE 1
  END AS is_valid
FROM users;

-- Train a logistic regression model to predict validity
SELECT 
  PREDICT_LOGISTIC_REG(
    is_valid, 
    ARRAY[LENGTH(name), 
          LENGTH(email), 
          SIGN(STRPOS(phone, ‘+‘))]
  ) AS predicted_valid  
FROM user_validity;

The model learns patterns of invalidity, like short names, malformed emails, and invalid phone numbers. We can then apply this model to new data to predict which records are likely to be problematic.

ML can also spot anomalies and outliers that rule-based approaches miss. Techniques like clustering and principal component analysis (PCA) identify records that deviate from the norm.

Here we use k-means clustering to group users by age and income, then find the smallest clusters which may represent data quality issues:

WITH clustered_users AS (
  SELECT 
    *, 
    ML.KMEANS(ARRAY[age, income], 5) OVER () AS cluster_id
  FROM users  
),
cluster_sizes AS (
  SELECT
    cluster_id,
    COUNT(*) AS size
  FROM clustered_users
  GROUP BY cluster_id
),
anomalous_clusters AS (  
  SELECT cluster_id 
  FROM cluster_sizes
  ORDER BY size
  LIMIT 2
)
SELECT *
FROM clustered_users 
WHERE cluster_id IN (SELECT * FROM anomalous_clusters);

The smallest clusters likely contain outliers or data entry issues that warrant further examination.

Conclusion

Data is a critical asset, but inconsistencies can make it a liability. As data professionals, we must vigilantly monitor and enforce data quality. SQL and data science techniques provide powerful tools for this mission.

By leveraging aggregations, window functions, and cross-table checks, we can efficiently identify a wide range of inconsistencies. From there, SQL enables surgical repairs via conditional updates, regular expressions, and fuzzy matching.

Data science takes inconsistency detection to the next level. Machine learning models can learn complex patterns to predict low-quality records and surface subtle anomalies. Incorporating ML into our data quality processes will be increasingly important as data scales.

However, detection and repair alone are insufficient. We must also invest in proactive data quality measures like schema validation, pipeline testing, and data entry constraints. By coupling SQL and data science with rigorous engineering practices, we can build truly trustworthy and reliable data assets.

As W. Edwards Deming, the pioneer of statistical quality control, once said: "Without data, you‘re just another person with an opinion." Let‘s use every tool in our arsenal to ensure our data is an unimpeachable foundation for insight and action.