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.
| 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 |
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.
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 |
Generated by python/generate_entity_matching_corpus.py, standard library
only, fully deterministic (SEED = 20260901).
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.
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 |
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.
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.
Built by sql/01_ground_truth_and_reference_tables.sql:
ENTITY_GROUND_TRUTH— every naming variant mapped back to its true entity (loaded fromentities.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.
- Python 3.9+, standard library only for corpus generation
(
matplotlib/numpyonly to regenerate figures — seerequirements.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 |
python python/generate_entity_matching_corpus.pyProduces 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/.
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.
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).
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.
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.
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.
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_entityon 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.
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.
| 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.
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.
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?
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 separatePIPEobject needed) and pushes rows intoMERIDIAN_STREAM_INGEST/FIELDSTONE_STREAM_INGESTin four timed phases, 30s apart. Each phase after the first introduces columns not sent in any prior phase —ENABLE_SCHEMA_EVOLUTION = TRUEon both tables lets Snowflake auto-add them rather than requiring a manualALTER 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 toSTREAM_ARRIVAL_LOG(via a plain SQL connection, separate from the ingest channel) the moment that phase's rows commit.streaming_matcher.py(matcher) — pollsSTREAM_ARRIVAL_LOGevery 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 inSTREAM_ARRIVAL_LOG, without touchingPROCESSED_PHASESor re-running Tier 3. Useful for backfillingTIER4_STREAMING_RESULTSafter 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.
sql/06_streaming_ingest_tables.sql creates:
MERIDIAN_STREAM_INGEST,FIELDSTONE_STREAM_INGEST— the streaming ingest targets,ENABLE_SCHEMA_EVOLUTION = TRUE, starting with onlyEVENT_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 aphasecolumn.
-- once
sql/06_streaming_ingest_tables.sql# two terminals, either order
python python/streaming_ingestion.py
python python/streaming_matcher.pyA 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.
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.
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.
J. Ramalingam, "Context Is Not Always King: An Empirical Study of
Adversarial Precision in Column-Name Entity Matching," [venue, year,
volume, pages once assigned].