Autonomous ETL Pipelines for Salesforce M&A Consolidation: A Reference Framework, Jitterbit Mapping, and Reproducible Simulation Study

Imran Basha Shaik; Sahla Sultana; Khader Moinuddin Shaik

cs.DBNov 4, 2026

Autonomous ETL Pipelines for Salesforce M&A Consolidation: A Reference Framework, Jitterbit Mapping, and Reproducible Simulation Study

Authors: Imran Basha Shaik, Sahla Sultana, Khader Moinuddin Shaik

Organizations: Frisco, TX, USA

Abstract

Merging companies that each run Salesforce must consolidate their customer data into a single organization while both source systems remain in use. This requires reconciling different schemas and picklists, resolving duplicate customers across and within organizations, preserving relationships between records, and keeping the target current until cutover. This article proposes a reference framework for an autonomous extract-transform-load pipeline for this task. It combines partitioned bulk extraction, declarative mapping templates, rule-based and statistical quality gates, master data management with deterministic and blocked fuzzy matching, constrained loading with per-record error capture, and change data capture guarded by a data-health circuit breaker. Each stage is mapped to Jitterbit Integration Studio constructs. The framework was implemented as an open reference prototype and evaluated on synthetic two-organization scenarios with known ground truth at three scales, from 32,040 to 383,973 source records, over five random seeds. At a preset threshold, hybrid matching reached pairwise F1 scores of 0.918 for accounts and 0.885 for contacts; calibrating the threshold per object would raise these to 0.938 and 0.963. The quality gates prevented all load failures and all silent loss of unmapped picklist values without wrongly flagging any clean record, whereas removing them produced about 1,000 load failures per run. Over five simulated days of hourly incremental synchronization, replayed events were ignored, an injected upstream fault was contained in the batch in which it occurred, and the target agreed with a full rebuild on more than 99.3 percent of field values. Validation on a production deployment remains future work.

Figures & tables

Figure 1: Reference architecture. Numbered stages run in the orchestration layer (Jitterbit operations in a production deployment; the Python reference prototype in this study). Rejected and held records go to quarantine with audit entries; the crosswalk records lineage from source to golden identifiers.

Stage	Purpose	Main output	Controls
1 Extraction	Capture a baseline snapshot of each source object	Compressed staged shards partitioned by creation year	Partition keys; parallel workers; shard manifest
1b CDC intake	Capture creates, updates and deletes after the baseline	Ordered micro-batches of change events	Event identifiers; idempotent skip of replays
2 Transformation	Map each org’s schema, picklists and formats to the canonical model	Canonical records with normalized match keys	Versioned templates; unmapped values flagged, never defaulted
3 Quality gates	Stop records that would break target rules; flag statistical outliers	Accepted records; quarantine with audit entries	Rule catalog; outlier fence; hold of children whose parent is quarantined
4 MDM	Find duplicates and build golden records	Golden accounts and contacts; crosswalk	Deterministic rules; blocking; weighted score threshold; survivorship
5 Load	Write golden records and re-parented children	Target records; per-record error log	Batches of 10,000; row-level fallback on failure

Table 1: Pipeline stages and their controls

Framework stage	Jitterbit construct	Notes
Extraction	Salesforce connector query or bulk query activities, one operation per object and partition	Partition by CreatedDate or SystemModstamp to bound job size
CDC intake	Scheduled operation with incremental query on SystemModstamp, or an external subscriber to Salesforce change events feeding an API endpoint	Persist the last checkpoint and processed event identifiers in project variables or a staging table
Transformation	Transformations (mappings) with Jitterbit Script; picklist maps held in lookup tables	Version templates with the project
Quality gates	Conditional logic in scripts; failed records routed to a quarantine target	Write rule, field and action to an audit table
MDM	Script or external matching service called from an operation; crosswalk written to a staging database	Matching at scale may be better delegated to a database or service
Load	Salesforce connector bulk upsert activities using external identifiers	Capture per-record success and failure files

Table 2: Mapping of framework stages to Jitterbit Integration Studio constructs (design mapping, not evaluated)

ID	Question	Data	Repetitions
E1	RQ4 scaling	5,000, 20,000 and 60,000 canonical companies (32,040 to 383,973 source records)	3 runs per scale; median reported
E2	RQ1 matching and ablation	10,000 canonical companies (about 64,000 source records)	5 random seeds
E3	RQ2 threshold sweep and blocking	Same as E2 (sweep); 2,000 companies (blocking)	5 seeds (sweep); 1 (blocking)
E4	RQ3 quality gates, with and without	Same as E2	5 seeds
E5	RQ5 CDC, idempotency, fault containment, equivalence	20,000 companies (128,037 source records); 120 hourly batches	1 run

Table 3: Experiments

Object	Strategy	Precision	Recall	F1
Account	Deterministic only	0.902 $\pm$ 0.015	0.850 $\pm$ 0.011	0.875 $\pm$ 0.011
Account	Fuzzy only	0.999 $\pm$ 0.001	0.769 $\pm$ 0.010	0.869 $\pm$ 0.007
Account	Hybrid	0.906 $\pm$ 0.015	0.931 $\pm$ 0.008	0.918 $\pm$ 0.011
Contact	Deterministic only	0.997 $\pm$ 0.003	0.694 $\pm$ 0.012	0.818 $\pm$ 0.009
Contact	Fuzzy only	0.999 $\pm$ 0.001	0.753 $\pm$ 0.004	0.859 $\pm$ 0.003
Contact	Hybrid	0.997 $\pm$ 0.003	0.796 $\pm$ 0.007	0.885 $\pm$ 0.004

Table 4: Pairwise matching quality at

\tau

= 0.85 (mean

\pm

standard deviation over 5 seeds)

Figure 2: Precision, recall and F1 of the hybrid matcher as the fuzzy threshold $\tau$ varies (mean of 5 seeds). The dashed line marks the pre-set value used elsewhere in the evaluation.

Defect type	Records with defect	Flagged	Detection rate
Invalid e-mail address	1,390	1,390	100%
Reference to non-existent account	1,280	1,280	100%
Missing required name	966	961	99.5%
Negative opportunity amount	615	615	100%
Unparseable date	412	412	100%
Unmapped picklist value	366	366	100%

Table 5: Detection of injected defects by the quality gates (5 seeds pooled; 319,606 source records)

Figure 3: Wall-clock time by pipeline stage at three data volumes (median of three runs on two CPU cores; local computation only, excluding network and API latency).

Measure	32k records	128k records	384k records
Source records	32,040	128,037	383,973
Total time (median of 3)	2.0 s	9.7 s	39.1 s
Throughput	16,183 rec/s	13,178 rec/s	9,827 rec/s
Account pairs compared	125,984	2,001,702	18,910,752
Golden accounts / contacts	4,963 / 13,319	19,847 / 53,157	59,571 / 158,894
Records unaccounted for	0	0	0

Table 6: Batch pipeline results by scale

Figure 4: Share of contact updates with a blank e-mail address in each CDC batch. The first 24 batches form the baseline profile; the injected upstream fault in batch 83 was detected and held.

Comparison	Records compared	Agreement
Opportunities: identical set of records	46,078	Yes
Opportunities: amount and stage values	46,078	100%
Contacts: golden field values per source record	57,392	99.94%
Accounts: golden field values per source record	23,750	99.36%
Contact clusters: pairwise precision against rebuild	57,392	0.994
Contact clusters: pairwise recall against rebuild	57,392	0.999

Table 7: Agreement between the incrementally synchronized target and a full rebuild from the final source state

Data Integration

See all 23 papers

Entity Resolution in Practice: Lessons from a Self-Serve Pipeline
Jul 28, 2026Kaushik Pavani, Ganga Aluri, Pravin Jadhav +2Entity Resolution
Walmart
MaDI-Bench: An End-to-End Data Integration Benchmark
Jun 29, 2026Aaron Steiner, Ralph Peeters, Christian BizerBenchmark Design Data Integration
Data and Web Science Group University of Mannheim Mannheim, Germany
SINT-Flow: Schema Integration using Large Language Model Workflows
Jul 27, 2026Keti Korini, Christian BizerSemantic Mapping Relational Databases
University of Mannheim Mannheim, Germany

Jul 28, 2026J/K move · Enter open · S save

Entity Resolution in Practice: Lessons from a Self-Serve Pipeline

Kaushik Pavani, Ganga Aluri, Pravin Jadhav, Neeraj Prasad+1

Walmart

Entity Resolution

Open paper AskPDF arXiv

We built and evaluated a self-serve entity resolution (ER) system on six benchmarks spanning 864 to 5M records, and three lessons emerged that are absent from existing ER literature. (1) No single matching algorithm wins everywhere - a self-serve pipeline cannot predict its next dataset, so we recommend training several algorithm families per dataset and letting an automatic bake-off pick the winner. (2) Precision and recall need separate fixes, not a shared threshold - precision needs hard rule-based vetoes, recall needs more diverse candidate retrieval. (3) One false-positive link can silently merge unrelated entities - assuming "A matches B" and "B matches C" implies "A matches C" lets a single bad link chain hundreds of records together, so every cross-group merge must be actively re-verified. We hope these lessons save practitioners the months of dead-end experiments that led us to them.

Abstract

Data Integration

Explore similar work

Entity Resolution in Practice: Lessons from a Self-Serve Pipeline

MaDI-Bench: An End-to-End Data Integration Benchmark

SINT-Flow: Schema Integration using Large Language Model Workflows