Keeping Bad Data Out

🌐 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 2 of 6 in the chapter Data Quality and Governance, chapter 5 of the Data Architect course. Each module stands on its own. Previous: Governance Is Designed Into the Org · Next: Know Your Data

The question this module answers: How do you keep data good, and keep it that way?

There are two ways to deal with bad data: stop it before it gets in, or clean it up afterward. You’ll do some of both. But given the choice, I’d rather prevent the problem than build an organization around cleaning it up forever. Duplicates, bad identifiers, missing information, non-standard values, users changing fields another system owns, integrations overwriting good data with bad: all of it becomes somebody’s cleanup project. So the design question is what bad data can I reasonably keep out in the first place? And “reasonably” matters, because the answer isn’t locking every field until the system is unusable.

Good Enough Is a Business Definition

Before preventing bad data, define good. There’s no single definition. Good might mean complete enough to run the process, accurate enough to decide on, current enough for the workload, consistent enough to combine with other systems, unique enough to know which customer we mean, standard enough for automation, traceable enough to know where it came from. It depends on the data. If Steve Trucking is invoicing a Retail Partner, the partner’s legal identity must be right. If a rep is jotting notes after a meeting, nobody needs a committee approving each sentence. Good enough is a business definition; the architect makes it measurable and enforceable.

Prevent the Expensive Errors

Not every bad field deserves the same attention. I care most about errors with downstream consequences. A duplicate customer means two sales histories, two service histories, two sets of contacts, two external IDs, two integrations synchronizing the same company, two versions in the warehouse and in Data 360, and eventually an agent deciding which customer is real. That’s far more expensive than a typo in a description. So don’t start with “how do we validate every field?” Start with which bad data creates the biggest business or architectural problem? and put the strongest controls there.

Open or Locked Down?

Account creation is the classic example. Should every salesperson be able to create an account? My answer: it depends on the governance around the data. For most small and mid-sized orgs, open creation is fine. A rep meets a prospect, creates the account, gets to work; that’s what CRM is for. Duplicate rules, required information and stewardship manage the quality. I don’t add a heavyweight approval process just because governance sounds responsible.

Now change the landscape. The legal customer is mastered in the ERP, flows into Salesforce with an ERP identifier, and the ERP is authoritative. Now open creation means two ways to create the same governed entity. When the ERP and a rep both create the same company, which wins, which identifier do integrations use, which record holds the history? Open creation stops being a usability question and becomes a master-data question. When accounts are mastered in or synchronized with an ERP, lock creation down.

But locking it completely fails too. The rep has met a prospect who isn’t in the ERP and wants to open an opportunity, and the governance model says “no account for you.” So they wait three days for Finance, or attach the opportunity to the wrong account, or keep the prospect in a notes field or a spreadsheet. We protected the data model and damaged the business.

The Middle Path: a Provisional Record Type

Here’s the middle path I like. Let sales create the account under a specific record type, a prospect or provisional type, and control its conversion into the governed type. Sales captures the company and starts selling. The record says plainly: not yet established in the authoritative system. Then a controlled process converts it: Finance approves it, or an integration submits it to the ERP, which either matches it to an existing customer or creates a new one and sends back the authoritative identifier. Now the record crosses a boundary, provisional → governed, which is very different from two systems each creating what they believe is the master record.

The record type is the mechanism I teach. Whatever you use, make the state visible in the data: where the record came from, whether it has crossed the boundary, who may change it now, and the identifier that ties it to the authoritative system. The ERP customer ID that only exists after conversion is the clearest signal of all.

A Record Type for Each Flow

Put the whole flow on one picture and you’ll see why this works. Customers and products come in from the ERP. Opportunities and their line items go out and become the ERP’s orders. And accounts? Some come in, some go out. So a common pattern I’ve seen across implementations is two record types on Account, one for each direction. A Sales account is born in Salesforce: reps create it, edit it, sell to it, and when it becomes a customer it’s sent out to the ERP. An ERP account (I’ve called it Customer, or just SAP) is born in the ERP and comes in through the integration. Nobody creates that record type except the integration, and its master-data fields (legal name, addresses, payment terms) are read-only to users. The record type tells you which way the record flows, so the direction of mastering is right there on the record.

Click to enlarge

A record type for each flow: Sales accounts go out, ERP accounts come in.

It doesn’t remove the hard case. A rep converts a lead for Steve’s Plumbing, but Steve’s Plumbing already exists as an ERP account, and somehow the duplicate check didn’t catch it. Now you’re merging, re-parenting the children and deciding which record survives (chapter 6, module 3).

And don’t get stuck on the record type itself. Normally I avoid record types unless there’s a strong case for them. I used them here because, before Dynamic Forms, the record type picked the page layout, and one layout showed the ERP fields read-only. With Dynamic Forms, visibility rules can show and hide fields by a field’s value, a field can be read-only on a given page, and Salesforce itself says Dynamic Forms reduce the number of page layouts and record types you need (Break Up Your Record Details with Dynamic Forms (opens in a new tab); Required and Read-Only Fields in Dynamic Forms (opens in a new tab)). But read-only on a page is read-only on that page: the API, a data load or an integration can still write the field. The real enforcement is field-level security or a validation rule; UI validation isn’t data validation (chapter 3, module 9). Record types still earn their place when the two kinds of account need different picklist values or business processes. What does matter is that some field clearly says where the record came from: the ERP key itself, a master-data flag, or a source picklist. Then drive editability from that field.

Click to enlarge

Provisional to governed: sales starts selling at once; only the authoritative process makes the record governed.

Protect the Governed Record

Once the customer comes back from the ERP, its ERP ID, legal name, tax identifier and billing details are governed attributes. Should the rep change them? Not if the ERP owns them. That’s module 1’s policy needing its enforcement point: the ERP ID read-only, legal identity controlled, while the rep still edits territory, relationship notes and Salesforce’s own classifications. The goal isn’t an untouchable account. It’s a clear line between data Salesforce owns and data Salesforce references. (The erp_ prefix from chapter 4 makes that line visible in the schema.)

What you end up with is a golden record that’s really a hybrid. The ERP’s master-data fields come in: legal name, billing and shipping addresses, payment terms. Then Salesforce adds its own fields that never go back: a sales rating, the last order date, pretty much anything marketing uses. Same account, two kinds of field, and each kind has one source.

The hardest thing are the bi-directionals. Say a rep needs the ERP’s legal name changed from Steve’s Plumbing to Steve’s Plumbing LLC. In that same five minutes, someone in the ERP changes it to Steve’s Plumbing L.L.C. Whose change wins? Here’s the mindset: if a field is mastered in the ERP, you aren’t changing the field, you’re asking to change it. Decide what happens when the request collides: the ERP wins, the request is rejected if the value changed in the meantime, or it waits for a person to resolve it. And decide whether related fields travel together as one change or one at a time. Chapter 6 takes collisions further (Loops and Collisions).

Stop Duplicates at the Door

Duplicates compound. Catch one before it’s created and it’s a small problem. Find it two years later, with opportunities, cases, contacts, invoices, integrations and analytics hanging off both, and it’s a big one. Chapter 3’s module 7 covered the mechanisms (matching rules, duplicate rules, unique External IDs), so I won’t reteach them. The governance question is where must we prevent duplication, and where will we tolerate it and resolve it later? A casual marketing lead can live with ambiguity. An ERP-governed customer with financial transactions can’t.

Standardize What Machines Read

Free text is wonderful for people and terrible when a system has to interpret it. Put Arizona, AZ, Ariz., Phoenix area and Southwest in a field that drives routing, and you have a quality problem. If a value drives automation, integration, reporting, security, assignment or an agent’s action, think hard about how standard it must be. Not every text field becomes a picklist, but distinguish information people read from information machines decide on. The second deserves stronger control.

Don’t Let Required Fields Create Fake Data

We decide a field matters and make it required. The user doesn’t know the answer, the system won’t let them continue, so they type N/A, Unknown, 999999 or test@test.com. The field is complete; the data is garbage. Before requiring something, ask whether the user actually knows it at that point. If not, require it later in the process, let another system supply it, use the provisional state, or offer an explicit Unknown when unknown is a legitimate answer. A validation rule can enforce a policy. It can’t make someone know what they don’t know.

Every Data Boundary Needs a Quality Decision

People aren’t the only source of bad data; integrations produce it faster. A Retail Partner’s manifest arrives with a few hundred lines. A handful have invalid postal codes, a couple name an unknown distribution center, two repeat the same order reference. What happens? Reject the whole file? Accept the good lines and return the bad? Load everything and clean later? Quarantine the exceptions? Who’s told, and how does the partner learn what failed? There’s no universal answer, but there must be one. Chapter 4 said every arrow needs a mechanism. Add: every data boundary needs a quality decision: what we accept, reject or quarantine, and who owns the exception.

Click to enlarge

Every data boundary needs a quality decision.

Historical Data Is Different

RouteDesk holds eight years of Steve Trucking’s history, and its quality varies by distribution center. Should we clean all eight years to today’s standard before moving it? Probably not automatically. What’s it for: daily operations, reference, claims, reporting, trend analysis? Does all of it need to be in Salesforce, or can some stay elsewhere? The migration chapter goes deep. The governance point: history doesn’t automatically need the same standard as active data. If you’ll make decisions from it, quality matters. If you’re keeping it for reference, a different standard may do. Know the purpose before you spend a fortune cleaning the past.

Measure What Someone Will Act On

Governance is never finished, so you need to see when quality slips: duplicate rates rising, more records in quarantine, more integration rejects, placeholder values spreading, more bypass requests, reports that no longer reconcile. But don’t build 150 metrics and ignore 143 of them. For Steve Trucking, maybe it’s deliveries without a valid partner reference, deliveries without a usable address, repeated order references, rejected partner messages, accounts stuck in the provisional state. A dashboard full of green circles doesn’t mean the data is good. Measure what somebody is prepared to act on.

Beyond the exam: what AI changes. Bad data increasingly becomes bad action. A person might notice a phone number looks wrong before calling it; an agent simply uses the value it’s given. Duplicate customers, stale statuses, fake required values and wrong identifiers turn straight into the wrong message or the wrong update. That raises the value of prevention. It doesn’t remove the need for stewardship.

What an Architect Decides Here

• Which quality failures do enough harm to justify preventive controls.
• Whether governed records are created freely, only by the authoritative system, or through a provisional-to-governed path.
• Which attributes Salesforce owns and which it only references.
• What happens to inbound data that fails checks (accept, reject or quarantine) and who owns the exception.
• Which quality signals are measured, so stewards see the slip.

You’ll never prevent every bad record. The job isn’t perfect data; it’s keeping avoidable bad data from becoming expensive data. Stop the duplicate before it has two years of history. Protect the identifier before ten integrations depend on the wrong one. Let sales create the prospect, but don’t pretend it’s the governed customer until it is. And when something bad gets through, make sure someone can see it, own it and fix it.

Explore

• Salesforce Help, Things to Know About Duplicate Rules (opens in a new tab), checked 2026-10-14.
• Salesforce Help, Break Up Your Record Details with Dynamic Forms (opens in a new tab) and Required and Read-Only Fields in Dynamic Forms (opens in a new tab), checked 2026-10-14.