Draft a source-to-target ETL mapping that names every transformation instead of leaving it implicit

Draft a source-to-target mapping document for an ETL job, at the level of specificity an engineer could actually implement from without asking follow-up questions.

SOURCE SCHEMA
legacy_users table: id (int), full_name (varchar), signup_date (varchar, format MM/DD/YYYY), status_code (int, 1-4), country (varchar, free text)

TARGET SCHEMA
users table: user_id (uuid), first_name (varchar not null), last_name (varchar not null), created_at (timestamp UTC not null), account_status (enum: active/paused/cancelled/pending), country_code (char(2), ISO 3166-1)

KNOWN BUSINESS RULES THAT AFFECT THE MAPPING
status_code 1 and 2 both map to "active" historically, but the business wants status_code 2 (trial) split out as its own state going forward

LOAD PATTERN
Nightly incremental load, upserting on legacy id mapped to a new surrogate user_id

For every target field, state which source field(s) it comes from and the exact transformation applied — not just "maps to," but the actual logic: a data type cast, a unit conversion, a concatenation, a lookup against a reference table, or a conditional (if X then Y else Z). Where a target field has no obvious single-source mapping — it's derived from a calculation across multiple source fields, or it doesn't exist in the source at all and needs a default or an external lookup — say so explicitly and state what the default or derivation rule should be, asking me to confirm it rather than inventing a plausible-sounding default silently. Flag every place where a naive direct copy would silently produce wrong data: a source field that can be null where the target requires not-null, a source date in a different timezone than the target expects, a source enum whose value set doesn't fully match the target enum's allowed values, or a source field that can contain duplicates where the target has a uniqueness constraint. For Nightly incremental load, upserting on legacy id mapped to a new surrogate user_id, state how the mapping handles updates to existing rows versus new rows — specifically whether this is a full overwrite, an upsert keyed on a stated field, or an append-only load, since the transformation logic for a field can differ between an initial load and an incremental one.

WHAT NOT TO DO
Do not write "direct mapping" for any field without checking whether type, nullability, or value-set actually align between source and target — check each of those three before calling anything a "direct" pass-through. Do not invent a default value for a required target field without flagging it as a decision that needs sign-off.

OUTPUT FORMAT
Table: target field | source field(s) | transformation logic | edge cases/flags | confirmed or needs sign-off.
Followed by: a short list of fields that need a business-rule decision before this mapping can be implemented as-is.