Data Transformation Strategies for Clean and Consistent Data

Introduction

In data processing, raw data from source systems is often inconsistent, messy, or lacks standardization. Data transformation techniques help to clean, normalize, and harmonize this data before it is used for analysis or loaded into structured schemas like a data warehouse. This blog explores multiple techniques used in SQL-based data transformation to convert raw data into clean, meaningful information.

1. Removing Unnecessary Whitespaces

Purpose: Trim leading and trailing whitespace to maintain consistency in textual fields.

Example:

Data Before:

SQL Transformation:

SELECT customer_id, 
       TRIM(first_name) AS first_name, 
       TRIM(last_name) AS last_name
FROM customers;

Data After:

2. Handling Missing or Invalid Data

Purpose: Replace null or incorrect values with default values or meaningful alternatives.

Example:

Data Before:

SQL Transformation:

SELECT customer_id, 
       COALESCE(email, 'unknown@xyz.com') AS email
FROM customers;

Data After:

3. Deduplication

Purpose: Identify and remove duplicate records while retaining the most recent or relevant entry.

Example:

Data Before:

SQL Transformation:

WITH RankedCustomers AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM customers
)
SELECT customer_id, first_name, last_name, created_at
FROM RankedCustomers
WHERE rn = 1;

Data After:

4. Normalization and Harmonization

Purpose: Standardize categorical values to ensure consistency and accuracy.

Example:

Data Before:

SQL Transformation:

SELECT 
    CASE marital_status 
        WHEN 'S' THEN 'Single' 
        WHEN 'M' THEN 'Married' 
        ELSE 'n/a' END AS marital_status,
    CASE gender 
        WHEN 'F' THEN 'Female' 
        WHEN 'M' THEN 'Male' 
        ELSE 'n/a' END AS gender,
    CASE product_line 
        WHEN 'M' THEN 'Mountain' 
        WHEN 'R' THEN 'Road' 
        WHEN 'S' THEN 'Other Sales' 
        WHEN 'T' THEN 'Touring' 
        ELSE 'n/a' END AS product_line,
    CASE country 
        WHEN 'DE' THEN 'Germany' 
        WHEN 'US' THEN 'United States' 
        WHEN 'USA' THEN 'United States' 
        ELSE 'n/a' END AS country
FROM products;

Data After:

5. Deriving Values

Purpose: Compute missing or dependent values based on existing fields.

Example:

Data Before:

SQL Transformation:

SELECT order_id, 
       quantity, 
       COALESCE(unit_price, total_price / quantity) AS unit_price, 
       COALESCE(total_price, quantity * unit_price) AS total_price
FROM orders;

Data After:

6. Conditional Mapping Using CASE WHEN

6a. Standardizing Categorical Data

Purpose: Convert inconsistent category formats to a standard format.

Data Before:

SQL Transformation:

SELECT product_code, 
       REPLACE(category_code, '-', '_') AS category_code
FROM products;

Data After:

6b. Handling Date Transformations

Purpose: Format dates from a compact string to a standard date format.

Data Before:

SQL Transformation:

SELECT order_date, 
       TO_DATE(order_date, 'YYYYMMDD') AS formatted_order_date
FROM orders;

Data After:

6c. Calculating Order Status

Purpose: Derive the shipment status based on shipment and delivery dates.

Data Before:

SQL Transformation:

SELECT shipped_date, delivered_date, 
       CASE 
           WHEN shipped_date IS NULL THEN 'Pending Shipment' 
           WHEN delivered_date IS NOT NULL THEN 'Delivered' 
           ELSE 'In Transit' 
       END AS status
FROM shipments;

Data After:

7. Extracting Year and Month from Dates

Purpose: Isolate year and month components from a date for time-series analysis.

Data Before:

SQL Transformation:

SELECT order_date, 
       EXTRACT(YEAR FROM order_date) AS order_year, 
       EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

Data After:

8. Aggregating Data

Purpose: Summarize data by grouping and applying aggregate functions like SUM, AVG, COUNT, etc.

Data Before:

SQL Transformation:

SELECT product_id, 
       SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_id;

Data After:

9. Pivoting Data

Purpose: Transform rows into columns to create a pivot table.

Data Before:

SQL Transformation:

SELECT product_id,
       SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan_sales,
       SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb_sales
FROM sales
GROUP BY product_id;

Data After:

10. Unpivoting Data

Purpose: Transform columns into rows to normalize a pivot table.

Data Before:

SQL Transformation:

SELECT product_id, 
       'Jan' AS month, Jan_sales AS sales
FROM sales
UNION ALL
SELECT product_id, 
       'Feb' AS month, Feb_sales AS sales
FROM sales;

Data After:

11. Joining Tables

Purpose: Combine data from multiple tables based on a related column.

Data Before:

Orders Table:

Customers Table:

SQL Transformation:

SELECT orders.order_id, 
       customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

Data After:

12. Filtering Data

Purpose: Extract specific rows based on a condition.

Data Before:

SQL Transformation:

SELECT order_id, order_date, total_amount
FROM orders
WHERE total_amount > 150.00;

Data After:

13. Calculating Running Totals

Purpose: Compute cumulative totals over a specified order.

Data Before:

SQL Transformation:

SELECT order_id, order_date, total_amount,
       SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Data After:

14. Calculating Moving Averages

Purpose: Compute the average of a specified number of preceding rows.

Data Before:

SQL Transformation:

SELECT order_id, order_date, total_amount,
       AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

Data After:

15. Data Type Conversion

Purpose: Convert data from one type to another for consistency and compatibility.

Data Before:

SQL Transformation:

SELECT order_id, 
       TO_DATE(order_date, 'YYYY-MM-DD') AS order_date, 
       CAST(total_amount AS DECIMAL(10, 2)) AS total_amount
FROM orders;

Data After:

16. Removing Duplicates

Purpose: Eliminate duplicate rows from a dataset to ensure data integrity.

Data Before:

SQL Transformation:

SELECT DISTINCT customer_id, email
FROM customers;

Data After:

17. Calculating Percentages

Purpose: Compute the percentage of a value relative to a total.

Data Before:

SQL Transformation:

SELECT product_id, 
       sales, 
       (sales / SUM(sales) OVER ()) * 100 AS sales_percentage
FROM products;

Data After:

18. String Concatenation

Purpose: Combine multiple string fields into a single field.

Data Before:

SQL Transformation:

SELECT first_name, 
       last_name, 
       CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

Data After:

19. Conditional Aggregation

Purpose: Aggregate data based on a condition.

Data Before:

SQL Transformation:

SELECT product_id, 
       SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS north_sales,
       SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS south_sales
FROM sales
GROUP BY product_id;

Data After:

20. Data Masking

Purpose: Protect sensitive data by masking it.

Data Before:

SQL Transformation:

SELECT customer_id, 
       CONCAT(SUBSTRING(email, 1, 2), '*****', SUBSTRING(email, CHARINDEX('@', email), LEN(email))) AS masked_email
FROM customers;

Data After:

21. Calculating Differences

Purpose: Compute the difference between values in consecutive rows.

Data Before:

SQL Transformation:

SELECT order_id, 
       order_date, 
       total_amount, 
       total_amount - LAG(total_amount) OVER (ORDER BY order_date) AS amount_difference
FROM orders;

Data After:

22. Data Binning

Purpose: Group continuous data into bins or intervals.

Data Before:

SQL Transformation:

SELECT order_id, 
       total_amount, 
       CASE 
           WHEN total_amount < 150 THEN 'Low' 
           WHEN total_amount BETWEEN 150 AND 250 THEN 'Medium' 
           ELSE 'High' 
       END AS amount_bin
FROM orders;

Data After:

23. Handling Outliers

Purpose: Identify and handle outliers in the data.

Data Before:

SQL Transformation:

SELECT order_id, 
       total_amount, 
       CASE 
           WHEN total_amount > 3 * (SELECT AVG(total_amount) FROM orders) THEN 'Outlier' 
           ELSE 'Normal' 
       END AS amount_status
FROM orders;

Data After:

Conclusion

By applying these transformation techniques, ETL pipelines can ensure that raw data is clean, consistent, and ready for analytics. Whether dealing with text normalization, handling missing values, deduplication, or deriving computed fields, these methods help maintain high-quality datasets essential for business insights.