Skip to content

About

Context Is Not Always King: An Empirical Study of Adversarial Precision in Column-Name Entity Matching

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

5 Commits

Folders and files

Repository files navigation

EntityMatching — Reproducibility Package

Companion repository for:

Context Is Not Always King: An Empirical Study of Adversarial Precision in Column-Name Entity Matching Jeyaraman Ramalingam

A controlled, four-tier empirical comparison of column-name entity matching methods — string similarity, embedding similarity, direct LLM classification, and context-augmented LLM classification — evaluated against a fully synthetic corpus modeling a fictional utility-company merger.

This repository contains the corpus generator, all SQL used to compute and score every tier, and the figures used in the paper. No real data, no real company names, and no employer-affiliated information appear anywhere in this repository — the corpus is entirely synthetic.

The headline result: added context (Tier 4) raises precision but collapses recall relative to Tier 3 — context is not always king.


1. What's in this repo

Path Purpose
python/generate_entity_matching_corpus.py Generates the 20-entity × 5-variant corpus, 5 adversarial pairs, source-table DDL, and deterministic sample values
python/generate_figures.py Generates the two figures used in the paper from real measured results
sql/00_create_tables.sql Synthetic source-system table DDL (Meridian / Fieldstone tables)
sql/01_ground_truth_and_reference_tables.sql Ground truth, candidate-pair, and true-synonym reference tables
sql/02_tier1_string_similarity.sql Tier 1: Jaro-Winkler / Levenshtein
sql/03_tier2_embedding_similarity.sql Tier 2: AI_EMBED + cosine similarity
sql/04_tier3_llm_classification.sql Tier 3: direct LLM classification (includes both the failed v1 prompt and the corrected v2 prompt, for reproducibility of the reported prompt-sensitivity finding)
sql/05_tier4_context_augmented.sql Tier 4: context-augmented LLM classification
sql/06_streaming_ingest_tables.sql DDL for the streaming extension: schema-evolution-enabled ingest tables + arrival log
python/streaming_ingestion.py Streaming producer — Snowpipe Streaming, phased schema evolution (§10)
python/streaming_matcher.py Streaming matcher — live Tier 3 + Tier 4 classification on each new arrival (§10)
python/tier4_backfill.py Standalone Tier 4 backfill for arrivals already logged (§10)
corpus/entities.csv 20 entities × 5 naming variants (100 rows)
corpus/adversarial_pairs.csv The 5 constructed adversarial pairs with origin stories
corpus/sample_values.csv Deterministic, seeded sample values used for Tier 4
figures/ The two figures included in the paper
requirements.txt Empty for corpus generation (stdlib only); lists matplotlib/numpy for regenerating figures
LICENSE MIT

2. The scenario

All data is invented for this study. Meridian Power (a large, fictional utility with modern naming conventions) acquires Fieldstone Electric Cooperative (a small, fictional cooperative with legacy, aggressively abbreviated naming conventions). The merger gives every naming collision in the corpus a plausible origin story rather than an arbitrary one — see corpus/adversarial_pairs.csv for each pair's.

No real company, product, dataset, employer system, or proprietary taxonomy is represented anywhere in this repository.


3. Research question

When two schemas are merged, an automated matcher must decide, for every pair of column names, whether the two columns denote the same logical entity (true synonyms — one column redundant with the other) or different entities that merely look or sound alike.

The study measures how four families of matcher behave on a corpus deliberately stocked with hard negatives:

Tier Method Metered cost
1 String similarity — Jaro-Winkler, Levenshtein none (native SQL)
2 Embedding similarity — AI_EMBED + cosine ~100 embedding calls
3 Direct LLM classification — AI_CLASSIFY on the bare names ~1,081 calls
4 Context-augmented LLM classification — names + table + sample values ~1,081 calls

4. Corpus construction

Generated by python/generate_entity_matching_corpus.py, standard library only, fully deterministic (SEED = 20260901).

4.1 Base entities and naming variants

20 base grid/utility entities (meter identifier, substation identifier, feeder identifier, …). Each is expressed in 5 naming conventions:

Variant Convention Example
full_english upper snake, spelled out METER_ID
snake_case Meridian modern meter_id
pascal_case Meridian modern, alt style MeterID
legacy_abbrev Fieldstone legacy, truncated MTRNUM
camel_case API / portal style meterIdentifier

→ 100 naming instances; every pair of variants within an entity is a true synonym. These are the positives — the recall denominator.

4.2 Adversarial pairs

Five constructed hard-negative pairs, one per failure pattern. Each is two genuinely different entities whose post-merger names are lexically or semantically close enough to fool a matcher. Full origin stories in corpus/adversarial_pairs.csv.

Pattern Pair Why it is hard
Prefix/Suffix Overlap OUTAGE_ID vs OUTAGE_EVENT_ID look like synonyms, differ in granularity (whole outage vs. one logged event)
Type Qualifier Confusion TRANSFORMER_ID vs TRANSFORMER_TYPE_ID a specific unit vs. an equipment-class lookup
Temporal/Lifecycle Confusion OUTAGE_START_TIME vs RESTORATION_TIME same record, same datatype, co-occur — opposite ends of one lifecycle
Abbreviation Collision XFMR_ID vs XFMR_LOC same 4-char legacy prefix, second is a location not an identifier
Role-Reversal UPSTREAM_FEEDER_ID vs DOWNSTREAM_FEEDER_ID identical except one directional word carrying all the meaning — near worst case for string and embedding matchers

4.3 Deterministic sample values

write_sample_values_csv() emits 5 seeded sample values per column (corpus/sample_values.csv), used only as Tier 4 context. Modern variants get prefixed alphanumeric IDs (MTR-8548), legacy variants get bare numerics (8548), and adversarial-only fields get values structurally typical of what that genuinely different entity would hold (e.g. TRANSFORMER_TYPE_ID → PAD-MOUNT, POLE-MOUNT, …). This replaces hand-authored examples so the context itself is reproducible.

4.4 Source-system tables

sql/00_create_tables.sql creates synthetic "source system" tables drawing entities under one company's convention each, plus an ADVERSARIAL_PAIR_CANDIDATES table holding all five pairs for direct pairwise evaluation. A real pipeline discovers candidate columns by scanning INFORMATION_SCHEMA.COLUMNS; the corpus is built to be discovered the same way.


5. Reference tables and ground truth

Built by sql/01_ground_truth_and_reference_tables.sql:

  • ENTITY_GROUND_TRUTH — every naming variant mapped back to its true entity (loaded from entities.csv, reshaped wide → long, 100 rows).
  • ALL_COLUMN_NAMES — live metadata scan of the source tables.
  • CANDIDATE_PAIRS — distinct unordered pairs of discovered column names (a.column_name < b.column_name); the evaluation set (~1,081 pairs).
  • TRUE_SYNONYM_PAIRS — all within-entity variant pairs, derived from ground truth; the recall denominator.

Case normalization is load-bearing. Snowflake uppercases unquoted identifiers, so variant names that preserve original case (accountIdentifier) must be UPPER()-normalized before joining to scanned column names, and ENTITY_GROUND_TRUTH / COLUMN_SAMPLE_CONTEXT must be deduplicated by case-insensitive key. This is the single most common source of reproduction drift.


6. Requirements

  • Python 3.9+, standard library only for corpus generation (matplotlib / numpy only to regenerate figures — see requirements.txt)
  • A Snowflake account with Cortex AI Functions enabled (AI_CLASSIFY, AI_EMBED)
  • An isolated virtual warehouse (XSMALL is sufficient)

Cost estimate for a full reproduction:

Item Approx. cost
Tier 1 (string similarity) $0 — pure SQL
Tier 2 (embeddings, ~100 calls) < $0.01
Tier 3 (LLM classification, 1,081 calls) ~$0.75–$1.00
Tier 4 (LLM + context, 1,081 calls) ~$1.00–$1.50
Full four-tier reproduction under $3 total
Streaming extension (§10, 256 calls) < $0.20 — optional, additive

7. Step-by-step reproduction

7.1 Generate the corpus (no cloud cost)

python python/generate_entity_matching_corpus.py

Produces entities.csv, adversarial_pairs.csv, synthetic_tables.sql, and sample_values.csv in a local corpus_entity_matching/ directory. These match the reference copies committed under corpus/.

7.2 Create Snowflake objects

CREATE DATABASE IF NOT EXISTS ENTITY_MATCH_STUDY;
CREATE SCHEMA IF NOT EXISTS ENTITY_MATCH_STUDY.CORPUS;
CREATE WAREHOUSE IF NOT EXISTS ENTITY_MATCH_WH
  WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

Run sql/00_create_tables.sql to create the synthetic source-system tables.

7.3 Load the corpus

Load entities.csv into ENTITY_GROUND_TRUTH (reshaped to long format — see comments in 01_ground_truth_and_reference_tables.sql), and sample_values.csv into COLUMN_SAMPLE_CONTEXT. Run sql/01_ground_truth_and_reference_tables.sql to build the derived reference tables (ALL_COLUMN_NAMES, CANDIDATE_PAIRS, TRUE_SYNONYM_PAIRS).

7.4 Run each tier in order

sql/02_tier1_string_similarity.sql
sql/03_tier2_embedding_similarity.sql
sql/04_tier3_llm_classification.sql
sql/05_tier4_context_augmented.sql

Each file includes the scoring queries needed to reproduce the paper's reported precision, recall, and confusion-matrix figures.


8. The four tiers — design notes and measured results

Tier 1 — String similarity

Pure SQL (JAROWINKLER_SIMILARITY, EDITDISTANCE) over every candidate pair. Question: is there a similarity threshold that separates true synonyms from adversarial pairs?

Result — no.

  • True synonyms score 55–91 (mean 75.4)
  • Adversarial pairs score 70–94
  • Overlap band 70–91 — no threshold separates the two distributions.

Legacy abbreviations (MTRNUM vs METER_ID) are lexically far apart though synonymous; UPSTREAM_FEEDER_ID vs DOWNSTREAM_FEEDER_ID are lexically near-identical though different.

Tier 2 — Embedding similarity

One AI_EMBED('snowflake-arctic-embed-m', …) call per distinct column name (~100 calls), then VECTOR_COSINE_SIMILARITY over every pair.

Result — the ranking inverts.

  • True synonyms: cosine 0.830–0.907 (mean 0.856)
  • Adversarial pairs: cosine 0.870–0.968
  • Adversarial pairs score higher on average than true synonyms — embeddings invert the intended ranking rather than merely blurring it. Token overlap reads as semantic identity.

Tier 3 — Direct LLM classification

AI_CLASSIFY over the bare pair of names into same_entity / different_entity. ~1,081 calls, ~$0.75–1.00.

Both prompts are kept in sql/04_tier3_llm_classification.sql:

  • v1 (failed, retained deliberately): asked "do these refer to the same logical entity?" — the model treated topical relatedness as sufficient and returned same_entity on 16/16 validation pairs including all 5 adversarial pairs.
  • v2 (reported): reframed as a functional-equivalence / redundancy test — "would these always contain the exact same value for the same record, making one redundant?"

v2 result — precision 75%, recall 91.3% (TP 21, FP 7, FN 2, TN 1051).

The v1→v2 correction, with no model change, is itself a reported finding: LLM classification is highly sensitive to how the question is framed, and that sensitivity has to be measured. The negative result stays in the repo.

Tier 4 — Context-augmented LLM classification

Same v2 framing, plus each column's table name and 5 seeded sample values, plus an explicit instruction not to treat ID-format differences (prefixed MTR-8548 vs bare 8548) as evidence of non-identity. ~1,081 calls, ~$1.00–1.50.

Result — precision 67%, recall 26.1% (TP 6, FP 3, FN 17, TN 1055).

Precision roughly holds versus Tier 3; recall collapses (91.3% → 26.1%). True cross-system synonyms whose IDs are formatted differently get penalized despite the anti-format-bias instruction. Adding context made the matcher conservative in exactly the wrong place — the paper's title.

Summary

Tier Method Precision Recall
1 String similarity no viable threshold (55–91 vs. 70–94, overlapping) —
2 Embedding similarity inverted (adversarial pairs score higher than true synonyms) —
3 Direct LLM classification (v2 prompt) 75% 91.3%
4 Context-augmented LLM classification 67% 26.1%

Arc of the study: cheap methods (1, 2) fail outright on adversarial pairs; a well-framed direct LLM prompt (3) does well; adding "helpful" context (4) makes it worse by destroying recall.


9. Scoring

Each tier writes a *_SCORED table pairing predicted_label with a ground-truth label derived from ENTITY_GROUND_TRUTH, then a confusion matrix (GROUP BY predicted_label, ground_truth). Precision, recall, and the score-distribution figures (figures/, python/generate_figures.py) follow directly.

If your reproduction diverges substantially, check first whether ENTITY_GROUND_TRUTH and COLUMN_SAMPLE_CONTEXT are deduplicated by case-insensitive key (see §5) — this is the most common source of join mismatches when reproducing this study.


10. Streaming extension: live schema evolution matching

Sections 1–9 describe the batch study: a fixed, complete corpus, matched in one pass per tier. This extension asks a follow-on question — does the Tier 3 → Tier 4 recall collapse still show up when columns arrive live, one schema-evolution phase at a time, and matching has to run incrementally against a stream that isn't finished yet?

10.1 Architecture

Needs snowpipe-streaming (Snowpipe Streaming SDK), snowflake-connector-python, and cryptography, in addition to requirements.txt — not needed for the batch study, only for this extension. Key-pair auth via a profile.json (account, user, role, host, private_key_file) alongside the scripts.

Two long-running Python processes, run concurrently (either start order works — the matcher catches up on anything already logged):

  • streaming_ingestion.py (producer) — opens a Snowpipe Streaming channel per source system (StreamingIngestClient.from_table, table mode, no separate PIPE object needed) and pushes rows into MERIDIAN_STREAM_INGEST / FIELDSTONE_STREAM_INGEST in four timed phases, 30s apart. Each phase after the first introduces columns not sent in any prior phase — ENABLE_SCHEMA_EVOLUTION = TRUE on both tables lets Snowflake auto-add them rather than requiring a manual ALTER TABLE. The two source systems run in separate threads with independently seeded RNGs, so both streams progress concurrently rather than one finishing before the other starts. Every phase's new columns are also logged to STREAM_ARRIVAL_LOG (via a plain SQL connection, separate from the ingest channel) the moment that phase's rows commit.
  • streaming_matcher.py (matcher) — polls STREAM_ARRIVAL_LOG every 10s for (table, phase) combinations not yet processed. As soon as a new phase's columns are detected, it runs both Tier 3 and Tier 4 classification on those newly-arrived columns against every column accumulated so far, across both source systems — the point being cross-system reconciliation, not within-system matching. It does this while the producer is still streaming later phases, not after the stream ends.
  • tier4_backfill.py — a standalone one-time backfill that re-runs Tier 4 (only) against every (table, phase) already present in STREAM_ARRIVAL_LOG, without touching PROCESSED_PHASES or re-running Tier 3. Useful for backfilling TIER4_STREAMING_RESULTS after the fact — e.g. against arrivals logged before Tier 4 was wired into the live matcher loop — without re-triggering the whole pipeline.

The Tier 3 and Tier 4 prompts here are the same v2 / context-augmented prompts as §8.3–8.4 (AI_CLASSIFY, sample values from the same COLUMN_SAMPLE_CONTEXT table built in §7.3), applied incrementally instead of in one full-corpus pass.

10.2 Tables

sql/06_streaming_ingest_tables.sql creates:

  • MERIDIAN_STREAM_INGEST, FIELDSTONE_STREAM_INGEST — the streaming ingest targets, ENABLE_SCHEMA_EVOLUTION = TRUE, starting with only EVENT_ID / EVENT_TIMESTAMP / SOURCE_SYSTEM.
  • STREAM_ARRIVAL_LOG — one row per column per phase, written by the producer as evolution happens: (arrival_phase, arrival_timestamp, target_table, new_column_name, evolution_type).

streaming_matcher.py additionally creates, on first run (ensure_tracking_tables(), not part of the SQL DDL since these are implementation bookkeeping rather than study-defined data):

  • PROCESSED_PHASES — which (table, phase) combinations have already been matched, so the poller doesn't reprocess them.
  • TIER3_STREAMING_RESULTS, TIER4_STREAMING_RESULTS — same shape as the batch tiers' result tables, plus a phase column.

10.3 Running it

-- once
sql/06_streaming_ingest_tables.sql
# two terminals, either order
python python/streaming_ingestion.py
python python/streaming_matcher.py

A full run takes ~3–4 minutes (4 phases × 30s, two source systems concurrently). Stop the matcher once the producer's run is complete and PROCESSED_PHASES's row count matches STREAM_ARRIVAL_LOG's distinct (table, phase) count.

10.4 Measured result

A full run streams 16 columns total (8 per source system, matching the snake_case / legacy_abbrev naming variants for 8 of the 20 base entities), scored against the same ENTITY_GROUND_TRUTH used in §9:

Tier Setting TP FP FN TN Precision Recall
3 Streaming (16 columns, 128 pairs) 14 0 2 112 100% 87.5%
4 Streaming (16 columns, 128 pairs) 1 0 15 112 100% 6.25%

The same recall collapse from §8.4 reproduces live: Tier 4's sample-value context does not introduce false positives here either, but recall falls from 87.5% to 6.25% — sharper than the batch run's 91.3% → 26.1%, because this subset is entirely paired identifiers (METER_ID vs MTRNUM, ACCOUNT_ID vs ACCT_NO, …), so the ID-format mismatch the anti-bias instruction fails to neutralize (§8.4) dominates a larger share of the true positives than it does across the full, more heterogeneous corpus. The finding is not an artifact of the batch pipeline's specific query structure — it holds when the same prompts run incrementally, mid-stream, against a schema that is still evolving.

Cost for a full streaming reproduction: 128 pairs × 2 tiers = 256 AI_CLASSIFY calls, under $0.20.


11. Notes on independence

The corpus, scenario, and all reported results were produced independently on a personally provisioned Snowflake account using entirely synthetic data and an invented company scenario. No production system, employer data, or proprietary taxonomy is represented anywhere in this repository. Reproduction cost for the full four-tier run is under $3.


12. Citation

J. Ramalingam, "Context Is Not Always King: An Empirical Study of
Adversarial Precision in Column-Name Entity Matching," [venue, year,
volume, pages once assigned].

About

Context Is Not Always King: An Empirical Study of Adversarial Precision in Column-Name Entity Matching

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages