Lab 05 · Warehouse cost & query tuning with AI

The three seeded anti-patterns are the ones that dominate real warehouse bills: a filter expression that defeats partition pruning combined with SELECT star, an aggregate-then-filter join that scans everything to keep almost nothing, and a dashboard query re-aggregating the full table 400 times a day instead of reading a summary table. The skill this trains is the FinOps loop — evidence in, ranked hypotheses out, fixes measured, savings written up — with the LLM doing the pattern recognition and you doing the verification.

Time & tier

~90 minutes hands-on · Core tier (maps to module M4 · AI-Native Orchestration, Docs & Cost).

Setup

DuckDB over hive-partitioned Parquet stands in for the warehouse — the same pruning, projection, and materialization physics as Snowflake, BigQuery, or Databricks at $0. Every technique maps one-to-one; the real-warehouse equivalents are called out inline.

mkdir cost-lab && cd cost-lab
python3 -m venv .venv && source .venv/bin/activate
pip install duckdb

Save and run make_warehouse.py — 3M events across 30 daily partitions plus a users dimension:

"""Build a laptop-scale 'warehouse': 3M events as hive-partitioned Parquet + dims in DuckDB."""
import duckdb, os
os.makedirs("lake", exist_ok=True)
con = duckdb.connect("warehouse.duckdb")
con.sql("""
create or replace table users as
select r as user_id, 'user_' || r as name,
       ['IN','US','DE','SG','BR'][1 + r % 5] as country,
       ['free','pro','enterprise'][1 + r % 3] as plan
from range(1, 50001) t(r)""")
con.sql("""
create or replace temp table ev as
select r as event_id,
       1 + (r * 48271) % 50000 as user_id,
       ['page_view','click','purchase','signup','api_call'][1 + r % 5] as event_type,
       (date '2025-06-01' + interval (r % 30) day)::date as event_date,
       timestamp '2025-06-01 00:00:00' + interval (r % 30) day + interval (r % 86400) second as ts,
       '/page/' || (r % 500) as url,
       case when r % 5 = 2 then round((r % 9000) / 100.0, 2) else 0 end as revenue
from range(1, 3000001) t(r)""")
con.sql("copy ev to 'lake/events' (format parquet, partition_by (event_date), overwrite_or_ignore)")
print(con.sql("select count(*) as n_rows, min(event_date), max(event_date) from ev"))

Expected: 3,000,000 rows, 2025-06-01 to 2025-06-30, ~18 MB under lake/events/ in 30 partition folders.

Now save query_history.json — the synthetic account-usage extract you will hand the LLM (in Snowflake this comes from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY, in BigQuery from INFORMATION_SCHEMA.JOBS_BY_PROJECT, in Databricks from system.query.history):

[
  {"query_id": "Q1", "owner": "growth_dashboard",
   "sql": "select * from events where strftime(ts, '%Y-%m-%d') between '2025-06-25' and '2025-06-30'",
   "runs_per_day": 48, "avg_ms": 251, "avg_files_read": 30, "note": "feeds last-6-days activity page"},
  {"query_id": "Q2", "owner": "exec_summary",
   "sql": "select country, users_active from (select u.country, count(distinct e.user_id) users_active from events e join users u on e.user_id = u.user_id group by 1) where country in ('IN','US')",
   "runs_per_day": 24, "avg_ms": 9, "avg_files_read": 30, "note": "two-country weekly-active tile"},
  {"query_id": "Q3", "owner": "ops_dashboard",
   "sql": "select event_date, event_type, count(*), sum(revenue) from events group by 1,2",
   "runs_per_day": 400, "avg_ms": 7, "avg_files_read": 30, "note": "auto-refresh 3.6 min, all widgets"},
  {"query_id": "Q4", "owner": "ml_features",
   "sql": "select user_id, count(*) from events where event_date = current_date - 1 group by 1",
   "runs_per_day": 1, "avg_ms": 12, "avg_files_read": 1, "note": "nightly feature build"}
]

Guided steps

1. 1.

   Build the evidence pack before asking anything. LLM tuning advice is only as good as the plans you feed it. In the DuckDB CLI (duckdb warehouse.duckdb), capture the plan for the worst query — note DuckDB's EXPLAIN ANALYZE is the analog of Snowflake's query profile or BigQuery's execution details:

   Copy

   explain analyze select * from read_parquet('lake/events/**/*.parquet', hive_partitioning=1)
   where strftime(ts, '%Y-%m-%d') between '2025-06-25' and '2025-06-30';
   

   In the plan tree, find Total Files Read: 30 — a six-day query reading all thirty partitions, because the filter wraps the timestamp in strftime(), so the planner cannot map it onto the event_date partition column. Save the plan text to evidence/q1_plan.txt, and capture the analogous plans for Q2 and Q3.

2. 2.

   Ask for the ranked cost diagnosis. Save this as cost_triage_prompt.md — it is the reusable artifact:

   Copy

   You are a warehouse FinOps analyst. Below: (1) a query-history extract with
   per-query run frequency and scan stats, (2) EXPLAIN ANALYZE plans, (3) the
   physical layout: events is Parquet, hive-partitioned by event_date, 30 daily
   partitions; users is a 50k-row dimension.
   
   Rank the queries by estimated total daily cost (avg_ms x runs_per_day and
   files_read x runs_per_day). For each of the top 3: name the anti-pattern,
   point to the exact evidence in the plan, propose a rewrite or materialization,
   and estimate the saving. Flag anything that should NOT be tuned.
   
   {query_history.json} {plans}
   

   A good response ranks Q3 first (7 ms is cheap once — 400 runs/day scanning 30 files each is 12,000 file-reads/day), Q1 second (48 runs × full scan × SELECT star), Q2 third, and flags Q4 as already healthy — one run, one partition, leave it alone. Check the ranking arithmetic yourself: the LLM is pattern-matching, and runs × cost is exactly the multiplication dashboards teams forget.

3. 3.

   Fix Q1 — pruning plus projection. Two changes: filter on the partition column directly, and name only needed columns:

   Copy

   select event_id, user_id, event_type, revenue
   from read_parquet('lake/events/**/*.parquet', hive_partitioning=1)
   where event_date between date '2025-06-25' and date '2025-06-30';
   

   Re-run EXPLAIN ANALYZE: Total Files Read drops 30 → 6, and the plan shows Scanning Files: 6/30. Wall time falls ~251 ms → ~145 ms on a laptop; in a bytes-billed warehouse the 80% file reduction is the bill reduction. Warehouse equivalents: keep predicates sargable on the partition/cluster key (no functions over it) — BigQuery partition filters, Snowflake clustering keys, Delta partition columns all die on wrapped predicates the same way.

4. 4.

   Fix Q2 — push the filter down. The subquery aggregates all five countries across full history, then keeps two. Push both the country filter and a time window into the aggregation:

   Copy

   select u.country, count(distinct e.user_id) as users_active
   from read_parquet('lake/events/**/*.parquet', hive_partitioning=1) e
   join users u on e.user_id = u.user_id
   where u.country in ('IN','US') and e.event_date >= date '2025-06-24'
   group by 1;
   

   Measured ~9 ms → ~5 ms locally — modest here, but the shape is the point: aggregate-then-filter over full history is how "weekly active" tiles end up scanning years. Confirm with the business owner that a 7-day window matches the tile's intent before shipping — a tuning change that alters semantics is a bug with good latency.

5. 5.

   Fix Q3 — materialize the summary. The highest-cost line item needs no rewrite, it needs a schedule:

   Copy

   create or replace table daily_summary as
   select event_date, event_type, count(*) as n, sum(revenue) as rev
   from read_parquet('lake/events/**/*.parquet', hive_partitioning=1)
   group by 1, 2;
   -- dashboard now reads:
   select event_date, event_type, n, rev from daily_summary;
   

   The dashboard read drops to sub-millisecond against a 150-row table, and the full scan happens once per load cycle instead of 400 times a day: 12,000 file-reads/day become 30 (one refresh) plus 400 trivial reads. This is dbt-style materialization, a BigQuery materialized view, or a Snowflake dynamic table — pick the mechanism, the economics are identical. Note the trade-off in your report: the dashboard is now as fresh as the refresh schedule, which for an ops dashboard on daily data costs nothing.

6. 6.

   Benchmark honestly. Save bench.py so the numbers are reproducible rather than eyeballed:

   Copy

   import duckdb, time
   con = duckdb.connect("warehouse.duckdb")
   def bench(label, sql, n=3):
       ts = []
       for _ in range(n):
           t0 = time.perf_counter(); con.sql(sql).fetchall(); ts.append(time.perf_counter() - t0)
       print(f"{label}: {min(ts)*1000:.0f} ms")
       return min(ts) * 1000
   

   Run each bad/fixed pair three times, keep the minimum (cache-warm, like repeated dashboard hits). Reference results from this lab's data: Q1 251 → 145 ms and 30 → 6 files; Q2 9 → 5 ms; Q3 7 ms → ~0 ms per dashboard read.

7. 7.

   Write the FinOps summary. tuning-report.md, one page: a before/after table (per query: ms, files read, runs/day, daily totals), the modeled daily saving (Q3's 12,000 → 430 file-reads/day headlines it), the one-line anti-pattern name for each fix, the semantic caveats (Q2's window, Q3's freshness), and the Q4 non-action with its justification — knowing what not to tune is what separates FinOps from cargo-culting. Close with the prevention rule you would wire into CI: warn on any dashboard query with runs_per_day × files_read above a threshold, and lint filters that wrap partition columns in functions.

Verification

• •
  evidence/ contains before-plans for Q1–Q3; the Q1 before-plan shows Total Files Read: 30, the after-plan shows Scanning Files: 6/30.
• •
  bench.py output shows all three fixes faster, with Q1 files-read reduced 80% and Q3's dashboard read at ~0 ms against daily_summary.
• •
  The LLM triage transcript ranks Q3 first on total daily cost and explicitly leaves Q4 untouched.
• •
  tuning-report.md has the before/after table, the daily cost model (runs × scan), one semantic caveat per fix, and a CI prevention rule.
• •
  Rewritten Q2 returns the same two countries as the original (correctness before speed).

Completion artifact

The cost-lab/ folder: evidence pack, cost_triage_prompt.md, fixed queries, bench.py with output, and tuning-report.md. Self-grading rubric (pass = 8/10):

• Evidence discipline (0–2): plans captured before and after for every fix; files-read cited from the plan text, not asserted = 2.
• Ranking arithmetic (0–2): daily cost computed as frequency × scan; Q3 correctly identified as the top driver despite the lowest per-run ms = 2.
• Fix correctness (0–3): pruning restored via a sargable partition predicate, projection narrowed, summary table materialized with the dashboard repointed = 3.
• Semantic care (0–2): Q2's window change and Q3's freshness trade-off documented as business decisions, and Q4 left alone with a reason = 2.
• Reusability (0–1): the triage prompt is slot-based and would work on next month's query history unchanged = 1.

Stuck?

• •
  Total Files Read missing from your plan: you ran EXPLAIN (plan only) — use EXPLAIN ANALYZE (executes and reports actuals).
• •
  Q1 fixed still reads 30 files: hive_partitioning=1 is missing from read_parquet, so event_date is not a partition column to the planner.
• •
  Timings noisy: keep the minimum of three runs; close other DuckDB sessions; do not benchmark the first-ever run (cold OS file cache).
• •
  Partition copy fails on re-run: keep overwrite_or_ignore in the COPY options, or delete lake/events/ first.

Client-safe variant

Query history is one of the most sensitive artifacts in a client warehouse — SQL text embeds table names, business logic, and sometimes literal customer values in predicates. Before any history extract reaches an LLM, scrub literals and alias identifiers (a scripted rename map, as in the migration lab), or run the triage through the client's approved tenancy (Amazon Bedrock or Azure OpenAI) or a local model via Ollama. Plans and benchmarks stay entirely inside the warehouse boundary. The strongest client-safe pattern is warehouse-native: Snowflake Cortex, BigQuery AI functions, or Databricks assistant features analyze query history without data ever leaving the platform's governance perimeter — position that option first for regulated clients, and keep your measured before/after table as the deliverable either way.

Answer key (don't read before attempting)

1. 1Q1:strftime(ts, ...) over the timestamp defeats partition pruning (non-sargable predicate) and select * scans every column; fix = filter on event_date + explicit projection. Evidence: 30 → 6 files, 251 → 145 ms.
2. 2Q2: aggregate-then-filter — the subquery computes all countries over full history, the outer query discards 60% of it; fix = push the country filter and time window inside. 9 → 5 ms locally; the pattern, not the milliseconds, is the lesson.
3. 3Q3: the top cost driver by frequency: 7 ms × 400 runs × 30 files daily; fix = materialized daily_summary refreshed on schedule, dashboard reads the 150-row table. 12,000 file-reads/day → ~430.
4. 4Q4: correctly tuned already (single partition, single daily run) — the right recommendation is no action.