ADF Mapping Data Flow Interview Questions: Schema Drift, Alter Row, and Sink Results

ADF mapping data flow interview questions often hide three different contracts in one diagram: which source fields arrive, which operation each row requests, and what the destination actually commits. Allowing schema drift does not automatically migrate a SQL table. Seeing a delete icon in a preview does not prove a row was deleted. A strong answer follows the evidence across all three boundaries.

This article covers Azure Data Factory mapping data flows using Microsoft documentation checked October 6, 2026. The customer batch and result ledgers are original examples. Local Python and SQLite checks validate their classification and arithmetic; they are not Azure Data Factory or Azure SQL executions. No candidate report establishes these exercises as an employer's assessment.

Begin with Explain ETL schema changes and ensure integrity. Focus on what a schema change permits and what it still requires you to verify downstream.

What is the first distinction an interviewer should hear?

Mapping data flow describes transformations and their destinations. A row's shape, its operation marker, and the stored database record are separate states. Diagnose a failed result by locating the earliest boundary where observation diverges from the expected contract.

Our practice response is: “I will inspect the source projection and actual fields, trace classification through Alter Row, then verify the sink's allowed operations, match keys, mappings, and committed records. Preview confirms a transformation sample; destination queries confirm persisted effects.” This creates a debugging sequence rather than a list of settings.

Keep the product boundary explicit. Microsoft's current Alter Row documentation says the transformation is not currently supported in Dataflow Gen2. This article concerns ADF mapping data flows, not an assumed feature-for-feature translation into Fabric. Alter Row documentation.

Does schema drift make the source contract optional?

Official facts: schema drift handles metadata changes such as added, removed, or changed fields. ADF treats drifted flows as late-bound, so drifted names are not automatically present in design-time schema views. Drifted source columns can flow through when the relevant setting is enabled. Schema drift, source transformation.

Our customer feed originally has customer_id, amount, and op. A producer adds region and changes amount from a numeric value to a string. Accepting the additional field does not decide whether region should enter the curated table, nor whether a malformed amount becomes a rejected record or a failed batch.

Define required keys independently of permissive source ingestion. A raw landing layer might retain additional fields for investigation. A curated SQL table may accept only approved names and types. Those are compatible policies if the transformation explicitly carries accepted fields and retains rejected-record evidence.

An interviewer may ask, “Why not turn validation off?” Answer with the contract it protects. Turning off one schema check can reveal more data, but it cannot establish that a changed key still identifies the same customer or that a field rename preserves business meaning.

How do you make a drifted column usable downstream?

Officially, drifted columns can be referenced with expressions such as byName or byPosition; column patterns and rule-based mappings can select fields by characteristics. The Map Drifted action can generate a Derived Column transformation from observed fields. Schema drift, column patterns.

For our feed, promote customer identity and amount into explicit normalized columns. Give the amount a numeric type and validate conversion results before classification. Microsoft's expression reference documents the available conversion, null, and type-check functions; choose a rule appropriate to the actual format. Expression functions.

Avoid positional assumptions when column order can change. byPosition may be useful under a positional file contract, but the third column becoming region instead of amount should not silently change a financial measure. A name-based reference still needs a missing-column and rename policy.

Also inspect what a quick action generated. A sample containing only integer-looking amounts might not represent later decimal or malformed values. Our recommended test fixture includes both a valid decimal representation and an invalid string; design-time convenience is not evidence that all production values fit the inferred type.

Which Alter Row condition wins?

Official facts: Alter Row assigns insert, update, delete, or upsert policies according to the first matching condition. A row that matches none defaults to insert; an explicit final true() policy can define another fallback. Alter Row documentation.

In our original feed, a tombstone may carry op='U' from an upstream envelope while also setting is_deleted=true. If update is checked first, that row can be marked for update instead of deletion. Put the intended priority into the design and test a row matching both predicates.

This illustrative data flow script fragment assumes the input has already been validated and normalized. It is a source-reviewed fragment, not a full deployed flow.

Validated alterRow(
    deleteIf(is_deleted == true()),
    updateIf(op == 'U'),
    insertIf(op == 'I')
) ~> Classified

Unknown operations should be rejected before this fragment if they are invalid for the business feed. Otherwise the default insert policy can turn an unexpected op into a write request. Adding a catch-all condition without defining its business meaning merely makes the same ambiguity more explicit.

Native CDC sources are another boundary: current documentation explains that their operation markers can make an additional Alter Row unnecessary. Do not treat every incremental feed as native CDC; an ordinary file containing an op column is still your transformation's responsibility.

Work through a six-record customer batch

Our target initially contains customers 101, 102, and 105 with amounts 10, 20, and 30. Input records below belong to one immutable batch. Customer 103 appears twice with conflicting instructions; our chosen policy quarantines both occurrences rather than inventing a survivor from arrival order.

Input record	Relevant input	Our expected disposition
T1	101, U, amount 12	Update 101
T2	102, U, deleted=true	Delete 102; deletion wins
T3	103, I, amount 7	Quarantine conflicting repeated key
T4	103, U, amount 8	Quarantine conflicting repeated key
T5	104, U, amount bad	Reject invalid amount
T6	106, I, amount 5	Insert 106

That gives three accepted operations, two conflict records, and one invalid record. Reconciliation accounts for all six source records. It does not silently count quarantine rows as successful customer changes.

After the accepted operations, the expected target is 101=12, 105=30, and 106=5: three rows totaling 47. The unchanged row count conceals both a deletion and an insertion. Checking only “three rows before and after” would miss incorrect identities or values.

This quarantine policy is our exercise assumption, not a mandatory ADF behavior. An event stream with versions might instead choose the latest valid change per key, but it needs a deterministic sequence and delete semantics. A warehouse snapshot may require a different duplicate policy again.

Why do correct markers still produce wrong sink results?

Official facts: sink settings specify the allowed operations and match keys; default behavior allows inserts. Updates, upserts, and deletes require the relevant settings and identifying keys. Connector-specific capabilities still apply. Alter Row documentation, sink transformation.

For our Azure SQL-style exercise, map normalized customer_id to the intended target key. Check that it identifies one record in the required scope. If IDs repeat across tenants, using only customer_id can affect the wrong tenant; the business key may need both tenant and customer identity.

Trace one operation rather than guessing from the overall activity status. T2 should target customer 102 and request deletion. A correct marker paired with disabled delete behavior is not a correct end-to-end configuration. Inspect the actual execution's error or write evidence instead of assuming every connector silently skips that row.

An upsert is not a substitute for duplicate-input reasoning. Our T3 and T4 conflict before writing. Allowing both to target customer 103 does not define whether amount 7 or 8 is correct. Normalize and resolve—or quarantine—the conflict before relying on destination write behavior.

Does a preview delete icon prove that data was deleted?

Official fact: fetching a mapping data flow preview does not write to the sink. It can show operation markers and transformed samples. To test destination writes, execute the data flow through a pipeline, including an appropriate pipeline debug run. Sink transformation.

For our exercise, preview can support the claim “T2 is classified as delete.” It cannot support “customer 102 is absent from the destination.” That second claim requires querying the actual target used by the execution.

Record the exact source slice, parameters, linked service, destination table, and run ID. A developer may preview one file while a pipeline resolves another dataset parameter, or inspect a development database while the run writes elsewhere. These are evidence mismatches, not proof that Alter Row is unreliable.

When describing testing, distinguish a sampled preview from a complete batch. A sample may omit the duplicate customer or invalid amount. Our six-record fixture is small enough to inspect fully; a larger run needs complete disposition counts and a bounded method for examining rejects.

Does allowing drift automatically add SQL columns?

Official facts: sink drift and mapping determine which fields are passed onward. Automatic mapping or rule-based mapping can carry additional fields, but the destination's schema remains a separate constraint. Sink schema validation checks projected names and types against the store. Schema drift, sink transformation.

Our new region column must therefore have an explicit destination decision. Drop it from the curated projection while preserving raw evidence, add it through an authorized schema migration, or send it to another compatible destination. Do not claim an ingestion toggle performs a safe database migration.

Microsoft also documents a recreate-table option that drops and recreates a target. That is a consequential schema action, not a harmless response to drift. In an interview, explain what data, constraints, grants, and dependencies require separate review before anyone selects such an option.

How do you reconcile writes and replay safely?

Our destination verification checks keys 101, 102, 105, and 106 individually, compares totals, and preserves conflict/reject records with batch identity. Check that 102 is absent, 105 remains unchanged, and no accepted operation escaped its tenant scope.

Activity monitoring supplies run and transformation evidence such as row metrics and timings. It helps locate problems but does not replace business reconciliation. Monitoring mapping data flows.

Suppose T1 commits but a later write fails. Do not assume the entire multi-operation flow is one database transaction unless that guarantee is established for the exact path. Inspect what persisted. Our recovery proposal stages accepted changes with stable batch and record identities, then applies them through a controlled destination procedure with explicit replay rules.

Replaying T6 as an ordinary insert can collide with its existing key. Changing every insert to upsert can also overwrite newer target data if old batches arrive late. A stable replay ledger or version-aware destination rule must decide whether an operation has already applied and whether it is still eligible.

Finish with an accountable statement: “The preview verifies classification for these records; the run evidence identifies the destination attempt; the target checks establish the committed business outcome. These are different proofs.” That is stronger than interpreting a green activity as complete data correctness.

Sources and Further Reading

• Microsoft: Schema drift
• Microsoft: Alter Row transformation
• Microsoft: Sink transformation
• Microsoft: Column patterns
• Microsoft: Mapping data flow expressions
• Microsoft: Source transformation
• Microsoft: Monitor mapping data flows

Documentation checked October 6, 2026. Local model results are not a captured Azure execution; product and connector behavior should be validated in the specified environment.