# Time in Force Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: mid ## 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 ### Why this problem exists in real interviews This is a point-in-time attribution problem wearing a support-desk costume. The skill being probed: can you grade a fact against a policy that drifts, and pin each ticket to the SLA that was actually 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 retroactively flips to breached, and your compliance dashboard rewrites its own history overnight. > **Trick to Solving** > > The moment the prompt says SLA targets get renegotiated over the life of the contract, you have a slowly-changing dimension. Two defensible moves: version the contract as a Type 2 dimension keyed by a surrogate and point each ticket at the version live at open time, or snapshot the SLA threshold directly onto the ticket. Either way the ticket remembers the rule it was judged by. Never resolve the rule at read time. --- ### Break down the requirements #### Step 1: Declare the ticket grain One row in fact_tickets equals one support ticket, identified by ticket_id, with opened_at and resolved_at. Every SLA measure hangs off that single grain, so a ticket appears exactly once in any compliance count. #### Step 2: Version the contract dim_contracts becomes Type 2: a surrogate contract_sk, the natural contract_id, the SLA thresholds, and valid_from / valid_to. Each renegotiation closes the old row and opens a new one. The natural key groups versions; the surrogate addresses one. #### Step 3: Bind the ticket to the version in force At load time, resolve which contract version was valid on opened_at and store that contract_sk on the ticket. Optionally copy the resolution threshold onto the row too. Now the join is by surrogate, so a later renegotiation cannot touch a closed ticket. #### Step 4: Keep the assignment trail Reassignments and escalations go into an append-only fact_ticket_events, one row per transition with occurred_at and actor. The ticket's current engineer is a projection of the latest event, never an overwrite that erases who held it before. --- ### The reference model Below is one defensible design. The anchor is that dim_contracts is versioned and fact_tickets points at the version that was live when the ticket opened, which is what keeps monthly compliance stable as contracts get renegotiated. > **Why this works** > > The Type 2 contract dimension plus the snapshotted threshold give you two independent guarantees of stability: the surrogate join is frozen, and even a schema change to the contract cannot alter a closed ticket's threshold. The event fact preserves the assignment trail without bloating the ticket grain. > **Interviewers Watch For** > > A strong candidate says the words Type 2 and point-in-time before drawing anything, and asks whether old tickets keep the old SLA. They store business_mins_to_resolve and a breach flag as ingredients, not a compliance percentage. Weak candidates give tickets a plain contract_id FK and only notice the re-grading bug when asked what happens after a renegotiation. > **Common Pitfall** > > Joining fact_tickets to a single current-state contracts table on contract_id. It looks correct until the first renegotiation, at which point last quarter's compliance number silently changes and finance stops trusting the dashboard. Storing a precomputed breach_pct is the sibling mistake: it cannot roll up from client to team to month. > **How It Scales** > > At 5 million tickets a year the surrogate FK on fact_tickets is a plain integer join into a few thousand contract versions, cheap and prunable. Partition fact_tickets by opened_date_key so monthly compliance scans one month, and cluster fact_ticket_events by ticket_id for fast history reconstruction. --- ### The analysis pattern **Monthly SLA compliance per client** ```sql 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 when a contract is renegotiated.* --- ### Trade-offs and alternatives **Type 2 dim plus snapshot on the ticket** Ticket points at contract_sk and carries sla_resolution_mins_at_open. * History is immutable by construction * Compliance queries are a simple GROUP BY * Two representations of the threshold to keep in sync at load **Single current-state contract, resolve SLA at read time** Ticket carries only contract_id; the threshold is joined live. * One contract row, no versioning to maintain * Every renegotiation silently rewrites past compliance * Point-in-time reads need a temporal range join even when it works --- ## Common follow-up questions - The SLA clock should count only business hours in the client's timezone and pause while waiting on the client. Where does that logic live? _(Tests whether elapsed-time is a derived load-time measure over the event log plus a business calendar, not raw wall-clock subtraction.)_ - A ticket is reassigned across three teams before it closes. How do you attribute the breach to a team? _(Tests whether the candidate reasons from fact_ticket_events and picks a defensible attribution rule (opening team, closing team, or holder at breach).)_ - How would you support an as-of-any-date view of which SLA applied to a given contract? _(Tests point-in-time reconstruction against the Type 2 valid_from / valid_to interval.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/time-in-force-sla-model) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.