BaseBrainers | Airtable Data Quality: Deduplication, Validation Rules, and Bases That Stay Clean

Every Airtable base decays. Six months after launch you have three records for the same customer, a Status field holding Done, done, and Complete, phone numbers in four formats, and a rollup that is wrong because two line items point at a duplicate parent. Nobody did anything malicious; the base simply had no way to say no.

Airtable has no UNIQUE constraint, no NOT NULL, and no referential integrity beyond linked records. So data quality has to be designed in: part field choice, part validation formulas, part automations that flag problems, and part a weekly habit. This tutorial covers all four, with patterns you can copy into a base today.

Step 1: prevent what you can with field types

The cheapest validation is the one the editor enforces before a bad value ever exists.

Single select instead of text for any value from a known list. Free-text Status fields are the single biggest source of dirty data in Airtable bases.
Linked record instead of a text name. If Company is a text field you will get "Acme", "Acme Ltd", and "acme ltd." If it links to a Companies table, the picker offers the existing record first.
Date fields with a fixed time zone. Mixed-format text dates cannot be filtered or sorted reliably.
Number fields with precision set, so 1.5 and 1.50 are the same value.
Checkbox instead of a Yes/No text field.
Percent and currency types rather than a bare number plus a convention in someone's head.

Retrofitting is possible: create the new typed field alongside the old one, convert with a formula or a script, spot-check, then archive the old field. Do it additively, as in our change management workflow.

Step 2: build a normalised match key

Deduplication needs a comparable value, not the raw one. Add a formula field, call it Match key, that strips everything that varies without meaning:

LOWER(
  REGEX_REPLACE(
    TRIM(CONCATENATE({Company}, "|", {Email})),
    "[^a-zA-Z0-9|@.]",
    ""
  )
)

For people, the lowercased email is usually the key. For companies, the email domain is far more reliable than the typed company name:

IF(
  FIND("@", {Email}),
  LOWER(MID({Email}, FIND("@", {Email}) + 1, 255)),
  ""
)

For addresses or product names, normalise aggressively: lowercase, remove punctuation, collapse whitespace, strip suffixes like ltd, inc, llc.

Step 3: detect the duplicates

With a Match key in place there are three practical detection routes.

Group by the key. Group a grid view by Match key and sort the groups by count descending. Any group with more than one record is a duplicate cluster. Free, instant, and good enough for bases under a few thousand records.

A count rollup via a link. For a permanent flag, create a Keys table with one record per distinct match key, link each record to it, and roll the count back down. Duplicate? then becomes IF({Key count} > 1, "⚠️ Duplicate", ""). Filter a view on it and duplicates surface themselves as they appear.

A script. For larger tables a Run script automation is the reliable option. Pull the records, bucket them by key, and write the flag on every record in a bucket larger than one:

const table = base.getTable("Contacts");
const query = await table.selectRecordsAsync({ fields: ["Match key", "Duplicate?"] });

const buckets = new Map();
for (const record of query.records) {
    const key = record.getCellValueAsString("Match key").trim();
    if (!key) continue;
    if (!buckets.has(key)) buckets.set(key, []);
    buckets.get(key).push(record);
}

const updates = [];
for (const [, records] of buckets) {
    const isDupe = records.length > 1;
    for (const record of records) {
        if (record.getCellValue("Duplicate?") !== isDupe) {
            updates.push({ id: record.id, fields: { "Duplicate?": isDupe } });
        }
    }
}

for (let i = 0; i < updates.length; i += 50) {
    await table.updateRecordsAsync(updates.slice(i, i + 50));
}

The 50-record batching is not optional; see our scripting guide for why.

Fuzzy matches such as "Jon Smith" versus "John Smith" are not worth chasing inside Airtable. Flag exact key matches automatically, and let a human review a "possible duplicates" view built on a looser key, surname plus company for instance.

Step 4: merge duplicates without orphaning links

Deleting the duplicate is the last step, not the first. Before it:

Pick a survivor. Usually the oldest record, or the one with the most linked records. Make it explicit with a Survivor checkbox set by the reviewer.
Repoint the links. Anything linked to the loser (deals, tasks, invoices, time entries) must be relinked to the survivor or it becomes an orphan. A script that reads the loser's linked record IDs and appends them to the survivor's link field handles this.
Fill the gaps. Copy across any field the survivor is missing and the loser has.
Archive, do not delete, on the first pass. Set Status = Merged plus a Merged into link, filter losers out of every view, and delete for real a month later. Trash recovery has a limited window and a bad merge script destroys history fast. Take a snapshot before any bulk merge.

Step 5: validation rules without constraints

Airtable will not stop a bad value, so make bad values loud. The pattern is one Data issues formula field per table that concatenates every rule the record breaks:

TRIM(
  IF({Email} = "", "Missing email. ") &
  IF(AND({Email} != "", NOT(FIND("@", {Email}))), "Email malformed. ") &
  IF(AND({Stage} = "Won", {Amount} = 0), "Won deal with no amount. ") &
  IF(AND({Close date}, {Close date} < {Created}), "Close date before created. ") &
  IF(AND({Stage} = "Won", {Owner} = BLANK()), "No owner. ")
)

Then:

Build a view ⚠️ Data issues filtered to Data issues is not empty.
Show the count on an interface dashboard so it is visible to the team, not only to the admin.
Put the field on the record detail layout so whoever is editing sees the problem while they are already in there.

This beats a rules document nobody reads, and it costs nothing to run.

Required fields on forms

Airtable forms can mark fields required, and that is your best enforcement point for externally submitted data. Combine required fields with conditional form logic, so that Budget only appears, and is only required, when Project type is New build. Anything created via API or automation bypasses forms entirely, which is exactly why the Data issues field stays as the backstop.

Step 6: stop the inflow

Detection without prevention means cleaning forever. The three highest-value inflow fixes:

Dedupe at creation. In the automation that creates records from a form or webhook, add a Find records step that searches on the match key first. If it finds one, update that record instead of creating a new one. This single change eliminates most duplicates.
Convert text to links during import. In any migration, convert entity columns to linked records before the base goes live, not after.
Control who can create. Editors can create anything anywhere. For reference tables (Companies, Products, Cost codes), restrict creation to a small group and let everyone else request additions through a form.

Step 7: make it a habit

Data quality is an operational routine, not a project:

Weekly: one named person owns the ⚠️ Data issues view and clears it. Ten minutes.
Weekly: review the duplicate-flag view and merge or dismiss each cluster.
Monthly: re-check reference tables for new select options that duplicate old ones, and companies created outside the request flow.
Quarterly: audit the rules themselves. A rule that is always violated is usually a wrong rule, not a careless team.

Log the numbers in a small Data health table: issues open, duplicates found, records merged. A trend line is what persuades a stakeholder to fund the structural fix.

What not to do

Do not run a bulk merge script without a snapshot and a dry run that only logs what it would change.
Do not enforce rules that block real work. A record half-filled at 4pm is normal; fire the rule on stage transitions, not on creation.
Do not treat "delete the duplicates" as the project. Repointing the links is the work.
Do not scatter data-quality logic across three places. One Data issues field, one detection method, one owner.

BaseBrainers cleans up, de-duplicates, and re-architects bases that have drifted, often as part of an Airtable project rescue or a data migration. If your rollups no longer match reality, tell us what you are seeing.