Build a deduplication strategy across all your data sources
Designs end-to-end deduplication: a confidence-ordered match key ladder, implementable normalization rules, per-field merge precedence that never destroys activity history, entry-path blocking so duplicates stop at the door, a right-sized review queue, and merge logging for undo. It handles both failure modes — duplicates and bad merges — instead of trading one for the other.
You are a data quality engineer who has designed deduplication for GTM databases from ten thousand to ten million records, and you hold two truths simultaneously: duplicates quietly poison everything (double-contacted prospects, split deal history, inflated pipeline), and a bad merge is worse than a duplicate, because merges destroy information invisibly and permanently. Design my deduplication strategy. Output: 1. MATCH KEY LADDER — ordered from deterministic to fuzzy: exact email, normalized email (dots, plus-addressing, case), domain plus normalized full name, fuzzy company name plus person match. For each rung: confidence level, and whether it auto-merges or flags for review. 2. NORMALIZATION RULES — the transformations applied before matching: casing, whitespace, legal suffixes stripped from company names (Inc, GmbH, Ltd), domain aliases, common nickname mapping. Written precisely enough to implement as formulas. 3. PRECEDENCE RULES — when two records merge, which source wins per field (CRM wins lifecycle stage, freshest enrichment wins firmographics, oldest record keeps creation date, activity history always unions — never overwrites). 4. SOURCE-SPECIFIC HANDLING — the dedupe check each inbound path gets (CSV imports, Clay pushes, form fills, sequencer syncs) so duplicates are blocked at the door, not swept up later. 5. REVIEW QUEUE — how flagged fuzzy matches surface, who reviews them, expected weekly volume at my scale, and the tie-breaking guidance reviewers follow. 6. UNDO PLAN — what gets logged at every merge so a bad one can be reconstructed, because eventually one will be. Rules: fuzzy matches never auto-merge, no exceptions. Activity and touch history always survive merges. Any rule I can't implement in my actual tools is worthless — tailor the mechanics to the stack from the interview. Before designing, interview me. Ask ONE AT A TIME, waiting for my answer each time: 1. Where do records live, and roughly how many in each system? 2. Through which paths do new records enter (imports, forms, enrichment pushes, syncs)? 3. What duplicate pain have you actually felt — double outreach, split histories, bad reporting? 4. What tools could run the matching — CRM native dedupe, Clay, n8n, a dedicated tool? 5. Who can own the fuzzy-match review queue, and for how many minutes a week? Then produce the strategy, sized to my stack and volume.
How to use it
- 1
Copy the prompt into Claude, ChatGPT, or any LLM.
- 2
Answer the interview with real record counts and entry paths — the strategy differs sharply between 20,000 and 2 million records.
- 3
Implement entry-path blocking first (it stops new debt), then run the ladder over existing records in flag-only mode.
- 4
Review a week of flags to measure false positives before enabling any auto-merge rung.
Best practices
Normalize before you match, always — most 'fuzzy' duplicates are exact duplicates wearing different casing and suffixes.
Never let enrichment tools create records directly; route them through an upsert that checks the ladder first.
Track duplicate rate monthly as a KPI; if it climbs after cleanup, an entry path is leaking and the fix is upstream.
Rehearse the undo plan once on a test pair — discovering your merge log is incomplete during a real incident is too late.
Example: what this looks like in practice
An ops manager at a 55-person staffing firm has 140,000 CRM contacts fed by Clay pushes, two job boards, form fills, and quarterly CSV imports — and reps keep calling candidates a colleague contacted last week under a duplicate record. The interview reveals HubSpot native dedupe plus n8n as available tooling and 45 minutes a week of review capacity. The strategy blocks at entry with an n8n upsert keyed on normalized email, runs a four-rung ladder over the existing base flagging 9,200 candidate pairs, auto-merges only the 6,100 exact-email matches with union-based activity handling, and queues the fuzzy remainder at roughly 80 reviews a week. Six weeks later the double-contact complaints stop, and the monthly duplicate-rate KPI holds under 1% because the door, not the mop, was fixed.
Best fit
This prompt is one gear in a bigger machine. We orchestrate 20+ tools into outbound systems our clients own — and guarantee the results.
Apply for a Pilot Spot → →Frequently asked questions
Match on a ladder of keys from deterministic to fuzzy: exact email first, then normalized email, then domain plus name, with fuzzy company matching last. Auto-merge only the deterministic rungs; flag the rest for human review. Critically, also add dedupe checks at every entry path — cleaning the database without fixing the doors just schedules the next cleanup.
More automation & workflow design prompts
Build a system prompt for an AI cold email reply agent
Interviews you about your offer, booking flow, and voice, then generates a complete production system prompt for an AI reply agent — classification taxonomy, response rules, escalation conditions, and a strict JSON output contract an n8n or Zapier workflow can parse. You get the artifact teams pay thousands to have written, tuned to how you actually talk.
Turn a manual sales process into an automation blueprint
Converts a fuzzy 'we do this by hand every week' description into a structured blueprint with a detectable trigger, labeled steps, edge cases, human checkpoints, and failure alerts. It forces the design decisions people usually discover mid-build, so what you hand to n8n, Zapier, or an ops hire is assembly work, not archaeology.
Architect a Clay table before you burn the credits
Designs the Clay table before you build it: sources, column order, run conditions, waterfalls, outputs, and a credit estimate. Column ordering and gating are where most teams silently overspend, so the prompt enforces filter-first architecture and makes every paid column defend its run condition — typically cutting credit burn 40-70% versus enrich-everything builds.