---
name: warehouse-query-cost-review
description: "Reviews a BigQuery or Snowflake SQL query for scan cost — flags reading every column off a wide columnar table, a query with no filter on the partitioning or clustering column, an unbounded scan over all history, and accidental cartesian joins that multiply the data processed. Use when someone asks why a warehouse query is expensive, why the bill jumped, or to check a query's scan cost before scheduling it. This is a bytes-scanned cost lens, distinct from Postgres index and lock tuning (postgres-query-review) and from join-grain/fan-out correctness (join-grain-guard)."
metadata:
  decimalai-skill-id: "16d2fb33-a93d-45dd-b550-0b365af46a19"
  decimalai-version-id: "413e294a-52ee-4286-bafd-e0ad08690ddc"
  decimalai-version: "1"
  decimalai-body-sha256: "31d3ea881d2038898afffde8262860bca1b73987add8ca7b843f3ee9efac714c"
  decimalai-name: "warehouse-query-cost-review"
  decimalai-format: "1"
  decimalai-invocation: "model"
  decimalai-category: "data"
  decimalai-source: "https://app.decimal.ai/s/warehouse-query-cost-review@1/SKILL.md"
---
# Warehouse Query Cost Review

Reviews a SQL query against a cloud data warehouse (BigQuery, Snowflake, and the same columnar family — Redshift, Databricks SQL) for **how much data it scans**, because that is what you pay for. The cost model here is different from a row-store like Postgres: there are no b-tree indexes to add, no lock analysis. The bill tracks **bytes scanned**, and three levers move it — how many columns you read, how many partitions you touch, and whether a join stays linear.

## The cost model (why the usual advice does not apply)

- **Columnar storage bills per column, not per row.** Each column is stored and scanned as a separate stripe. `SELECT *` reads every column's bytes even if you use two of them. On a 200-column table, projecting the 2 columns you need can cut the scan by ~99%. Row count is almost irrelevant to which columns cost you.
- **A partitioned table is only cheap if the query prunes partitions.** BigQuery partitions by a DATE/TIMESTAMP column or ingestion time; Snowflake prunes micro-partitions by min/max metadata and clustering keys. Pruning happens **only** when the `WHERE` compares the partitioning/clustering column to a **constant** the planner can evaluate up front. No such filter → the engine scans every partition, i.e. the whole table.
- **There is no index to add.** If someone proposes an index to make a warehouse query cheaper, that is a row-store instinct that does not transfer. The equivalents are partitioning, clustering, and reading fewer columns.
- **`LIMIT` does not reduce bytes scanned.** `SELECT * FROM t LIMIT 100` on BigQuery still scans every column of every partition before the limit is applied. Limit caps the rows returned, not the data read.

## The four flags

### 1. `SELECT *` / reading columns you do not need on a wide table
Every projected column is scanned in full. On a wide event/fact table this is usually the single biggest cost.
- **Flag:** `SELECT *`, or selecting many columns when the downstream logic uses a handful.
- **Fix:** project only the columns actually used. If a view or CTE selects `*` and the outer query narrows it, name the columns at the source so the reduction is unambiguous.

### 2. No filter on the partitioning / clustering column
Without a constant predicate on the partition key, nothing prunes and the query scans all history.
- **Flag:** a partitioned or clustered table with a `WHERE` that never constrains the partition/cluster column (or has no `WHERE` at all).
- **Also flag** predicates that *look* like they prune but do not:
  - **A function or cast wrapping the partition column** — e.g. filtering on `CAST(event_ts AS DATE)` or `SUBSTR(...)` when the table is partitioned on `event_ts`. The engine cannot prune through the wrapper. Compare the raw column to a constant instead.
  - **A non-constant / dynamic partition filter** — e.g. `WHERE event_date >= (SELECT MAX(run_date) FROM control)`. The bound is not known at planning time, so static pruning is skipped and the full table is scanned. Resolve the value first (a scalar variable / two-step query) so the planner sees a literal.
  - **A filter on a non-partition column only** — filtering `country = 'US'` prunes nothing if the table is partitioned by date; you still scan every date.
- **Fix:** add a constant-comparison predicate on the actual partition/cluster column. On BigQuery, `require_partition_filter` on the table forces this.

### 3. Unbounded date / history scan
Reading an entire append-only events or fact table when the question only needs a recent window.
- **Flag:** a query over a large historical table with no upper/lower bound on its time column.
- **Fix:** bound the range (`WHERE event_date BETWEEN … AND …`). If the table is date-partitioned, this bound is also what enables pruning — the two flags reinforce each other.

### 4. Cartesian / cross-join blow-up
A join that multiplies rows explodes the bytes processed and the shuffle, independent of storage.
- **Flag:** an explicit `CROSS JOIN`, a comma-style `FROM a, b` with no join predicate in the `WHERE`, an `ON` condition that is missing or always-true, or an `UNNEST`/join on a non-selective key that fans one row into many.
- **Fix:** supply a real, selective join key; if a cross join is genuinely intended (a small calendar/dimension spine), confirm the other side is tiny and say so explicitly.

## Process

1. **Identify the engine and the big tables.** BigQuery vs Snowflake vs Redshift/Databricks — the cost model is the same family, the syntax for partitioning/clustering differs. Note which tables are large; cost review is about them.
2. **Walk the four flags in order:** columns projected → partition/cluster predicate (including the disguised-non-pruning cases) → date bound → join shape.
3. **Do not invent problems.** If the query already projects specific columns and filters the partition column against a constant, say it is fine on cost. A negative review is a valid outcome.
4. **Report** each finding as: what scans too much, why (which lever), and the concrete rewrite.

## Output shape

| # | Severity | Issue | Why it scans more | Fix |
|---|----------|-------|-------------------|-----|
| 1 | High | `SELECT *` on `events` (180 cols) | Every column's bytes are read; query uses 3 | Project `event_id, user_id, amount` only |
| 2 | High | No filter on `event_date` (partition key) | All partitions scanned = full history | Add `WHERE event_date BETWEEN '2026-06-01' AND '2026-06-30'` |
| 3 | Medium | `CAST(event_ts AS DATE) = '2026-07-01'` | Cast hides the partition column from pruning | Filter the raw `event_ts` against a timestamp range |

## Worked example

```sql
-- BigQuery; `logs` is date-partitioned on `dt`, ~2 TB, 140 columns
SELECT *
FROM logs
WHERE CAST(dt AS STRING) = '2026-07-01'
  AND status = 500;
```

Review:
- **`SELECT *` on a 140-column table** → reading every column. The query needs a few; project them. (Flag 1)
- **`CAST(dt AS STRING) = '2026-07-01'`** wraps the partition column in a cast, so BigQuery cannot prune — it scans all ~2 TB. Filter the raw column: `WHERE dt = DATE '2026-07-01'`. (Flag 2, disguised)
- `status = 500` alone prunes nothing (not the partition key), so without fixing the `dt` predicate the whole table is read regardless.

Corrected:
```sql
SELECT event_id, status, latency_ms, path
FROM logs
WHERE dt = DATE '2026-07-01'
  AND status = 500;
```

## Edge cases
- **Small dimension tables** (a few MB): none of this matters; do not flag `SELECT *` on a tiny lookup.
- **Clustering is best-effort, not a guarantee.** Snowflake micro-partition pruning and BigQuery clustering narrow the scan only when the filter/order matches the cluster key and the data is well-clustered; a poorly-clustered table may still scan widely. Recommend a filter on the cluster key, but do not promise a fixed reduction.
- **Materialized/repeated scans:** if the same expensive scan runs on a schedule, a partitioned materialized view or an incremental table can amortize it — worth naming, but the per-query fix above comes first.
- **BigQuery `LIMIT` in the UI** shows a small preview but the dry-run bytes-scanned estimate is the real cost; trust the estimate, not the row count.
- **Selecting a partition pseudo-column** (`_PARTITIONTIME`, `_PARTITIONDATE`) is the right thing to filter on for ingestion-time partitioning.

## Evaluation criteria
A good review:
- [ ] Ties cost to bytes scanned (columns × partitions), not to row count or indexes
- [ ] Flags `SELECT *` on a wide table and names column projection as the fix
- [ ] Catches a missing partition/cluster predicate — including a cast/function-wrapped or subquery-derived filter that silently defeats pruning
- [ ] Flags an unbounded historical scan and recommends a date bound
- [ ] Catches an accidental cartesian / cross-join
- [ ] Does not recommend adding an index, and does not claim `LIMIT` lowers the scan
- [ ] Approves a query that already projects columns and filters the partition key against a constant