Model the ticket lifecycle with an accumulating snapshot fact (one row per ticket, updated as it moves through milestones), plus a status history fact for the full trail, and ordinary dimensions around them.
Sources and freshness
The ticketing tool (Zendesk, Freshdesk or similar) has an API and often webhooks. Polling with updated_at > watermark is reliable but can be minutes late. Webhooks give near-instant events, but can be missed. A good design uses both: webhooks for freshness, and a frequent incremental poll with overlap as a safety net that repairs any gaps. If the target is a 15-minute freshness SLA, polling every 5 to 10 minutes can be enough.
Land raw JSON untouched, then flatten into staging tables.
The facts
Accumulating snapshot, fact_ticket, one row per ticket:
ticket_id | created_ts | first_response_ts | resolved_ts | closed_ts agent_key | customer_key | channel_key | priority_key time_to_first_response_min | time_to_resolve_min | reopen_count
Milestone columns start NULL and are filled as the ticket progresses, so the row is updated several times, and MERGE by ticket_id handles it. Derived durations make SLA reporting simple.
Status history, fact_ticket_status_change, one row per change (ticket, from status, to status, changed_by, changed_at). It answers questions the snapshot cannot, such as how long tickets wait in "pending customer", or how often tickets bounce between agents.
Dimensions
dim_agent, dim_customer, dim_channel (email, chat, phone), dim_priority, dim_date and a time-of-day dimension. Agents change teams, so give dim_agent SCD Type 2 so that old tickets are credited to the team the agent was in then.
Metrics
First response time, resolution time, backlog (tickets open at a point in time, which comes from the status history), reopen rate, tickets per agent, and SLA breach rate. Define business hours once: do response times exclude nights and weekends? Decide with the support leads, and write it in the metric definition.
Quality and operations
Check that every ticket has an agent or a queue, that timestamps are in order (resolved after created), and that counts match the source tool daily. Late changes to old tickets (a reopened ticket from last month) must update the old rows, which the MERGE does. Alert if the data is more than 15 minutes stale.