When data must be moved alongside a schema change, what does writing it as SQL statements buy over running it through the mapping?

middlemust knowfreq 64%

answer

two places the data change can live
set-based versus row-at-a-time
the step must reproduce a historical shape
code needs a key, a parser, a call
compute in code, write in bulk

basics

~20 s

SQL statements run set-based against the schema as that point in history left it; no later code edit can alter them. Running the change through the mapping adds logic the database lacks, but ties a historical step to moving code.

solid answer

~50 s

Two places can hold the data movement. Statements inside the change — plain UPDATE/INSERT ... SELECT written against explicit column names — run in one round trip, see the schema frozen by the steps before them, and cannot be changed by a later refactor of the application. A run driven through the mapping goes row by row through mapped classes, so it can reuse validation, derivation or anything the database cannot express: decrypting a field, parsing a document with a library, calling out for a value. The cost is that it reads today's classes to reproduce a historical shape, so it rots. The usual rule is: default to statements; reach for code only when the transformation genuinely needs logic the database has no access to, and then pin that code to a frozen copy of the shape it was written against rather than importing the live model.

Two places can hold the data movement. **Statements inside the change** — plain `UPDATE`/`INSERT ... SELECT` written against explicit column names — run in one round trip, see the schema frozen by the steps before them, and cannot be changed by a later refactor of the application. **A run driven through the mapping** goes row by row through mapped classes, so it can reuse validation, derivation or anything the database cannot express: decrypting a field, parsing a document with a library, calling out for a value. The cost is that it reads today's classes to reproduce a historical shape, so it rots. The usual rule is: default to statements; reach for code only when the transformation genuinely needs logic the database has no access to, and then pin that code to a frozen copy of the shape it was written against rather than importing the live model.

go deeper

for a junior

Recall that the data movement can be written as SQL inside the change or as application code, and that SQL is the usual default because it runs over the whole set at once.

for a middle

Explain both sides concretely: set-based versus row-at-a-time cost, the frozen schema a statement sees, and the hidden callbacks and cascades a run through the mapping triggers.

for a senior

Demonstrate the decision rule on a real transformation, including the compute-in-code, write-in-bulk middle ground, and say what you do to stop a code-driven step from drifting.

for a principal

Argue the durability angle: a change history is an artifact that must reproduce the same result years later, and coupling any part of it to a moving code base trades a one-off convenience for a permanent liability.

The choice

A schema change often has to move data as well as change structure: fill a new column from an old one, split a field, normalise a value into a lookup table, or seed rows. That work can live in two places, and the choice is not a matter of taste.

Statements inside the change. The step itself contains UPDATE, INSERT ... SELECT, DELETE written against explicit table and column names.
A run driven through the mapping. Application code loads objects through the data-access layer, changes them, and writes them back through the same layer.

What statements buy

Set-based execution. One statement asks the engine to change every matching row. A row-by-row run over the mapping pays a round trip and object construction per row, which is the difference between minutes and hours on a large table.
A frozen view of the schema. At its point in the ordered history, the shape is exactly what the preceding steps produced. Explicit column names in the statement pin it there; the step keeps meaning the same thing forever.
Immunity to code changes. A refactor of the application cannot alter what an old statement does, because the statement never mentions a class.
No hidden behaviour. Nothing fires validation, lifecycle callbacks, auto-populated timestamps, cascades or change events. The statement writes what it says.
It runs where the data is. No data crosses the network to be inspected.

What driving it through the mapping buys

Expressiveness the database does not have. Decrypting a value with the application's key, parsing an encoded document with a library, deriving a value from a call to another system, or applying a rule that exists only in code.
Reuse of existing, tested logic rather than re-implementing it in SQL where it can drift from the original.
Familiarity. The people writing it work in the same idiom as the rest of the code base, and can test it with the tools they already use.

And it costs:

Row-at-a-time work and, in a layer with a tracked set, growing memory unless the run is deliberately kept small.
Hidden writes. Cascades, callbacks and automatic timestamp columns fire, so the run writes more than the diff suggests.
Rot. This is the decisive one. The step must reproduce a historical shape, but the classes it imports keep moving. A field it uses gets removed and the step stops loading; a column gets renamed and the step keeps working while writing somewhere else. Neither failure is visible in the diff that caused it.

Side by side

Axis	Statements in the change	Run through the mapping
Unit of work	Whole set in one statement	One object at a time
Schema it sees	Frozen by the preceding steps	Whatever today's mapping says
Survives a later refactor	Yes	Only if pinned to a frozen shape
Hidden behaviour	None	Callbacks, cascades, defaults
Non-database logic	Not available	Available
Cost on a large table	Low	High, and grows with row count
Reviewability	The diff is the change	Must read the model to know the effect

How to decide

Can the transformation be expressed in standard SQL over the columns present at that point? If yes, write it as statements. This covers most cases: copying, concatenating, splitting on a delimiter, mapping values through a join, computing an arithmetic column.
Does it need something only the application has — a key, a parser, a remote call, an intricate rule? Then code is justified.
If code is justified, cut it off from the live model. Read and write through explicit column lists with a thin row mapper, or copy the shape it needs into the step itself, so a later refactor cannot silently change what a historical step does.
Either way, make the run idempotent, because it will be interrupted and re-run.

A useful middle ground exists: compute in code, write in bulk. The application derives the values it alone can derive, then applies them with a single set-based statement joined against a temporary set of keys and values. That keeps the expressive part in code and the write cheap — but the pinning rule still applies to the reading half.

One honest caveat: data-access layers differ in how much they help here. Some offer a set-based write path that bypasses the tracked set entirely, which narrows the performance gap; others offer nothing but object-at-a-time writes. The structural argument — frozen shape versus moving code — is unaffected either way.

## The choice A schema change often has to move data as well as change structure: fill a new column from an old one, split a field, normalise a value into a lookup table, or seed rows. That work can live in two places, and the choice is not a matter of taste. 1. **Statements inside the change.** The step itself contains `UPDATE`, `INSERT ... SELECT`, `DELETE` written against explicit table and column names. 2. **A run driven through the mapping.** Application code loads objects through the data-access layer, changes them, and writes them back through the same layer. ## What statements buy - **Set-based execution.** One statement asks the engine to change every matching row. A row-by-row run over the mapping pays a round trip and object construction per row, which is the difference between minutes and hours on a large table. - **A frozen view of the schema.** At its point in the ordered history, the shape is exactly what the preceding steps produced. Explicit column names in the statement pin it there; the step keeps meaning the same thing forever. - **Immunity to code changes.** A refactor of the application cannot alter what an old statement does, because the statement never mentions a class. - **No hidden behaviour.** Nothing fires validation, lifecycle callbacks, auto-populated timestamps, cascades or change events. The statement writes what it says. - **It runs where the data is.** No data crosses the network to be inspected. ## What driving it through the mapping buys - **Expressiveness the database does not have.** Decrypting a value with the application's key, parsing an encoded document with a library, deriving a value from a call to another system, or applying a rule that exists only in code. - **Reuse of existing, tested logic** rather than re-implementing it in SQL where it can drift from the original. - **Familiarity.** The people writing it work in the same idiom as the rest of the code base, and can test it with the tools they already use. And it costs: - **Row-at-a-time work** and, in a layer with a tracked set, growing memory unless the run is deliberately kept small. - **Hidden writes.** Cascades, callbacks and automatic timestamp columns fire, so the run writes more than the diff suggests. - **Rot.** This is the decisive one. The step must reproduce a historical shape, but the classes it imports keep moving. A field it uses gets removed and the step stops loading; a column gets renamed and the step keeps working while writing somewhere else. Neither failure is visible in the diff that caused it. ## Side by side | Axis | Statements in the change | Run through the mapping | | --- | --- | --- | | Unit of work | Whole set in one statement | One object at a time | | Schema it sees | Frozen by the preceding steps | Whatever today's mapping says | | Survives a later refactor | Yes | Only if pinned to a frozen shape | | Hidden behaviour | None | Callbacks, cascades, defaults | | Non-database logic | Not available | Available | | Cost on a large table | Low | High, and grows with row count | | Reviewability | The diff is the change | Must read the model to know the effect | ## How to decide 1. **Can the transformation be expressed in standard SQL over the columns present at that point?** If yes, write it as statements. This covers most cases: copying, concatenating, splitting on a delimiter, mapping values through a join, computing an arithmetic column. 2. **Does it need something only the application has** — a key, a parser, a remote call, an intricate rule? Then code is justified. 3. **If code is justified, cut it off from the live model.** Read and write through explicit column lists with a thin row mapper, or copy the shape it needs into the step itself, so a later refactor cannot silently change what a historical step does. 4. **Either way, make the run idempotent**, because it will be interrupted and re-run. A useful middle ground exists: compute in code, write in bulk. The application derives the values it alone can derive, then applies them with a single set-based statement joined against a temporary set of keys and values. That keeps the expressive part in code and the write cheap — but the pinning rule still applies to the reading half. One honest caveat: data-access layers differ in how much they help here. Some offer a set-based write path that bypasses the tracked set entirely, which narrows the performance gap; others offer nothing but object-at-a-time writes. The structural argument — frozen shape versus moving code — is unaffected either way.

Why is a statement's explicit column list part of what makes it durable?
Because it names the shape the step ran against instead of deferring to whatever the model says today. Explicit columns keep the step meaning one thing forever; a wildcard or a mapped class defers the meaning to code that keeps changing, so the same historical step can quietly write different columns after a refactor.
The transformation needs a value only the application can compute. How do you avoid a row-at-a-time write?
Compute in code, write in bulk: derive the key-and-value pairs in the application, stage them, and apply them with a single set-based update joined against that staged set. The expressive half stays in code and the write stays one statement, so the cost no longer scales with round trips.
Which hidden behaviours does a run through the mapping trigger that a statement does not?
Validation, lifecycle callbacks, automatically maintained timestamp and version columns, cascading writes to related rows, and change events other components consume. On a historical data step these are usually unwanted: they apply today's rules to yesterday's data and write more rows than the change describes.

saying these in an interview costs you the question

Says data changes should always go through the mapping so the domain rules apply.
Writes a row-at-a-time loop over a large table and is surprised it takes hours.
Thinks a statement in the change is unreviewable or untestable.
Forgets that a run through the mapping fires callbacks, cascades and automatic columns.
Assumes a step written against the classes will keep meaning the same thing next year.
Treats the choice as style preference rather than a durability question.

When data must be moved alongside a schema change, what does writing it as SQL statements buy over running it through the mapping?

§ answer

basics

solid answer

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The choice

What statements buy

What driving it through the mapping buys

Side by side

How to decide

▾common follow-ups3 questions

▾red flags6 flags

The choice

What statements buy

What driving it through the mapping buys

Side by side

How to decide

answer

go deeper