Time in Force

A medium 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
medium
Seniority
mid
Asked in
DXC Technology

Problem

We run a managed IT services business handling support tickets under client contracts whose SLA targets get renegotiated over the life of a contract. Design a warehouse model for monthly SLA compliance per client, where every ticket is judged against the SLA that was in force the day it was opened, alongside mean time to resolve per engineering team.

Worked solution and explanation

What this problem really is

This is a point-in-time attribution problem dressed up as a support desk. The real question: can you grade a fact against a policy that changes, pinning each ticket to the SLA that was in force the day it opened? Anyone can draw a tickets table with an FK to contracts. The trap is joining tickets to the current contract row. Renegotiate one client to a tighter SLA and every closed ticket from last year flips to breached overnight. Your compliance dashboard has rewritten its own history.

Build it in four decisions

Step 1: Declare the ticket grain

One row in fact_tickets is one support ticket, keyed by ticket_key with the source ticket_id alongside. Every SLA measure hangs off that grain, so a ticket is counted exactly once in any compliance rate.

Step 2: Version dim_contracts as Type 2

Surrogate contract_sk, natural contract_id, the SLA thresholds, and valid_from / valid_to. A renegotiation closes the old row and opens a new one. The natural key groups the versions; the surrogate points at exactly one.

Step 3: Bind the ticket to the version in force

At load, find the version whose interval contains opened_at, store its contract_sk, and compute is_breached from business_mins_to_resolve against sla_resolution_mins_at_open. A later renegotiation now has nothing to touch.

Step 4: Keep the assignment trail separate

Reassignments go into append-only fact_ticket_events, one row per transition, linked by ticket_key. current_engineer_key is a projection of the latest event, not an overwrite that erases who held the ticket before.

The reference model

`dim_contracts` is Type 2; `fact_tickets` carries `contract_sk` and `sla_resolution_mins_at_open`, so history never re-grades.
  • dim_clients: client_key INT PK, client_name TEXT, industry TEXT, timezone TEXT
  • dim_contracts: contract_sk BIGINT PK, contract_id BIGINT, client_key INT FK, tier TEXT, sla_response_mins INT, sla_resolution_mins INT, valid_from TIMESTAMP, valid_to TIMESTAMP
  • dim_engineers: engineer_key INT PK, full_name TEXT, team TEXT, hired_at DATE
  • dim_date: date_key INT PK, full_date DATE, month INT, is_business_day BOOLEAN
  • fact_tickets: ticket_key BIGINT PK, ticket_id BIGINT, client_key INT FK, contract_sk BIGINT FK, opened_date_key INT FK, current_engineer_key INT FK, opened_at TIMESTAMP, resolved_at TIMESTAMP, sla_resolution_mins_at_open INT, business_mins_to_resolve INT, is_breached BOOLEAN
  • fact_ticket_events: event_id BIGINT PK, ticket_key BIGINT FK, event_type TEXT, engineer_key INT FK, occurred_at TIMESTAMP
  • dim_contracts.client_key references dim_clients.client_key
  • fact_tickets.client_key references dim_clients.client_key
  • fact_tickets.contract_sk references dim_contracts.contract_sk
  • fact_tickets.opened_date_key references dim_date.date_key
  • fact_tickets.current_engineer_key references dim_engineers.engineer_key
  • fact_ticket_events.engineer_key references dim_engineers.engineer_key
  • fact_ticket_events.ticket_key references fact_tickets.ticket_key

The analysis it enables

Monthly SLA compliance per client
SELECT
    cl.client_name,
    d.month,
    COUNT(*) AS tickets,
    SUM(CASE WHEN t.is_breached THEN 0 ELSE 1 END)::NUMERIC
        / NULLIF(COUNT(*), 0) AS compliance_rate
FROM fact_tickets t
JOIN dim_clients cl ON cl.client_key = t.client_key
JOIN dim_date d ON d.date_key = t.opened_date_key
WHERE t.resolved_at IS NOT NULL
GROUP BY cl.client_name, d.month
ORDER BY cl.client_name, d.month

is_breached was fixed at load against sla_resolution_mins_at_open, so this number never moves after a renegotiation.

Type 2 dimension plus snapshot
The ticket points at contract_sk and carries sla_resolution_mins_at_open. History is immutable by construction and compliance is a plain GROUP BY. The cost: the threshold lives in two places, so the load must keep them consistent.
Current-state contract, SLA joined live
The ticket carries only contract_id and the threshold is read at query time. One contract row, nothing to version. The cost: every renegotiation rewrites past compliance, and fixing it later needs a range join on valid_from / valid_to you never modeled.

Common follow-up questions