How to Deduplicate Data Across Multiple Sources

Deduplication inside a single table is a solved problem. GROUP BY email, COUNT(*) > 1, pick a winner, delete the rest. We covered that workflow in our duplicate records guide.

Cross-source deduplication is a different beast. You have the same customer in four systems, and none of them agree on how to identify her:

• Stripe knows her as cus_N8kL2x, keyed by jane.smith@acme.io
• HubSpot has contact vid 48201, email jsmith@acme.io, name "Jane Smith"
• Postgres stores user_id 7734, email jane.smith@acme.io, display name "J. Smith"
• Google Sheets has a row with "Jane D. Smith", phone "(415) 555-0192", no email

Four records. One customer. No shared key. This is the identity resolution problem, and it shows up the moment you try to build a cross-source dashboard or reconcile revenue across systems.

Start with email. It gets you 70%.

Email is the closest thing to a universal join key across SaaS systems. But you can't just JOIN ON email = email and call it done. Casing, whitespace, and plus-addressing will silently drop matches.

-- Cross-source email match: Stripe + HubSpot + Postgres
SELECT
    s.id     AS stripe_id,
    h.vid    AS hubspot_vid,
    p.id     AS postgres_user_id,
    s.email  AS stripe_email,
    h.email  AS hubspot_email,
    p.email  AS postgres_email
FROM stripe.customers s
FULL OUTER JOIN hubspot.contacts h
    ON LOWER(TRIM(h.email)) = LOWER(TRIM(s.email))
FULL OUTER JOIN postgres.users p
    ON LOWER(TRIM(p.email)) = COALESCE(
        LOWER(TRIM(s.email)),
        LOWER(TRIM(h.email))
    )
WHERE COALESCE(s.email, h.email, p.email) IS NOT NULL;

LOWER(TRIM()) handles "Jane.Smith@Acme.io " vs "jane.smith@acme.io". That's table stakes. The real problems are structural.

Email is not a universal key

I assumed it was for about two years. Then I ran a reconciliation across Stripe and HubSpot for a company with 3,000 customers and found 400+ that didn't match by email. The reasons:

• Sales entered the deal contact's personal email in HubSpot. Billing used the company's ap@ address in Stripe.
• A customer changed their email in one system but not the others.
• The Google Sheet had no email at all, just a name and phone number from a trade show.

So email gets you to ~70%. You need fallbacks for the rest.

Fuzzy name matching for the gaps

When emails don't match, names are your next signal. But "Jane Smith" in HubSpot, "J. Smith" in Postgres, and "Jane D. Smith" in Google Sheets won't pass an equality check.

DuckDB has jaro_winkler_similarity built in, which works well for person names:

-- Fuzzy name matching across HubSpot and Postgres
-- for records that didn't match on email
SELECT
    h.vid           AS hubspot_vid,
    p.id            AS postgres_user_id,
    h.firstname || ' ' || h.lastname AS hubspot_name,
    p.display_name  AS postgres_name,
    jaro_winkler_similarity(
        LOWER(h.firstname || ' ' || h.lastname),
        LOWER(p.display_name)
    ) AS name_score
FROM hubspot.contacts h
CROSS JOIN postgres.users p
WHERE LOWER(TRIM(h.email)) != LOWER(TRIM(p.email))  -- not already matched
  AND jaro_winkler_similarity(
        LOWER(h.firstname || ' ' || h.lastname),
        LOWER(p.display_name)
  ) > 0.85
ORDER BY name_score DESC;

A threshold of 0.85 catches "Jane Smith" vs "J. Smith" (score ~0.87) while rejecting "Jane Smith" vs "John Smith" (score ~0.78). You'll want to tune this depending on your data. Below 0.80, false positives start piling up fast.

Name-only matching gets unreliable with common names. "John Smith" appears in every system and half the time it's a different person. That's where you combine name with company: if the name is a fuzzy match AND the company domain matches, confidence goes way up.

Phone number normalization

Phone numbers are the third fallback, and they're messier than names. Your Postgres table has +14155550192. HubSpot has (415) 555-0192. The Google Sheet has 415.555.0192.

Strip everything except digits, then compare the last 10:

-- Phone normalization: strip formatting, compare last 10 digits
SELECT
    h.vid        AS hubspot_vid,
    p.id         AS postgres_user_id,
    p.phone      AS raw_phone,
    REGEXP_REPLACE(p.phone, '[^0-9]', '', 'g') AS digits_only,
    RIGHT(REGEXP_REPLACE(p.phone, '[^0-9]', '', 'g'), 10) AS normalized_phone
FROM postgres.users p
JOIN hubspot.contacts h
    ON RIGHT(REGEXP_REPLACE(p.phone, '[^0-9]', '', 'g'), 10)
     = RIGHT(REGEXP_REPLACE(h.phone, '[^0-9]', '', 'g'), 10)
WHERE LENGTH(REGEXP_REPLACE(p.phone, '[^0-9]', '', 'g')) >= 10
  AND LENGTH(REGEXP_REPLACE(h.phone, '[^0-9]', '', 'g')) >= 10;

RIGHT(..., 10) drops the country code. A US number stored as 14155550192 or 4155550192 both become 4155550192. This breaks for countries where local numbers are shorter than 10 digits, so if you operate internationally, you'll need a more sophisticated normalization. For US/Canada, the last-10 approach is good enough.

The fallback hierarchy

Here's the order that works in practice:

1. Email (exact after LOWER/TRIM) — catches ~70%
2. Phone (last 10 digits) — catches another ~10-15%
3. Name + company (fuzzy, threshold 0.85+, same domain) — catches ~5-10%
4. Manual mapping — the rest

You run each tier only on records that didn't match in a higher tier. The identity resolution table stores the result:

-- Identity resolution mapping table
CREATE TABLE identity_map (
    canonical_id   VARCHAR PRIMARY KEY,  -- your internal unified ID
    source         VARCHAR NOT NULL,     -- 'stripe', 'hubspot', 'postgres', 'sheets'
    source_id      VARCHAR NOT NULL,     -- id in the source system
    match_method   VARCHAR NOT NULL,     -- 'email', 'phone', 'name_fuzzy', 'manual'
    match_score    DECIMAL(5,4),         -- NULL for exact matches, 0-1 for fuzzy
    verified       BOOLEAN DEFAULT FALSE,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source, source_id)
);
 
-- Example rows:
-- ('canon_001', 'stripe',   'cus_N8kL2x', 'email',      NULL,   true,  ...)
-- ('canon_001', 'hubspot',  '48201',      'email',      NULL,   true,  ...)
-- ('canon_001', 'postgres', '7734',       'email',      NULL,   true,  ...)
-- ('canon_001', 'sheets',   'row_42',     'name_fuzzy', 0.91,   false, ...)

The verified flag matters. Fuzzy matches should start unverified. A human reviews the low-confidence matches, marks them verified, and now the system knows. Over time, the unverified pile shrinks to near zero.

The master record: one row per customer

Once you have the mapping table, build the unified view. The master record pattern picks one source of truth per field because no single system has all the right data:

• Name from the CRM (sales keeps it current)
• Email from your auth system (the user controls it)
• Revenue data from billing (Stripe is the financial record)
• Phone from wherever it exists

-- Unified customer table: one row per real customer
SELECT
    base.canonical_id,
    -- Name: prefer CRM, fall back to auth, then billing
    COALESCE(
        h.firstname || ' ' || h.lastname,
        p.display_name,
        s.name
    ) AS name,
    -- Email: prefer auth system (user-controlled), then billing, then CRM
    COALESCE(p.email, s.email, h.email)   AS email,
    -- Company: CRM is the authority
    h.company,
    -- Phone: take whatever exists
    COALESCE(p.phone, h.phone)            AS phone,
    -- Revenue: Stripe is the financial record
    s.currency,
    s.balance,
    -- Source tracking
    s.id  IS NOT NULL AS in_stripe,
    h.vid IS NOT NULL AS in_hubspot,
    p.id  IS NOT NULL AS in_postgres
FROM (SELECT DISTINCT canonical_id FROM identity_map) base
LEFT JOIN identity_map im_s ON base.canonical_id = im_s.canonical_id AND im_s.source = 'stripe'
LEFT JOIN stripe.customers s ON im_s.source_id = s.id
LEFT JOIN identity_map im_h ON base.canonical_id = im_h.canonical_id AND im_h.source = 'hubspot'
LEFT JOIN hubspot.contacts h ON im_h.source_id = CAST(h.vid AS VARCHAR)
LEFT JOIN identity_map im_p ON base.canonical_id = im_p.canonical_id AND im_p.source = 'postgres'
LEFT JOIN postgres.users p   ON im_p.source_id = CAST(p.id AS VARCHAR);

The COALESCE order is the opinionated part. You're saying "for name, I trust HubSpot first." That's a decision you make once and document, not something you leave ambiguous. If two systems disagree on a customer's name, the master record picks whichever you've designated as authoritative for that field.

The in_stripe / in_hubspot / in_postgres flags are useful for coverage analysis. A customer in HubSpot but not Stripe is a deal that closed but never got billed. A customer in Stripe but not HubSpot is revenue you can't attribute to a deal. Both are worth investigating.

Running this across sources

All five queries above assume you can query Stripe, HubSpot, and Postgres in the same SQL session. That's not a given if your data lives in separate systems.

The traditional answer is a warehouse: land everything in Snowflake or BigQuery, run your queries there. That works, but it means maintaining three data pipelines just to answer "who are my customers."

Fastero's cross-source DuckDB store pulls data from your connected sources into a single DuckDB instance. You connect Stripe, HubSpot, Postgres, and Google Sheets, and you can write cross-source SQL against all of them without setting up Fivetran or Airbyte. The identity resolution mapping table lives in DuckDB alongside the source data, and you can schedule the resolution queries to run on sync.

That said, the SQL patterns in this post work anywhere you can get the tables into one query engine. DuckDB locally, a warehouse, even a Jupyter notebook with multiple database connections. The identity resolution logic is the same regardless of where it runs.

Gotchas I've hit

Duplicate canonical IDs. If your fuzzy matching is too aggressive, you'll merge two different people into one canonical ID. This is worse than having duplicates because now you're mixing their data. Start conservative (high thresholds) and loosen only after reviewing false negatives.

Email changes over time. A customer changes their email in Postgres but not in Stripe. Your next resolution run creates a new canonical ID instead of updating the existing one. The fix: always check the mapping table for existing canonical IDs before creating new ones. If source=postgres, source_id=7734 already maps to canon_001, update the email, don't create canon_002.

The Google Sheets wildcard. Sheets data has no enforced schema. Someone types "Acme Corp" in one row and "Acme Corporation, Inc." in another. These are the same company. You need company name normalization (strip "Inc.", "LLC", "Corp.", "Ltd.") before fuzzy matching, or you'll miss obvious matches.

Try Fastero free — connect Stripe, HubSpot, Postgres, and Sheets in one place and run cross-source identity resolution without a warehouse. No credit card required.