cross-system-identity-resolution

name	cross-system-identity-resolution
description	Use when stitching the same real-world entity — a customer account — across multiple source systems into one conformed spine; e.g. Salesforce + Planhat + Intercom + Slack. Operationalizes the deterministic-keys-before-fuzzy best practice: inventory candidate keys, build the precedence ladder, construct bridge_account_xref with confidence + match_method, quarantine unresolved records, run resolution_audit, and gate on stewardship review for low-confidence matches. K-12 LEAID confidence-tier ladder + district-name normalization rules added 2026-06-04.
last_reviewed	2026-06-04T00:00:00.000Z
confidence	high

Skill: cross-system-identity-resolution

Invoked by: etl-pipeline-engineer (owns the pipeline correctness) + ravenclaude-core/architect (owns the cross-system data model). Also consulted by dashboard-builder when a mart metric seems wrong and the root cause may be a resolution error.

When to invoke: Phase 0 of any multi-source build; whenever a new source system is added to an existing conformed spine; after a "the numbers don't match" incident where the suspected cause is a bad cross-system join.

Output: a populated bridge_account_xref table seeded with high-confidence rows, a resolution_audit dbt model running on schedule, a stewardship surface for low-confidence candidates, and dbt tests guarding the join spine.

The discipline (the floor, not the ceiling)

Every metric built on a wrong join is wrong, silently. Identity resolution is the #1 technical risk in a multi-source analytics build — both expert panels in the CS-health build plan agreed on this independently. The correct shape: exhaust deterministic options first (30 minutes of investigation), fall back to domain matching (imperfect but checkable), and treat name matching as a human-review queue, never an auto-trusted join.

Two-line rule:

1. No match without a record — every resolved and every unresolved record gets a row in bridge_account_xref
2. No metric off a name-only match without a review record — reviewed_by IS NOT NULL is a precondition for publishing

Step 1 — Inventory candidate keys per source system

Before writing any matching code, audit each source system for cross-reference fields. This takes 30 minutes and often eliminates 90% of the resolution problem.

Source system	Candidate key to check	How to check
Planhat	externalId on Company — designed to hold the Salesforce Account ID when the SFDC↔Planhat sync is configured	GET /companies for a sample; inspect externalId field
Intercom	company_id on Company — populated with Salesforce Account ID when Intercom's Salesforce integration is on	GET /companies for a sample; inspect company_id
Slack	No native account concept — see Step 4	N/A for the ladder; use seed table
HubSpot	hs_object_id, or salesforce_account_id custom property if SFDC sync is on	Check HubSpot company properties
Stripe	metadata.salesforce_account_id if set by the billing flow	Check Stripe customer metadata

Document what you find. Record in the Phase 0 notes: which systems have deterministic keys populated, which do not, and what action was taken (configured the sync, escalated for ops setup, proceeded to domain matching).

Step 2 — Build the resolution precedence ladder

Apply in order. Stop at the first level that resolves the record. Never skip to a lower-confidence level because it is faster to code.

Priority	Method	Confidence	Auto-resolve?	match_method value
1 — Deterministic	External ID / cross-reference field set by a configured integration	Exact	Yes	'external_id'
2 — Email domain	Derive primary domain per account; join on normalized domain (lowercase, strip www., strip known TLD suffixes)	Strong, not perfect	Yes — unless domain is shared/generic	'email_domain'
3 — Normalized name	Strip legal suffixes, lowercase, collapse whitespace, Levenshtein distance ≤ threshold	Weak	No — human review required	'name_fuzzy'
Unresolved	No match found at any level	None	N/A	'unresolved'

Email domain caveats: exclude known shared/generic domains (gmail.com, outlook.com, yahoo.com). Maintain a dim_account_domains(account_key, domain) table for accounts with multiple legitimate domains (post-acquisition, holding companies). Domain matching on a large consulting firm's domain will produce false positives.

Name fuzzy caveats: use normalized Levenshtein (or trigram similarity), not ILIKE '%acme%'. Set a threshold (e.g., similarity ≥ 0.85) and never auto-accept — every name-fuzzy candidate goes to the stewardship queue regardless of similarity score.

Step 3 — Construct bridge_account_xref

Every resolution — at any confidence level — is recorded in the bridge table. Unresolved records get a null account_key and are retained, never dropped.

-- transform/models/staging/bridge_account_xref.sql (or infra DDL)
create or replace table transform.bridge_account_xref (
    source          varchar not null,        -- 'planhat' | 'intercom' | 'slack' | 'hubspot' | 'stripe'
    source_id       varchar not null,        -- the source system's native primary ID
    account_key     varchar,                 -- FK to dim_account.account_key; NULL = unresolved
    match_method    varchar not null,        -- 'external_id' | 'email_domain' | 'name_fuzzy' | 'manual' | 'unresolved'
    confidence      varchar not null,        -- 'high' | 'medium' | 'low' | 'unresolved'
    similarity_score float,                  -- for name_fuzzy matches; null otherwise
    reviewed_by     varchar,                 -- email of reviewer; required before name_fuzzy used in metrics
    reviewed_at     timestamp,
    created_at      timestamp default current_timestamp,
    updated_at      timestamp default current_timestamp,
    primary key (source, source_id)
);

Seeding order:

1. Run the deterministic pass first — insert all external_id rows with confidence = 'high'
2. Run the domain pass for the remaining unresolved records — insert with confidence = 'medium'; flag shared/generic domains to stewardship queue
3. Run the name-fuzzy pass for any still-unresolved records — insert with confidence = 'low', account_key = NULL until reviewed
4. Insert remaining unresolved records explicitly with match_method = 'unresolved'

-- Example: deterministic pass for Planhat
insert into transform.bridge_account_xref (source, source_id, account_key, match_method, confidence)
select
    'planhat',
    p.planhat_id,
    a.account_key,
    'external_id',
    'high'
from stg_planhat__companies p
join dim_account a
    on p.external_id = a.sfdc_account_id   -- deterministic: Planhat externalId = Salesforce Account ID
where p.external_id is not null
  and a.sfdc_account_id is not null
on conflict (source, source_id) do nothing;   -- idempotent; don't overwrite existing rows

-- Unresolved records (those not matched above)
insert into transform.bridge_account_xref (source, source_id, account_key, match_method, confidence)
select 'planhat', p.planhat_id, null, 'unresolved', 'unresolved'
from stg_planhat__companies p
where p.planhat_id not in (
    select source_id from transform.bridge_account_xref where source = 'planhat'
)
on conflict (source, source_id) do nothing;

Step 4 — Slack channel → account mapping (separate mechanism)

Slack has no native account concept; the three-step ladder does not apply. Use the seed table mechanism instead.

See ../../knowledge/slack-as-data-source.md for the full slack_channel_account_map DDL, seeding process, and weekly diff script.

The key discipline: the Slack → account mapping is always human-confirmed. The diff script proposes; a human approves. Never auto-insert a channel-to-account mapping from naming-convention heuristics alone.

Step 5 — Quarantine and null-FK propagation

Unresolved records (account_key = NULL in the bridge) are retained, never dropped. Downstream models use an explicit WHERE account_key IS NOT NULL to exclude them from aggregated metrics. This makes the exclusion visible and auditable.

-- In mart_cs_health or equivalent: exclude unresolved AND unreviewed low-confidence joins
left join transform.bridge_account_xref xref
    on xref.source = 'planhat'
    and xref.source_id = p.planhat_id
where xref.account_key is not null               -- exclude unresolved
  and (
    xref.match_method != 'name_fuzzy'            -- auto-trusted methods
    or xref.reviewed_by is not null              -- OR name-fuzzy with human review
  )

A null FK in the mart signals "source record exists but account is unknown" — which is a data quality alert, not a missing record. A silent drop would make an unmatched account look like a healthy account with zero signals.

Step 6 — resolution_audit dbt model + alert

A dbt model that runs daily and alerts when resolution quality degrades. The >5% unresolved threshold comes directly from the build plan's guardrails.

-- models/marts/resolution_audit.sql
with summary as (
    select
        source,
        count(*) as total_records,
        sum(case when account_key is null then 1 else 0 end) as unresolved_count,
        round(100.0 * sum(case when account_key is null then 1 else 0 end) / count(*), 2)
            as unresolved_pct,
        sum(case when match_method = 'name_fuzzy' and reviewed_by is null then 1 else 0 end)
            as unreviewed_fuzzy_count
    from {{ ref('bridge_account_xref') }}
    group by source
),
slack_unmapped as (
    select channel_id, added_at
    from {{ ref('seed_slack_channel_account_map') }}
    where account_key is null
      and added_at < current_date - 7
)
select 'unresolved_pct_breach' as alert_type, source, unresolved_pct as value, null as channel_id
from summary
where unresolved_pct > 5

union all

select 'unreviewed_fuzzy_match' as alert_type, source, unreviewed_fuzzy_count, null
from summary
where unreviewed_fuzzy_count > 0

union all

select 'slack_channel_unmapped_gt_7d', 'slack', datediff('day', added_at, current_date), channel_id
from slack_unmapped

# _models.yml — warn severity so the pipeline continues but alert fires
models:
  - name: resolution_audit
    tests:
      - dbt_utils.expression_is_true:
          expression: "count(*) = 0"
          severity: warn
          config:
            error_if: ">= 1"

Wire the warn severity to a Slack/email alert channel. A rising unresolved percentage or an accumulating unreviewed-fuzzy queue are correctness risks, not outages — they need visibility, not a wake-up page.

Step 7 — Stewardship review surface

Low-confidence (name-fuzzy) match candidates queue to a stewardship view in the BI tool before they are published to any metric. The minimum viable stewardship surface:

Column to surface	Purpose
source, source_id	Identify the source record needing review
Source-system display name (e.g., planhat_company_name)	Human-readable context
Candidate account_key and dim_account.account_name	The proposed match
match_method, confidence, similarity_score	Basis for the proposal
reviewed_by, reviewed_at	Approval status
Approve / Reject action	Write back to bridge_account_xref

The approval action writes reviewed_by and reviewed_at to the bridge. Until that write, the name-fuzzy record's account_key is excluded from all mart metrics by the WHERE clause in Step 5.

Step 8 — Manual top-N review before launch

Before Phase 1 launch: manually audit the top ~20 accounts by ARR. For each account:

1. Confirm the correct Planhat company is mapped
2. Confirm the correct Intercom company is mapped
3. Confirm the correct Slack channel(s) are in slack_channel_account_map
4. Review the match_method and confidence for each mapping
5. Document the match evidence in a "pre-launch resolution sign-off" doc

This catches systematic resolution errors before they compound through a quarter of health-score data. The CS team typically knows their top accounts well; this review takes 30–60 minutes and is non-negotiable.

dbt tests that guard the join spine

Add these to the bridge_account_xref model's _models.yml:

models:
  - name: bridge_account_xref
    columns:
      - name: source
        tests:
          - not_null
          - accepted_values:
              values: ['planhat', 'intercom', 'slack', 'hubspot', 'stripe']
      - name: source_id
        tests:
          - not_null
      - name: match_method
        tests:
          - not_null
          - accepted_values:
              values: ['external_id', 'email_domain', 'name_fuzzy', 'manual', 'unresolved']
      - name: confidence
        tests:
          - not_null
          - accepted_values:
              values: ['high', 'medium', 'low', 'unresolved']
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [source, source_id]

Also add a singular test asserting no name-fuzzy row with account_key IS NOT NULL and reviewed_by IS NULL reaches the mart layer (the WHERE clause in Step 5 is the runtime gate; this test catches a missing WHERE clause):

-- tests/assert_no_unreviewed_fuzzy_in_mart.sql
-- Any name-fuzzy join without a reviewer is an accuracy risk; this catches a missing WHERE clause.
select b.source, b.source_id, b.account_key
from {{ ref('bridge_account_xref') }} b
join {{ ref('fct_account_health_snapshot') }} f on f.account_key = b.account_key
where b.match_method = 'name_fuzzy'
  and b.reviewed_by is null
{{ config(severity='error') }}

Anti-patterns this skill flags

• Writing a JOIN ON LOWER(TRIM(company_name)) = LOWER(TRIM(account_name)) directly in a dbt model — bypasses the bridge entirely; produces invisible duplicates with no audit trail
• Auto-publishing a metric for a record matched on normalized name without a human review record
• Dropping records that don't resolve — null FK, retain, alert, review
• Treating a rising unresolved percentage as "normal" and not investigating — it means source records exist that are invisible to every CS health metric
• Skipping the Phase 0 candidate-key inventory and jumping straight to fuzzy matching — the highest-value 30-minute investment in the whole build

---

K-12 LEAID matching (added 2026-06-04)

The base ladder (Steps 1–3) is domain-neutral. For K-12 EdTech engagements there is a higher-confidence deterministic anchor than email-domain: LEAID (Local Education Agency ID), the NCES-issued 7-digit identifier for every public school district in the United States. This section is additive — apply it inside Steps 1–3 when the engagement is K-12.

LEAID structure [verify-at-use — 2026-06-04]

• 7 digits total. First 2 = State FIPS code. A Texas district always starts with 48, Wisconsin with 55, California with 06, New York with 36, etc.
• Issued by the National Center for Education Statistics (NCES) via the Common Core of Data (CCD) survey.
• Updated annually. Changes on district consolidation/split (see § "District consolidation/split" below).
• NCES School ID = LEAID + SCHNO (12 digits total) — school-grain identifier.
• Public lookup: nces.ed.gov/ccd/districtsearch/. Programmatic flat-file: nces.ed.gov/ccd/ccddata.asp.

LEAID confidence-tier ladder

Layered on top of the base ladder in Step 2. T0–T2 are auto-resolve; T3 is human-review; T4 is reject.

Tier	Match logic	Confidence	Auto-resolve?	match_method value
T0 — Exact LEAID	SFDC Account.NCES_LEAID__c populated AND matches dim_lea.leaid	100%	Yes	'leaid_exact'
T1 — Deterministic compound	(state_fips, normalized_district_name, city) exact match against dim_lea	~95% [verify-at-use — 2026-06-04]	Yes (with audit log)	'leaid_compound'
T2 — Fuzzy district name	Levenshtein/Jaro on normalized name within state; threshold ≥ 0.92	85–95% [verify-at-use — 2026-06-04]	Yes (flag for review at first sync)	'leaid_fuzzy_high'
T3 — Probabilistic	Multi-signal: name + city + zip + enrollment-band agreement	70–85%	No — human review	'leaid_probabilistic'
T4 — Below threshold	<70% confidence on any signal combination	—	No — reject	'unresolved'

Threshold rationale: industry-standard threshold for the deterministic-vs-probabilistic boundary is 85–95% [verify-at-use — 2026-06-04]. For B2B district matching the cost of a wrong match is higher than consumer identity resolution (a wrong district = wrong cohort = wrong health score), so tighten thresholds at the high end of the industry range: 0.92 for the T2 fuzzy threshold instead of the consumer-standard 0.85.

Why tighter than consumer identity: the entity universe is smaller (~13,500 US public school districts), so the prior probability of two real districts having very similar names is higher than in consumer matching where names are drawn from a much larger distribution. False positives are more likely without tighter thresholds.

District-name normalization rules

Before any fuzzy match, normalize the district name. Apply in order:

1. Lowercase + collapse whitespace. " Chicago Public Schools " → "chicago public schools".
2. Drop common district-type suffixes (idempotent — strip any that appear):
   • "school district" → ``
   • "public schools" → ``
   • "public school district" → ``
   • "independent school district" → ``
   • "unified school district" → ``
   • "consolidated school district" → ``
   • "city school district" → ``
   • "central school district" → ``
   • "community school district" → ``
   • "regional school district" → ``
   • "isd" (with word boundary) → `` (Texas-heavy convention — "Plano ISD" → "plano")
   • "csd" (with word boundary) → ``
   • "usd" (with word boundary) → ``
3. Normalize "Saint" variants: "st." → "saint", "st " → "saint " (word boundary). "st. louis" and "saint louis" collapse to one form.
4. Normalize ampersands and "and": "&" → "and".
5. Strip punctuation (after the above replacements): apostrophes, periods, commas. Hyphens stay (they're often semantically meaningful — "winston-salem").
6. Collapse multiple spaces to one.
7. Strip leading/trailing whitespace.

-- A reference implementation as a Snowflake UDF
create or replace function normalize_district_name(name string)
returns string
language sql
as $$
    trim(
        regexp_replace(
            regexp_replace(
                regexp_replace(
                    regexp_replace(
                        regexp_replace(
                            regexp_replace(
                                regexp_replace(
                                    regexp_replace(
                                        regexp_replace(
                                            regexp_replace(
                                                regexp_replace(
                                                    regexp_replace(
                                                        regexp_replace(
                                                            lower(name),
                                                            '\\bindependent school district\\b', ''
                                                        ),
                                                        '\\bunified school district\\b', ''
                                                    ),
                                                    '\\bconsolidated school district\\b', ''
                                                ),
                                                '\\bcity school district\\b', ''
                                            ),
                                            '\\bcentral school district\\b', ''
                                        ),
                                        '\\bcommunity school district\\b', ''
                                    ),
                                    '\\bregional school district\\b', ''
                                ),
                                '\\bpublic school district\\b', ''
                            ),
                            '\\bschool district\\b|\\bpublic schools\\b|\\bisd\\b|\\bcsd\\b|\\busd\\b', ''
                        ),
                        '\\bst\\.\\s|\\bst\\s', 'saint '
                    ),
                    '&', 'and'
                ),
                '[\\.,'']', ''
            ),
            '\\s+', ' '
        )
    )
$$;

State-ID systems by state

LEAID is the federal identifier; states often issue their own. The state ID is sometimes the more useful key in single-state engagements because it's the one districts know.

State	State agency	State district ID name	LEAID prefix (FIPS)	Notes
California	CDE (Cal. Dept. of Education)	CDS code (14-digit county-district-school)	06	First 7 digits = district; full 14 = school.
Texas	TEA (Texas Education Agency)	CDN (County-District Number, 6-digit)	48	Maps deterministically to LEAID.
New York	NYSED (NY State Education Dept.)	BEDS code (12-digit)	36	First 6 = district; 12 = school.
Florida	FLDOE	District number (2-digit)	12	Often used standalone in FL contracts.
Illinois	ISBE (Ill. State Bd. of Education)	RCDTS (Region-County-District-Type-School, 15-digit)	17
Pennsylvania	PDE	AUN (Administrative Unit Number, 9-digit)	42
Ohio	ODE	IRN (Information Retrieval Number)	39
Michigan	MDE	District code (5-digit)	26
Georgia	GaDOE	District ID (3-digit)	13
North Carolina	NCDPI	LEA Code (3-digit)	37
Massachusetts	DESE	District Code (4-digit)	25	Often called "ORG code."
Washington	OSPI	District Code (5-digit)	53
Virginia	VDOE	Division Number (3-digit)	51	"Division" not "district" in VA.
Wisconsin	DPI	District Number (4-digit)	55
Indiana	IDOE	Corporation Number (4-digit)	18	"Corporation" not "district."
Tennessee	TDOE	System Number (3-digit)	47
Missouri	DESE-MO	District Code (6-digit)	29

[verify-at-use — 2026-06-04] — confirm per state. Each state agency publishes its own crosswalk to LEAID; ingest the crosswalk into a dim_state_district_id table when the engagement scope includes that state.

Implementation pattern — dim_lea materialization

• Seed source: seed_nces_districts.csv from NCES CCD flat-file (nces.ed.gov/ccd/ccddata.asp) — refreshed annually.
• Alternative source: an NCES Snowflake share if available, or an S3 pull from nces.ed.gov.
• Materialize monthly — district data changes annually but the refresh cadence is conservative.
• dbt model: dim_lea.sql materializes the typed dimension; int_district_match__sfdc_to_lea.sql is a stage-of-stages with one CTE per tier.

-- models/marts/dim_lea.sql
{{ config(materialized='table') }}
select
    leaid,                                                  -- 7-digit LEAID
    substring(leaid, 1, 2) as state_fips,
    state_abbreviation,
    district_name as district_name_official,
    normalize_district_name(district_name) as district_name_normalized,
    city,
    state,
    zip_code,
    enrollment_total,
    enrollment_band,
    effective_from,                                         -- SCD2
    effective_to,
    is_current
from {{ ref('seed_nces_districts') }}

-- models/intermediate/int_district_match__sfdc_to_lea.sql
with t0_exact as (
    select a.account_key, l.leaid,
           'leaid_exact' as match_method, 'high' as confidence, 1.00 as similarity_score
    from {{ ref('stg_salesforce__account') }} a
    join {{ ref('dim_lea') }} l on a.nces_leaid_c = l.leaid
    where a.nces_leaid_c is not null and l.is_current
),
unresolved_after_t0 as (
    select a.* from {{ ref('stg_salesforce__account') }} a
    where a.account_key not in (select account_key from t0_exact)
),
t1_compound as (
    select a.account_key, l.leaid,
           'leaid_compound' as match_method, 'high' as confidence, 0.95 as similarity_score
    from unresolved_after_t0 a
    join {{ ref('dim_lea') }} l
        on l.state_fips = substring(a.state_fips_code, 1, 2)
       and normalize_district_name(a.account_name) = l.district_name_normalized
       and lower(a.billing_city) = lower(l.city)
    where l.is_current
),
unresolved_after_t1 as (
    select a.* from unresolved_after_t0 a
    where a.account_key not in (select account_key from t1_compound)
),
t2_fuzzy as (
    select a.account_key, l.leaid,
           'leaid_fuzzy_high' as match_method, 'medium' as confidence,
           jarowinkler_similarity(normalize_district_name(a.account_name), l.district_name_normalized) as similarity_score
    from unresolved_after_t1 a
    join {{ ref('dim_lea') }} l on l.state_fips = substring(a.state_fips_code, 1, 2)
    where l.is_current
      and jarowinkler_similarity(normalize_district_name(a.account_name), l.district_name_normalized) >= 0.92
)
select * from t0_exact
union all select * from t1_compound
union all select * from t2_fuzzy

Surface match_method + similarity_score on the final mart row so dashboards can render the confidence to PSMs ("matched by name — 0.94 confidence").

District consolidation/split — the SCD2 gotcha

LEAIDs change on consolidation. When two districts merge, both old LEAIDs are retired and a new one issued; the historical fact data must still resolve to the new partner key.

Pattern:

• Keep dim_lea as SCD Type 2 (effective_from / effective_to / is_current).
• Don't embed LEAID directly in fact tables — fact tables hold partner_key.
• Join chain: fact → dim_partner → dim_partner_lea_link → dim_lea.
• dim_partner_lea_link is many-to-many (one partner may span multiple LEAIDs through history) and SCD2.
• The fact survives consolidation because partner_key is stable; the LEAID join walks through the link table to find the LEAID effective at the fact's date.

create table dim_partner_lea_link (
    partner_key      varchar not null,
    leaid            varchar not null,
    effective_from   date not null,
    effective_to     date,
    is_current       boolean,
    link_source      varchar,         -- 'sfdc_field' | 'manual_review' | 'nces_consolidation_event'
    primary key (partner_key, leaid, effective_from)
);

LEAID-specific anti-patterns

• Joining Account.account_name directly against dim_lea.district_name without normalization → false negatives on every "ISD" suffix.
• Embedding LEAID in fact tables → consolidation breaks historical joins. Use partner_key + the link table.
• Using LEAID without state FIPS → cross-state collisions are unlikely but possible. Always carry state FIPS in the join.
• Trusting a single LEAID per partner forever → multi-LEAID partners exist (charter networks, regional consortia, post-merger districts). Use the link table.
• Skipping the SCD2 on dim_lea → a consolidation event silently rewrites historical answers ("which district owned this contract in 2023?" gives the 2026 LEAID).

LEAID-specific dbt tests

models:
  - name: dim_lea
    columns:
      - name: leaid
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "length(leaid) = 7"
      - name: state_fips
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "length(state_fips) = 2"
  - name: dim_partner_lea_link
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [partner_key, leaid, effective_from]
      - dbt_utils.expression_is_true:
          expression: "(is_current = true and effective_to is null) or (is_current = false and effective_to is not null)"

Decision Tree: K-12 District Identity Resolution

When this applies: You are matching an SFDC Account (or Planhat Company) to a public-school-district identity for a K-12 EdTech engagement. The choice of tier controls whether the resolution is auto-published or human-reviewed.

Last verified: 2026-06-04 against NCES CCD docs, Cometly identity-resolution 2026 thresholds guidance, CustomerLabs deterministic-vs-probabilistic guide.

flowchart TD
    START[K-12 district match needed] --> Q1{SFDC Account.NCES_LEAID__c populated and matches dim_lea?}
    Q1 -->|YES| LEAF_A[T0 — Exact LEAID auto-resolve]
    Q1 -->|NO| Q2{State FIPS plus normalized district name plus city exact match?}
    Q2 -->|YES| LEAF_B[T1 — Deterministic compound auto-resolve with audit log]
    Q2 -->|NO| Q3{State-bounded Jaro-Winkler on normalized name greater-equal 0.92?}
    Q3 -->|YES| LEAF_C[T2 — Fuzzy high auto-resolve flag for first-sync review]
    Q3 -->|NO| Q4{Multi-signal — name plus city plus zip plus enrollment-band — combined greater-equal 0.70?}
    Q4 -->|YES| LEAF_D[T3 — Probabilistic stewardship review required no auto-resolve]
    Q4 -->|NO| LEAF_E[T4 — Reject mark unresolved surface in resolution_audit]

Rationale per leaf:

• Leaf A — T0 Exact LEAID — the strongest deterministic key. Requires: SFDC custom field NCES_LEAID__c populated and synced to the warehouse.
• Leaf B — T1 Deterministic compound — strong enough to auto-resolve when LEAID isn't populated. The compound key is hard to collide accidentally.
• Leaf C — T2 Fuzzy high — the 0.92 threshold is tighter than the consumer-identity standard because the K-12 entity universe is small enough that 0.85 produces too many false positives.
• Leaf D — T3 Probabilistic — multi-signal but never auto-resolve. Always stewardship review.
• Leaf E — T4 Reject — below threshold. Quarantine, alert, hand to ops team.

Tradeoffs summary table:

Tier	Auto-resolve?	Confidence	Use when
T0 Exact LEAID (A)	Yes	100%	LEAID field populated.
T1 Compound (B)	Yes (with audit)	~95%	LEAID missing; name + city + state available.
T2 Fuzzy high (C)	Yes (first-sync review)	85–95%	Name match only; tight similarity.
T3 Probabilistic (D)	No	70–85%	Multi-signal but ambiguous; needs human.
T4 Reject (E)	No	<70%	Unresolved; route to ops.

See also

• Best practice: ../../best-practices/resolve-identity-deterministic-keys-before-fuzzy.md — the named rule this skill operationalizes
• Skill: ../data-quality-tests/SKILL.md — the resolution_audit model is a cross-source reconciliation test in disguise; wire it to the same alert infrastructure
• Skill: ../dbt-project-scaffolding/SKILL.md — the dbt project layer that hosts the bridge model and resolution audit
• Knowledge: ../../knowledge/planhat-integration.md — Planhat externalId / sourceId keys; the SFDC bridge anchor
• Knowledge: ../../knowledge/intercom-integration.md — Intercom company_id as the Salesforce ID hook
• Knowledge: ../../knowledge/slack-as-data-source.md — the slack_channel_account_map seed-table pattern
• Knowledge: ../../knowledge/salesforce-integration.md — the Salesforce Account ID is the master key
• Knowledge: ../../knowledge/salesforce-cpq-integration.md — CPQ field mapping for ARR/TCV/renewal-date (the contract grain joined via account_key)

References

All URLs accessed 2026-06-04.

• https://nces.ed.gov/ccd/aadd.asp — NCES Common Core of Data district address file.
• https://nces.ed.gov/ccd/ccddata.asp — NCES CCD flat-file download.
• https://nces.ed.gov/ccd/districtsearch/ — NCES district search.
• https://nces.ed.gov/programs/edge/docs/EDGE_GEOCODE_PUBLIC_FILEDOC.pdf — NCES EDGE geocode file doc (LEAID + SCHNO structure).
• https://www.cometly.com/post/identity-resolution-marketing — Identity resolution 2026 (85–95% threshold guidance).
• https://www.customerlabs.com/blog/deterministic-vs-probabilistic-identity-resolution-guide/ — Deterministic vs probabilistic match guide.
• https://towardsdatascience.com/fuzzywuzzy-basica-and-merging-datasets-on-names-with-different-transcriptions-e2bb6e179fbf/ — FuzzyWuzzy / Levenshtein for name matching.
• https://xebia.com/blog/a-practical-guide-to-creating-slowly-changing-dimensions-type-2-in-dbt-part-1/ — SCD2 in dbt (the consolidation/split pattern).