The Two-Stage Pattern
Data lands in an import set (staging). A transform map moves it from staging to the target table. The two-stage pattern is the safety net — you can inspect, fix, and re-run before touching production tables.
Coalesce Keys
Coalesce fields tell the transform: ‘if this key already exists, update; otherwise insert’. Without coalesce, you duplicate. Pick the right key (external ID, composite of 2-3 fields) and test it.
Field Mapping
Straight mapping for 1:1 fields. Source script for transformations. Target script for post-insert logic. Keep transforms deterministic — same input, same result, every run.
Error Handling
Transform errors leave records in the import set with error state. Schedule a job that checks for stuck records daily and alerts an admin. Silent import failures are the worst kind.
Bulk Load Performance
For large loads, increase the batch size, set update only if required, and run off-hours. A data load hitting a hot production table at 2pm disrupts users.