Interview prompt
Problem context
Skills being evaluated
Use the sequence below to surface constraints, choose boundaries, test failure behavior, and defend trade-offs. Concrete numbers are interview assumptions, not claims about a real production system.
Clarify the decision
- Define interactive SLO, analytical freshness and deadline, consistency needs, query shapes, and resource bottlenecks. Identify background work that can pause versus maintenance that protects long-term health.
Establish scale assumptions
- Measure CPU, I/O, memory, cache eviction, locks, connections, and compaction by workload. Model the worst scan and concurrent backfill, not average query count.
Functional and non-functional requirements
- Guarantee interactive resource floors, bound analytical impact, expose queue and freshness state, and preserve database maintenance. Isolation policy must remain effective during failover.
High-level architecture
- Use workload-class admission, separate connection and thread pools, resource groups, read replicas or columnar projections for analytics, and a scheduler for backfills and compaction. Propagate deadlines and cost estimates.
Data model and flow
- Classify requests from trusted identity and query plan, reserve work units, route to appropriate stores, and release budget on completion. CDC maintains analytical projections with freshness watermarks.
Consistency and transaction boundaries
- Operational writes stay authoritative; analytics reads declare snapshot or staleness. Maintenance and replica lag thresholds prevent a supposedly isolated path from returning misleading data.
Failure modes and recovery
- Pause or shed analytical work when interactive saturation or replication lag breaches gates. Reserve control and maintenance pools so cancellation and recovery remain possible.
Security and privacy
- Workload class cannot be client-forged, analytical replicas enforce identical tenant policy, and extracted projections retain deletion and residency rules.
Observability and SLOs
- Track resource use, queue age, SLO, freshness, cache churn, lock time, replica lag, and cost by workload and tenant. Correlate maintenance windows with tail latency.
Capacity and cost
- A separate analytical store costs duplication but can use cheaper compute and protect revenue traffic. Choose based on isolation need, query economics, and acceptable freshness.
Alternatives and trade-offs
- Resource groups reduce interference in one engine but share deeper failure domains; separate stores isolate better but create CDC lag and operational duplication. Combine for high-risk analytics.
Evolution and migration
- Tag work and split pools first, enforce budgets, move expensive reads to replicas, then build projections for workloads that still violate SLO. Shadow result parity and freshness.
What Staff and Principal candidates should emphasize
- Principal candidates identify the actual shared resource and protect maintenance as well as interactive work. They connect store choice to consistency and cost instead of saying CQRS reflexively.
Decision trade-offs
Isolation depth
Option A
Resource groups in the primary engine
Option B
Separate analytical read model
Recommendation:Start with pools and resource groups; add a projection when scans still threaten primary I/O, cache, or lock behavior.
Freshness
Option A
Query authoritative state synchronously
Option B
Serve a delayed analytical snapshot
Recommendation:Use explicit watermarked staleness for analytics unless the business decision truly requires current transactional state.
Follow-up interview questions
- 01How do you classify an unexpectedly expensive query?
- 02What background work must never be paused indefinitely?
- 03How does failover preserve resource floors?
- 04When is a separate analytical store worth the duplication?
Common weak answers and mistakes
- 01Separating connection pools while ignoring shared I/O, cache, and locks.
- 02Giving analytics unrestricted access to a read replica needed for failover.
- 03Pausing compaction or maintenance until the database degrades further.
- 04Building a duplicate store without freshness, deletion, or parity controls.
Interviewer evaluation rubric
Adds replicas or more capacity without workload classification, resource accounting, or failure analysis.
Uses trusted classes, admission, separate pools, resource groups, lag gates, and observable freshness.
Adds cost estimation, maintenance protection, CDC projections, failover policy, tenant security, and staged extraction.
Builds an adaptive workload contract balancing revenue latency, analytical value, database health, and total cost.