duplicate_detection.sql

-- ============================================================
-- Script  : duplicate_detection.sql
-- Purpose : Identify duplicate records in source and target
-- Author  : Arunkumar Aravindhakshan
-- Tools   : Snowflake / AWS Redshift / Oracle
-- ============================================================

-- -------------------------------------------------------
-- 1. SIMPLE DUPLICATE CHECK ON PRIMARY KEY
-- -------------------------------------------------------
SELECT
    claim_id,
    COUNT(*) AS duplicate_count
FROM <target_schema>.claims_target
GROUP BY claim_id
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;


-- -------------------------------------------------------
-- 2. FULL DUPLICATE ROWS (ALL COLUMNS IDENTICAL)
-- -------------------------------------------------------
SELECT
    claim_id,
    patient_id,
    claim_amount,
    admission_date,
    COUNT(*) AS row_count
FROM <target_schema>.claims_target
GROUP BY
    claim_id,
    patient_id,
    claim_amount,
    admission_date
HAVING COUNT(*) > 1;


-- -------------------------------------------------------
-- 3. DUPLICATE DETECTION USING WINDOW FUNCTION
--    Tags each duplicate with a row number — keeps row 1, flags rest
-- -------------------------------------------------------
WITH ranked_records AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY claim_id
            ORDER BY load_timestamp DESC
        ) AS row_num
    FROM <target_schema>.claims_target
)
SELECT *
FROM ranked_records
WHERE row_num > 1;


-- -------------------------------------------------------
-- 4. DUPLICATE SUMMARY REPORT
-- -------------------------------------------------------
SELECT
    'Total Records'         AS metric,
    COUNT(*)                AS value
FROM <target_schema>.claims_target

UNION ALL

SELECT
    'Unique claim_ids',
    COUNT(DISTINCT claim_id)
FROM <target_schema>.claims_target

UNION ALL

SELECT
    'Duplicate Records',
    COUNT(*) - COUNT(DISTINCT claim_id)
FROM <target_schema>.claims_target;