You must import 100,000 rows and a small unknown number of them will violate constraints. Describe how you would structure the transaction scope so that bad rows are rejected and recorded while good rows still land, and explain the tradeoffs of the approach you choose.

## Why one big transaction is the wrong default The instinct is "the import must be atomic", so wrap all 100,000 rows in one transaction. That buys a guarantee nobody asked for and costs a lot: - **All-or-nothing failure.** Row 99,998 violating a constraint destroys hours of work. - **Lock hold time and cleanup pressure.** Every lock is held for the whole run, and every version written stays as garbage the engine must reclaim afterwards. - **A gigantic rollback**, which is often slower than the insert itself and cannot be interrupted. - **No progress signal**, so nothing is resumable and nothing is observable. The requirement here is not "all rows or none"; it is "every good row lands, every bad row is reported". That is a *per-item* atomicity requirement, and the scope should match it. ## The structure that works **1. Validate before writing what you cheaply can.** Format, required fields, referential lookups that can be batched. Rejecting a row before it reaches an INSERT is far cheaper than writing it, hitting a constraint, and rewinding. Constraint checks that only the database can do — unique keys, foreign keys under concurrency, exclusion rules — necessarily fail at write time, which is what the recovery machinery is for. **2. Chunk and commit.** Process in chunks of N rows, one transaction per chunk. N trades round-trip and commit overhead (small N) against redo cost and lock hold time (large N); a few hundred to a few thousand is typical, and it should be measured, not guessed. Persist the last successfully committed offset or a per-row imported marker so a killed run resumes. Note the consequence honestly: mid-run, other sessions can see a partially imported dataset. If that is unacceptable, import into a staging table and make the final switch a single small transaction — that is how you keep atomicity without holding one transaction across the whole load. **3. Recover inside the chunk with savepoints.** The mechanism matters: after a statement error, engines commonly put the transaction into a state where every subsequent statement is refused until it is rolled back — to a savepoint or entirely. So without a savepoint taken before the failing row, a single bad row costs the whole chunk. Two strategies: - *Savepoint per row*: set a savepoint before each row, roll back to it on error, log the reject, continue. Simple and precise, but you pay the savepoint cost on all 100,000 rows. - *Optimistic chunk with fallback* (usually better): run the chunk with no per-row savepoints; if it fails, roll back the chunk and replay those N rows individually with a savepoint each. Clean chunks cost nothing extra; only dirty chunks pay for isolation. With rare failures this is markedly faster. **4. Make rejects first-class.** Insert each rejected row plus its error into a reject table. Two subtleties: write it in a way that survives — either after the rollback to the savepoint (inside the still-open chunk transaction) or in a separate connection — because anything written inside the rolled-back section disappears with it. Rejects then become a report, a re-run input, and the basis for an abort threshold: if more than X% of rows fail, stop the run, since that usually signals a bad source file rather than bad rows. **5. Make the whole import idempotent.** Resumption and retries mean rows can be attempted twice. Import against a natural or source key with an upsert (insert-or-update on conflict) so a replayed chunk converges instead of duplicating. This also lets you fix and re-run rejects without a cleanup step. ## Alternatives worth naming - **Bulk-load into a staging table with no constraints**, then move data with set-based statements that filter out the invalid rows by joining against the constraint conditions. This turns row-by-row error handling into two SQL statements and is dramatically faster at large volumes: one INSERT ... SELECT for the valid rows, one for the rejects. The cost is expressing every rule as a predicate. - **Conflict-tolerant statements** (insert-ignore-on-conflict style) handle the single most common failure — duplicate keys — with no savepoints at all, but they skip silently unless you capture what was skipped. - **Deferred constraint checking** can move some failures to commit time, which is usually the *opposite* of what you want here: it converts a per-row failure into a whole-transaction failure. ## The judgment being tested The interviewer wants to hear that transaction scope is a design choice driven by the atomicity the business actually needs, that you know a failed statement can poison a transaction until it is unwound, and that savepoints are the tool for continuing after an expected error — while chunked commits, not savepoints, are what bounds lock hold time and rollback cost.