Data Warehouse: Reusable SQL Models for IT Operations Metrics

The first version of most IT operations reporting is a folder of .sql files, each written for a specific dashboard or a question someone asked once and needed answered by Friday. That works right up until two dashboards report different numbers for the same metric. Mean time to resolution, say, because one query counted a reopened ticket once and the other counted it twice, and nobody remembers which is "correct" because both were written under deadline pressure by different people six months apart. Reusable models exist to prevent exactly that drift, and they're worth building well before the pile of one-off queries gets large enough to hurt.

Below is the model set I'd start with for a service desk: MTTR, ticket aging, SLA compliance, and patch compliance. It's plain PostgreSQL views, a function, a deploy script, and SQL tests, followed by the same thing in dbt for teams that want it. It builds on the raw / staging / marts layout from building a reporting layer without a BI team. Source field names are placeholders for whatever your PSA and RMM actually return.

A metric should have exactly one definition

The core idea is unglamorous: every metric that gets reported more than once gets one model that defines it, and every dashboard or downstream query references that model instead of recalculating the metric from raw tables. Mean time to resolution gets defined once, in a single view: what counts as "resolved," how reopens are handled, which ticket types are excluded. Every report pulls from it. When someone (rightly) asks to change the definition, there's one place to change it, and every consumer updates together instead of drifting apart one query at a time.

Write the definition into the database itself, not only a wiki page. COMMENT ON VIEW shows up in psql's \dv+ listing and in most SQL clients, right where someone is about to use the view.

Build in layers, not one wide query

The models that have held up best in practice follow a staging-then-mart pattern, even at small scale. A staging layer cleans and normalizes each source (deduplicating records, standardizing status values, converting types) without imposing business logic yet. A mart layer built on clean staging applies the actual metric definitions. Skip the staging layer and write metric logic directly against a raw ticketing export, and every model reimplements the same cleanup. A source schema change then breaks every downstream model separately instead of in one traceable place.

Here's the chain for resolution time. Staging exposes the ticket and its status history as typed columns:

sql

-- models/010_stg_ticket_status_changes.sql
CREATE OR REPLACE VIEW staging.stg_ticket_status_changes AS
SELECT payload ->> 'ticketId'                 AS ticket_id,
       (payload ->> 'changedAt')::timestamptz AS changed_at,
       lower(trim(payload ->> 'fromStatus'))  AS from_status,
       lower(trim(payload ->> 'toStatus'))    AS to_status
FROM raw.psa_ticket_status_changes;

An intermediate model does the one hard part, reopens, exactly once. A reopen is any transition out of a resolved state:

sql

-- models/020_int_ticket_lifecycle.sql
CREATE OR REPLACE VIEW staging.int_ticket_lifecycle AS
WITH changes AS (
    SELECT ticket_id,
           changed_at,
           to_status   IN ('resolved', 'closed') AS enters_resolved,
           from_status IN ('resolved', 'closed') AS leaves_resolved
    FROM staging.stg_ticket_status_changes
)
SELECT ticket_id,
       min(changed_at) FILTER (WHERE enters_resolved)                     AS first_resolved_at,
       max(changed_at) FILTER (WHERE enters_resolved)                     AS last_resolved_at,
       count(*)        FILTER (WHERE leaves_resolved AND NOT enters_resolved) AS reopen_count
FROM changes
GROUP BY ticket_id;

The fact model applies the business definition and documents it:

sql

-- models/030_fct_ticket_resolution.sql
CREATE OR REPLACE VIEW marts.fct_ticket_resolution AS
SELECT t.ticket_id,
       t.client_id,
       t.priority,
       t.queue,
       t.created_at,
       l.first_resolved_at,
       l.last_resolved_at,
       l.reopen_count,
       l.last_resolved_at - t.created_at   AS time_to_resolve,
       t.first_response_at - t.created_at  AS time_to_first_response
FROM staging.stg_tickets t
JOIN staging.int_ticket_lifecycle l USING (ticket_id)
WHERE t.status IN ('resolved', 'closed')
  AND t.queue NOT IN ('spam', 'merged', 'internal-project');

COMMENT ON VIEW marts.fct_ticket_resolution IS
  'One row per resolved ticket. Resolution time runs from creation to the FINAL resolution, '
  'so a reopened ticket counts once with its full elapsed time. Excludes spam, merged, and '
  'internal project queues. Wall-clock time, not business hours. Owner: service desk lead.';

Everything else reads from that one model. MTTR by month and priority, with the median next to the mean because a few tickets left open for weeks drag the average far from what a typical customer sees:

sql

-- models/040_mttr_monthly.sql
CREATE OR REPLACE VIEW marts.mttr_monthly AS
SELECT date_trunc('month', last_resolved_at)::date AS month,
       priority,
       count(*)                                      AS tickets_resolved,
       round((avg(extract(epoch FROM time_to_resolve)) / 3600)::numeric, 1) AS mttr_hours,
       round((percentile_cont(0.5) WITHIN GROUP (
                 ORDER BY extract(epoch FROM time_to_resolve)::float8) / 3600)::numeric, 1) AS median_hours,
       count(*) FILTER (WHERE reopen_count > 0)      AS reopened_tickets
FROM marts.fct_ticket_resolution
GROUP BY 1, 2;

SLA compliance joins against a small, version-controlled targets table instead of hardcoding hours in the query. A ticket with no first response yet counts as a miss, because a null comparison never satisfies the FILTER:

sql

-- models/050_sla_compliance_monthly.sql
CREATE TABLE IF NOT EXISTS staging.sla_targets (
    priority        text PRIMARY KEY,
    first_response  interval NOT NULL,
    resolution      interval NOT NULL
);

INSERT INTO staging.sla_targets VALUES
    ('p1', interval '15 minutes', interval '4 hours'),
    ('p2', interval '1 hour',     interval '8 hours'),
    ('p3', interval '4 hours',    interval '3 days'),
    ('p4', interval '1 day',      interval '10 days')
ON CONFLICT (priority) DO UPDATE
    SET first_response = EXCLUDED.first_response, resolution = EXCLUDED.resolution;

CREATE OR REPLACE VIEW marts.sla_compliance_monthly AS
SELECT date_trunc('month', f.created_at)::date AS month,
       f.priority,
       count(*) AS tickets,
       round(100.0 * count(*) FILTER (WHERE f.time_to_first_response <= s.first_response) / count(*), 1) AS first_response_pct,
       round(100.0 * count(*) FILTER (WHERE f.time_to_resolve <= s.resolution) / count(*), 1)            AS resolution_pct
FROM marts.fct_ticket_resolution f
JOIN staging.sla_targets s USING (priority)
GROUP BY 1, 2;

The targets above are example values. Use the ones in your actual contracts.

Ticket aging answers "how bad is the backlog right now," so it reads open tickets from staging rather than the resolved-ticket fact:

sql

-- models/060_ticket_aging.sql
CREATE OR REPLACE VIEW marts.ticket_aging AS
SELECT client_id,
       priority,
       CASE
           WHEN age < interval '1 day'   THEN '1: under 1 day'
           WHEN age < interval '3 days'  THEN '2: 1-3 days'
           WHEN age < interval '7 days'  THEN '3: 3-7 days'
           WHEN age < interval '30 days' THEN '4: 7-30 days'
           ELSE '5: over 30 days'
       END      AS age_bucket,
       count(*) AS open_tickets
FROM (
    SELECT client_id, priority, now() - created_at AS age
    FROM staging.stg_tickets
    WHERE status NOT IN ('resolved', 'closed', 'cancelled')
) AS open_tickets
GROUP BY 1, 2, 3;

Patch compliance follows the same shape against RMM data. The definition, written in one place, is that a device is compliant when it has no missing critical updates and has checked in recently. That second condition matters: a laptop that hasn't reported in a month shows zero missing updates only because nobody has asked it.

sql

-- models/070_patch_compliance.sql
CREATE OR REPLACE VIEW marts.patch_compliance AS
SELECT client_id,
       os_family,
       count(*)                                    AS active_devices,
       count(*) FILTER (WHERE is_compliant)        AS compliant_devices,
       round(100.0 * count(*) FILTER (WHERE is_compliant) / nullif(count(*), 0), 1) AS compliance_pct,
       count(*) FILTER (WHERE NOT reported_recently) AS not_reporting_devices
FROM (
    SELECT client_id,
           os_family,
           last_seen_at >= now() - interval '14 days' AS reported_recently,
           missing_critical_updates = 0
             AND last_seen_at >= now() - interval '14 days' AS is_compliant
    FROM staging.stg_device_patch_status
    WHERE NOT is_retired
) AS devices
GROUP BY 1, 2;

COMMENT ON VIEW marts.patch_compliance IS
  'Compliant = zero missing critical updates AND checked in within 14 days. '
  'Retired devices excluded. Not-reporting devices count as non-compliant.';

Parameterize the model, not the copy

The second most common failure after duplicated logic is a model that only serves one dashboard's filters (one date range, one client, one team) hardcoded into the query. The fix isn't a new copy of the query for every variant. It's a model that exposes the varying pieces as columns or parameters and keeps the metric logic itself in one place. Every view above already does the first half: month, client_id, and priority are columns, so a dashboard filters them instead of editing SQL. When a caller needs an arbitrary date range, a set-returning SQL function wraps the same fact model:

sql

-- models/080_fn_mttr.sql
CREATE OR REPLACE FUNCTION marts.mttr(p_from date, p_to date, p_client_id bigint DEFAULT NULL)
RETURNS TABLE (priority text, tickets_resolved bigint, mttr_hours numeric, median_hours numeric)
LANGUAGE sql
STABLE
AS $$
    SELECT f.priority,
           count(*),
           round((avg(extract(epoch FROM f.time_to_resolve)) / 3600)::numeric, 1),
           round((percentile_cont(0.5) WITHIN GROUP (
                     ORDER BY extract(epoch FROM f.time_to_resolve)::float8) / 3600)::numeric, 1)
    FROM marts.fct_ticket_resolution f
    WHERE f.last_resolved_at >= p_from
      AND f.last_resolved_at <  p_to + 1
      AND (p_client_id IS NULL OR f.client_id = p_client_id)
    GROUP BY f.priority
    ORDER BY f.priority
$$;

-- Q3 for every client, then Q3 for one client
SELECT * FROM marts.mttr('2026-07-01', '2026-09-30');
SELECT * FROM marts.mttr('2026-07-01', '2026-09-30', 1042);

A tool like dbt makes this natural with its templating, but plain SQL views with a consistent set of filter columns get most of the same benefit without adopting a new toolchain. That matters when there's no dedicated team to own that toolchain.

Materialize only when a view gets slow

Every model above is a plain view, which means every dashboard load re-runs the whole chain down to raw. That's fine as long as the whole chain re-runs faster than people will wait for a dashboard tile, which at service desk volumes it usually does, and views have one big advantage: they're never stale. It stops being fine when a tile's query takes longer than that and EXPLAIN ANALYZE shows the time going into re-deriving the chain, typically once the raw status-change history has grown or several tiles hit the same model at once. When one does get slow, materialize that model alone and keep its name, so nothing downstream has to change. Replace the contents of 030 with:

sql

-- models/030_fct_ticket_resolution.sql, materialized version
DROP MATERIALIZED VIEW IF EXISTS marts.fct_ticket_resolution CASCADE;
CREATE MATERIALIZED VIEW marts.fct_ticket_resolution AS
SELECT t.ticket_id, t.client_id, t.priority, t.queue, t.created_at,
       l.first_resolved_at, l.last_resolved_at, l.reopen_count,
       l.last_resolved_at - t.created_at  AS time_to_resolve,
       t.first_response_at - t.created_at AS time_to_first_response
FROM staging.stg_tickets t
JOIN staging.int_ticket_lifecycle l USING (ticket_id)
WHERE t.status IN ('resolved', 'closed')
  AND t.queue NOT IN ('spam', 'merged', 'internal-project');

CREATE UNIQUE INDEX fct_ticket_resolution_ticket_id ON marts.fct_ticket_resolution (ticket_id);

-- run after each load, outside deploy.sh
REFRESH MATERIALIZED VIEW CONCURRENTLY marts.fct_ticket_resolution;

CONCURRENTLY lets dashboards keep reading during the refresh. PostgreSQL only allows it when the materialized view has at least one unique index on plain column names, with no expression and no WHERE clause, which is what the ticket_id index is for. The first time, drop the old plain view by hand (DROP VIEW marts.fct_ticket_resolution CASCADE;), since the materialized drop won't touch a plain view. After that, every deploy rebuilds the fact, and CASCADE drops the views built on it, which files 040 onward recreate in the same transaction. The numbered layout makes that a normal deploy, not a special procedure.

Version control the models like application code

SQL models for metrics that leadership makes decisions from deserve the same discipline as production code: checked into source control, reviewed before a definition changes, with a history someone can point to when a number moves and a stakeholder asks why. I've seen too many "why did our SLA compliance number jump overnight" conversations resolve to an undocumented WHERE clause edit made directly against a production view, with no record of who changed it or when. A pull request with a one-line description would have made that a five-minute answer instead of a half-day investigation.

The repository needs nothing more than numbered model files, test files, and a deploy script:

text

reporting-models/
  models/010_stg_ticket_status_changes.sql
  models/020_int_ticket_lifecycle.sql
  models/030_fct_ticket_resolution.sql
  ...
  tests/assert_fct_ticket_unique.sql
  tests/assert_resolution_not_negative.sql
  tests/assert_priorities_have_sla.sql
  deploy.sh

Tests follow the convention dbt uses for singular data tests: each file is a query that returns the rows that break an assertion, and zero rows means pass.

sql

-- tests/assert_fct_ticket_unique.sql
SELECT ticket_id, count(*) FROM marts.fct_ticket_resolution GROUP BY ticket_id HAVING count(*) > 1;

-- tests/assert_resolution_not_negative.sql
SELECT ticket_id, time_to_resolve FROM marts.fct_ticket_resolution WHERE time_to_resolve < interval '0';

-- tests/assert_priorities_have_sla.sql
SELECT DISTINCT t.priority FROM staging.stg_tickets t
LEFT JOIN staging.sla_targets s USING (priority)
WHERE s.priority IS NULL;

The deploy script applies every model in one transaction, so a broken model (one that fails to apply) leaves the previous versions in place, then runs the tests. The tests run after that transaction has committed, so a failing test doesn't roll anything back: the new views are already live, and the non-zero exit is a signal to fix forward or revert the commit and redeploy:

bash

#!/usr/bin/env bash
# deploy.sh: apply models in order in one transaction, then run SQL tests.
# Needs psql and MODELS_DSN for the transformer role. Exits non-zero on any failure.
set -euo pipefail
cd "$(dirname "$0")"

args=()
for model in models/*.sql; do
  args+=(--file "$model")
done
psql "$MODELS_DSN" --set ON_ERROR_STOP=1 --single-transaction --quiet "${args[@]}"
echo "Applied ${#args[@]} model file(s)."

failed=0
for test in tests/*.sql; do
  # A psql error stops the script here (set -e); only grep's no-match status is tolerated.
  output=$(psql "$MODELS_DSN" --set ON_ERROR_STOP=1 --no-align --tuples-only --file "$test")
  rows=$(grep -c . <<<"$output" || true)
  if [[ "$rows" -gt 0 ]]; then
    echo "FAIL $test ($rows row(s))"
    failed=1
  else
    echo "pass $test"
  fi
done
exit "$failed"

Run it from CI on every merge to the main branch. The pull request becomes the changelog.

The same models in dbt

If the model count grows, or someone on the team already knows dbt, the move is mostly mechanical. Each view becomes a select in its own file, table names become ref() calls so dbt can work out the build order, and the test files become YAML:

sql

-- models/marts/fct_ticket_resolution.sql
{{ config(materialized='view') }}

select t.ticket_id,
       t.client_id,
       t.priority,
       t.queue,
       t.created_at,
       l.last_resolved_at,
       l.reopen_count,
       l.last_resolved_at - t.created_at as time_to_resolve,
       t.first_response_at - t.created_at as time_to_first_response
from {{ ref('stg_tickets') }} t
join {{ ref('int_ticket_lifecycle') }} l using (ticket_id)
where t.status in ('resolved', 'closed')
  and t.queue not in ('spam', 'merged', 'internal-project')

yaml

# models/marts/schema.yml
models:
  - name: fct_ticket_resolution
    description: >
      One row per resolved ticket. Resolution time runs to the final resolution,
      so reopened tickets count once. Excludes spam, merged, and internal project queues.
    columns:
      - name: ticket_id
        data_tests:
          - unique
          - not_null
      - name: priority
        data_tests:
          - accepted_values:
              arguments:
                values: ['p1', 'p2', 'p3', 'p4']

data_tests: is the current key (tests: still works as an alias), and nesting inputs under arguments: applies from dbt 1.10.5. On older versions, put values directly under the test name. dbt build then runs models and tests in dependency order, which replaces deploy.sh.

Reusable doesn't mean over-engineered

The temptation once this pattern clicks is to model everything, including metrics nobody has asked for twice. My rule of thumb: a query becomes a model the second time it's needed, not before. The first time, a one-off is fine. That keeps the model library focused on metrics that have proven they'll be asked about again, instead of piling up unused abstraction that's harder to maintain than the duplicated queries it was meant to replace.