How to Do Cross-Source Identity Resolution Without a CDP

Your customer exists in five places. jane.doe@acme.co in HubSpot. cus_R4kT9z in Stripe. user_id 11042 in your Postgres users table. A cookie ID in Mixpanel. A phone number in a Google Sheet someone brought back from a conference.

CDPs like Segment, mParticle, and Rudderstack promise to unify all of these into a single identity profile. They do it well. They also cost $50-100k/year, require instrumenting every event source with their SDK, and lock your identity graph inside their platform. For a 2,000-person enterprise, that's reasonable. For most teams under 50 people, it's a tax on a problem you can solve with SQL.

I've built identity resolution at three different companies. The pattern is always the same: match on what you have, score the match confidence, store the mapping, build a master view. The SQL is not complicated. The edge cases are.

What a CDP actually does

Strip away the marketing and a CDP does three things: collect events from your sources, resolve identities by matching users across those events, and activate audiences by pushing segments to downstream tools. Event collection is just tracking. Audience activation is reverse ETL. The only genuinely hard part is identity resolution. That's what we're building.

The identity problem, concretely

Here's what a real match looks like across four systems:

Same person. Four different email formats. Two have no phone. One has no email. A naive JOIN ON email = email misses HubSpot entirely because j.doe@ != jane.doe@.

Tier 1: Email matching with normalization

Email is still your best join key. But raw email equality drops 15-30% of matches due to casing, whitespace, and aliases. Always normalize before comparing.

-- Tier 1: Normalized email match across three systems
SELECT
    h.vid                    AS hubspot_id,
    s.id                     AS stripe_id,
    p.id                     AS app_user_id,
    LOWER(TRIM(h.email))     AS hs_email,
    LOWER(TRIM(s.email))     AS stripe_email,
    LOWER(TRIM(p.email))     AS app_email
FROM hubspot.contacts h
FULL OUTER JOIN stripe.customers s
    ON LOWER(TRIM(s.email)) = LOWER(TRIM(h.email))
FULL OUTER JOIN app_db.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;

This catches Jane.Doe@ACME.CO vs jane.doe@acme.co. It won't catch j.doe@acme.co vs jane.doe@acme.co — those are genuinely different addresses and treating them as equal produces false positives. Two gotchas: plus-addressed emails (jane+billing@acme.co = jane@acme.co) need the +... stripped before matching, and empty-string emails in HubSpot pass IS NOT NULL but fail every join — check for LENGTH(email) > 0 too.

Tier 2: Domain matching for B2B

When emails don't match at the address level, the domain often still tells you something. For B2B identity resolution — where you're matching accounts, not individuals — two contacts at @acme.co are likely the same company.

-- Tier 2: Domain-level match for unmatched records
SELECT
    h.vid                    AS hubspot_id,
    s.id                     AS stripe_id,
    SPLIT_PART(LOWER(TRIM(h.email)), '@', 2) AS hs_domain,
    SPLIT_PART(LOWER(TRIM(s.email)), '@', 2) AS stripe_domain
FROM hubspot.contacts h
JOIN stripe.customers s
    ON SPLIT_PART(LOWER(TRIM(h.email)), '@', 2)
     = SPLIT_PART(LOWER(TRIM(s.email)), '@', 2)
WHERE LOWER(TRIM(h.email)) != LOWER(TRIM(s.email))  -- already matched in Tier 1
  AND SPLIT_PART(LOWER(TRIM(h.email)), '@', 2)
      NOT IN ('gmail.com', 'yahoo.com', 'hotmail.com', 'outlook.com', 'icloud.com');

That NOT IN clause is critical. Without it, you'll merge every Gmail user into one mega-account. Domain matching only works for company domains. I keep a blocklist of ~30 free email providers; the five above cover 90% of cases.

Tier 3: Phone normalization

Phone numbers are the tiebreaker when email fails entirely. (415) 555-0142, +14155550142, 415.555.0142 are all the same number.

-- Tier 3: Phone match after stripping formatting
SELECT
    h.vid        AS hubspot_id,
    sh.row_num   AS sheets_row,
    h.phone      AS hs_phone_raw,
    sh.phone     AS sheets_phone_raw,
    RIGHT(REGEXP_REPLACE(h.phone, '[^0-9]', '', 'g'), 10)  AS hs_phone_norm,
    RIGHT(REGEXP_REPLACE(sh.phone, '[^0-9]', '', 'g'), 10) AS sheets_phone_norm
FROM hubspot.contacts h
JOIN sheets.leads sh
    ON RIGHT(REGEXP_REPLACE(h.phone, '[^0-9]', '', 'g'), 10)
     = RIGHT(REGEXP_REPLACE(sh.phone, '[^0-9]', '', 'g'), 10)
WHERE LENGTH(REGEXP_REPLACE(h.phone, '[^0-9]', '', 'g')) >= 10
  AND LENGTH(REGEXP_REPLACE(sh.phone, '[^0-9]', '', 'g')) >= 10;

RIGHT(..., 10) drops the country code so 14155550142 and 4155550142 both normalize to 4155550142. Works for US/Canada. International numbers need proper E.164 normalization upstream. Watch for short numbers — some systems store extensions or partials that happen to be 10 digits after stripping. Add HAVING COUNT(*) = 1 if you see a single phone matching too many records.

Building the master identity table

Once you've run all three tiers, store the results. One row per source record, grouped by a canonical ID.

CREATE TABLE identity_map AS
WITH email_matches AS (
    -- Tier 1: your email-matched pairs from above
    SELECT canonical_id, source, source_id, 'email' AS match_method, NULL AS confidence
    FROM tier1_results
),
domain_matches AS (
    -- Tier 2: domain-matched pairs, lower confidence
    SELECT canonical_id, source, source_id, 'domain' AS match_method, 0.70 AS confidence
    FROM tier2_results
),
phone_matches AS (
    -- Tier 3: phone-matched pairs
    SELECT canonical_id, source, source_id, 'phone' AS match_method, 0.85 AS confidence
    FROM tier3_results
)
SELECT * FROM email_matches
UNION ALL
SELECT * FROM domain_matches
UNION ALL
SELECT * FROM phone_matches;
 
-- Add a unique constraint so each source record maps to exactly one canonical ID
-- If you get a conflict, the record was already matched at a higher tier
ALTER TABLE identity_map ADD CONSTRAINT uq_source UNIQUE (source, source_id);

The confidence column matters. Email matches are definitive — NULL means certain. Domain matches at 0.70 need human review. Phone at 0.85 is high-confidence but not guaranteed. For anything customer-facing, filter on confidence IS NULL OR confidence > 0.80.

The unified customer view

With identity_map in place, the master record query picks one authoritative source per field:

SELECT
    im.canonical_id,
    COALESCE(h.firstname || ' ' || h.lastname, p.display_name, s.name) AS name,
    COALESCE(p.email, s.email, h.email)   AS email,
    h.company                              AS company,
    COALESCE(h.phone, p.phone)             AS phone,
    s.id IS NOT NULL                       AS has_stripe,
    h.vid IS NOT NULL                      AS has_hubspot,
    p.id IS NOT NULL                       AS has_app_account,
    MIN(im.confidence)                     AS lowest_match_confidence
FROM (SELECT DISTINCT canonical_id FROM identity_map) im
LEFT JOIN identity_map im_h ON im.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_s ON im.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_p ON im.canonical_id = im_p.canonical_id AND im_p.source = 'app_db'
LEFT JOIN app_db.users p ON im_p.source_id = CAST(p.id AS VARCHAR)
GROUP BY im.canonical_id, h.firstname, h.lastname, p.display_name, s.name,
         p.email, s.email, h.email, h.company, h.phone, p.phone,
         s.id, h.vid, p.id;

The COALESCE order encodes your business rules. Name from the CRM because sales keeps it current. Email from your app because the user controls it. Revenue from Stripe because it's the financial system of record. Flip them and you'll get stale data in customer-facing reports. lowest_match_confidence surfaces records where the weakest link is a fuzzy match — flag anything below 0.80 for manual review.

Where to run this

All the SQL above assumes your data is queryable in one session. You can land everything in a warehouse, but that means maintaining ingestion pipelines just to answer "who are my customers." Or export CSVs into DuckDB locally — free but painful to repeat weekly.

Fastero's Cross-Source DuckDB Store connects to Stripe, HubSpot, Postgres, and 30+ other sources, syncs on a schedule, and lets you write cross-source SQL directly. The identity resolution queries in this post run as-is. No pipelines, no warehouse.

Edge cases that will bite you

Merged contacts in HubSpot. When sales reps merge duplicates, the losing contact's vid disappears. If your identity_map references it, the JOIN returns NULL silently. Sync regularly and handle missing source records.

Stripe customers with no email. Customers created via the API without an email fall through every tier. Run SELECT COUNT(*) FROM stripe.customers WHERE email IS NULL and decide if the volume justifies special handling.

Case sensitivity in source IDs. Stripe IDs are case-sensitive. HubSpot vid is numeric. Postgres id might be UUID. Store everything as VARCHAR in identity_map and never apply LOWER() to source IDs — only to match keys like email.

The deduplication trap. Cross-source identity resolution is different from deduplication within a source. Don't use your cross-source identity map to deduplicate records inside HubSpot — that's a CRM hygiene problem with different rules. Conflating the two creates cascading errors.

I've seen teams spend six months evaluating CDPs when the identity resolution logic fits in 100 lines of SQL. The hard part was never the matching — it was getting the data into one query engine. Solve that and the rest is SQL joins and careful normalization.

Try Fastero free — connect Stripe, HubSpot, Postgres, and 30+ sources, then run cross-source identity resolution in SQL without a warehouse or a CDP. No credit card required.