Find the dbt models that are burning your warehouse budget

Build me a performance workbench where our analytics engineers hunt down slow and expensive dbt models. It reads build history from dbt Cloud and model code from our dbt repo on GitHub, and it exists because dbt's own insights screens show you the ranking but stop there, without telling you what to change.

The main view is a ranked table of models by average build time across the last 20 production runs. Assemble it like this: use List Jobs and List Environments to work out which jobs belong to production environments, use List Runs to pull the most recent completed runs for those jobs (newest first, only runs that finished successfully, status code 10), then for each run use List Run Artifacts to confirm run_results.json is there and Retrieve Run Artifact to download it. run_results.json gives you every node's execution time, status, and timing breakdown. Separately use Retrieve Job Artifact with the production job id to fetch the current manifest.json, which gives you model metadata: materialization, schema, tags, file path, and the upstream refs each model depends on. Join the two on the node unique id.

Columns in the table: model name, average runtime across the run window, most recent runtime, trend against the prior period (compare the average over the last 7 days to the average over the 7 days before that, shown as a signed percentage with an up or down indicator), materialization from the manifest, and estimated warehouse cost. For cost, multiply the model's execution time by the per hour warehouse rate the admin sets in settings, so a model averaging 6 minutes at a 4 dollar hourly rate reads as roughly 40 cents per build. Also show each model's share of total execution time across all models in the window, which is the proportional allocation an admin can apply to a warehouse bill they already know. Sort by average runtime descending by default, and let people sort by any column.

Default the table to production environment jobs only. Add a project filter so teams with several dbt projects can look at one at a time, and a toggle for models that got materially slower week over week, meaning their trailing 7 day average is up by more than a threshold the admin sets (default 20 percent) against the prior 7 days. Those regressions are what actually costs money, so make that filter prominent and show the count of flagged models near it.

Clicking a model opens a detail page. Show its run by run timing history as a chart plus a table, one row per run with the run date, duration, status, and a link out to the run in dbt Cloud. Show the model's manifest config alongside it: materialization, schema, tags, file path, and the list of upstream refs. Below that, let people open the underlying step logs for any run using Retrieve Run Step, so they can read the actual build output for the step that ran the model without leaving the app.

Every model gets an Investigate button that kicks off a background agent. The agent takes the model's file path from the manifest and uses GitHub Get a File to read the model SQL, decoding the Base64 content the API returns, then uses List Files in a Directory to walk the models folder and Get a File again on each upstream ref so it can see the shape of the dependencies. It combines that code with the model's timing history and its manifest config, and writes an optimization brief back into the app. The brief should cover concrete changes: whether the model should move to an incremental materialization and on what unique key and incremental strategy, whether there is a fan out join multiplying rows, whether the materialization should change between table, view, and ephemeral, whether filters or aggregations should be pushed further upstream, and whether the model is simply rebuilding data that has not changed. Ask for an expected impact on build time for each recommendation. Store the brief against the model with its status (queued, running, complete, failed) and a timestamp, show it in a panel on the detail page, and keep past briefs so people can see what was already recommended.

Once a brief exists, a follow up button files it as a GitHub issue on the dbt repo using Create an Issue. Title it with the model name and the headline number, something like "Optimize marts.fct_orders (avg build 6m12s, up 38 percent week over week)", put the brief in the body along with the timing summary and a link back to the model's page in this app, and apply the labels configured in settings. Save the returned issue number and URL on the brief, show it as a link on the detail page, and change the button to point at the existing issue rather than filing a duplicate.

Add a settings page for the admin: warehouse cost per hour and currency, the dbt Cloud account and which projects and environments count as production, how many recent runs the averages cover (default 20), the week over week slowdown threshold, and the GitHub repo owner, name, default branch, models directory (default models/), and issue labels. Everyone else sees the table and detail pages read only and can run Investigate; only the admin edits settings.

A few practical notes. Artifact endpoints return the last run step's artifacts by default, so pass the step parameter when the run has multiple steps and the build you want is not the last one. Retrieve Job Artifact takes a job id plus run filters and returns the latest matching artifact, which is the cleanest way to keep the manifest current. dbt Cloud list endpoints take limit (max 100) and offset, and every response wraps the payload in a data envelope. Fetching 20 runs of artifacts is a lot of calls, so assemble the table in a handler that caches the parsed per model timings and give the page a Refresh button rather than refetching everything on every load. Skip any run missing run_results.json so incomplete builds do not distort the averages.