← Level 6 · Module Home · Level 7 (of 0–9)
Pilot set: 6 of ~40 target questions. These distinguish experienced engineers from candidates who’ve only memorized syntax — no single “correct query” exists for most of these; the answer is a tradeoff analysis.
Difficulty: ⚫ Staff/Production Category: Schema Design Type: I — Design
Product wants to let customers store arbitrary custom fields on an
employes record (different companies using this HR system want
different custom attributes). Two proposals: add columns dynamically per
tenant, or an Entity-Attribute-Value (EAV) table
(emp_id, attribute_name, attribute_value).
Argue the tradeoffs; do not just pick one.
Confidently recommending EAV or wide-columns as “the correct” approach without acknowledging the real cost the other approach avoids.
“Which would you actually recommend for THIS specific scenario (multi- tenant custom fields), and what would make you reconsider that choice as the product evolves?”
Difficulty: 🔴 Expert Category: Query Architecture, Maintainability Type: I — Design / Code Review
You’re reviewing a PR with this query embedded in application code:
SELECT * FROM employes e1, departments d1, locations l1, employes e2
WHERE e1.dept_id = d1.dept_id AND d1.location_id = l1.location_id
AND e2.manager_id = e1.emp_id AND l1.country = 'India'
AND e1.hire_date > '2020-01-01' OR e1.salary > 100000
ORDER BY 3, 1;
List every maintainability and correctness concern, not just style nitpicks.
JOIN ... ON — harder to
verify join conditions are complete and correct, and easy to
accidentally omit one (silent cartesian product risk, Module 16)SELECT * across a 4-table join — unclear what columns actually flow
through, and any schema change silently changes output shapeAND ... OR
combination almost certainly doesn’t mean what the author intended —
OR e1.salary > 100000 likely applies more broadly than intended due to
AND binding tighter than ORORDER BY 3, 1 (positional ordering) — fragile against any column
reordering in SELECT *, and unreadable without cross-referencing
column position“Rewrite this query to fix every issue you identified, preserving its (most likely) intended logic.”
Difficulty: ⚫ Staff/Production Category: Schema Design, Performance Type: I — Design
A dashboard query joining employes, departments, and locations
three ways has become the single most expensive query in the system as
company headcount grew 50x. Someone proposes denormalizing: adding
dept_name and location_city directly onto employes to eliminate the
joins.
Evaluate this proposal.
employes row, or the denormalized copy silently
drifts from the source of truthEXPLAIN that
the join itself (not a missing index) is the cost driver is a common,
expensive mistakeImmediately agreeing to denormalize without questioning whether the actual root cause has been diagnosed yet.
“If you do decide to denormalize, how would you keep the denormalized copy consistent with the source of truth over time?”
Difficulty: ⚫ Staff/Production Category: Data Contracts, Technical Debt Type: J — Production Incident
An upstream team changes an internal API’s employment_status field from
a boolean (active/inactive) to a string enum (active, on_leave,
terminated) as part of a feature launch. A downstream headcount report
that filtered on employment_status = TRUE silently starts undercounting
active headcount (rows with the new enum values don’t match the old
boolean-style filter).
Diagnose why this went undetected until finance flagged wrong numbers, and propose both an immediate fix and a structural prevention.
employment_status IN ('active', 'on_leave') or similar,
depending on the actual business definition of “active headcount” now
that a third state exists)“Design a lightweight automated check that would have caught this specific class of bug before it reached finance’s report.”
Difficulty: ⚫ Staff/Production Category: Batch Processing, Idempotency Type: I — Design
A nightly batch job inserts a salary_history snapshot row per
employee. If the job fails partway and is re-run, it must not produce
duplicate snapshot rows for employees already processed that run.
Design the job’s SQL-level idempotency strategy, not just “add a retry”.
emp_id, snapshot_date) with a unique
constraint, so a re-run’s INSERT either upserts
(ON CONFLICT DO UPDATE / MERGE) or safely no-ops
(ON CONFLICT DO NOTHING) instead of duplicating“Just wrap it in try/catch and retry the whole job” — this doesn’t address duplicate rows if some inserts already succeeded before the failure.
“How does this design change if the snapshot needs to reflect a computed aggregate over other tables that might themselves be updated concurrently while the batch runs?”
Difficulty: ⚫ Staff/Production Category: Schema Design Type: I — Design
An HR dashboard reads employee name, department name, and city on
nearly every page load — currently a 3-table join every time. Someone
proposes denormalizing: storing dept_name and city directly on
employes to avoid the joins.
Evaluate the proposal; state under what conditions it’s the right call.
employes row,
or an explicit sync mechanism) for read-path simplicity and fewer
joinsDenormalizing immediately because “joins are slow,” without measuring whether the join is actually the bottleneck at this data volume.
“At what scale or access pattern would you actually recommend the denormalized column, if not now?”
← Level 6 · Module Home · Next: Level 8 — Interviewer Questions