Data_pipeline.sql

SELECT * From raw_customers LIMIT 5;

-- Identify data quality issues
WITH data_quality_check AS (
    SELECT
        customer_id,
        first_name,
        last_name,
        email,
        phone,
        city,
        age,
        registration_date,
        CASE WHEN email NOT LIKE '%@%' THEN 'Invalid Email' END AS email_issue,
        CASE WHEN age < 0 OR age > 120 THEN 'Invalid Age' END AS age_issue,
        CASE WHEN phone IS NULL THEN 'Missing Phone' END AS phone_issue,
        CASE WHEN city IS NULL THEN 'Missing City' END AS city_issue
    FROM raw_customers
)

SELECT * FROM data_quality_check;

-- Remove duplicates
WITH deduplicated AS (
    SELECT *,
    ROW_NUMBER() OVER(
        PARTITION BY customer_id, email
        ORDER BY registration_date
    ) AS ROW_NUMBER
    FROM raw_customers
)

SELECT * FROM deduplicated WHERE ROW_NUMBER = 1;

-- Standardize text formating
SELECT
    customer_id,
    TRIM(INITCAP(first_name)) AS first_name,
    TRIM(INITCAP(last_name)) AS last_name,
    LOWER(TRIM(email)) AS email,
    TRIM(phone) AS phone,
    TRIM(INITCAP(city)) AS city,
    age,
    registration_date
FROM raw_customers;

-- Handling missing values
SELECT
    customer_id,
    first_name,
    last_name,
    email,
    COALESCE(phone,'Not Provided') AS phone,
    COALESCE(city,'Unkown') AS city,
    COALESCE(age, 0) AS age,
    registration_date
FROM raw_customers;

-- Filter invalid records
SELECT * 
FROM raw_customers
WHERE email LIKE '%@%'
    AND age BETWEEN 0 and 120
    AND email IS NOT NULL;

-- Enrich with calculated fields
SELECT
    customer_id,
    first_name,
    last_name,
    first_name || '' || last_name AS full_name,
    email,
    phone,
    city,
    age,
    CAST(registration_date AS DATE) AS registration_date,
    DATEDIFF(CURRENT_DATE, CAST(registration_date AS DATE)) AS days_since_registration
FROM raw_customers;

-- Add data quaity flags

SELECT
    customer_id,
    first_name || ' ' || last_name AS full_name,
    email,
    COALESCE(phone, 'Not Provided') AS phone,
    COALESCE(city, 'Unkown') AS city,
    age,
    CASE 
        WHEN phone IS NULL OR city IS NULL THEN 'Incomplete'
        ELSE 'Complete'
    END AS data_quality_flag
FROM raw_customers
WHERE email LIKE '%@%' AND age BETWEEN 0 AND 120;