The Transform That Silently Dropped 30% of Rows
A company imports 50,000 user records nightly from an HR CSV via a scheduled transform. The CSV has columns emp_id, email, dept, manager_id. The transform map has a coalesce on emp_id and field maps for the rest. After go-live, the team notices only ~35,000 users exist in the target table, and manager_id is blank on nearly all records. The CSV is confirmed to contain 50,000 rows with no duplicate emp_id values. The dept column maps to a choice field whose choices were recently renamed. No errors appear in the import set - the transform reports 'complete' with zero failures.
1. Identify the most likely causes of the missing ~15,000 rows and the blank manager_id, and explain why no errors were logged.
Read the worked answer
Missing rows most likely come from the coalesce key not matching what the team assumes. If emp_id is stored as a string in the CSV but the target field is a different type, or if leading zeros are stripped on import, the coalesce fails to find existing records and may create duplicates - but the bigger suspect here is that the import set itself was truncated or filtered before transform. Check the import set row count first: if it's ~35,000, the problem is upstream (data source filter, file encoding, or a scheduled job that overwrites rather than appends). If the import set has all 50,000 but the target has 35,000, the transform is skipping rows via onBefore transform scripts returning false or a coalesce that matches an existing inactive record and the transform is configured to skip.
Blank manager_id: the CSV's manager_id is an employee ID, not a sys_id. Mapping it directly to a reference field will fail to resolve unless the transform uses a reference field lookup / coalesce against the Users table on emp_id. Without that, the reference value is invalid and silently nulled.
Why no errors: transform 'complete with zero failures' only means no script exceptions were thrown. Coalesce skips, unresolved references, and choice mismatches are non-fatal and do not increment the failure counter. The transform log's 'rows ignored' and 'rows skipped' counts are the place to look - they are easy to miss.
2. The `dept` choice values were renamed. What happens to the imported `dept` values, and how should the admin fix it without corrupting existing records?
Read the worked answer
When a choice field's value (the stored string) is renamed, incoming CSV text that matches the old value no longer matches any active choice. Depending on configuration, the transform either:
- leaves the field blank (if choices are enforced), or
- writes the raw text (if the field allows it), creating orphaned values that display oddly and break reporting.
Fix approach:
- Do not rename choice values in place going forward - rename the label only, keeping the stored value stable. If values must change, update existing records first.
- For this import, add a transform script or a choice-mapping step that maps old CSV text → new choice value before the field map writes it. A small
onBeforescript with a lookup table is cleaner than editing the CSV. - To repair already-imported rows, run a targeted update: find target records whose
deptis blank/orphaned and backfill from the source import set using the same mapping. - Re-run the transform in coalesce mode so existing records update rather than duplicate, then verify counts against the 50,000 source rows.