Keeping Data Consistent

🌐 Also in 28 languages

Español · 日本語 · Português (Brasil) · Français · Deutsch · Italiano · 한국어 · 简体中文 · Português (Portugal) · 繁體中文 · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Nederlands · العربية · עברית · Українська · हिन्दी · Русский · Svenska · Dansk · Norsk bokmål · Suomi · Español (México) · Français (Canada) · Deutsch (Schweiz)

Image generated with AI

Module 9 of 10 in the chapter Salesforce Data Architecture, chapter 3 of the Data Architect course. Each module stands on its own. Previous: Ownership and Lifecycle · Next: Design the Salesforce Physical Model

The question this module answers: How do I keep data consistent, and what does each layer cost?

Everybody wants clean data. I’ve never walked into a requirements meeting where someone said, “What we’d really like is inconsistent data.” The disagreement comes later: where do we enforce the rules? On the screen? A required field? A picklist, a validation rule, a duplicate rule, a flow, Apex? They all help. They aren’t interchangeable, and every layer has a cost.

My general rule is to push the rule as low as you reasonably can. If a value can only ever be one of five things, don’t let people type anything and clean it up later with automation. If two records must never share an identifier, don’t rely on someone spotting it in a report. If a rule has to hold whether the record comes from a screen, an API, an integration, a data load or an agent, don’t enforce it only in the UI. The closer the rule sits to the data, the harder it is to get around. I call it the consistency ladder.

Rung One: The Field Type and the Vocabulary

The first consistency rule is the one from module 6: choose the right type. A date is a Date, a moment is a Date/Time, money is Currency. That’s consistency before anyone writes a rule.

Make a delivery’s status a text field and you’ll get Delivered, delivered, DELIVERED, Complete, Done and Dropped off, and someone will have to work out whether those six mean the same thing. Or define the vocabulary once: Delivered, one value, one meaning. Make it a restricted picklist so it rejects anything not on the list, even through the API, and a global value set when several fields share the list (module 6 has the details).

But don’t use a picklist just to stop people typing. Is it really a value, or another entity? Nine distribution centers with a name each might look like a picklist; nine with addresses, managers, capacity and trucks are records. The rule should reinforce the model, not compensate for a bad one. And if it really is a controlled vocabulary, control it at the field. Don’t allow chaos and then build a flow to turn AZ, Arizona, Ariz and Phoenix area into the value you wanted in the first place.

Rung Two: Required and Unique

Required and Unique are attractive because they’re simple: check the box, problem solved. Sometimes. If every truck has a fleet number from the day it’s created, and no two trucks share one, both boxes express real business rules. That’s strong consistency.

But once the platform enforces a rule, it doesn’t care that your process has an exception on Tuesday afternoon. I’ve used this example in class for years. An object has been around for a while, with plenty of records. Someone adds a field and checks Required. They test creating a record: works beautifully. Then Saturday night, deployment, the tests run, and an old test class creates the object without the new field. Boom. Or someone edits an old record from before the field existed. Or the integration sends the same payload it’s sent for five years. Boom. “But we didn’t change that field.” The platform doesn’t care: you changed the contract for what a valid record is. A new rule doesn’t only affect new data; it changes what you can do with the old data.

Rung Three: Validation Rules

Validation rules let us say much richer things. If the status is Delivered, the actual delivery time must be filled in. If the stop is an install, the checklist must be complete before the stop can be closed. They run when the record is saved, so they protect the record whether it was saved from a page, the API, a data load, an integration or an agent. That’s the point. If the rule has to protect the record, put it where every write meets it.

They also have teeth. That’s their strength and their cost.

The Old Record Problem

Here’s where it gets ugly. You have a million records, and today you add a rule: the delivery time can’t be before the route’s departure. Sensible. But twenty thousand old records break it, thanks to a migration seven years ago. A user opens one of them and changes the phone number. Save. The rule fires. “I changed a phone number. Why are you telling me about a delivery time from 2019?” Fair question.

So a validation rule shouldn’t only say what must be true. It should say when to enforce it. Functions like ISNEW() and ISCHANGED() let you scope a rule to new records, or to saves where the relevant fields actually change (Validation Rule Considerations (opens in a new tab)).

But a record doesn’t only change when a person edits it, and many of the reasons are out of your control. The integration updates the status overnight. A flow stamps a date. A manager reassigns a territory and the owner changes on ten thousand records. Somebody edits a delivery item, the roll-up on the delivery recalculates, and that saves the delivery, so the delivery’s validation rule fires for a user who never touched it (Roll-Up Summary Field (opens in a new tab) warns that validation errors can show on the detail or the master). Each of those saves meets your new rule. So for every new rule, ask: should this rule apply to that save?

Those are the choices, and you weave them into the rule:

• Always, on every save. Right when the rule is truly about the record and the old data passes. But know what “every save” means: a batch job that only changes the owner, or any small edit to any field anywhere on the record, has to pass it too.
• Only for new records, with ISNEW(). The rule checks the record once, when it’s created, and never again. The past stays as it was, but so does the future: a later edit can make a new record break the rule and nobody will stop it. If that matters, pair it with the next one.
• Only when the fields it’s about change, with ISCHANGED(). Editing the phone number doesn’t wake up the delivery-time rule.
• Not for certain writers. A custom permission, checked in the formula as $Permission, lets the integration user or a data migration pass while people still meet the rule (How to Reference a Custom Permission in a Validation Rule (opens in a new tab)). Give it on purpose and to few.
• From a date forward, comparing CreatedDate with the day the rule went live, when “new” means “since we started enforcing this.” Unlike ISNEW(), it keeps checking those records on every later save, so the same warning applies: a minor change, like a batch owner update, can fail on a record that has drifted.

Adding a rule to records you already have? Think through the ramifications before you turn it on. Every existing record now has to pass it on its next save, and that save may come from somewhere you didn’t expect: a nightly integration, a batch owner change, a roll-up, a mass update from a list view. Which of those will hit it, and what happens to that job when one record fails? The section Before You Turn It On has the sequence I use.

Don’t Rewrite History to Make the Rule Happy

What about the twenty thousand old records? If the data is wrong and you know the right values, correct it. But I’ve seen another kind of “fix”: nobody knows the historical truth, they just need the records to pass, so they update them with values that satisfy today’s rule. I don’t like that. Updating old records to comply changes LastModifiedDate and destroys key historical information, and inventing values means pretending we know something about the past that we don’t. Give the rule a bypass for records that predate it rather than rewriting history. It can be ugly, but it’s honest.

UI Validation Isn’t Data Validation

Make a field required on a page layout and users can’t save without it. Then the nightly integration inserts 50,000 records. Did it use your page? No. Nor did the bulk load, the API client or the agent. A UI requirement protects that screen, not the data. Field validation on dynamic forms can be dodged the same way. So ask: where can this data enter? Lightning, the portal, mobile, the integration, the API, a bulk load, Flow, Apex, an agent. The more doors there are, the less I trust a rule that guards only one of them.

Rung Four: Duplicate Rules

Now we move from consistency inside a record to consistency across records. Acme Corporation, ACME Corp, Acme Inc.: four customers or one? No field rule can answer that. Matching rules define how records are compared; duplicate rules decide what happens when a likely duplicate is found: allow with a warning, block, or report. You can have up to five active duplicate rules per object and up to three matching rules in each, and match keys narrow each comparison to the 100 likeliest duplicates (Things to Know About Duplicate Rules (opens in a new tab)).

Their cost is friction. If every account a rep creates says Possible duplicate found, sometimes that’s exactly what you want, and sometimes people learn to click through without reading. Legitimate records can look alike, and an integration may need different behavior from a person. And the warning is Salesforce’s own: its alert and its list of possible duplicates may not blend in with a custom screen you’ve built, and when the save is headless, an integration, a data load, an agent, there’s no screen at all to show it on. So decide how a duplicate surfaces on every path: a warning for a person, a blocked save and an error for the API, or the Report option and duplicate record sets for someone to review later. Good architecture isn’t maximum enforcement; it’s the right enforcement.

And check which doors a duplicate rule actually guards (Things to Know About Duplicate Rules (opens in a new tab)). Through the API and the data import tools, nobody sees the warning: a rule set to alert simply blocks the save. Some paths skip the rules entirely: Quick Create, the Undelete button, a manual merge, records added by Lightning Sync or Einstein Activity Capture. And records saved at the same moment aren’t compared with each other, only with records already in Salesforce. Load a file with the same customer in it twice, and both rows can get through. A duplicate rule is a good net. It isn’t a lock on the door.

Test knowledge. Duplicate rules don’t compare records saved at the same moment with each other, only with records already in Salesforce. Through the API and the import tools there’s no alert: a duplicate the rule catches simply isn’t saved. And a save and everything it sets off run as one transaction: if any part fails, it all rolls back.

One boundary. Duplicate rules keep one org clean. They don’t solve enterprise identity. If Salesforce has Acme Corporation, the ERP has ACME-10382 and billing has customer 77193, a duplicate rule can’t tell you those are the same company. That’s master data, chapter 6.

Rung Five: Automation

Some rules are too complex for the lower rungs: they need related records, another system, dependent records, a multi-step process. That’s Flow, Apex, sometimes integration logic. (Not Workflow Rules or Process Builder; they’re out of support.) Automation is powerful and more expensive: more moving parts, more testing, more error handling, more ways for one automation to trip over another. And code brings errors of its own. If the field type can enforce it, I don’t write Apex. If a validation rule says it clearly, I don’t build a flow because Flow is there. Don’t solve a field problem with a process engine.

All or Nothing: When Records Change Together

So far each rung protects one save. But records change together: a delivery and its items, an order and its lines, a flow that updates three objects. Here the platform gives you a strong guarantee. A save and everything it sets off, the triggers, the flows, the classes they call, run as one transaction: if any part fails, the whole thing is rolled back and nothing is committed (Apex Transactions (opens in a new tab)). You don’t get half a delivery.

Bulk work is the exception you choose. A data load or an Apex Database method can allow partial success: the good records save, the bad ones come back with their errors, and someone has to deal with them (DML Statements vs. Database Class Methods (opens in a new tab)). That’s often right for a load of a million rows, but then consistency is a process: who reads the error file, and what happens to the records that failed?

And then there’s timing. Two saves at the same moment can both pass a check that each would fail alone. A flow that looks for an existing order and then creates one can let two through, and we just saw that duplicate rules don’t compare records saved at the same time. A field marked Unique, or an External ID, is checked by the platform on every save, so only one of them wins. That’s one more reason to push uniqueness down to the field. Locks, contention and retries are a subject of their own; chapter 11, Transactions, Concurrency, and Reliability, goes deep.

Push the Rule Down, Until It Doesn’t Belong There

“As low as you reasonably can” doesn’t mean “always the lowest.” If a field is only required once an opportunity reaches a certain stage, making it required in the schema is wrong; the rule is conditional, so a validation rule is the right rung. If a duplicate should warn a person but an approved integration has its own reconciliation, design for both paths. The goal: the lowest layer that correctly represents the business rule.

Every Layer Has a Cost

Click to enlarge

This is the trade-off I want you to see. Field types and controlled values are simple and strong, but only for what a field can express. Required and Unique are blunt: once on, every write must satisfy them. Validation rules express richer conditions but can block edits to old records and surprise a deployment. Duplicate rules protect across records but add friction for users and integrations. Automation handles anything but makes you own orchestration, errors, tests and performance. There’s no free layer. Choose each rule’s layer knowing how it fails.

Before You Turn It On

Adding a rule to an existing org is a rollout, not a checkbox. How many records break it today, and why? Are they actually wrong? Can we know the right value? Is the rule true historically, or only from now on? Which integrations, tests and automations will hit it? A good sequence: measure, clean what you can prove is wrong, scope the rule, add a deliberate bypass for legitimate history, test every write path, then enforce. Measuring and cleaning are disciplines of their own, profiling, standardization, monitoring, stewardship, and chapter 5, Data Quality and Governance, covers them. Much better than checking a box on Friday and discovering the consequences on Saturday night.

Beyond the exam: what AI changes. An agent can create records faster than any person, and bad records just as fast. “Users know not to do that” isn’t a protection when the user is an agent. Put the important rules in the data layer, where every writer meets them, so the agent doesn’t have to remember the company’s unwritten rules.

Steve Trucking: Protect the Delivery

Take a delivery. Its status comes from one vocabulary: a picklist, restricted. The Retail Partner’s order reference must be unique per partner, so it’s an External ID, marked unique, used to upsert the manifest. The actual delivery time is required once the status is Delivered: a validation rule, scoped to that change. An install stop can’t be closed until its checklist is complete and its required photos are captured (for a dishwasher, the water line, drain line and level): a validation rule, or automation if it has to check the photos stored off-platform. The same order arriving twice from a re-sent manifest is caught by the External ID; two different orders that look alike are a matching question.

Notice I didn’t say “put them all in a flow.” Each rule sits on its own rung. For every rule, ask: what’s the lowest layer that enforces this correctly for every write path that matters?

What an Architect Decides Here

• Which rung each rule belongs on: field type, controlled vocabulary, required or unique, validation rule, duplicate rule, or automation.
• Which write paths must be protected: UI, portal, mobile, integrations, bulk loads, APIs, automation and agents.
• Whether a new rule applies to historical records, or is scoped to new and changed data with a bypass for the past.
• Whether existing bad data can be corrected from evidence, rather than rewritten to pass.
• How much friction, complexity and maintenance a rule is worth.

Clean data doesn’t come from asking people to enter good data. It comes from an architecture that makes the right thing easy, the wrong thing hard, and the important rules hard to bypass. Next, we put the chapter together on a slice of Steve Trucking.

Explore

• Salesforce Help, How to Reference a Custom Permission in a Formula, Workflow Rule, or Validation Rule (opens in a new tab) and Roll-Up Summary Field (opens in a new tab), checked 2026-10-13.
• Salesforce Help, Picklist Limitations (opens in a new tab), checked 2026-10-02.
• Salesforce Help, Validation Rule Considerations (opens in a new tab), checked 2026-10-02.
• Salesforce Help, Things to Know About Duplicate Rules (opens in a new tab), checked 2026-10-13: the limits, the paths where rules don’t run, API and import saves blocked without an alert, simultaneous saves not compared.
• Apex Developer Guide, Apex Transactions (opens in a new tab) and DML Statements vs. Database Class Methods (opens in a new tab), checked 2026-10-13: one transaction rolls back as a unit; Database methods can allow partial success.
• Salesforce Help, Workflow Rules & Process Builder End of Support (opens in a new tab), checked 2026-10-02.