Entity Resolution with Snowflake Cortex Search Batch

Resolve 20,000 messy Healthcare Provider (HCP) records against a 5,000-row golden registry using Snowflake's CORTEX_SEARCH_BATCH function — no ML training, no infrastructure, pure SQL.

The Problem

Provider / prescriber data arrives from multiple vendor systems (call reports, claims feeds, sample management, speaker programs, congress attendees), each with:

• Inconsistent naming ("Rob Smith" vs "Robert A. Smith, MD")
• Abbreviated specialties ("Hem/Onc" vs "Hematology/Oncology")
• Shortened affiliations ("MGH" vs "Massachusetts General Hospital")
• Missing fields, typos, and mixed formats

Traditional entity resolution requires complex rule-based matching or a dedicated MDM platform. This demo shows how Snowflake's Cortex Search solves it with semantic embeddings in a single SQL statement.

Results

Representative numbers from one sample run (results vary slightly by run and seed):

Metric	Value
Overall accuracy	~99.6% (19,918 / 20,000 correct in top-1)
High confidence (>=0.8)	~4,800 records, 100% accuracy
Medium confidence (0.5-0.8)	~15,000 records, ~99.6% accuracy
Low confidence (<0.5)	small tail (~160 records) — route to human review
State-blocked cascading pass	~99.9% accuracy on records with a known state
Batch runtime	~12 seconds for 20,000 records
Infrastructure	Zero (runs entirely in Snowflake)

How It Works

Golden HCP Registry (5K) ──> Cortex Search Service (semantic index)
                                        │
Dirty Incoming Records (20K) ──> CORTEX_SEARCH_BATCH ──> Matched Results + Confidence Scores

1. Index: A Cortex Search Service embeds the golden registry using snowflake-arctic-embed-m-v1.5
2. Match: CORTEX_SEARCH_BATCH runs all 20K queries in parallel via a LATERAL join
3. Score: METADATA$RESULT_DETAIL provides cosine similarity scores for confidence tiering
4. Block: The filter parameter constrains matches by state for higher precision

Files

File	Description
cortex_search_hcp_entity_resolution.ipynb	Main demo notebook (runs in Snowflake Workspaces with Container Runtime)
setup_infrastructure.sql	One-time infrastructure setup (compute pool, database, schema, stage, notebook object) + cleanup block
snowflake.yml	Project definition for snow notebook deploy
requirements.txt	Python dependency (faker) used by the notebook

Synthetic Data

All data is generated at runtime inside the notebook — nothing is checked into the repo.

• Names, cities, and ZIP codes are generated with Faker (fake.first_name(), fake.last_name(), fake.city(), fake.zipcode_in_state()), seeded for reproducibility.
• Medical specialties and credentials come from a small curated taxonomy (Faker has no medical-specialty provider).
• Affiliations are real, well-known public health systems, used purely so that abbreviation matching (MGH -> Massachusetts General Hospital) is recognizable. All provider names attached to them are fake.

Everything is deterministic given the fixed seeds (random, numpy, and Faker are all seeded with 42).

Prerequisites

• Snowflake account (any edition, AWS/Azure/GCP)
• A warehouse (default: COMPUTE_WH — change the WAREHOUSE variable in the notebook config cell if yours differs)
• SNOWFLAKE.CORTEX_USER database role granted to your role
• A compute pool for Container Runtime (created by setup_infrastructure.sql)
• faker (installed automatically by the notebook's first cell)

Quick Start

1. Run setup_infrastructure.sql to create the compute pool, database, schema, stage, and notebook object.
2. Upload the notebook to a Snowflake Workspace (or deploy with snow notebook deploy).
3. Connect the notebook to the compute pool with SYSTEM$BASIC_RUNTIME.
4. (Optional) Edit the DATABASE, SCHEMA, and WAREHOUSE variables in the config cell to match your environment.
5. Run all cells top-to-bottom.

The notebook is fully self-contained — it installs Faker, generates all synthetic data, creates the search service, runs batch matching, and analyzes results.

Key Concepts Demonstrated

• CORTEX_SEARCH_BATCH: High-throughput batch matching via LATERAL join
• Blocking rules: filter parameter constrains the search space (e.g., match only within the same state)
• Confidence scoring: METADATA$RESULT_DETAIL cosine similarity for auto-accept vs human-review routing
• Cascading strategy: Multi-pass matching (strict blocking first, then relaxed)
• Production pattern: Stream + Task for continuous incremental resolution

Cleanup

When you're done, uncomment and run the cleanup block at the bottom of setup_infrastructure.sql to drop the search service, notebook, tables, stage, schema, and compute pool.

References

• Cortex Search Overview
• Batch Cortex Search
• CREATE CORTEX SEARCH SERVICE
• Snowflake Notebooks in Workspaces

License

MIT — see LICENSE.