Airtable Data Quality Scan - Find Duplicates

Scan any Airtable base for the problems inside the cells: duplicate records, blank primary values, email and URL fields holding neither, child records with no parent, formulas erroring on specific rows, and records nothing has touched in a year. Every finding carries the record ID and a link that opens that row in Airtable, so what you get back is a cleanup worklist rather than a report.

Press Start with nothing filled in and the Actor scans a bundled sample base: 50 records across three tables, 36 findings, no token, no network, no charge. When you want your own data, paste an Airtable personal access token and a base ID.

• Read-only. Two read scopes, no write scope, nothing in your base is changed.
• Deterministic. Rules and string normalisation, no AI model, no sampling — the same base scanned twice gives the same answer.
• Record-level. Not "this field looks badly configured" but "these two rows are the same company, here are the links".

What does the Airtable Data Quality Scan Actor do?

Airtable will store anything you type. An email field accepts no email on file; a url field accepts ask Dave for the website. Verified against a live base: both are stored verbatim and read back verbatim, with no warning anywhere in the product. A record can exist with a completely blank primary field, which shows up as an unnamed row everywhere it is linked. A formula that is perfectly valid can still fail on specific rows — divide by a zero headcount and that one cell becomes an error while the field keeps reporting itself as healthy.

None of this is visible from Airtable's own interface until someone scrolls to the row it is on. There is no duplicate detection, no validation report, and no way to list every record whose formula is erroring.

This Actor reads the base's schema to learn what each field is declared to be, then reads the records and judges each cell against that declaration. It writes one dataset row per problem — the record ID, the field, the offending value, a plain sentence about what is wrong, what to do about it, and a direct airtable.com link to the row. A second dataset carries one row per table with the counts and how much of the table was read. An HTML page with the same content goes to the key-value store, ready to forward to whoever owns the base.

Which data quality problems does it find?

Problem	What it catches	Severity	Confidence
duplicate	Records whose primary field matches after trimming spaces and ignoring case, with email, URL and phone fields reported as corroborating evidence	High	1.0
typeViolation	An email or url field holding a value that is neither	High	1.0
formulaError	A valid formula, rollup or lookup that errors on specific records — #ERROR!, NaN, Infinity, -Infinity	High	1.0
emptyKeyField	A record whose primary field is blank, so it shows as an unnamed row wherever it is linked	Medium	1.0
unlinkedRecord	A record in a child table with nothing linked in its one link field	Medium	0.9
staleRecord	A record not modified for longer than you set, read from the table's "Last modified time" field	Low	1.0

A duplicate has to agree on the primary field — Airtable's own identity column. Email, URL and phone fields corroborate a match and are listed in matchedOn, but they never create one on their own. That is measured rather than chosen: clustering on every typed field independently reported four duplicates on a base that is genuinely clean, because four records legitimately shared one owner's email address. An email field in Airtable is as often an attribute as it is an identity.

Why use the Airtable Data Quality Scan?

• You get a worklist, not a verdict. Every row names the record and links to it. Sort the dataset by severity, open the links, fix the rows.
• Nothing to install, and nothing to set up to try it. The prefilled input scans a bundled sample base offline. You see the exact output shape before you create a token.
• Read-only, and it says what it reads. data.records:read and schema.bases:read, no write scope. Only the cell a finding is about is ever written out — there is no column holding the rest of the record, by design, and every value is cut at 200 characters.
• It says what it could not judge. A table with no "Last modified time" field reports staleness as unknown, never as clean. A table the record limit stopped partway through is marked truncated, because the duplicates found in it are real but records never read were never compared.
• No AI model, so the same base gives the same answer. Nothing here is probabilistic. Three duplicate-matching strictnesses are offered and each one reports a confidence you can filter on.
• It schedules. Run it nightly on the Apify platform, export to JSON, CSV or Excel, or call it from the API and get the findings back in the same request.

How to scan an Airtable base for duplicate records

Try it first with no credential:

1. Press Start with the input untouched.
2. Wait a second or two.
3. Open the Findings tab. You are looking at 36 problems in a deliberately broken sample base — 14 duplicate rows in 7 groups, 7 records with no parent, 6 bad emails and URLs, 5 erroring formulas, 4 blank primary fields.

Then scan a base of your own:

1. Go to airtable.com/create/tokens and create a personal access token.
2. Give it two scopes: data.records:read and schema.bases:read. Both are basic scopes available on every Airtable plan, including Free.
3. Under Access, add the base you want scanned. A personal access token reaches only the bases you list on it individually.
4. Paste the token into Airtable personal access token and your base ID into Base ID. The base ID starts with app and is the first path segment of the base URL.
5. Press Start. The findings arrive in the default dataset, one row per table in the summaries dataset, and the HTML report in the key-value store.

Input

Field	What it does
Airtable personal access token	Your token, needing data.records:read and schema.bases:read. Left empty, the Actor scans the bundled sample base instead.
Base ID	The base to scan, one per run. Starts with app.
Tables	Scan only these tables, by table ID. Empty means every table.
Problems to look for	Any of the six above. Narrowing this narrows the scan, and the report says what was not looked for.
Only records modified since	Airtable filters by this before sending, so an incremental re-run pays for only the records that changed.
Match duplicates on these fields	Field names to match on instead of the primary field. For a table keyed by autonumber. Findings from it are reported at lower confidence.
How closely values must agree	Trim and ignore case (default); also ignore punctuation and Inc / Ltd / LLC; or fuzzy word-overlap matching.
Stale after (days)	How long a record may go unmodified before it is reported. Needs a "Last modified time" field on the table.
Maximum records per run	Stops the run after this many records across the whole base. The default, 50,000, is Airtable's own per-base record limit on the Team plan.
Save an HTML report	Also writes the shareable page to the key-value store.

Scan one base for everything, with the default duplicate strictness:

{
    "airtableToken": "<your Airtable personal access token>",
    "baseId": "app5k3ELevzFMTPH7"
}

Find duplicates and bad email or URL values in two tables only, matching a little more loosely so that Litware Inc and Litware, Inc. are reported as the same company:

{
    "airtableToken": "<your Airtable personal access token>",
    "baseId": "app5k3ELevzFMTPH7",
    "tableIds": ["tblWhSGz82YKyST0m", "tblOh0kzNJzrBsp2d"],
    "issueTypes": ["duplicate", "typeViolation"],
    "duplicateMatching": "normalisedStripPunctuation"
}

Re-scan nightly, looking only at what changed since yesterday:

{
    "airtableToken": "<your Airtable personal access token>",
    "baseId": "app5k3ELevzFMTPH7",
    "incrementalSince": "2026-09-30T00:00:00.000Z"
}

Output

Findings go to the default dataset, one row per problem, with a second view showing just the duplicates. One row per table goes to the summaries dataset. The shareable HTML page and the run totals go to the key-value store. Everything exports as JSON, CSV or Excel.

The first input example above produces this row, among its 36:

{
    "baseId": "app5k3ELevzFMTPH7",
    "baseName": "A3 Data Quality Fixture (messy)",
    "tableId": "tblWhSGz82YKyST0m",
    "tableName": "Companies",
    "recordId": "rec3OXXN48O4KM9Rp",
    "recordUrl": "https://airtable.com/app5k3ELevzFMTPH7/tblWhSGz82YKyST0m/rec3OXXN48O4KM9Rp",
    "issue": "duplicate",
    "severity": "high",
    "confidence": 1,
    "fieldName": "Company Name",
    "fieldType": "singleLineText",
    "value": "Tailspin Toys",
    "matchedRecordIds": ["rechsgWPqV26a14LH"],
    "matchedOn": ["Billing Email", "Company Name", "Website"],
    "affectedRecordCount": 0,
    "message": "\"Tailspin Toys\" appears on 2 records in this table; the other one agrees on Billing Email, Company Name, Website.",
    "remediation": "Open the records, keep the one with the most complete data, re-point anything linked to the others at it, then delete them.",
    "scannedAt": "2026-10-01T07:43:03.100Z"
}

And this one, from the same run:

{
    "tableName": "Companies",
    "recordId": "rec1NBQKJmr5Tisi1",
    "recordUrl": "https://airtable.com/app5k3ELevzFMTPH7/tblWhSGz82YKyST0m/rec1NBQKJmr5Tisi1",
    "issue": "typeViolation",
    "severity": "high",
    "confidence": 1,
    "fieldName": "Website",
    "fieldType": "url",
    "value": "ask Dave for the website",
    "message": "\"Website\" is a URL field, but this record holds \"ask Dave for the website\", which is not a URL.",
    "remediation": "Correct the value, or change \"Website\" to a text field if it is not meant to hold a URL."
}

What does each finding row contain?

Columns	Meaning
baseId, baseName, tableId, tableName	Where the problem is.
recordId, recordUrl	The record, and a link that opens it in Airtable. Both are empty for formulaError, which is reported per field rather than per record.
issue, severity, confidence	Which of the six problems, how bad, and how sure.
fieldName, fieldType, value	The field the finding is about, its declared Airtable type, and the offending value, cut at 200 characters. No other field's value appears anywhere on the row.
matchedRecordIds, matchedOn	Duplicates only: the other records in the group, and the field names that agreed.
affectedRecordCount	formulaError only: how many scanned records the formula errors on.
message, remediation	What is wrong, and what to do about it, in plain sentences.
scannedAt	When the scan ran.

formulaError is reported once per broken field rather than once per record, and that is a measured decision: a formula that errors, errors on every row that trips it. Reported per record, the sample base produced 51 rows from 15 records and drowned out every other finding. Per field it produces 5 rows carrying affectedRecordCount as the evidence. The defect is the formula; the records are what proves it.

What does each table summary row carry?

One row per table, in the summaries dataset:

{
    "tableName": "Companies",
    "recordsScanned": 15,
    "scanMode": "full",
    "duplicateCount": 4,
    "duplicateClusters": 2,
    "emptyKeyFieldCount": 1,
    "typeViolationCount": 4,
    "unlinkedRecordCount": 0,
    "formulaErrorFieldCount": 5,
    "staleRecordCount": 0,
    "staleScanState": "unknown",
    "cleanRecordCount": 0,
    "issueRate": 0.9333
}

Two columns are worth reading carefully. scanMode is full when the table was read to the end, incremental when a date filter narrowed it, truncated when the record limit stopped it partway, and skipped-cap when the limit was already spent and the table was never read at all. staleScanState is judged or unknown — unknown means the table has no "Last modified time" field, so staleRecordCount: 0 there means not checked, not nothing stale.

What is in the shareable HTML report?

A single self-contained page per base, stored under report-<baseId>.html: the totals, a table of what was scanned and how completely, then the findings ranked worst first with a link on each row. It states what the run did not look for and which tables were not read in full, so a page with few findings cannot be mistaken for a clean base. If a run produces more than 500 findings the page shows the 500 most serious and says so; the dataset carries all of them.

How is the confidence score decided?

1.0 is reported when the match needs no judgement: the primary values are identical after trimming spaces and folding case, or the value in an email field has no @ in it. 0.9 covers the two places a heuristic is involved — the looser duplicate matching that also drops punctuation and Inc / Ltd / LLC (which would merge Acme Ltd with Acme Inc), and unlinkedRecord, which infers that a table with exactly one link field is a child of it. 0.7 is reported when you supply your own match fields and they do not include the primary field, because that removes the identity anchor the default relies on. Confidence is never used to hide a finding; it is a column you can filter on yourself.

How much does it cost to scan an Airtable base?

The Actor is pay per event, at the prices shown on this page.

Event	Charged	When
Records scanned	Per 1,000 records, rounded up	After the rows those records produced are written

So a 1,000-record base is one event, a 10,000-record base is ten, and the 50,000-record default limit is fifty. An incremental re-run that finds 2,000 changed records is charged for those 2,000, not for the whole base — Airtable applies the date filter before sending, so you pay for what you receive.

The sample base is free. It reads nothing of yours, makes no network request, and is charged nothing.

Nothing is charged for a record that could not be read. If your Apify credit cannot cover the next page of records, the run stops there, writes everything it found, marks the table truncated, and tells you how many records were compared — rather than delivering output you have not paid for or billing you for output you did not get.

How long it takes depends on your base, because Airtable hands out records 100 at a time behind a cursor that has to be followed in order. Measured on Apify at about 1,000 records a second on a 40-field table, so a 10,000-record base takes around ten seconds and the 50,000-record default limit under a minute.

How to run the scan from the API or on a schedule

Call it and get the findings back in the same request:

curl -X POST "https://api.apify.com/v2/acts/mediocre_interest~airtable-data-quality-scan/run-sync-get-dataset-items?token=<YOUR_APIFY_TOKEN>" \
  -H 'Content-Type: application/json' \
  -d '{
    "airtableToken": "<your Airtable personal access token>",
    "baseId": "app5k3ELevzFMTPH7",
    "issueTypes": ["duplicate"]
  }'

That returns the finding rows as a JSON array. Add &format=csv for a spreadsheet, or &fields=tableName,issue,value,recordUrl to cut it down to the columns you want to work from.

To run it every night, use the Apify platform's Schedules — point one at this Actor with incrementalSince set and it scans only what changed. The run's findings are available from the API afterwards, and the HTML report sits in the key-value store under a stable key per base.

Tips for better results

• Start with the default duplicate strictness, then try normalisedStripPunctuation if you know your base has Inc / Ltd variants. The default deliberately misses Litware Inc versus Litware, Inc. in exchange for never inventing a duplicate; the looser mode recovers it at 0.9 confidence.
• Only override the match fields if your primary field is meaningless. A table keyed by autonumber cannot be deduplicated any other way, but naming email fields instead will group records that merely share an owner.
• Use tableIds on a big base. The scan reads every record of every table it is given, and tables are the only thing you can narrow before paying for them.
• Set incrementalSince for scheduled runs. A nightly scan of what changed costs a fraction of a full one and finds the same new problems.
• Add a "Last modified time" field to any table where staleness matters. Without one, that table reports staleScanState: "unknown". This Actor will not create the field for you — it never writes to your base.

FAQ

Is my Airtable token safe?

The token is an Apify secret input: it is encrypted at rest, never written to the log, never stored in a dataset or key-value record, and never sent anywhere except api.airtable.com. It needs no write scope. You can revoke it from your Airtable token page at any time, and you can scope it to a single base.

Does this Actor change anything in my base?

No. It calls three read endpoints and nothing else. There is no write scope on the token it asks for, so it could not change your base even if it tried.

Which Airtable scopes do I need, and are they on the Free plan?

Two: data.records:read and schema.bases:read. Both are basic scopes available to all Airtable users, on every plan including Free. The schema scope alone is not enough — verified: a token holding only schema.bases:read is refused on every records call.

Does it use an AI model?

No. It is string normalisation and rules. There is no LLM, no embedding, and no sampling — the same base scanned twice produces the same findings in the same order.

Why did my run come back with no findings?

Three possibilities, and the output tells you which. If scanMode is incremental and recordsScanned is 0, your incrementalSince date matched nothing — nothing was judged. If scanMode is skipped-cap, the record limit was spent before that table. And if staleScanState is unknown, that table has no "Last modified time" field, so staleness was not checked there. A genuinely clean table reads full with a cleanRecordCount equal to its recordsScanned.

Why did Airtable refuse my base ID?

Airtable answers with the same error for four different causes, so check each one: the base ID is wrong; the token was never granted that base (add it under Access on the token); the token is missing schema.bases:read; or it is missing data.records:read. The run's error message lists all four, because the API does not say which it was.

Can it find duplicates across two different tables, or two bases?

No — one base per run, and duplicates are found within a table. Airtable's record limit is per base and cumulative across its tables, which is what makes one run-wide record limit meaningful. To compare two bases structurally, use Airtable Schema Diff.

How is this different from an Airtable schema audit?

A schema audit tells you the base is badly built: a formula that no longer compiles, a link pointing into an empty table, two fields with the same name. This tells you which rows to fix. The two do not overlap — one reports per field, this reports per record — and they are useful in that order: fix the structure, then clean the data.

What other Actors work with this one?

• Airtable Base Schema Auditor & Link Checker — audits how the base is built: broken formulas, links into empty tables, duplicate field names, invite links that give strangers edit access. Run it first and this one second: there is no point deduplicating records in a table whose links are wired wrongly.
• Airtable Schema Diff - Compare & Monitor Bases — compares two bases field by field, or snapshots one and reports what changed since the last run, with renames reported as renames.
• Airtable Base Documentation & ER Diagram Generator — a data dictionary for every table and field plus a Mermaid ER diagram of the relationships, as a shareable page.

Support

Found a problem, or want a rule this does not have? Open an issue on the Issues tab of this Actor, with the run ID and the smallest input that reproduces it. Custom rules and variations on this scan are available on request through the same tab.