1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

-- Comprehensive cleansing transformation examples
 
-- 1. NULL handling with business-appropriate defaults
SELECT 
    customer_id,
    -- Use COALESCE with appropriate defaults
    COALESCE(first_name, 'Unknown') AS first_name,
    COALESCE(email, 'no-email@placeholder.com') AS email,
    -- Numeric NULLs often default to 0
    COALESCE(credit_limit, 0) AS credit_limit,
    -- Dates may use sentinel values
    COALESCE(last_purchase_date, '1900-01-01') AS last_purchase_date
FROM raw_customers;
 
-- 2. Whitespace normalization
SELECT 
    customer_id,
    -- Trim and normalize internal whitespace
    REGEXP_REPLACE(TRIM(company_name), '\s+', ' ', 'g') AS company_name,
    LOWER(TRIM(email)) AS email_normalized
FROM raw_customers;
 
-- 3. Value standardization and mapping
SELECT 
    order_id,
    CASE UPPER(TRIM(status))
        WHEN 'PENDING' THEN 'PENDING'
        WHEN 'P' THEN 'PENDING'
        WHEN 'PEND' THEN 'PENDING'
        WHEN 'SHIPPED' THEN 'SHIPPED'
        WHEN 'S' THEN 'SHIPPED'
        WHEN 'SHIP' THEN 'SHIPPED'
        WHEN 'DELIVERED' THEN 'DELIVERED'
        WHEN 'D' THEN 'DELIVERED'
        WHEN 'CANCELLED' THEN 'CANCELLED'
        WHEN 'C' THEN 'CANCELLED'
        WHEN 'CANCELED' THEN 'CANCELLED'  -- Spelling variation
        ELSE 'UNKNOWN'
    END AS status_standardized
FROM raw_orders;
 
-- 4. Data validation with rejection tracking
INSERT INTO rejected_records (source_table, record_key, rejection_reason, record_data)
SELECT 
    'orders',
    order_id::text,
    CASE 
        WHEN order_total < 0 THEN 'Negative order total'
        WHEN order_date > CURRENT_DATE THEN 'Future order date'
        WHEN customer_id IS NULL THEN 'Missing customer ID'
    END,
    row_to_json(raw_orders.*)::text
FROM raw_orders
WHERE order_total < 0 
   OR order_date > CURRENT_DATE 
   OR customer_id IS NULL;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Date standardization with explicit format handling
SELECT 
    order_id,
    -- Parse various date formats to standard ISO format
    CASE 
        -- MM/DD/YYYY format
        WHEN raw_date ~ '^\d{2}/\d{2}/\d{4}$' THEN
            TO_DATE(raw_date, 'MM/DD/YYYY')
        -- DD-MM-YYYY format
        WHEN raw_date ~ '^\d{2}-\d{2}-\d{4}$' THEN
            TO_DATE(raw_date, 'DD-MM-YYYY')
        -- YYYY-MM-DD (already ISO)
        WHEN raw_date ~ '^\d{4}-\d{2}-\d{2}$' THEN
            TO_DATE(raw_date, 'YYYY-MM-DD')
        -- Month name formats: 'Jan 1, 2024'
        WHEN raw_date ~ '^[A-Za-z]{3} \d{1,2}, \d{4}$' THEN
            TO_DATE(raw_date, 'Mon DD, YYYY')
        ELSE NULL  -- Unknown format - flag for review
    END AS order_date_standardized
FROM raw_orders;
 
-- Phone number standardization to E.164 format
SELECT 
    customer_id,
    -- Strip all non-numeric characters
    '+1' || REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS phone_e164
FROM raw_customers
WHERE LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '', 'g')) = 10;
 
-- Address standardization
SELECT 
    address_id,
    UPPER(TRIM(street_line_1)) AS street_1,
    -- Standardize common abbreviations
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                UPPER(TRIM(street_line_1)),
                '\bSTREET\b|\bSTR\.?\b', 'ST', 'g'
            ),
            '\bAVENUE\b|\bAVE\.?\b', 'AVE', 'g'
        ),
        '\bBOULEVARD\b|\bBLVD\.?\b', 'BLVD', 'g'
    ) AS street_1_standardized,
    UPPER(TRIM(city)) AS city,
    -- State abbreviation mapping
    COALESCE(state_mapping.abbreviation, UPPER(TRIM(state))) AS state_abbr,
    -- ZIP code format: 5 digits or 5+4
    REGEXP_REPLACE(postal_code, '[^0-9]', '', 'g') AS zip_clean
FROM raw_addresses
LEFT JOIN state_mapping ON UPPER(TRIM(raw_addresses.state)) = state_mapping.full_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- Exact match deduplication - keep most recent
WITH ranked_customers AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY 
                LOWER(TRIM(email)),  -- Dedupe key
                LOWER(TRIM(first_name)),
                LOWER(TRIM(last_name))
            ORDER BY 
                last_modified_date DESC,  -- Most recent first
                customer_id ASC           -- Tie-breaker
        ) AS duplicate_rank
    FROM staging_customers
)
SELECT * FROM ranked_customers WHERE duplicate_rank = 1;
 
-- Fuzzy match candidate identification using blocking
-- Step 1: Create blocks and identify candidates
WITH blocked_customers AS (
    SELECT 
        c1.customer_id AS id1,
        c2.customer_id AS id2,
        c1.first_name AS first1,
        c2.first_name AS first2,
        c1.last_name AS last1,
        c2.last_name AS last2,
        c1.email AS email1,
        c2.email AS email2,
        -- Calculate similarity scores
        similarity(c1.last_name, c2.last_name) AS last_name_sim,
        similarity(c1.first_name, c2.first_name) AS first_name_sim,
        similarity(c1.email, c2.email) AS email_sim
    FROM staging_customers c1
    JOIN staging_customers c2 
        ON c1.customer_id < c2.customer_id  -- Avoid self-join and duplicates
        AND LEFT(c1.last_name, 2) = LEFT(c2.last_name, 2)  -- Blocking key
        AND c1.state = c2.state  -- Additional blocking
)
-- Step 2: Score and threshold
SELECT 
    id1, id2,
    first1, first2,
    last1, last2,
    email1, email2,
    -- Weighted composite score
    (last_name_sim * 0.35 + first_name_sim * 0.25 + email_sim * 0.40) AS match_score
FROM blocked_customers
WHERE (last_name_sim * 0.35 + first_name_sim * 0.25 + email_sim * 0.40) > 0.85
ORDER BY match_score DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- Building a conformed Customer dimension from multiple sources
 
-- Step 1: Combine and integrate source customer records
WITH integrated_customers AS (
    -- Match customers across CRM and ERP using email (after standardization)
    SELECT 
        COALESCE(crm.crm_customer_id, 'CRM-' || erp.erp_cust_id) AS customer_natural_key,
        COALESCE(crm.contact_name, erp.company_name) AS customer_name,
        COALESCE(crm.email, erp.email_address) AS email,
        crm.industry_code,
        erp.credit_limit,
        CASE 
            WHEN crm.crm_customer_id IS NOT NULL AND erp.erp_cust_id IS NOT NULL THEN 'BOTH'
            WHEN crm.crm_customer_id IS NOT NULL THEN 'CRM'
            ELSE 'ERP'
        END AS source_system,
        GREATEST(crm.last_modified, erp.last_update) AS last_updated
    FROM staging_crm_customers crm
    FULL OUTER JOIN staging_erp_customers erp 
        ON LOWER(TRIM(crm.email)) = LOWER(TRIM(erp.email_address))
)
 
-- Step 2: Generate surrogate keys and create dimension records
INSERT INTO dim_customer (
    customer_sk,
    customer_natural_key,
    customer_name,
    email,
    industry_code,
    credit_limit,
    source_system,
    effective_date,
    expiration_date,
    is_current
)
SELECT 
    NEXTVAL('customer_sk_seq') AS customer_sk,
    customer_natural_key,
    customer_name,
    email,
    industry_code,
    credit_limit,
    source_system,
    CURRENT_DATE AS effective_date,
    '9999-12-31'::DATE AS expiration_date,
    TRUE AS is_current
FROM integrated_customers
WHERE customer_natural_key NOT IN (
    SELECT customer_natural_key FROM dim_customer WHERE is_current = TRUE
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

-- Comprehensive derived field examples
 
-- Customer lifetime value calculation
SELECT 
    c.customer_id,
    c.customer_name,
    c.registration_date,
    -- Tenure in months
    DATE_PART('year', AGE(CURRENT_DATE, c.registration_date)) * 12 +
    DATE_PART('month', AGE(CURRENT_DATE, c.registration_date)) AS tenure_months,
    
    -- Aggregate purchase metrics
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(o.order_total) AS total_revenue,
    AVG(o.order_total) AS avg_order_value,
    
    -- Days since last purchase
    DATE_PART('day', CURRENT_DATE - MAX(o.order_date)) AS days_since_last_order,
    
    -- Customer lifetime value (simplified: total revenue / tenure in years)
    CASE 
        WHEN DATE_PART('year', AGE(CURRENT_DATE, c.registration_date)) > 0 THEN
            ROUND(SUM(o.order_total) / 
                  DATE_PART('year', AGE(CURRENT_DATE, c.registration_date)), 2)
        ELSE SUM(o.order_total)
    END AS annual_value,
    
    -- Customer segment classification
    CASE 
        WHEN SUM(o.order_total) >= 10000 AND COUNT(o.order_id) >= 20 THEN 'VIP'
        WHEN SUM(o.order_total) >= 5000 OR COUNT(o.order_id) >= 10 THEN 'High Value'
        WHEN SUM(o.order_total) >= 1000 OR COUNT(o.order_id) >= 5 THEN 'Medium Value'
        WHEN COUNT(o.order_id) >= 1 THEN 'Low Value'
        ELSE 'Inactive'
    END AS customer_segment,
    
    -- Churn risk indicator
    CASE 
        WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '365 days' THEN 'HIGH'
        WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '180 days' THEN 'MEDIUM'
        WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days' THEN 'LOW'
        ELSE 'NONE'
    END AS churn_risk
    
FROM dim_customer c
LEFT JOIN fact_orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.registration_date;
 
-- Revenue tier derivation with explicit bucket definitions
SELECT 
    order_id,
    order_total,
    CASE 
        WHEN order_total >= 1000 THEN 'Large (≥$1000)'
        WHEN order_total >= 500 THEN 'Medium ($500-$999)'
        WHEN order_total >= 100 THEN 'Small ($100-$499)'
        WHEN order_total >= 0 THEN 'Micro (<$100)'
        ELSE 'Invalid'
    END AS order_size_tier,
    -- Numeric bucket for sorting
    CASE 
        WHEN order_total >= 1000 THEN 4
        WHEN order_total >= 500 THEN 3
        WHEN order_total >= 100 THEN 2
        WHEN order_total >= 0 THEN 1
        ELSE 0
    END AS order_size_tier_rank
FROM fact_orders;