The migration was scheduled for Saturday night.

Nobody profiled the data first.

By Monday: 4,000 duplicate accounts, three date formats fighting over one field, and a free-text “Notes” column that turned out to contain credit card numbers.

All of it was findable in advance. In about an hour. That hour is called data profiling.

In plain English: data profiling is examining a dataset to learn what’s actually in it, its structure, its values, its relationships, and its problems, before you cleanse, migrate, or integrate it. Diagnosis before treatment.

What’s in this guide:

→  Part 1: The three lenses – what profiling actually looks at

→  Part 2: The 7 metrics that matter (cheat sheet)

→  Part 3: The 60-minute profile – copy-paste SQL kit

→  Part 4: Profiling before you integrate or migrate

→  Plus a rapid-fire FAQ

Part 1: The three lenses of data profiling

Profiling looks at data in three ways. Each lens catches problems the others miss.

Lens 1 – Structure analysis

Can this data be trusted mechanically?

Structure analysis examines format and organization: column data types, field lengths, null rates, format patterns.

It answers questions like:

→  Are all email fields actually formatted as emails?

→  Are dates in one format, or three?

→  Is that “numeric” field hiding text? (The classic: a Phone column stored as VARCHAR(255), containing “call after 5pm.”)

Lens 2 – Content analysis

Do the values make sense?

Content analysis examines what’s inside: frequency distributions, min/max ranges, unique-value counts, outliers.

This is the lens that reveals 40% of your phone fields are empty. That 12 customers have negative balances. That a price field runs from $0.01 to $999,999, and only the business can say which end is the typo.

It’s also how you discover PII you didn’t know you were storing. Profiling free-text fields before a migration has saved more than one company from copying credit card numbers into a new system.

Lens 3 – Relationship analysis

Does the data hold together?

Relationship analysis examines how tables and objects connect: foreign-key integrity, parent-child links, cross-system references.

It catches orphans, invoices referencing customers that don’t exist, line items pointing at deleted products, an ERP record holding a CRM ID that no longer resolves.

*Structure tells you whether data can be trusted. Content tells you whether it should be. Relationships tell you whether it holds together.*

Part 2: The 7 metrics that matter

Every profiling tool, and every hand-rolled SQL session, should produce these seven numbers per field or table:

Metric	What it measures	Red flag
Completeness rate	% of non-null values per field	Any required field below 100%; key contact fields below ~90%
Uniqueness rate	% of distinct values per field	ID fields below 100%; emails below ~95% (duplicates hiding)
Pattern compliance	% of values matching the expected format	Emails or phones below ~98%
Referential integrity	% of foreign keys that resolve	Anything below 100%, every miss is an orphan
Value distribution	Frequency histogram per field	One value above 90% (default-value abuse) or a long tail of variants (“US”, “USA”, “United States”…)
Outlier count	Values outside expected ranges	Negative quantities, $0 prices, dates in 1900 or 2099
Duplicate rate	% of records matching on key fields	Above ~5% on accounts or contacts, time for a dedup pass

One thing to keep straight:

Profiling doesn’t fix anything. That’s the point. You can’t choose the right fix until you’ve measured the problem, and half the time, the “fix” is a decision, not a cleanup.

Part 3: The 60-minute profile

You don’t need a profiling tool to start. You need read access and an hour. (Syntax varies slightly by database, these are the portable versions.)

Check 1 – Completeness sweep. Which fields are actually filled?

SELECT
  COUNT(*) AS total_rows,
  ROUND(100.0 * COUNT(email) / COUNT(*), 1) AS email_pct,
  ROUND(100.0 * COUNT(phone) / COUNT(*), 1) AS phone_pct
FROM contacts;

Check 2 – Duplicates on key fields.

SELECT LOWER(TRIM(email)), COUNT(*)
FROM contacts
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 2 DESC;

Anything back? You have a deduplication project before you have an integration project. [Internal link placeholder: “What Is Data Deduplication?” glossary post]

Check 3 – Pattern compliance. How many “emails” aren’t?

SELECT COUNT(*)
FROM contacts
WHERE email NOT LIKE '%_@_%._%';

Check 4 – Ranges and outliers.

SELECT MIN(amount), MAX(amount), AVG(amount) FROM invoices;
SELECT COUNT(*) FROM invoices WHERE amount < 0;

If MAX makes you blink, ask the business before you ask the cleanup script.

Check 5 – Value distribution.

SELECT country, COUNT(*)
FROM accounts
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

If the top 20 includes “US,” “USA,” and “United States,” you’ve just found a value-mapping job.

Check 6 – Orphans.

SELECT COUNT(*)
FROM invoices i
LEFT JOIN customers c ON i.customer_id = c.id
WHERE c.id IS NULL;

Any number above zero is an integration failure waiting for a foreign key.

Profiling a SaaS app without SQL

For Salesforce, HubSpot, or QuickBooks, you have three routes:

→  Native reports – count-by-field reports and dashboards get you completeness and distributions.

→  Metadata APIs – Salesforce’s Describe call gives you the structural profile: types, lengths, picklists, required flags.

→  Replicate first, profile second – the fastest path to SQL-grade profiling of a SaaS app is copying it into a database. Plenty of teams run Cloud Replication as step zero of a project for exactly this reason – once Salesforce is sitting in SQL Server or PostgreSQL, all six checks above just work.

Reading the results: 15-minute triage

Sort every finding into one of three buckets:

Bucket	Meaning	Examples
Fix at source	Correct it in the origin system first	Merge duplicate accounts, backfill required emails
Fix in flight	Handle it with transformation rules in the map	Normalize date formats, value-map country variants
Redesign or escalate	The finding changes the project	Target field shorter than source data; PII discovered in free text

Profiling finds the anomalies. Only the business knows which ones are errors. A $999,999 price is either a data entry mistake or your enterprise tier – and no query can tell you which.

Part 4: Profiling before you integrate or migrate

Here’s the sequence most failed projects follow: map → build → sync → discover → clean up for weeks.

And the sequence that works: profile → decide → map → build → sync.

In the documented Salesforce migration case, profiling would have surfaced the duplicate records and field mismatches before the sync ran, instead of during the weeks of cleanup that followed.

Two hours of profiling is the cheapest insurance an integration project can buy.

Profile both sides, not just the source

Before any integration, run the profile against source and target, then compare:

→  Type mismatches – source sends strings, target expects decimals.

→  Length gaps – source allows 500 characters, target caps at 255. That’s truncation scheduled for go-live day.

→  Picklist vocabularies – a Status field on each side, sharing a name and nothing else.

→  Null rates vs. required fields – the source field that’s 40% empty feeding a target field that’s mandatory. Decide the default now, not in the error logs.

Every one of those findings becomes a line in your field map – which is the whole point. Profiling is the research; [mapping](https://www.mydbsync.com/blogs/what-is-data-mapping) is the design. Skipping the research doesn’t skip the problems; it just moves them to production.

Relationship profiling earns its keep here too: it dictates load order. Customers before invoices, products before line items – dependency direction comes straight from the foreign-key profile.

When to profile (and re-profile)

→  Before every migration or new integration both sides

→  After any large import, acquisition, or system consolidation

→  Whenever the schema changes, a new custom field is an unprofiled field

→  Quarterly, on the objects that feed revenue and reporting

Where DBSync fits

A straight answer: DBSync is not a standalone profiling tool. What it does do is perform the structural half implicitly. Its schema-aware connectors run discovery at setup – detecting field types, surfacing custom objects, and mapping relationships automatically – which hands integration architects the structural profile they’d otherwise assemble by hand. And for content-level profiling of SaaS data, replication into a real database turns every check in Part 3 into a one-liner.

FAQs

What’s the difference between data profiling and data cleansing?

Profiling diagnoses; cleansing treats. Never cleanse unprofiled data, you’ll fix the wrong things in the wrong order.

Data profiling vs. data validation?

Profiling is a batch diagnosis of data that already exists. Validation checks rules at the moment of entry or transfer. Profiling tells you which validation rules you need.

Do I need a dedicated profiling tool?

Not to start. Read access, the six queries above, and a spreadsheet cover the first pass. Tools earn their keep when you need automation, scheduling, and hundreds of tables.

How long does data profiling take?

First pass on one object: about an hour. Full source-and-target profile for an integration project: a day or two, still the cheapest phase of the entire project.

What’s a good completeness rate?

100% for required fields, context for everything else. A 60%-complete “Fax” field is fine; a 60%-complete “Email” field is a crisis. Trend matters more than the absolute number.

Who should do the profiling?

An engineer or analyst runs it; a business owner interprets it. Profiling surfaces anomalies, only the business can classify them as errors, exceptions, or enterprise pricing.

How often should profiling run?

Before every migration and integration, after schema changes or big imports, and quarterly on business-critical objects. One-time profiles expire the day the data changes.