Airtable Builder Certification: The Duplicate Client Onboarding Base

The Duplicate Client Onboarding Base

Your team runs a client onboarding tracker where each client is a single record in a Clients table. A second Projects table links each project back to one client via a linked record field. Recently, two staff members independently imported spreadsheets, and now the Clients table has 412 records where there should be roughly 260. Symptoms: project rollups show inflated counts, some clients appear twice with slightly different name spellings ("Acme Co" vs "Acme Company"), and a few records have the same email but different phone numbers. Projects are already linked to both duplicate copies in some cases. Leadership wants clean rollups by end of week without losing any project links.

1. What is the correct order of remediation, and why does deleting duplicates first create problems?

Read the worked answer

Deleting first is dangerous because linked records in Projects point at specific record IDs. Deleting a duplicate client record silently removes that link from any project attached to it, so project rollups would lose projects rather than consolidate them.

Correct order:

Identify the duplicate clusters using a view grouped by a normalized key (e.g., email, or a formula that lowercases/trims the name). A formula field like LOWER(TRIM({Client Name})) grouped in a view surfaces clusters without altering data.
Re-point links before deletion: for each cluster, decide the surviving record, then update Projects' linked field to reference the survivor. Because linked fields are many-to-one here, this is a per-project edit or a bulk find-and-replace on the linked cell.
Verify counts: compare a COUNTA rollup of projects per survivor against the sum of the cluster's prior counts.
Delete the now-unlinked duplicates.
Add a guardrail: a unique-ish check or an automation that flags new records whose normalized key already exists.

2. The team proposes merging duplicates by simply overwriting one record's fields with the other's values. What data-integrity risk does this introduce, and what would you do instead?

Read the worked answer

Overwriting assumes the two records are equivalent, but the setup states they are not: names differ in spelling, and phone numbers diverge for the same email. Blindly overwriting discards whichever value you did not copy, and you cannot tell after the fact which value was authoritative.

Instead:

Field-by-field reconciliation for the fields that matter (phone, address), choosing the value with the most recent or most trusted source rather than the survivor by default.
Preserve provenance: temporarily keep the losing values in a text field or a separate 'merged from' note so a human can audit before the duplicate is deleted.
Flag conflicts (same email, different phone) for human review rather than auto-resolving; the platform cannot infer which phone is current.
Only after reconciliation do you delete, and only after links are re-pointed.

3. How would you prevent recurrence without blocking legitimate near-duplicate names (e.g., two different 'Acme' entities in different cities)?

Read the worked answer

A hard uniqueness constraint on name would create false positives, since 'Acme Co' in two cities are legitimately distinct. Better guardrails:

Composite key: build a formula combining normalized name + city/region, and use it only as a warning signal, not a block.
Automation on record creation: when a new record's normalized email or composite key matches an existing record, post a comment or set a 'Possible duplicate - review' checkbox rather than preventing the save.
Import discipline: require imports to go through a staging table where a duplicate-check view is reviewed before records are linked into Projects. This catches the real-world failure (two people importing overlapping lists) while allowing genuinely distinct same-name clients.