Triage Warehouse Spend from Query History and Draft the FinOps Narrative

Tier: Core · Workflow area: Orchestration, ops & cost

The evidence is all there — account usage views log every query, its bytes, its warehouse, its user — but turning a million rows of query history into "these five workloads drove the increase, here's the fix for each, and here's who pays for what" is analyst work that data engineers get handed anyway. This playbook uses SQL for the aggregation (facts) and an LLM for the parts humans are slow at: recognizing anti-patterns across thousands of query shapes and drafting the chargeback narrative stakeholders will actually read.

When to reach for this

• •
  A cost spike needs a root-cause explanation with named workloads and owners.
• •
  Monthly/quarterly FinOps reporting: cost drivers, trends, and a chargeback/showback draft per team.
• •
  A cost-reduction initiative needs a ranked backlog of fixes with estimated savings — the input to per-query tuning (pb-24).

If the bill is small and flat, a quarterly glance at the console beats building this.

The workflow

1. 1.

   Aggregate with SQL — facts come from queries, not the model. Pull from account usage (Snowflake ACCOUNT_USAGE.QUERY_HISTORY, BigQuery INFORMATION_SCHEMA.JOBS, Databricks system tables) an aggregate by query fingerprint (normalized query text hash — every platform exposes one or lets you compute it):

   Copy

   -- ANSI-flavored shape; adapt view/column names per platform
   SELECT query_fingerprint,
          any_value(sample_query_text)          AS sample_text,
          count(*)                              AS runs,
          sum(cost_estimate)                    AS total_cost,
          sum(bytes_scanned)                    AS total_bytes,
          avg(execution_seconds)                AS avg_seconds,
          any_value(user_name)                  AS typical_user,
          any_value(warehouse_name)             AS warehouse
   FROM query_history_30d
   GROUP BY query_fingerprint
   ORDER BY total_cost DESC
   LIMIT 200;
   

   Also pull the same aggregate for the prior period — the delta view is what explains a spike. Export both as CSV/JSON.

2. 2.

   Have the LLM rank drivers and detect anti-patterns.

   Copy

   Attached: top-200 query fingerprints by cost for this period and last period
   (columns: fingerprint, sample_text, runs, total_cost, bytes, avg_seconds,
   user, warehouse).
   
   Produce:
   1. Cost-driver ranking: which fingerprints/workloads explain the period-over-period
      increase; show the arithmetic from the attached numbers.
   2. Anti-pattern tags per offender, from this list only: SELECT-star-wide-table,
      unpruned-scan (bytes >> plausible partition size), high-frequency-small-query
      (scheduling problem), cartesian/exploding-join (output >> input), retry-storm
      (same fingerprint, burst timing), oversized-warehouse (low avg utilization),
      zombie-schedule (output table has no readers — flag for the lineage check).
   3. For each: the fix category, effort guess (S/M/L), and estimated monthly savings
      derived from the attached cost — show the derivation.
   4. Chargeback draft: cost per team (user→team mapping attached), one paragraph
      per team, neutral tone, stating drivers and recommended actions.
   Do not invent numbers; every figure must be computable from the attachment.
   

   Expected output: a ranked fix backlog and a narrative draft where every number traces to your export. Tagging accuracy is high because you constrained the taxonomy; free-form "insights" are where hallucination lives.

3. 3.

   Verify the top findings against ground truth. For each of the top ~10: re-run the arithmetic in SQL, and check the anti-pattern claim against the actual query profile (an "unpruned scan" claim should show in bytes-scanned vs. table partition sizes). Kill any finding you can't reproduce; note it — a model that mis-tagged once will mis-tag again, and your prompt taxonomy may need sharpening.

4. 4.

   Route fixes and attach owners. Zombie schedules → confirm with lineage/readers then disable; unpruned scans and exploding joins → per-query tuning (pb-24); high-frequency patterns → scheduling/caching change; oversized warehouses → right-size and measure a week. Every backlog item gets an owner and its estimated saving — this is what turns a report into a program.

5. 5.

   Draft, humanize, ship the narrative. The LLM's chargeback draft is 80% done; the human pass adds context the data doesn't show (the marketing spike was the campaign launch, and was approved). Publish period-over-period per team with trend, top drivers, actions taken, savings realized from last period's actions — that last line is what builds credibility for the program.

6. 6.

   Automate the cadence. Schedule the aggregation queries, re-run the analysis monthly, and track realized-vs-estimated savings per closed backlog item. A one-off triage decays in a quarter; the loop compounds.

Verify before you trust it

• •
  Recomputed the period-over-period delta for the top drivers in SQL; the model's arithmetic matches.
• •
  Spot-checked each claimed anti-pattern against an actual profile or timing pattern — at least the top 10.
• •
  Zombie-schedule candidates confirmed via lineage/audit (no readers in 30+ days) before anything is disabled.
• •
  Chargeback team totals sum to the actual bill (reconcile against the invoice/console, not just the export).
• •
  Savings estimates are labeled as estimates and tracked against realized numbers next period.

Pitfalls

• Pasting raw query history into a prompt. A million rows won't fit and shouldn't leave the platform. Aggregate first; the fingerprint summary is both smaller and less sensitive.
• Query text contains data. WHERE clauses embed literal emails, IDs, amounts. Strip literals from sample_text before export — fingerprinting usually does this; verify.
• Cost attribution by user, not workload. The "top user" is often the service account running everyone's dbt. Map to teams via workload tags/roles, or the chargeback will start a war.
• Estimated savings treated as commitments. "Est. ₹2L/month" becomes a promise the moment it's in a deck. Label estimates, track realization, and let the track record speak.
• One-shot reporting. The first report is interesting; the third consecutive one with realized savings is influence. Automate or it won't happen.

Tools

• Warehouse account-usage views (Snowflake / BigQuery / Databricks system tables)the ground-truth source; learning these views is half the FinOps skill.
• Claude (or another frontier LLM)anti-pattern tagging over hundreds of query shapes and narrative drafting; constrained taxonomy, attached numbers.
• SELECT / Vantage-class cost toolspackaged alternative for the aggregation layer when budget exists; the LLM narrative step sits on top of either path.

Client-safe variant

Cost data is commercially sensitive twice over — it reveals the client's cloud spend and, via query text, their business logic. Run the aggregation entirely in the client's warehouse; export only fingerprint-level aggregates with literals stripped and, if required, table names pseudonymized before any external model sees them (or use an in-platform/self-hosted model and skip the concern). The chargeback narrative must go through a client-side review before circulation — cost allocation is organizationally political, and the consultant's draft should arrive as "proposed allocation with reproducible numbers", never as a fait accompli. The reproducibility pack (the SQL, the export, the arithmetic) is what makes the report defensible after you leave.