The real cost of SELECT * in BigQuery

BigQuery stores data in a columnar format. In theory, SELECT user_id, event_date FROM events reads two columns and ignores the other 180. In practice, a lot of production SQL is SELECT *: notebooks, 'I'll add a WHERE later' dashboards, dbt staging models that were supposed to be thin, and BI tools that expand a star schema explore into every available field.

-- ~40 GB: only the columns the dashboard needs
SELECT user_id, event_date, event_name
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE();

-- ~1.2 TB: payload, geo, ua, raw JSON, nested structs…
SELECT *
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE();

On a narrow dimension table this is an annoyance. On a nested events table with JSON payloads, repeated structs, and a 2 KB average row, it is the difference between scanning 40 GB and 1.2 TB. At $6.25/TiB, that one pattern, run 20 times a day, becomes a four-figure monthly line item with no business question behind it.

Width is not column count alone. A table with 40 columns can still be a SELECT * disaster if 8 of them are nested REPEATED STRUCT payloads that dominate physical bytes. INFORMATION_SCHEMA.COLUMNS will say 40 columns; TABLE_STORAGE will say 1.2 TB. The dashboard needed user_id, event_name, and a date. The bill was for the JSON.

Partition prune and projection are independent meters that multiply. A well-pruned day that still selects the nested payload can bill 10 to 40 GB; the same star on an unpruned 4 TB table bills 4 TB. Fix prune first if both are broken, then stop reading columns nobody asked for. Teams that partition but never project keep paying a permanent surcharge on every 'fixed' dashboard.

On-demand bills bytes scanned, not bytes returned. The 12 columns actually rendered in Looker are not the bill; the 200 columns that survived the star, including the ones the visualization discards, are. 'The chart only has three series' describes the UI, not the job that produced it.

Capacity and Editions hide this as slot time instead of a bytes line, but the mechanism is the same: the star occupies the reservation longer, shuffles fatter rows, and makes the next INTERACTIVE explore wait. Projection is not an on-demand-only concern; it determines how much work each slot does per row that survives the WHERE clause.

It is rarely one cause. It is a pile of defaults that each made sense on their own.

Staging models are the usual starting point in dbt shops. SELECT * FROM source('raw','events') is how a column gets into the warehouse on day one. Six months later the staging model is incremental, downstream marts select everything from staging, and Looker is built on the mart. Nobody ever froze a column list, so a 2 MB nested payload added to the source lands in every dashboard automatically.

BI tools optimize for 'new fields appear automatically,' which is a product feature and a cost bug at the same time. Looker views with include: all_fields, Tableau extracts pointed at the raw table, and Sheets connectors that expose the schema as a picker all emit stars or equivalent wide projections. The analyst did not type SELECT *. The tool did.

Notebooks are honest about where it starts: SELECT * FROM events LIMIT 1000 is how every exploration begins. The LIMIT gets deleted, the notebook gets scheduled, and the star remains. CREATE TABLE AS SELECT * used as a debug copy becomes a six-hour scheduled query because it already works.

The last source is copy-paste between environments. A query that was cheap on a 2 GB sandbox table is identical SQL on prod.events, same star, 600 times the bytes. Code review that only looks at Git will miss it if the SQL lives in LookML, a Sheets custom query, or a console saved query.

LIMIT 1000 after SELECT * still typically scans the columns for the partitions that were read; it just stops returning rows. TABLESAMPLE and WHERE RAND() < 0.01 can reduce bytes, but they are not what BI tools emit. Preview in the console is billed differently from a real query job. If your cost review is based on 'but we only needed a sample,' check total_bytes_billed on the job, not the row count in the UI.

SELECT *
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
LIMIT 1000;
-- Returns 1,000 rows. Still reads every selected column
-- in the partitions that survived the WHERE.
-- Check JOBS.total_bytes_billed, not the preview count.

Views that SELECT * from an underlying table pass the over-fetch through. Materialized views and scheduled query outputs that persist every column then get queried with SELECT * again, compounding storage and compute.

ORDER BY ... LIMIT 100 is worse, not better. A top-100 query still has to read the projected columns for the partitions in scope, often sort them, and then discard rows. You paid for the sort key's columns plus everything the star pulled. If you needed top-100 by revenue, project revenue and the dimensions you will display, then LIMIT.

EXCEPT (payload) is not a contract. SELECT * EXCEPT (raw_json) still reads the other 198 columns, including nested structs you forgot. An explicit list is the only projection the next intern cannot accidentally widen by adding a column to the table.

SELECT * EXCEPT (raw_json)
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE();
-- raw_json is skipped. The other nested structs are not.
-- Freeze the 8 columns the dashboard uses instead.

BI Engine and cache will make a starred dashboard look free until a filter changes. Cost control that watches wall-clock time will conclude the explore is fine. Cost control that watches cache-miss total_bytes_billed will not. Always split JOBS on cache_hit before you declare a pattern cheap.

Row access policies and authorized views do not shrink bytes for columns they still return. If the view is SELECT * FROM events WHERE region = 'US', you pruned rows (maybe) and still projected every column. Combine row filters with a thin column list or you have only done half the work.

Even a well-projected query still reads every clustered block that might contain matching rows. If you always filter events by account_id and event_name, and the table is only partitioned by date with no CLUSTER BY, each date partition is a full read of those columns. Clustering on the high-cardinality filter columns (up to four) lets BigQuery skip blocks inside the partition.

CREATE TABLE `proj.analytics.events`
PARTITION BY event_date
CLUSTER BY account_id, event_name
AS SELECT * FROM `proj.analytics.events_old`;

-- Inside each date partition, BigQuery can skip blocks
-- that cannot contain this account_id.
SELECT user_id, event_name
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE()
  AND account_id = @account_id;

The mistake is clustering on columns nobody filters, or on a column that is already the partition key. The opposite mistake is never clustering at all because 'we'll get to it.' High-usage filter columns missing from CLUSTER BY are one of the more mechanical findings you can extract from job history plus DDL, which is why SlashPeak has a dedicated rule for it.

BigQuery allows at most four cluster columns. Order matters: leading column should be the one in equality filters most often. Clustering on event_date when you already PARTITION BY event_date wastes a slot. Clustering on a BOOLEAN is usually a waste of a slot. Clustering on a 1:1 unique id can help point lookups and does nothing for dashboards that never filter that id.

The usage bar that is worth acting on is 'this column appears in storage-level filters for most distinct query patterns against the table,' not 'someone filtered it once.' Five distinct patterns and 80% usage is the kind of threshold that keeps a one-off notebook from dictating layout. Below that, you are optimizing for a ghost.

Clustering is not a metadata-only ALTER in every case. Changing CLUSTER BY typically means a copy or CREATE OR REPLACE in a maintenance window. Budget slot-hours and a validation query that compares bytes billed before/after on the same parameterized SQL. If bytes do not drop, you clustered the wrong columns.

Shuffle-heavy jobs are the remainder after projection and clustering. High slot-ms per GiB scanned usually means a JOIN or GROUP BY exploded a key. On-demand that is a runtime problem, not a bytes problem. On capacity it occupies the pool. Do not expect CLUSTER BY to fix a fan-out join.

Do not start by clustering a 20 TB table. Start by stopping the widest SELECT * jobs, then cluster based on the filters that remain.

Generate the first explicit list from INFORMATION_SCHEMA.COLUMNS if you must, then delete two-thirds of it. Freeze what the dashboard, the mart, and the incremental model actually use. Check that list into Git so a new nested payload cannot land in production by accident.

Put the list in a contract view and point BI at the view. Leave the 200-column heap for forensic queries that should be BATCH, rare, and owned. If Looker needs a new field, that is a PR to the view, not a reason to star the heap again.

CREATE OR REPLACE VIEW `proj.analytics.events_bi` AS
SELECT
  user_id,
  event_date,
  event_name,
  account_id,
  country
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
-- Point Looker / Sheets at events_bi, not the 200-column heap.

Extract nested fields you query into top-level columns. JSON_EXTRACT in every dashboard is both a star (you had to read the JSON column) and a CPU tax. Promote event_name, account_id, country. Leave raw_json on the heap.

Then cluster. Recreate or copy in a window. Re-run the expensive jobs with the same parameters and compare total_bytes_billed, not runtime. Runtime lies when slots are free or the cache is warm.

A staff engineer can grep query text for SELECT * in an afternoon. They cannot easily rank those jobs by dollars, join them to table width from DDL, exclude the scanner's own jobs, attribute them to Looker vs. dbt vs. a human, and repeat the exercise every day.

SlashPeak's scan does that from metadata: job history, table DDL, and storage. It never requests dataViewer. Findings are grouped across scans, so you can see whether last week's SELECT * rewrite actually moved the estimated savings, or whether a new Connected Sheets extract quietly replaced it.

The star rule is query-text based (SELECT *) plus table width plus bytes. It will miss tools that expand a field list in the UI but still emit 180 named columns; that is still over-fetch, it just will not match the regex. Contract views fix both. Clustering recommendations are plan-based (they look at filters that reached storage), so they still fire when the SQL never used a star.

The Usage page charts the heaviest shuffled queries next to these two, so you do not 'fix' a 40-second dashboard with a column list when the problem is a fan-out join. No dollar figure on shuffle is deliberate: mixing slot-ms into on-demand waste totals invents savings finance cannot find on the invoice.

Attribution is what makes the ticket closable. uniqueUsers, workloadSourceBreakdown, environmentBreakdown: the Slack message becomes 'Looker on prod.events, two explores' instead of 'stop using SELECT *.' Without that, the finding has no owner to assign.

A nested events table: 200 columns, 2 KB average row, 20 million rows in the current date partition, about 40 GB for a well-pruned 'today' query if you only read the columns you need. The payload, geo, ua, and raw JSON fields are roughly 90% of row weight. A named-column query that needs user_id, event_date, event_name, account_id reads about 40 GB; SELECT * on the same partition reads roughly 400 GB if the nested fields dominate, sometimes more depending on physical layout.

Use the numbers from the intro, which match what we see on wide event tables: named columns about 40 GB, star about 1.2 TB on the same date filter. That gap is unused column width, not extra rows. At $6.25/TiB, the named query is 40/1024 × $6.25 ≈ $0.24; the star is 1.2 × $6.25 = $7.50. One job is uninteresting. Twenty a day is not.

-- Named: 40 GB ≈ $0.24   Star: 1.2 TiB = $7.50
-- 20 starred jobs/day ≈ $150/day ≈ $4,500/30d
SELECT
  40 / 1024 * 6.25 AS usd_named,
  1.2 * 6.25 AS usd_star,
  1.2 * 6.25 * 20 AS usd_star_day,
  1.2 * 6.25 * 20 * 30 AS usd_star_30d;

Twenty paid jobs a day, say a Looker explore, a dbt staging model, Sheets, and two notebooks, at $7.50 each is $150/day, roughly $4,500/month, for answers that needed about $5/day of columns. The delta is roughly $4,450/month. SlashPeak estimates around 40% of billed bytes on matching star jobs, which is conservative relative to this 30x width gap, since not every starred table is 90% payload.

Add missing CLUSTER BY account_id on the same table. After projection, the dashboard still filters one tenant. Without clustering, today's 40 GB of useful columns is a full read of those columns in the date partition. With clustering, block skipping might cut another ~30% on filter-heavy jobs. Do that second. The star was the 30×. Clustering is the 1.3×.

If those 20 jobs also miss the partition filter, you are back in the 4 TB post. Projection does not save you from a heap read. Always stack: prune dates, then project columns, then cluster remaining filters. Dollar-rank that stack per table or you will cluster a starred heap and wonder why the invoice moved 10%.

Grep is necessary and insufficient. Search JOBS.query for SELECT * (case-insensitive, including SELECT\s+DISTINCT\s+\*) and you will get notebooks, dbt staging, and a lot of false comfort from comments. Join to referenced table width and bytes billed or you will chase a 12-column dim.

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 4), 3) AS tib,
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 2) AS usd,
  REGEXP_CONTAINS(query, r'(?i)select\s+(distinct\s+)?\*') AS is_star
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND cache_hit = FALSE
  AND REGEXP_CONTAINS(query, r'(?i)select\s+(distinct\s+)?\*')
  AND total_bytes_billed >= POW(1024, 3)
ORDER BY total_bytes_billed DESC
LIMIT 50;

COLUMNS aggregated per table gives column_count. TABLE_STORAGE gives bytes. A table with column_count >= 15 and jobs that both star and bill at least 1 GiB is the production bar; below that you are in demo-threshold territory. Nested types show up as STRUCT/ARRAY in data_type; count those separately if you want to know why a 40-column table is 1 TB.

SELECT
  table_name,
  COUNT(*) AS column_count,
  COUNTIF(STARTS_WITH(data_type, 'STRUCT')
    OR STARTS_WITH(data_type, 'ARRAY')) AS nested_cols
FROM `proj.analytics`.INFORMATION_SCHEMA.COLUMNS
GROUP BY table_name
HAVING COUNT(*) >= 15
ORDER BY column_count DESC;

Dry-run a starred query and the same query with 5 named columns against the same partition filter. The ratio of estimated bytes is your unused-width factor. Multiply billed bytes of production starred jobs by (1 − 1/ratio) for a back-of-envelope savings number. 40% is the conservative product ratio; your table may be worse.

For clustering, you need filters that reached storage, not SQL mentions. That is job_stages again. Ranking columns by how often they appear in READ predicates, minus the partition column, minus existing CLUSTER BY, is the candidate list. Cap at four.

Narrow tables are not the bug. An 8-column dimension with SELECT * is noise. The rule's 15-column and 1 GiB floors exist so you do not open tickets against information_schema clones and country codes; if the table is 12 columns and 400 MB, spend the afternoon on the 200-column events star instead.

CREATE TABLE AS SELECT * is fine for a true one-off copy: a migration, a backup before a schema change, reproducing a bug. The failure mode is scheduling it. If the job's fingerprint shows up three times in 30 days, it is not a one-off anymore.

External tables and loads sometimes need a star to ingest a changing schema. Keep that in the load layer and do not let it leak into marts; staging can be wide, but the BI contract cannot.

Clustering a table whose filters are always the partition column pays for a rewrite and gets nothing. Clustering on a column used in exactly one notebook, or adding a fourth cluster column 'for later,' has the same problem: empty slots on CLUSTER BY are cheaper than a wrong leading column.

Replacing a star with SELECT * EXCEPT (...) is not a fix, and neither is projecting 180 named columns because a generator dumped INFORMATION_SCHEMA.COLUMNS into the model. Both are a star with extra noise in the diff.

Shuffle-heavy queries will not improve from projection alone if the plan's JOIN still explodes a key. You will make a 10,000 slot-ms/GiB job slightly cheaper per byte and still pin the reservation. Pre-aggregate the build side of the join first.

dbt: replace source stars with explicit columns in staging, or with a YAML column contract and codegen that fails CI when the source adds a nested payload. Incremental models that SELECT * from a wide staging table will persist the width into marts forever. select * in a ephemeral CTE is still billed if it is the thing that reads the table.

-- stg_events.sql — do not SELECT *
SELECT
  user_id,
  event_date,
  event_name,
  account_id,
  country
FROM {{ source('raw', 'events') }}
WHERE event_date >= DATE_SUB({{ var('run_date') }}, INTERVAL 3 DAY);

Looker: explores built on SELECT * views will always widen. Use a view that lists fields, set default fields, and avoid include: all on the events view. A new column in BigQuery should not appear in the explore until someone adds it to the contract; that delay is the cost control.

Connected Sheets: the connector's 'select a table' UI is a star. A custom query with five columns is the fix. Scheduled extracts into a thin table beat live stars during business hours, since Sheets will refresh 50 times a day if someone leaves the workbook open.

Tableau, Power BI, and similar tools: extracts that pull the whole table are stars plus storage duplication. Point them at events_bi instead. If the tool genuinely needs nested JSON, give it a separate connection that is not the production reservation.

Codegen and schema-on-read pipelines will reintroduce stars every time the upstream schema changes. The freeze has to live in CI, not in a wiki: a PR that adds a column to the contract view is reviewable, and an unreviewed SELECT * is not.

Day 1: rank starred jobs by bytes billed joined to table column_count. Pick the widest table with recurring producers. Dry-run SELECT * vs the 8 columns the dashboard uses on one date partition. Write down the two byte numbers.

Day 2: ship a contract view with those columns and a partition filter. Point one Looker explore or one Sheets query at it. Compare cache-miss total_bytes_billed. Do not cluster yet.

Day 3: replace SELECT * in the dbt staging/mart that feeds that explore. CI the column list. Run the model in staging and diff bytes against prod.

Day 4: if remaining jobs still filter account_id (or similar) without clustering, schedule a CLUSTER BY copy for a maintenance window next week, not tonight. Tonight is for remaining stars.

Days 5 through 7: re-scan. Estimated SELECT * savings should fall. If the same explore still tops the shuffled-queries chart on the Usage page, that is a join problem; adding more columns to the view will not fix it. If a new Sheets extract starred the table, attribution now gives you a named owner.