sql-transformations

SQL Transformations

Essential SQL patterns for ETL, data cleaning, and transformation workflows.

Core Principles

1. Clean at the source - Fix data quality issues as early as possible
2. Idempotent transformations - Same input always produces same output
3. Document assumptions - Make data quality rules explicit
4. Handle NULLs explicitly - Never assume NULL behavior
5. Test transformations - Validate with sample data before production

Data Cleaning Strategies

WITH
    valid_records AS (
        SELECT * FROM raw_customer_data
        WHERE email IS NOT NULL AND email LIKE '%@%'
    ),
    standardized AS (
        SELECT
            customer_id,
            TRIM(UPPER(email)) AS email,
            CASE
                WHEN UPPER(country) IN ('US', 'USA', 'UNITED STATES') THEN 'United States'
                WHEN UPPER(country) IN ('UK', 'UNITED KINGDOM', 'GB') THEN 'United Kingdom'
                ELSE INITCAP(TRIM(country))
            END AS country,
            REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS phone_digits_only
        FROM valid_records
    )
SELECT * FROM standardized;

See data-cleaning-patterns.md for:

• Validation rules and quality checks
• Standardization patterns and multi-stage pipelines
• Outlier detection and data quality scoring

Type Conversions

SELECT
    -- Safe string to numeric
    CASE
        WHEN price_str ~ '^[0-9]+\.?[0-9]*$' THEN price_str::NUMERIC
        ELSE NULL
    END AS price,
    -- Safe string to date
    CASE
        WHEN date_str ~ '^\d{4}-\d{2}-\d{2}$' THEN date_str::DATE
        ELSE NULL
    END AS order_date,
    -- JSON extraction
    (json_data->>'customer_id')::INTEGER AS customer_id
FROM staging_data;

See type-conversions.md for:

• Safe type casting patterns across databases
• JSON and array handling
• Cross-database conversion syntax

String Operations

SELECT
    INITCAP(product_name) AS product_title_case,
    LPAD(id::TEXT, 10, '0') AS id_padded,
    SPLIT_PART(email, '@', 2) AS email_domain,
    REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS phone_numbers_only,
    CONCAT_WS(', ', city, state, country) AS location
FROM products;