HubSpot Deduplication Across Three Systems: Survivorship, Identity and the Sync That Recreates Them

The duplicate that returns

A portal is deduplicated on a Friday and holds duplicates again by the following Thursday. The cleanup was competent, the merges were correct, and the records came back anyway.

That pattern means the duplicates are not being typed in. They are being created by an integration, and an integration that created them once will create them again on its next run no matter how thoroughly the portal is cleaned. Cleaning without fixing the source buys a week. Redman (1998) describes poor data quality as a cost borne continuously and recognised rarely, which is what makes this particular loop so easy to fund repeatedly.

This is the ordinary condition in any business running HubSpot beside an enterprise system and an operational platform: three systems, each holding a version of the same customer, each having minted its own identifier for it.

Why in-portal deduplication does not settle it

HubSpot's own duplicate management is good at what it is for. It surfaces likely matches on a fixed set of criteria and offers a merge, and for duplicates created by human entry that is usually enough.

It cannot settle a three-system estate, for a reason that has nothing to do with the tool. The question of which record should survive is not answerable from inside HubSpot. A company record created by a dispatch platform and a company record created by a finance system differ in which fields each one is authoritative about, and HubSpot can see neither system.

Merging without answering that first is a coin toss with a destructive outcome, because a merge resolves conflicting values to the surviving record and the other values are gone. Batini, Cappiello, Francalanci and Maurino (2009) separate data-quality work into assessment and improvement as distinct phases requiring distinct techniques, and the separation matters more here than usual: the improvement step is irreversible.

Survivorship is decided per object type

The instinct is to pick one system and declare it canonical for everything. It is simple, and on at least one object type it will be wrong.

An enterprise system is authoritative about a customer's legal entity, billing address, payment terms and account status, because finance is accountable for those. It knows nothing useful about a service department, which exists because a dispatcher created it to route a technician. A CRM is authoritative about the commercial relationship and about nothing financial.

The rule is therefore set per object type, and the merge direction follows from that assignment rather than from which record happens to be older or more complete.

Two consequences follow. The assignment belongs to the business rather than to whoever runs the migration, because only the business knows whether the dispatch platform or the finance system is right about what a department is. Pipino, Lee and Wang (2002) argue that data-quality assessment has to combine objective measurement with subjective judgement, and which system owns a department is exactly the judgement half. It also has to be written down and agreed before a single merge executes, because a merge cannot be reversed by agreeing afterwards.

The three-identifier write

The mechanism that creates integration duplicates is usually a partial identifier write, which stays invisible until it is drawn.

A record created by an early sync carries the enterprise identifier and the CRM identifier. A record created later carries the operational identifier and the CRM identifier. Neither carries the full set. When the integration next runs, it cannot match an incoming record against either of them with confidence, so it creates a third.

The step that converts a deduplication into a reconciliation is therefore writing every identifier onto the surviving record. A deduplicated record carrying one identifier is correct in one system and orphaned in the other two, which is a cheaper form of the same problem rather than a solution to it.

Fixing the thing that made them

The allocation of effort in this work is counterintuitive.

The merging is the visible part and the larger part by hours. The diagnosis that precedes it produces no visible change at all and is what makes the merging safe. The smallest part, frequently a couple of hours against a couple of days of cleanup, is the correction to the synchronisation logic.

The correction is the only part with a permanent effect. A cleanup without a source fix produces a portal that is correct on the day of handoff and degrades from the next sync onward.

Validation has to leave the system

A record can be perfectly deduplicated inside HubSpot and still carry an enterprise identifier belonging to a different customer. Nothing inside HubSpot can detect that.

The check that matters samples records and traces each one across all three systems, confirming the identifiers reconcile end to end rather than confirming that one portal looks tidy. Wang and Strong (1996) define data quality by fitness for the consumer's use, and the consumers here are a dispatcher, a finance clerk and a salesperson each believing they are looking at the same customer. A check confined to one system cannot establish that they are. Associations merit a spot-check afterwards, because the association graph is what a merge is most likely to damage quietly.

Controls worth insisting on

Four, and none of them is technically interesting. A confirmed export point before any merge runs, because the operation is destructive and the alternative to a backup is hope. Staged execution reviewed before running at scale, so that a rule which turns out to be wrong is wrong about twenty records rather than twenty thousand. An instruction to pause and re-estimate if the duplicate volume proves materially larger than what was observable at diagnosis, which is the honest response to discovering the job is not the job that was scoped. And a written survivorship rule per object type, signed off by whoever owns the data, before anything executes.

The choice of improvement technique is a decision in its own right rather than an implementation detail (Batini et al. 2009), and these four are what that decision looks like when the technique cannot be undone.

What this does not cover

This describes duplicates created by integrations between systems. Duplicates created by people entering records twice are a different problem with a different remedy, and that remedy is validation and a required-field discipline at the point of entry rather than a reconciliation project.

Nothing here establishes that deduplication improves a commercial outcome. It removes a known source of error from reporting and from outbound communication, and whether that changes a number depends on what the error was costing. Redman (1998) puts that cost on the operating account rather than on a hygiene line, although quantifying it at a particular firm is work in itself.

Three systems is also where this gets interesting. Two systems with one integration have a single identity question. Three have three pairwise relationships, and the survivorship complexity grows faster than the count of systems suggests.

Where to start

Not with a merge. The starting question is, for each object type that carries duplicates, which system is telling the truth about what that record is. Where that question has never been answered, it is the reason the integration cannot recognise its own output, and answering it is most of the work.

The related mechanics of in-portal deduplication, what HubSpot merges automatically and what a merge destroys field by field, are covered separately in the HubSpot duplicate management guide. The decisions that govern any integration crossing a system boundary are set out in the integration architecture white paper.

References

Batini, C., Cappiello, C., Francalanci, C., & Maurino, A. (2009). Methodologies for data quality assessment and improvement. ACM Computing Surveys, 41(3), 1–52. https://doi.org/10.1145/1541880.1541883

Pipino, L. L., Lee, Y. W., & Wang, R. Y. (2002). Data quality assessment. Communications of the ACM, 45(4), 211–218. https://doi.org/10.1145/505248.506010

Redman, T. C. (1998). The impact of poor data quality on the typical enterprise. Communications of the ACM, 41(2), 79–82. https://doi.org/10.1145/269012.269025

Wang, R. Y., & Strong, D. M. (1996). Beyond accuracy: What data quality means to data consumers. Journal of Management Information Systems, 12(4), 5–33. https://doi.org/10.1080/07421222.1996.11518099