Original practice scenario: A hypothetical enterprise database service adds a required field while tenants upgrade at different speeds. Propose a migration and rollback strategy.
Read model answer +
Expand compatibility before enforcing the new requirement. Schema deployment, data backfill and client adoption are separate phases whose order determines whether old clients continue to work.
Work through it
Add the new representation in a backward-compatible form and define how old writes are interpreted, using a derivation or explicit unknown state rather than invented business data.
Backfill incrementally with progress checkpoints and validation, then compare old and new read paths for representative tenants.
Switch writes and reads under controlled versions, monitoring incompatible clients before enforcing constraints and defining what rollback can safely reverse.
Tradeoffs to discuss
Dual representations ease gradual migration but increase consistency obligations; a coordinated cutover reduces that period while increasing release coupling.
Common pitfalls
Do not drop the old field before all dependent readers and rollback paths are understood.
A successful backfill query does not prove that writes arriving during the backfill were handled correctly.
Original practice scenario: A hypothetical enterprise query is unchanged but becomes much slower after a few customers grow substantially. Explain a diagnosis that separates plan choice from logical correctness.
Read model answer +
Compare representative parameters, execution evidence and data distribution before changing the query. An unchanged SQL string can encounter a different workload or cardinality pattern.
Work through it
Capture the intended result grain, actual row counts at major operators and timing for both typical and unusually large tenants, protecting sensitive values in diagnostics.
Inspect join keys, filter selectivity, statistics and indexes, checking for correlated columns or skew that make independence estimates misleading.
Test a targeted change against equivalent results and multiple parameter sizes, measuring memory, spill behavior and concurrency effects rather than one fast isolated run.
Tradeoffs to discuss
A plan optimized for a dominant tenant may hurt small tenants; partitioning or separate strategies add operational complexity.
Common pitfalls
Do not add an index without examining write cost and whether the query can use it.
A faster query that changes join or NULL semantics is not a valid performance fix.
