Salesforce Data Quality Audit: The Complete 2026 Admin Guide

Stale data doesn't look broken. A duplicate Account looks identical to a real one. A custom field that hasn't been populated since 2023 looks like a useful field that's just "underused." A Contact last touched in November silently stops being a Contact the moment your reps stop following up — but your reports, your dashboards, and your Flow entry criteria don't know that.

This guide is the admin's end-to-end data quality audit. It covers how duplicate records form and how to find them with Workbench SOQL, what "field population" actually means and why most sparse-but-required fields are quietly breaking your reports, the last-modified heuristics that surface stale data and orphaned records, and the five Setup checks you can run from a browser today. It ends with when a heuristic-only scan stops being enough — and when it's time to escalate to a Founding Audit.

1. How to Find Duplicate Records with Standard Tools

Duplicates in Salesforce silently inflate Counts in reports, distort campaign attribution, and produce double-Task assignments. The worst kind are the ones that survived a partial merge — same Email, slightly different Phone, and the "winner" chosen by which record was created first.

You can find them with three standard tools — Workbench SOQL, the metadata API's DuplicateRecordItem object, and Skinny Table aggregations — but the first move is the simplest: a Workbench GROUP BY query against the Contact or Lead object.

Step 1: Workbench SOQL GROUP BY Email on Contacts

The fastest way to find suspected duplicate Contacts is a Workbench GROUP BY query against email. Run this under Utilities → SOQL Query:

SELECT Email, COUNT(Id) dupCount
FROM Contact
WHERE Email != null
GROUP BY Email
HAVING COUNT(Id) > 1
ORDER BY COUNT(Id) DESC
LIMIT 200

Each row back from this query represents an Email value shared by two or more Contacts — the canonical "duplicate on email" pattern. The dupCount tells you how many Contacts share the value; sort by it and you have a prioritized merge queue.

For Leads, replace the object with Lead and the field with normalized phone. Phone normalization is its own problem (line breaks, country codes, parens vs. dashes); the practical approach is to run a helper Flow that strips the formatting and writes to a denormalized Phone_Key__c formula field, then GROUP BY on that.

For the full detection-and-merge playbook, the next step is choosing winner records by LastModifiedDate, merging with /lead/leadMerge.jsp or the merge() Apex call, and writing off the losers with a flag field. The companion guide at Salesforce Duplicate Detection & Merge Playbook (2026) walks through the whole flow.

2. Field Population Rates — Finding Sparse Fields That Bloat the Schema

The second pillar of any data quality audit is field population — the share of records in an object that actually have a value in a given field. A field that's only populated on 8% of records isn't a "sparse" field — it's a field that's quietly lying about its importance. Reports filter on it. Validation rules gate on it. Layouts assume its presence.

The standard tool for this is the Workbench SOQL query against EntityDefinition and EntityParticle — the metadata objects that describe every custom field and its definition. Combine that with a population query against the underlying object, and you can score every custom field in an org on "% records populated" in under an hour.

Step 1: List every custom field with its object

First, list every custom field with the object it lives on. Run this in Workbench under the metadata API:

SELECT EntityDefinition.QualifiedApiName, QualifiedApiName, DataType, IsNillable
FROM EntityParticle
WHERE IsCustom = true
  AND IsCalculated = false
ORDER BY EntityDefinition.QualifiedApiName ASC

Each row gives you a field's API name, data type, and whether it's nullable (which is what most admin UIs tell you about). What it doesn't tell you is how often the field is populated on real records — that's the population question, and the next query answers it.

Step 2: Score population per field via COUNT_DISTINCT

For each custom field on the Account object, the time-window population query looks like this:

SELECT COUNT_DISTINCT(Id) totalRecords,
       COUNT_DISTINCT(Custom_Field__c) populatedRecords
FROM Account
WHERE IsDeleted = false

populatedRecords / totalRecords is the field's population rate. Anything below 30% is a candidate for deprecation. Anything below 10% almost certainly is — the only reason to keep it is compliance retention.

The free Field Usage Auditor runs this exact query against your org (no admin credential needed for the CSV-based scan), then computes the population rate per field and ranks them by deletion-candidate. For orgs with thousands of custom fields, it's the fastest way to surface the top-50 deletion candidates.

3. Stale Data Patterns and Last-Modified Heuristics

The third pillar of a data quality audit is the staleness question: which records in your org are "alive" only because nobody marked them dead? A stale Account doesn't poison a single report — it poisons the entire org's idea of what "an Account" means.

The standard pattern is the LastModifiedDate > N_DAYS_AGO heuristic. It runs from Workbench in five minutes and surfaces the records that haven't been touched in the time window you choose. The right threshold depends on the object: Opportunities and Cases need shorter windows (90 days is reasonable), Accounts and Contacts tolerate longer (180 days or 365 days).

Step 1: LastModifiedDate > N_DAYS_AGO

The basic staleness query is a Workbench SOQL against any object's LastModifiedDate:

SELECT Id, Name, LastModifiedDate, OwnerId
FROM Account
WHERE LastModifiedDate < LAST_N_DAYS:365
  AND IsDeleted = false
ORDER BY LastModifiedDate ASC
LIMIT 200

Each row is an Account untouched for at least a year. That's the candidate cleanup list. The next decision is whether the row is genuinely stale (no related Contacts, no related Opportunities, no active Project) or whether the Account is dormant-but-valid (still has an active contract).

Step 2: Heuristic thresholds differ per object

The 365-day threshold is a starting point — different objects want different cutoffs. The platform's routes/dashboard-signals.js signal engine already encodes this:

Step 3: Orphan detection — Accounts with no Contacts

The pattern that quietly breaks report rollups is the orphaned Account — one with no related Contacts for 6+ months. The standard heuristic for it is a subquery:

SELECT Id, Name, LastModifiedDate
FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Contact WHERE AccountId != null)
  AND LastModifiedDate < LAST_N_DAYS:180
  AND IsDeleted = false
LIMIT 200

That gives you Accounts with no Contacts and no recent activity — the kind that quietly inflate "total Accounts" while contributing nothing to pipeline or engagement. Similar patterns apply to Opportunity Owner IsActive = false (the owner has left and the Opp is sitting) and Cases whose Account.ParentId is unpopulated.

4. Five Setup/Workbench Checks You Can Run Today

You don't need a paid audit tool to surface the highest-impact data quality problems. Five checks, each one runnable from a browser in under an hour. For most orgs, completing all five is a half-day.

Check 1: SOQL Contact duplicates GROUP BY Email

How to find it: Open Workbench → Utilities → SOQL Query, run the GROUP BY Email query from Section 1, sort by dupCount. Save the result as your "duplicates to triage" list.

What good looks like: Zero rows back, or near-zero (a handful of test users). A 5%+ duplicate rate on the Contact object is a sign the existing duplicate rule isn't matched to the right matching criteria, or wasn't enabled at all.

Check 2: EntityParticle population query

How to find it: Run the EntityParticle query from Section 2 against the metadata API. For each custom field, run COUNT_DISTINCT against the field on the target object and divide by total record count. Rank by lowest population.

What good looks like: Every custom field at >10% population, with the field's only purpose either (a) being a required-on-create schema field, or (b) having a documented "we keep this for compliance" retention reason. Anything below 30% needs a written justification or a deprecation plan.

Check 3: LastModifiedDate threshold query per object

How to find it: For each of the four objects in the Section 3 table (Opportunity, Lead/Contact, Account, Case), run the LAST_N_DAYS query with the threshold from that column. Sort by oldest LastModifiedDate first.

What good looks like: Each row in the result is triaged as either (a) genuinely stale and a candidate for soft-delete, (b) dormant-but-active and tagged as such (e.g. "renewal pending"), or (c) a process gap to investigate (why is the Case at 9 months old with no owner?).

Check 4: Orphaned-Account detection

How to find it: Run the NOT IN (SELECT AccountId FROM Contact) query from Section 3. For each result, check whether the Account has any related Opportunities, Contracts, or Notes — if not, it's an orphaned record candidate.

What good looks like: A small number (relative to total Accounts) of orphaned records, each with a "to be cleaned" tag and a deprecation date. A large number — say 10%+ of total Accounts orphaned — means the org has stopped maintaining Account hygiene and needs an org-wide cleanup sprint before reports start lying.

Check 5: Picklist value distribution

How to find it: For every picklist field on the Account or Opportunity object, run the GROUP BY PicklistField__c query and rank by count. Look for tail values (one record at a value like "Other — please specify") that shouldn't be in the picklist at all.

SELECT LeadSource, COUNT(Id) cnt
FROM Lead
WHERE LeadSource != null
GROUP BY LeadSource
ORDER BY COUNT(Id) DESC
LIMIT 50

What good looks like: Every picklist value is documented and serves a distinct business purpose. Tail values are collapsed into a "Other" line in reports or restricted out of the picklist entirely. For the full standardization playbook including Global Value Sets, dependent picklists, and validation rule guards, see Picklist Standardization in Salesforce: How to Clean Inconsistent Values Without Breaking Reports.

5. From Manual Checks to a Founding Audit

The five checks above surface the highest-impact data quality problems — duplicate Contacts and Accounts, sparse custom fields that bloat the schema, stale records that quietly inflate totals, and picklist tail values that fragment reporting. For an org with fewer than 100,000 records, you can work through all five in a single working session.

For orgs with millions of records, the bottleneck is no longer detection — it's triage. Which of the 8,000 stale Accounts are real renewal candidates vs. noise? Which of the 240 sparse custom fields are required for compliance vs. safe to retire? Which of the 50 Contact duplicates should merge into which record without losing history?

A Founding Audit runs the full scoring, weights the findings by active blast radius (which duplicates are about to be touched by a Flow today, which sparse fields are required by active validation rules, which stale Accounts are sitting on active Opportunities), and surfaces the fixes in the order they'll deliver the highest reliability return. It's the same step you'd run manually — automated, repeatable, and tied to findings the team can action in one sprint.

Frequently Asked Questions

How often should I run a Salesforce data quality audit?
Quarterly for actively-maintained orgs, every six months for stable orgs. Run it immediately after any major org change — a sandbox refresh, a large migration to data Cloud, or a consulting engagement that touched duplicates or picklists.

What's the difference between a "data quality audit" and a "data quality scorecard"?
An audit enumerates problems: every Account with duplicates in the same Email, every custom field below 30% population, every Account stale for 365+ days. A scorecard weights those findings by impact and surfaces the top-N records to fix first. Most teams start with an audit to understand their full surface, then convert to a scorecard for prioritization on the second pass.

Can I run a data quality audit on a read-only org?
Yes. The Workbench SOQL queries in this guide are read-only — they don't write to the org's data. For org-scale audits that surface thousands of duplicates, a connected-org diagnostic is faster — but the Workbench path works for consultancies and auditors without full write access.

What's the single most common data quality issue you find?
Sparse-but-required custom fields. About 60% of custom fields are below 30% population, and a meaningful fraction of those are required for at least one validation rule or report filter — which means a report that "looks empty" is actually returning the wrong answer. Population scoring is the highest-leverage first step.

Does the audit catch Flow / Validation Rule issues too?
Not directly — that's a separate pillar (Flow health audit and Validation Rule audit). The data quality audit focuses on the records themselves; the Flow and Validation Rule audits focus on the automations and validation logic that act on those records. Run all three pillar audits for a complete org picture.

Continue Reading

Related audits