Designing a Validation Script: Structural Checks vs. Anomaly Detection

A structural validation gate answers one question about an incoming record: is it well-formed? Every required field has a value, every linked record resolves, every value is the type production expects. That question catches the escalation that arrives with no account owner. It has nothing to say about an escalation that claims $40,000 of ARR at risk on an account whose ARR in Airtable is $4,000, because every field in that record is filled in correctly and nothing about it breaks a rule.

Catching the second record takes a different kind of check, and that check will sometimes hold a perfectly correct record. This guide covers how to put structural checks and anomaly detection into one Airtable validation script without treating them as the same thing: what each is allowed to decide, how to set the range an anomaly check compares against, how to keep the flags it raises from burying the people who review them, and how each flag reaches a named person instead of a queue nobody owns.

The example is the escalation intake from InAir's guide to building a staging layer for third-party webhooks: a support desk posts each new escalation to Airtable, a receiving automation stores it in an Escalation Intake table as Pending, a validation script decides what happens to it, and only Approved records are promoted into the production Escalations table.

Two Checks That Fail in Different Ways

InAir's guide to running Airtable inside complex, high-volume system landscapes frames the anomaly check as a different question from the structural one: not "is this record well-formed," but "does this value fall within the range this field normally holds?" The two checks are reliable in different ways.

A structural check is deterministic. "Account Owner Email is missing" is either true or false, and a record that fails can't safely reach production in any form, so it's safe to hold it automatically with the reason attached. An anomaly check is probabilistic. A ratio of ten to one between ARR at risk and the account's ARR might be a typo, a value entered in the wrong currency, or an account whose ARR in Airtable hasn't been updated since a large expansion deal. The check can show that a value is rare, but it can't show that the value is wrong.

That's why a gate with both checks needs three outcomes instead of two. If an anomaly were treated as a failure, correct but unusual records would be rejected along with the broken ones, and the people who rely on the gate would learn to override it. If it were treated as a pass, the check would have no effect. Flagged for Review is the outcome for a record the script can't decide on: held out of production like a Schema Error, but cleared by someone who knows whether the value is plausible, not by someone fixing a mapping.

The Structural Half Stays Binary

The structural side is well covered already. InAir's post on enforcing data contracts in Airtable sets out the Schema Validation Gate for base-to-base syncs, and the staging-layer guide adapts it for webhooks with four checks: required fields present, select values that exist in production, an ARR at Risk that production's currency field can hold, and an account owner email that matches exactly one person. Those checks stay as they are.

The range check also needs a fifth structural check, which runs with the other four: the escalation’s account ID has to match exactly one record in Accounts, the same way the account owner has to match exactly one person, and that account needs an ARR and a Segment. A record that fails it is a Schema Error for the integration owner, not a judgment call for a reviewer, since a range can’t be compared against an account that doesn’t resolve or has nothing to compare with.

Two rules govern how the anomaly check sits next to the structural ones:

1. The anomaly check runs only on records that passed every structural check. A range check on an ARR at Risk of "48k" has nothing to compare, and a record with a missing owner gains nothing from also being told its value is unusual. Running structure first means a held record has one kind of problem at a time and goes to one kind of owner.
2. A reviewer's confirmation skips the anomaly check and nothing else. The staging-layer guide already includes a Reviewer Confirmed checkbox for this reason: a value that's unusual but real would fail the same range check on every pass, so confirming it has to switch that check off for that record. The structural checks still run every time, so a reviewer who confirms an amount and accidentally clears the owner email still gets a Schema Error. The checkbox stays ticked after that, so have the Schema Error page's Recheck button untick it too, and an amount changed while fixing a structural error is range-checked again.

Where a Sensible Range Comes From

The tempting version of an anomaly check is a fixed number: flag any escalation with more than $50,000 of ARR at risk. That rule fails in both directions at once. It flags routine escalations on large enterprise accounts whenever the amount crosses $50,000, and it passes the $40,000 escalation on a $4,000 account, which is the one that most needs a second look. A fixed threshold also goes stale without anyone noticing, because the business it was set against keeps changing.

Three decisions produce a range that holds up better.

Compare on the scale that makes records comparable. Raw ARR at Risk mixes accounts of every size, so its history says little about whether one escalation is out of proportion. The ratio of ARR at Risk to the account's ARR doesn't have that problem: a ratio of 0.3 means the same thing on a $4,000 account and on a $4 million one. Choosing what to measure is most of the work in an anomaly check. For a quantity field it might be the ratio to the order's usual size, and for a date it might be the gap from the record's creation.

Set the threshold at a percentile of the field's own history. The 99th percentile of the last twelve months of ratios is the value that 99% of past escalations stayed at or below. It moves when the business moves, and unlike a mean plus three standard deviations, it means the same thing whatever shape the data has. Ratios like this one are skewed, bunched close to zero with a long tail to the right, and the three-standard-deviation rule only maps to a known share of records when the data is normally distributed. A percentile also holds up better against the bad values that production data from before the gate will contain: each one above the threshold moves it up at most one position in the sorted values, while every extreme value inflates both the mean and the standard deviation.

Calculate it per segment when segments behave differently, with a floor on the sample size. Enterprise and SMB accounts may carry very different ratios, and one shared threshold would be too loose for one and too tight for the other. Splitting by segment shrinks each sample, though, and a percentile needs enough values to mean anything. With fewer than 100 values, the 99th percentile, calculated the nearest-rank way the script below uses, is simply the largest value seen so far. Below that floor, the script approves the record with a note that its range wasn't checked, rather than flagging it for lack of history.

The percentile is calculated inside the validation script each time a record arrives, from the escalations already in production. That keeps the whole check in one place with nothing extra to schedule, and when a record is flagged, the threshold it was judged against goes into Anomaly Detail, where the reviewer can see it. The cost is that every validation run reads the Escalations table, which is fine at the 600 escalations a month used in this guide. If production holds far more than that, the full read of Escalations is the first thing to revisit.

The ratio uses each account's ARR as it stands today rather than at the time of each past escalation, since production doesn't store the latter. For a threshold drawn from a year of history, that's close enough. If your accounts' ARR changes sharply and often, store the ratio on each escalation when it's promoted and read that instead.

Adding the Check to the Validation Script

The Accounts table needs an ARR field and a Segment field, filled in for every account, and Escalations needs a created time field called Created if it doesn't already have one. Accounts also gets a Data Ops Reviewer user field naming who reviews that account's flags. Add two fields to Escalation Intake, Anomaly Detail (long text) and Assigned Reviewer (user), and show Anomaly Detail read-only on the Flagged for Review page next to Validation Errors.

In the staging-layer validation script, keep checks 1 to 4 and replace the final updateRecordAsync call with this. It reuses rec, val, errors, arr, ownerId, escalations, intake, and recordId from the lines above it.

// 5. The account must match exactly one record in Accounts, with an ARR and a Segment.

const key = (s) => s.trim().toLowerCase();

const byName = new Map();

for (const a of (await base.getTable('Accounts').selectRecordsAsync()).records) {

const k = key(a.getCellValueAsString('Account Name'));

byName.set(k, [...(byName.get(k) || []), a]);

}

const found = byName.get(key(val('Account Name'))) || [];

const ok = found.length === 1 && found[0].getCellValue('ARR') > 0 && found[0].getCellValueAsString('Segment');

const account = ok ? found[0] : null;

if (val('Account Name') && !account) errors.push(`"${val('Account Name')}" must match one account with an ARR and a Segment.`);

// 6. Flag ARR at Risk when its share of the account's ARR is above the 99th

// percentile of the past year's escalations in the same segment.

let note = '';

let flagged = false;

if (errors.length === 0 && !rec.getCellValue('Reviewer Confirmed')) {

const segment = account.getCellValueAsString('Segment');

const yearAgo = Date.now() - 365 * 24 * 60 * 60 * 1000;

const ratios = [];

for (const e of (await escalations.selectRecordsAsync()).records) {

const [a, extra] = byName.get(key(e.getCellValueAsString('Account Name'))) || [];

const atRisk = e.getCellValue('ARR at Risk');

if (!a || extra || !(a.getCellValue('ARR') > 0) || typeof atRisk !== 'number') continue;

if (a.getCellValueAsString('Segment') !== segment) continue;

if (new Date(e.getCellValue('Created')).getTime() < yearAgo) continue;

ratios.push(atRisk / a.getCellValue('ARR'));

}

ratios.sort((x, y) => x - y);

const p99 = ratios[Math.ceil(0.99 * ratios.length) - 1];

const ratio = arr / account.getCellValue('ARR');

if (ratios.length < 100) note = `Range not checked: only ${ratios.length} past ${segment} escalations.`;

else if (ratio > p99) {

flagged = true;

note = `ARR at Risk is ${ratio.toFixed(2)}x the account's ARR. 99% of the last year's ` +

`${ratios.length} ${segment} escalations were at or below ${p99.toFixed(2)}x.`;

}

}

// 7. Record the outcome. A flag goes to the account's Data Ops reviewer.

const reviewer = flagged ? account.getCellValue('Data Ops Reviewer') : null;

if (flagged && !reviewer) throw new Error(`${val('Account Name')} has no Data Ops Reviewer.`);

await intake.updateRecordAsync(recordId, {

'Account Owner': errors.length ? [] : [{ id: ownerId }],

'Validation Status': { name: errors.length ? 'Schema Error' : flagged ? 'Flagged for Review' : 'Approved' },

'Validation Errors': errors.join('\n'),

'Anomaly Detail': note,

'Assigned Reviewer': reviewer ? { id: reviewer.id } : null,

});

We ran the full script, the staging-layer guide's checks plus this block, against a mock of Airtable's scripting API with twelve test records:

• The $40,000 escalation on a $4,000 account reached Flagged for Review, assigned to the account's Data Ops reviewer, with the note "ARR at Risk is 10.00x the account's ARR. 99% of the last year's 150 Mid-Market escalations were at or below 0.59x." So did a modest outlier at 0.70x.
• Normal escalations on small and large accounts, and one just inside the range at 0.55x, were Approved with no note.
• A blank account name, an unknown one, a name two accounts share, and an account with no ARR each gave Schema Error.
• ARR at Risk sent as "48k" gave Schema Error with the staging-layer guide's reasons, and the anomaly check didn't run.
• A record with Reviewer Confirmed ticked was Approved.
• A segment with fewer than 100 past escalations gave Approved with the note "Range not checked: only 30 past Mid-Market escalations."

The "fewer than 100" case is approved rather than flagged on purpose, since flagging every record in a new segment would teach reviewers to clear flags without reading them. If an account has no Data Ops Reviewer, the run fails with that reason and the record stays at Pending, so fill the field in for every account before turning the check on.

How Many Flags a Queue Can Take

A review queue fails the way an alerting system does. Google's SRE book describes the pattern in its chapter on monitoring distributed systems: "When pages occur too frequently, employees second-guess, skim, or even ignore incoming alerts, sometimes even ignoring a 'real' page that's masked by the noise." A reviewer who has cleared thirty real values in a month is likely to clear the thirty-first without reading it.

A percentile threshold makes the noise predictable, which is its most practical advantage over a fixed number. If new escalations look like the last year's, a 99th percentile threshold flags about 1% of normal records, and a 95th percentile flags about 5%. At 600 escalations a month, that's roughly 6 flags a month on records that are fine at the 99th percentile, against roughly 30 at the 95th, on top of the genuinely wrong values the check exists to catch. Choosing the percentile is choosing how many false flags the reviewer will see, and it's worth deciding that number with the reviewer before the check goes live.

The count compounds as checks are added. Five independent range checks, each at the 99th percentile, flag about 1 − 0.99⁵, or 4.9%, of normal records, close to a single check at the 95th. Each anomaly check should earn its place by guarding a value someone would act on wrongly. ARR at Risk is the number leadership reads off the dashboard. A free-text description length drives nothing, so a range check on it adds flags without protecting a decision.

The queue also tells you whether the threshold is right, as long as each review records how it ended. Add a Review Outcome single select (Confirmed, Corrected) to Escalation Intake, and extend the two buttons on the staging layer's Flagged for Review page: Confirm sets Review Outcome to Confirmed alongside what it already sets, and Recheck sets it to Corrected. Then add two number elements to the review page counting records by Review Outcome, and keep Promoted holding records for at least a month before archiving them, so the counts always include at least the last month of reviews. If reviewers confirm nearly every flag, the threshold is too tight or the check is measured on the wrong scale. If they correct most of them, the check is earning its place, and the integration owner should look at why the support desk keeps sending those values.

Among the five Integration Load factors, the pillar's description of validating writes before they commit includes what happens to a record that fails: it's "flagged and routed to someone for review." A queue that's routed to no one in particular, or that the reviewer has learned to clear on sight, holds the record back but never gets it to a person who reviews it.

A Flag Belongs to a Person

The staging-layer guide routes Schema Errors to the integration owner and Flagged for Review records to a named reviewer in Data Ops. One named reviewer works while there's one segment and modest volume. Across several segments or regions, that person either becomes the bottleneck, or the page gets opened to the whole team and turns into a queue everyone can see and nobody is responsible for. The Data Ops Reviewer field on Accounts keeps each flag with a named person, and the script writes that person onto the record in the same update that flags it.

Speed matters here for a specific reason. A flagged escalation isn't in the Escalations table, so the routing automation hasn't assigned it to anyone, and every hour it waits for review is an hour a customer escalation sits unrouted.

Two native pieces cover the rest, with no further scripts:

• A notification when the flag is raised. Create an automation with the When record matches conditions trigger on Escalation Intake, with the condition Validation Status is Flagged for Review, and a Send email action whose To field uses Assigned Reviewer's email from the triggering record. Put Account Name, Anomaly Detail and a link to the review page in the body. The trigger starts each time a record enters that state, including a record that's flagged again after Recheck sets it back to Pending.
• A page each reviewer can work from. Group the Flagged for Review page by Assigned Reviewer and sort it by Received At, so each reviewer finds their own records, oldest first.

If flags still wait too long, the fix is ownership rather than more automation: agree with each segment's reviewer who covers their flags when they're away, and how long a flag can sit before that person picks it up.

To reassign automatically without a scheduled script, have the validation script also write a Remind At time a set number of hours ahead when it flags a record. Then add a formula field, NOW() >= {Remind At}, and trigger a reminder or reassignment automation when that formula turns true on a record that’s still Flagged for Review.

Alternatively, you can instead write the script's Remind At calculation logic into the same formula field, and use a DATETIME_DIFF function to compare it to the Received At timestamp, which saves an extra field. Either way, the timing is approximate: Airtable’s guide to delaying automation runs says NOW() updates about every five to ten minutes while the base is open and about every hour while it’s closed. That’s close enough for a wait measured in hours.

Running cost stays close to what the staging layer already uses. The check adds no runs of its own, since it lives inside the validation run, and the notification adds one run per flag.

What to Look at After the First Month

Three numbers say whether the gate is working. The confirmed share of flags shows whether the threshold is tight enough to be worth a person's time. Notes that say "Range not checked" show which segments don't have enough history yet. And the Received At of the oldest record still waiting shows how long escalations are sitting unrouted, which is the number the support team will ask about first.

If incoming data from your support desk, CRM or other systems is well-formed but sometimes wrong, and you'd like range checks tuned to your own history without filling a review queue nobody reads, Schedule a Discovery Call with InAir and we'll help you set the thresholds and route each flag to the person who can actually clear it.