The Vanishing State

An easy Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
easy
Seniority
L3
Asked in
Amazon

Problem

We run an IT helpdesk platform. Users submit support tickets, which are assigned to agents. Tickets go through multiple status changes before being resolved. SLA compliance is critical: P1 tickets must be resolved within 4 hours, P2 within 24 hours. Design the schema, and describe how you would load data from a JSON API feed into it.

A status column forgets the moment it changes. Model the schema that remembers.

Worked solution and explanation

Why this problem exists in real interviews

This is a per-ticket audit trail dressed up as a helpdesk schema. The skill being probed: can you tell that SLA compliance is a history problem, not a current-state problem? A status column on tickets is overwritten the minute the ticket transitions, so the answer lives in an immutable status event log paired with a priority dimension that encodes the SLA contract. Anyone can draw four tables; the tell is whether the status log and the priority dimension appear before you ask for them. Get it wrong and you can report a ticket's status today but can never answer 'how long did it sit in P1 before resolution' because that moment was already erased.

Break down the requirements

Step 1: Separate the header from the history

tickets is the stable ticket identity. ticket_status_events is the append-only log of status changes. The current status on tickets is a cached convenience, not the source of truth.

Step 2: Model priority as a dimension

priority_sla carries (priority, resolution_sla_hours, response_sla_hours). P1 and P2 are rows, not strings scattered across the ticket table, so changing the SLA thresholds is a single update. Keying it by the natural 'P1' code or by a surrogate priority_id are both defensible; what matters is that the SLA hours live here.

Step 3: Append status changes

Every status transition (new, assigned, in-progress, waiting, resolved) is a new row on ticket_status_events. SLA breach time can be derived from the first 'resolved' event compared with the create time plus the SLA hours from the priority dimension.

Step 4: Keep agents and users as conformed dimensions

Both are first-class dimensions. tickets carries the current assigned agent; historical reassignments live in the event log.

The solution

Below is one defensible design: a stable ticket header, an append-only status event log, and a priority dimension that encodes the SLA contract. The graded canonical solution is the SLA-breach query further down, which reads directly off this shape.

`ticket_status_events` is append-only; `priority_sla` encodes the contract so SLA changes are a single row update.
  • agents: agent_id INT PK, agent_name TEXT, team TEXT, timezone TEXT
  • users: user_id BIGINT PK, email TEXT, company TEXT, plan_tier TEXT
  • priority_sla: priority TEXT PK, response_sla_hours INT, resolution_sla_hours INT
  • tickets: ticket_id BIGINT PK, user_id BIGINT FK, agent_id INT FK, priority TEXT FK, subject TEXT, created_at TIMESTAMP, current_status TEXT
  • ticket_status_events: event_id BIGINT PK, ticket_id BIGINT FK, from_status TEXT, to_status TEXT, event_ts TIMESTAMP, actor_agent_id INT FK
  • users references tickets
  • agents references tickets
  • priority_sla references tickets
  • tickets references ticket_status_events
  • agents references ticket_status_events

The analysis pattern

This query is the payoff of the schema above: because status lives in an append-only log and the SLA hours live in priority_sla, breach rate falls out of a single grouped join. Note there are zero hardcoded thresholds; the contract comes from the dimension.

SLA breach rate by priority for the last week
WITH resolved AS (
    SELECT
        t.ticket_id,
        t.priority,
        t.created_at,
        MIN(e.event_ts) AS resolved_at
    FROM tickets t
    JOIN ticket_status_events e ON e.ticket_id = t.ticket_id
    WHERE e.to_status = 'resolved'
      AND t.created_at >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY t.ticket_id, t.priority, t.created_at
)
SELECT
    r.priority,
    COUNT(*) AS resolved_count,
    SUM(CASE WHEN r.resolved_at > r.created_at + MAKE_INTERVAL(hours => p.resolution_sla_hours) THEN 1 ELSE 0 END) AS breached
FROM resolved r
JOIN priority_sla p ON p.priority = r.priority
GROUP BY r.priority

MIN(event_ts) over the resolved events gives the first resolution; the SLA hours are pulled from the dimension, never hardcoded.

Trade-offs and alternatives

Header plus append-only event log
Clean relational queries, indexable events, easy SLA computation. Cost: every status change is two writes (event plus cached current_status).
Single tickets table with status history JSON
One row per ticket, history nested inside a JSONB column. Cost: history queries rely on JSONB functions, window queries are awkward, and indexing the nested events is expensive.

Common follow-up questions