What is data profiling? A practical guide before B2B enrichment

What is data profiling, and what does it leave undecided?

To define data profiling operationally, think of it as an evidence inventory. You examine the data you actually have before choosing what to clean, match, enrich or test. IBM’s What is data profiling? describes structure, content and relationship discovery. These are useful distinctions for an enterprise CRM extract: what shape the fields take, what their values look like and how records connect.

Structure covers column names, types, formats and nesting. Content covers missingness, frequency, ranges and suspicious values. Relationships cover keys, references and expected cardinality between records. A contact table can look complete while referring to company IDs absent from the company table. A revenue column can be numeric while combining currencies or parent-group and subsidiary figures. Profiling makes those questions visible.

It is different from cleansing, which changes values; matching, which decides whether records refer to the same entity; and assessment, which judges fitness for a specified use. A profile might reveal that 15% of rows share repeated source keys. It cannot by itself determine which CRM records should merge or which record should survive. Those decisions require additional evidence and authorised rules.

The boundary matters economically. Requesting enrichment for every row can pay for fields already held, repeat the same job and send inputs the selected API cannot use. Yet excluding every imperfect row can remove the companies you most need to understand. The goal is a defensible work queue with explicit unknowns, not the highest-looking quality percentage.

Begin with a frozen extract and an explicit unit of analysis

Start with one intended operation: for example, finding a missing work email for existing CRM contacts. Record the extract time, source systems, filters, permitted purpose, row count and relevant schema version. Assign an immutable source-row reference so an analyst can trace a profile finding back to the original extract without exposing personal data in a report.

Then name the denominator. A source row, CRM contact ID, person, employment relationship, company and requested field are different units. Two rows sharing a contact ID may be duplicate work for this run. Two different contact IDs may still represent one person. A person can legitimately have more than one employment relationship. Do not call a count of distinct email strings a count of unique people.

Keep at least three totals: input rows, distinct stable source-record keys and eligible lookup operations. Add unique resolved entities only after identity resolution supports that claim. If email and phone are separate requested fields, report them separately rather than presenting two returned fields as two enriched people.

Freeze the source values for diagnosis. Create derived comparison fields where needed, but preserve the originals and the transformation rules. Trimming surrounding whitespace for a missingness check is different from overwriting the CRM. Removing punctuation from an identifier may destroy meaning; coercing an identifier into a number can lose leading zeroes. A profile should expose such transformations, not conceal them.

Separate missing, malformed and incorrect values

A database null, an empty string, whitespace and a placeholder such as “not provided” can all represent missing information, but they are not necessarily stored or counted the same way. Agree a field-specific missing-value dictionary. A zero can be a meaningful value in one field and an undocumented placeholder in another; do not classify it globally as missing.

PostgreSQL 18’s aggregate-function documentation distinguishes count(*), which counts input rows, from count(expression), which counts non-null expression values. That difference does not remove empty strings or business placeholders. Make the denominator and the missingness expression explicit in your query, workbook or profiling configuration so another analyst can reproduce the result.

An email can fit an address format while belonging to someone who left the company. A phone can parse while being an unverified switchboard rather than the requested mobile. A company domain can be well formed but refer to a parent rather than the intended subsidiary. These are factual or semantic questions, not syntax defects. Keep “passes format rule” distinct from “correct for this record”.

The missingness chart helps choose where to investigate, but it does not prescribe buying every absent field. A team routing inbound leads may need a company identifier and country but no mobile number. Another workflow may need a current employer before any contact-channel lookup. Rank gaps by the decision they block, not simply by how large their bars are.

Use three complementary data profiling techniques

Column profiling examines one field at a time: null and blank shares, type conformance, value lengths, minimum and maximum values, frequent values and distinct-value counts. Look for impossible dates, country-code variants, unexpectedly long strings and a single placeholder dominating a column. Treat those as diagnostic clues; a rare value can be legitimate.

Cross-column profiling checks dependencies within a record. Does a country-specific identifier have the country needed to interpret it? Does a revenue amount have a currency, period and entity scope? Does an employment-end date precede its start date? Is a contact linked to a company through a stable key or merely a free-text name? A column can be individually complete while the combination is unusable.

Cross-table profiling examines relationships. Count contacts whose company key has no corresponding company record; keys repeated where uniqueness is expected; and unexpected one-to-many joins. A join that increases rows can be legitimate, but it can also multiply enrichment requests. Compare row counts before and after joins and state the intended relationship rather than assuming one row per person.

Microsoft’s Power Query documentation distinguishes distinct values from unique values: the former counts different values, while the latter counts values appearing only once. Neither term automatically means unique entities. A repeated company name can describe different legal entities; a repeated CRM key can reveal an export issue. Label your reports with the actual metric instead of using “unique” loosely.

Profile evidence dates as well as values. Count missing source dates, future dates and concentrated timestamps suggesting a bulk import. Retrieval time tells you when your system obtained a record; it does not establish when employment or a mailbox was last checked. Profiling can reveal that dates are unavailable, but it cannot reconstruct them from a recent download.

How is data profiling done before an enrichment run?

After the descriptive checks, build a lookup-eligibility ledger for the specific operation. Give each source row one primary disposition, using an ordered first-applicable-reason rule. Keep secondary observations separately. Without this ordering, a row that is both suppressed and missing an identifier can be counted twice, and the reported exclusions will not reconcile to the extract.

For a missing-work-email job, a practical sequence is: outside the permitted job; already has an accepted requested field; repeats a stable source-record key already scheduled; lacks a supported identifier pattern; or eligible for lookup. The order is a declared operational choice, not a universal standard. Store it with a version so changes between runs are explainable. Namespace stable keys by source system. Compare the requested-field and identifier values before treating repeated keys as redundant; conflicting versions need review rather than arbitrary first-row retention. The 150 repeated-key exclusions in this example have identical relevant values.

In this separate illustrative job, 300 of 1,000 source rows become eligible lookup operations. That is an input-planning result, not a 30% provider match rate. No provider has been called, no email has been found and no person has been proved unique. The 150 repeated-key rows represent redundant work under this job’s key rule; the remaining rows may still contain unresolved duplicate entities.

For a real run, retain the reason code, source-row reference, requested field, supported identifier pattern and relevant policy version. Reconcile the dispositions back to the input total before release to an orchestration layer. Do not silently discard excluded rows: an unsupported input may need a different authorised discovery step rather than permanent deletion.

Split the profile by source, market and collection process

An aggregate can hide the exact problem enrichment is meant to solve. Break the relevant checks down by source system, import batch, geography, record age and collection method where those attributes are available and appropriate. Compare the same definition and denominator in each slice. Avoid mixing an account-level rate in one group with a contact-field rate in another.

In this independent example, company-domain presence is 90% for source A and 40% for source B. Because source A contributes most rows, the overall figure is 80%. The useful next action is to inspect source B’s capture or mapping process. Calling the whole file “80% complete” conceals that a particular import is missing a majority of its domain values. Presence still says nothing about whether those domains are correct.

Segment comparisons also need context. A source may intentionally omit a field because its collection process does not require it. A market can use different identifier conventions. A new connector may send empty strings where the old connector sent nulls. Investigate the underlying contract before labelling a group lower quality or purchasing enrichment to repair what is actually a mapping change.

Track both volume and composition between runs. If the share of source B increases, an overall completeness rate can fall even when neither source changes. Conversely, a shift towards the cleaner source can improve the headline while a faulty integration remains untouched. Preserve slice-level counts so the team can distinguish data drift from a changing mix of records.

Know what your sample can and cannot reveal

Large files tempt analysts to profile whichever rows load first. Microsoft’s Using the data profiling tools (updated 28 August 2025) states that Power Query profiles the first 1,000 rows by default and provides a switch to profile the entire dataset. If the extract is sorted by country, import date or source, those first rows can be a poor representation of the whole file.

Use a full scan for inexpensive structural checks where practical. For expensive inspection, document sampling method, sample size, extraction time, included strata and excluded records. Stratify deliberately when small but important markets or source systems would be underrepresented. Do not present a convenience sample as a random one.

The calculation illustrates a limitation, not a measured defect rate. Under its independent-draw assumption, and assuming every sampled defect is detected, a 50-record sample has about a 60.5% chance of seeing no defects even when the assumed underlying defect probability is 1%. Increasing the sample helps under that model, but cannot repair a sample selected only from the cleanest import. A finite extract sampled without replacement requires a different calculation.

A profile with no observed defects therefore means “none observed under these checks in this sample”, not “the dataset is accurate”. Some defects are invisible to structural checks regardless of sample size: stale employment, a plausible but wrong company match, or a mailbox attached to the wrong person. Those require separate truth evidence and a defined correctness test.

Choose data profiling tools by the questions they can reproduce

SQL, a data-preparation interface, a dataframe workflow and a dedicated quality platform can all contribute. The right choice depends on data volume, permitted access, repeatability, relationship checks and the team that must maintain the profile. A visually impressive dashboard is less useful than a small report whose counts reconcile and whose rules can be rerun after a connector changes.

Keep raw contact records inside authorised systems. Share aggregate findings and controlled examples with the people making the decision. A profile does not need to reproduce personal email addresses or phone numbers in a slide deck to explain a malformed-field pattern. If an analyst needs row-level inspection, retain restricted references rather than widening access to the full extract.

Turn the profile into a bounded enrichment brief

The hand-off should specify the requested fields, eligible operation count, supported inputs, excluded reasons and unresolved questions. Include expected request volume and a spending cap using the actual provider contract. Distinguish nominal price per request or credit from cost per usable result. The latter can only be calculated after returned candidates are evaluated, duplicates handled and permitted outcomes confirmed; profiling alone cannot predict it.

Keep downstream failure cases visible in the brief. Ambiguous people need identity review. Stale employment needs current relationship evidence. Catch-all domains remain uncertain mailbox evidence. Email verification is evidence about an address at a point in time, not a guarantee of delivery. Unverified numbers must not become verified mobiles. Conflicting providers require comparison of field scope and dates. Missing fields remain unresolved. API errors and timeouts belong in an execution ledger, rather than being counted as bad input or completed enrichment.

TargetWise’s current documentation describes contact and company enrichment through REST and MCP, with different supported identifier patterns for different operations. Contact requests can ask for email and phone separately, and missing requested fields are reported as unresolved. Use the relevant operation’s schema to design the input profile; do not assume a company identifier is also a valid person identifier.

The documentation does not promise automatic duplicate suppression or idempotent enrichment, and current company documentation says CSV and asynchronous batch uploads are unavailable. A team orchestrating many individual calls therefore needs its own job ledger, concurrency and retry controls. Profiling prepares that ledger; it does not replace those controls or prove that every returned field meets the eventual use policy.

For deeper work after profiling, use the existing B2B data matching guide for identity decisions and the data quality assessment framework for a release decision. Keep those stages separate so a descriptive input report is never mistaken for approval to merge records or activate contacts.