Dynamic Data Validation

Dynamic data validation checks data against rules that are not fixed in code, such as limits that depend on another field, settings loaded from your database, or lookups against records that already exist. A static rule says an email must look like an email; a dynamic rule says this account ID must exist in your system today.

The rules that matter most in an import are usually specific to the customer. Which plans they can choose, which locations they have, which account IDs already exist. Hard-coding those rules means a deploy every time they change. Loading them at runtime keeps validation accurate for every customer without code changes.

Load the rules and reference data once, then validate each row against them. Rules can depend on other fields in the same row, such as a discount limit that depends on the plan:

// Stand-ins for API calls: the customer's rules and the account IDs that already exist.
const fetchRules = async () => ({
  allowedPlans: ["starter", "growth", "scale"],
  maxDiscountByPlan: { starter: 0, growth: 10, scale: 25 },
  requireStateFor: ["US", "CA"],
});
const fetchExistingAccounts = async () =>
  new Set(["acct_1", "acct_2", "acct_3"]);

async function validate(rows) {
  // Load the rules and reference data once, not once per row.
  const [rules, existing] = await Promise.all([
    fetchRules(),
    fetchExistingAccounts(),
  ]);
  return rows.map((row) => {
    const errors = [];
    if (!existing.has(row.accountId))
      errors.push(`Unknown account ${row.accountId}`);
    if (!rules.allowedPlans.includes(row.plan))
      errors.push(`Plan must be one of ${rules.allowedPlans.join(", ")}`);
    const maxDiscount = rules.maxDiscountByPlan[row.plan] ?? 0;
    if (row.discount > maxDiscount)
      errors.push(
        `Discount ${row.discount}% is over the ${maxDiscount}% limit for ${row.plan}`
      );
    if (rules.requireStateFor.includes(row.country) && !row.state)
      errors.push(`State required for ${row.country}`);
    return { accountId: row.accountId, errors: errors.join("; ") || "none" };
  });
}

const rows = [
  {
    accountId: "acct_1",
    plan: "growth",
    discount: 5,
    country: "US",
    state: "NY",
  },
  {
    accountId: "acct_2",
    plan: "starter",
    discount: 15,
    country: "GB",
    state: "",
  },
  {
    accountId: "acct_9",
    plan: "enterprise",
    discount: 0,
    country: "CA",
    state: "",
  },
];

validate(rows).then((results) => console.table(results));

Input

Output

• Batch the lookups. One API call for the whole column is fast; one call per row is not. Fetch existing IDs once.
• Re-validate after edits. If a user changes the plan, the discount rule has to run again for that row.
• Stale reference data. A record can be deleted between validation and save. Check again at write time for anything critical.
• Explain the rule. "Discount 15% is over the 10% limit for growth" is fixable. "Invalid value" is a support ticket.

In Dromo, a column hook runs once per column and can be async, which makes it the place to check values against your API in a single batch. Row hooks look at several fields together and run again in update mode whenever the user edits a row. Row hooks can also override a select field's options for a single cell, and rules saved in Schema Studio can change without a deploy.

• Flagging Invalid Rows: Marking rows that fail validation so they can be fixed, not deleted.
• Requiring Values: Making sure required fields contain a real value.
• Field Name Matching: Mapping the column headers in a file to the fields your system expects.

Dynamic data validation checks data against rules that are loaded or computed at runtime, such as limits that depend on other fields or lookups against existing records, rather than rules fixed in code.

Static validation checks a value on its own, such as whether it is a valid email. Dynamic validation depends on context, such as other fields in the row, the customer's settings, or data in your database.