Duplicate Detection at Scale AI Prompt

Implement scalable duplicate detection for a large table ({{table_size}} rows) with a compound natural key.

Natural key: {{natural_key_columns}}
Table: {{table_name}}

1. Exact duplicate detection (fast):
   ```sql
   SELECT {{natural_key_columns}}, COUNT(*) AS cnt
   FROM {{table_name}}
   GROUP BY {{natural_key_columns}}
   HAVING COUNT(*) > 1
   ORDER BY cnt DESC
   LIMIT 100
   ```
   - Run this query and report: total duplicate groups, total excess rows, and sample examples

2. Near-duplicate detection for string keys:
   - Phonetic matching: Soundex or Metaphone for name fields
   - MinHash LSH for large-scale fuzzy deduplication (scales to billions of rows)
   - Blocking: reduce comparison space by only comparing within the same first-letter group or zip code

3. Deduplication strategy:
   - Deterministic: define a priority rule for which record to keep (most recent, most complete, specific source)
   - Write a SELECT DISTINCT-equivalent using ROW_NUMBER() to select the canonical record:
   ```sql
   WITH ranked AS (
     SELECT *,
       ROW_NUMBER() OVER (
         PARTITION BY {{natural_key_columns}}
         ORDER BY {{priority_column}} DESC
       ) AS rn
     FROM {{table_name}}
   )
   SELECT * FROM ranked WHERE rn = 1
   ```

4. Prevention (upstream fix):
   - Add a UNIQUE constraint if the warehouse supports it
   - Add a pre-load duplicate check that blocks the load if duplicates are detected in the incoming batch
   - Instrument the source system write path to prevent duplicates at origin

5. Monitoring:
   - Add a daily duplicate count metric to the DQ dashboard
   - Alert if duplicate count increases day-over-day

Return: exact duplicate query, ROW_NUMBER deduplication query, near-duplicate detection approach, and prevention implementation.