Data Warehouse: Building a Reporting Layer Without a BI Team

Every infrastructure team eventually piles up data it can't answer questions with. Ticket volumes live in the PSA, uptime lives in the monitoring platform, change records live in a spreadsheet someone started three years ago and never stopped using. Leadership asks a reasonable question, like "which client's environment generates the most after-hours tickets," and the honest answer is that nobody can produce it without a day of manual exporting and VLOOKUPs. A BI team would fix this properly. Most IT organizations I've worked with don't have one and won't get one budgeted anytime soon. The reporting layer still has to get built, just by whoever's already in the room.

This post is the build I'd reach for in that situation, with the actual SQL and the loader script. Everything runs on one PostgreSQL database and a scheduler you already have.

Start with one source of truth, not five

The instinct when you finally get permission to build something is to connect everything at once (the PSA, the RMM, the monitoring tool, the identity provider) and design a grand unified model. That's how these projects die before they ship anything. I've had better luck picking the single dataset that answers the most urgent recurring question, building a small, ugly, correct pipeline for just that, and shipping a report people actually use within the first month. Momentum and trust come from something working, not from an architecture diagram.

For most IT operations teams that first dataset is tickets. It answers volume, response and resolution time, and backlog questions, and every PSA has some kind of API for it.

Pick boring tools on purpose

Without a BI team, there's no one to maintain a Snowflake instance with a dbt pipeline feeding a Looker deployment. That's fine, because that stack solves a scale problem a shop this size doesn't have yet. A single Postgres database (or a managed one: Cloud SQL, Azure Database for PostgreSQL, RDS), a few scheduled scripts doing extract-and-load, and a reporting tool the organization already licenses will carry an IT operations reporting need further than the fancy version. It's something the same one or two people who built it can actually keep running.

The one structural decision worth making up front is three schemas with three roles, so nobody has to remember which tables are safe to point a dashboard at:

Schema	Holds	Written by	Read by
`raw`	API responses as landed, one row per source record, JSON payload intact	`etl_loader`	`transformer`
`staging`	Views that pull typed, cleaned columns out of `raw`	`transformer`	`transformer`
`marts`	Metric views and tables the dashboards use	`transformer`	`report_reader`

sql

CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS staging;
CREATE SCHEMA IF NOT EXISTS marts;

CREATE ROLE etl_loader    LOGIN PASSWORD '<password>';
CREATE ROLE transformer   LOGIN PASSWORD '<password>';
CREATE ROLE report_reader LOGIN PASSWORD '<password>';

-- loader owns raw
GRANT USAGE, CREATE ON SCHEMA raw TO etl_loader;

-- transformer reads raw, builds staging and marts
GRANT USAGE ON SCHEMA raw TO transformer;
ALTER DEFAULT PRIVILEGES FOR ROLE etl_loader IN SCHEMA raw GRANT SELECT ON TABLES TO transformer;
GRANT USAGE, CREATE ON SCHEMA staging, marts TO transformer;

-- dashboards see marts only, including views created later
GRANT USAGE ON SCHEMA marts TO report_reader;
ALTER DEFAULT PRIVILEGES FOR ROLE transformer IN SCHEMA marts GRANT SELECT ON TABLES TO report_reader;
ALTER ROLE report_reader SET statement_timeout = '60s';

ALTER DEFAULT PRIVILEGES ... FOR ROLE applies to objects that role creates in the future. Without it, every new view is invisible to the dashboard account until someone remembers to GRANT it, which is exactly the kind of tribal knowledge that doesn't survive a handoff. Default privileges don't reach back to existing objects, so grant those once by hand.

The extract step is where most of the effort goes

Nobody warns you that the actual warehouse part, a database with some fact tables, is the easy part. The hard part is getting clean, consistent data out of tools that were never designed to be queried by anything other than their own UI. PSA and RMM APIs paginate inconsistently, rate-limit without clear documentation, and return null in places their own UI quietly fills in a default. Budget twice the time you think you need for extraction and normalization, and assume every source has at least one field that means something different than its name suggests.

Two habits make that manageable:

Land the raw payload untouched. Store each record's full JSON in a jsonb column and pull typed columns out later in a view. When you discover the closed_date field means something odd, you fix a view, not a reload of two years of history.
Upsert on the source ID. Re-running the loader, or overlapping two runs, should never create duplicates.

The loader below does both. It's written against a generic REST API that returns a JSON array per page and supports an updated since filter, and it follows an RFC 8288 Link: <...>; rel="next" header for pagination. That's the part you adapt per vendor. It honors 429 Too Many Requests with a Retry-After header given in seconds, falling back to exponential backoff otherwise (RFC 6585 says servers may send one), and it writes a row to raw.etl_runs so the pipeline's health is queryable. It needs Python 3.10+, requests, and psycopg2-binary.

sql

CREATE TABLE IF NOT EXISTS raw.psa_tickets (
    source_id  text PRIMARY KEY,
    payload    jsonb NOT NULL,
    loaded_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS raw.etl_runs (
    run_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source      text NOT NULL,
    started_at  timestamptz NOT NULL,
    finished_at timestamptz,
    status      text NOT NULL,
    rows_loaded integer,
    error       text
);

python

#!/usr/bin/env python3
"""
load_api_to_raw.py

Pulls records changed since the last successful run from a paginated REST API
and upserts them into a raw jsonb landing table in PostgreSQL. Records every
run (success or failure) in raw.etl_runs.

Usage:
    PSA_TOKEN=... python load_api_to_raw.py --dsn <dsn> --source psa_tickets \
        --url https://psa.example.com/api/v1/tickets --since-param updatedSince
Reads:  the API at --url (bearer token from PSA_TOKEN); raw.etl_runs.
Writes: raw.<source> (upsert on source_id); one row in raw.etl_runs.
"""

import argparse
import datetime as dt
import os
import sys
import time
from urllib.parse import urljoin

import psycopg2
import requests
from psycopg2 import sql
from psycopg2.extras import Json, execute_values

MAX_RETRIES = 5


def last_success(cur, source):
    cur.execute(
        "SELECT max(started_at) FROM raw.etl_runs WHERE source = %s AND status = 'success'",
        (source,),
    )
    return cur.fetchone()[0]


def fetch_pages(session, url, params):
    """Yield one page (a list of records) at a time, following Link rel=next."""
    while url:
        for attempt in range(MAX_RETRIES):
            response = session.get(url, params=params, timeout=60)
            if response.status_code != 429:
                break
            retry_after = response.headers.get("Retry-After", "")
            time.sleep(int(retry_after) if retry_after.isdigit() else 2 ** attempt)
        response.raise_for_status()
        yield response.json()
        next_url = response.links.get("next", {}).get("url")
        url = urljoin(response.url, next_url) if next_url else None  # RFC 8288 allows relative links
        params = None  # the next link already carries the query string


def main() -> int:
    parser = argparse.ArgumentParser(description="Load changed API records into a raw jsonb table.")
    parser.add_argument("--dsn", required=True)
    parser.add_argument("--source", required=True, help="Raw table name, e.g. psa_tickets")
    parser.add_argument("--url", required=True)
    parser.add_argument("--since-param", default="updatedSince")
    parser.add_argument("--id-field", default="id")
    parser.add_argument("--overlap-minutes", type=int, default=15)
    args = parser.parse_args()

    session = requests.Session()
    session.headers["Authorization"] = f"Bearer {os.environ['PSA_TOKEN']}"
    started = dt.datetime.now(dt.timezone.utc)
    upsert = sql.SQL(
        "INSERT INTO {table} (source_id, payload) VALUES %s "
        "ON CONFLICT (source_id) DO UPDATE SET payload = EXCLUDED.payload, loaded_at = now()"
    ).format(table=sql.Identifier("raw", args.source))

    conn = psycopg2.connect(args.dsn)
    conn.autocommit = True
    rows = 0
    try:
        with conn.cursor() as cur:
            since = last_success(cur, args.source)
            params = {}
            if since:
                params[args.since_param] = (since - dt.timedelta(minutes=args.overlap_minutes)).isoformat()
            for page in fetch_pages(session, args.url, params):
                values = [(str(record[args.id_field]), Json(record)) for record in page]
                if values:
                    execute_values(cur, upsert.as_string(conn), values, page_size=500)
                    rows += len(values)
            cur.execute(
                "INSERT INTO raw.etl_runs (source, started_at, finished_at, status, rows_loaded) "
                "VALUES (%s, %s, now(), 'success', %s)",
                (args.source, started, rows),
            )
        print(f"{args.source}: {rows} records upserted")
        return 0
    except Exception as error:
        with conn.cursor() as cur:
            cur.execute(
                "INSERT INTO raw.etl_runs (source, started_at, finished_at, status, rows_loaded, error) "
                "VALUES (%s, %s, now(), 'failed', %s, %s)",
                (args.source, started, rows, str(error)[:2000]),
            )
        print(f"{args.source}: failed after {rows} records: {error}", file=sys.stderr)
        return 1
    finally:
        conn.close()


if __name__ == "__main__":
    sys.exit(main())

The staging view is where the vendor's field names become yours, and where the "null means default" surprises get handled once:

sql

CREATE OR REPLACE VIEW staging.stg_tickets AS
SELECT
    source_id                                           AS ticket_id,
    (payload ->> 'companyId')::bigint                   AS client_id,
    lower(trim(payload ->> 'status'))                   AS status,
    coalesce(payload ->> 'priority', 'p3')              AS priority,  -- UI shows P3 when unset
    payload ->> 'queue'                                 AS queue,
    (payload ->> 'createdAt')::timestamptz              AS created_at,
    (payload ->> 'firstResponseAt')::timestamptz        AS first_response_at,
    (payload ->> 'resolvedAt')::timestamptz             AS resolved_at,
    loaded_at
FROM raw.psa_tickets;

The JSON field names are placeholders for whatever your PSA returns. The pattern is the point. From here, metric views go in marts. I cover how to keep those consistent in reusable SQL models for IT operations metrics. The answer to the after-hours question from the intro ends up being one short query against a staging view, instead of a day in Excel:

sql

SELECT client_id,
       count(*) FILTER (
           WHERE extract(isodow FROM created_at AT TIME ZONE 'America/Chicago') IN (6, 7)
              OR (created_at AT TIME ZONE 'America/Chicago')::time NOT BETWEEN '08:00' AND '17:00'
       ) AS after_hours_tickets,
       count(*) AS all_tickets
FROM staging.stg_tickets
WHERE created_at >= now() - interval '90 days'
GROUP BY client_id
ORDER BY after_hours_tickets DESC
LIMIT 10;

Ownership has to survive the person who built it

The real risk in a no-BI-team reporting layer isn't the initial build. It's the day the person who built it moves teams or leaves. I've inherited more than one of these pipelines as a pile of undocumented scripts running on someone's forgotten scheduled task, silently failing for months because no alert was ever wired up. Every pipeline needs a runbook, a named backup owner even if that person only touches it twice a year, and monitoring on the pipeline itself, not just the dashboards it feeds, so a failure surfaces before three months of reports quietly go stale.

Put the ownership in the database, next to the data, where the next person will actually find it:

sql

CREATE TABLE IF NOT EXISTS raw.pipeline_owners (
    source          text PRIMARY KEY,
    owner           text NOT NULL,
    backup_owner    text NOT NULL,
    runbook_url     text NOT NULL,
    max_staleness   interval NOT NULL
);

INSERT INTO raw.pipeline_owners VALUES
    ('psa_tickets', 'ops-lead@example.com', 'sysadmin2@example.com',
     'https://wiki.example.com/reporting/psa-tickets', interval '2 hours')
ON CONFLICT (source) DO NOTHING;

Then a freshness view that turns "is this data current?" into a query any dashboard can show at the top of the page:

sql

CREATE OR REPLACE VIEW marts.pipeline_freshness AS
SELECT o.source,
       o.owner,
       o.backup_owner,
       o.runbook_url,
       max(r.finished_at) FILTER (WHERE r.status = 'success')          AS last_success,
       now() - max(r.finished_at) FILTER (WHERE r.status = 'success')  AS age,
       coalesce(now() - max(r.finished_at) FILTER (WHERE r.status = 'success') > o.max_staleness, true) AS is_stale
FROM raw.pipeline_owners o
LEFT JOIN raw.etl_runs r ON r.source = o.source
GROUP BY o.source, o.owner, o.backup_owner, o.runbook_url, o.max_staleness;

And a check that fails loudly. Cron mails a job's output to the MAILTO address, so a script that prints only when something is wrong turns a stale source into an email naming the owner and the runbook:

bash

#!/usr/bin/env bash
# check_freshness.sh: print stale pipelines and exit 1 if any exist.
# Needs psql and READER_DSN (a role that can read marts) in /opt/reporting/env.
set -euo pipefail
source /opt/reporting/env

stale=$(psql "$READER_DSN" --no-align --tuples-only --field-separator=' | ' --command \
  "SELECT source, coalesce(age::text, 'never'), owner, backup_owner, runbook_url
     FROM marts.pipeline_freshness WHERE is_stale")

if [[ -n "$stale" ]]; then
  echo "Stale reporting pipelines (source | age | owner | backup | runbook):"
  echo "$stale"
  exit 1
fi

The env file holds LOADER_DSN, READER_DSN, and PSA_TOKEN, and is readable only by the service account. Keep passwords out of the DSNs and put them in the service account's ~/.pgpass (mode 0600): the DSNs end up on the psql and Python command lines, where ps shows them to any local user. A small wrapper runs the loader:

bash

#!/usr/bin/env bash
# run_psa_tickets.sh: load PSA tickets; stays quiet on success, prints errors on failure.
set -euo pipefail
source /opt/reporting/env
export PSA_TOKEN
/opt/reporting/load_api_to_raw.py --dsn "$LOADER_DSN" --source psa_tickets \
    --url https://psa.example.com/api/v1/tickets --since-param updatedSince >/dev/null

text

# crontab for the reporting service account
MAILTO=reporting-alerts@example.com
*/30 * * * *  /opt/reporting/run_psa_tickets.sh
15 * * * *    /opt/reporting/check_freshness.sh

The loader's stdout goes to /dev/null but stderr doesn't, so failures still reach the mailbox. Create raw.etl_runs and raw.pipeline_owners as etl_loader, so the default privileges above let transformer (which owns the freshness view) read them. It's crude, and it's the kind of alerting that still works three years after its author left.

It doesn't need to be elegant to be worth it

The reporting layers that succeed in this kind of environment aren't the technically impressive ones. They're the ones that answer the three or four questions leadership actually asks on a regular cadence, reliably enough that people stop pulling manual exports to double-check them. That's a lower bar than a proper BI team would set for itself, and it's still a real improvement over the spreadsheet it replaced. If the load on that single database eventually becomes the problem, that's a good problem, and when to stop querying production covers how to tell.