Hidden scan costs in Looker and Connected Sheets

Connected Sheets, Looker explores, and I-will-just-pull-this-into-Colab jobs have a UI that shows a small table. BigQuery bills the bytes the job scanned. A Looker view defined as SELECT * FROM events, explored with three measures, still reads every column in the partitions that survive the date filter, including the 2 MB JSON payload nobody charted.

-- Looker view (simplified)
-- sql_table_name: `proj.analytics.events`  -- or a SELECT * view
SELECT *
FROM `proj.analytics.events`
WHERE DATE(event_ts) = CURRENT_DATE()
LIMIT 500;
-- Wrong date column, star, limit after the read.

LIMIT 500 does not help. LIMIT is applied after the scan. The first 500 rows of a 4 TB read still cost 4 TB. The Sheets preview that looks like a sample is not a sample at the storage layer. The Looker data table that paginates 50 rows did not paginate the warehouse. Check total_bytes_billed on the job, not the row count in the chart.

This gap is how BI waste survives code review. There is no SQL file in the pull request. The explore lives in LookML. The spreadsheet lives in Drive. Platform teams grep dbt and miss the majority of Monday's bytes. SlashPeak classifies those jobs so the backlog can be filtered to Looker and Sheets instead of pretending the warehouse is only a transform layer.

Width and prune both matter. SELECT * on a pruned day is still a fat day. A narrow select without a partition filter is still a skinny full table scan. The chart can be eight columns and 500 rows in either case. The invoice follows whichever layer lost: projection, partition, or both. The rules on this post are SELECT_STAR_OVERFETCH and FULL_TABLE_SCAN. When the refresh lands in business hours, it is also a peak-hour ad-hoc problem.

SELECT
  account_id,
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'buy') AS buys
FROM `proj.marts.daily_kpis`
WHERE kpi_date = CURRENT_DATE()
GROUP BY 1;
-- Narrow mart, partition column, no LIMIT-as-a-pruner.
-- Bytes billed should look like a dashboard, not like ETL.

If you take one habit from this article, it is this: every chart has a job_id. Open it, and if tib_billed is not in the neighborhood of the result you see, the explore is scanning far more than the chart shows. Then use workload source to find the other jobs from the same tool. One screenshot is an anecdote; a classified pattern is a sprint.

The durable fix is a slim daily mart: the 8 columns the dashboard needs, partitioned on the date the explores actually filter, clustered on account_id. Point Looker and Sheets at the mart. Leave events for ETL. A tool cannot SELECT * columns that do not exist. That is a stronger contract than a wiki that says please do not explore the heap.

CREATE OR REPLACE TABLE `proj.marts.daily_kpis`
PARTITION BY kpi_date
CLUSTER BY account_id
AS
SELECT
  event_date AS kpi_date,
  account_id,
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'buy') AS buys
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1, 2;

If you must explore the raw table, list columns in the view, require a partition filter, and disable the explore's all-fields default. Connected Sheets should hit a scheduled extract or a materialized table, not the fact table on every spreadsheet open. Extracts can be 8 columns and 90 days. Spreadsheets are not a warehouse accelerator.

LookML sql_table_name pointed at events is the original sin for a lot of companies. It makes new fields appear automatically and makes every payload change a BI event. Point sql_table_name at marts.daily_kpis and add fields on purpose. The extra modeling hour is cheaper than a year of 1.2 TB explores.

view: daily_kpis {
  sql_table_name: `proj.marts.daily_kpis` ;;
  dimension: kpi_date { type: date sql: ${TABLE}.kpi_date ;; }
  dimension: account_id { type: number sql: ${TABLE}.account_id ;; }
  measure: views { type: sum sql: ${TABLE}.views ;; }
  measure: buys { type: sum sql: ${TABLE}.buys ;; }
}
-- Do not sql_table_name: proj.analytics.events
-- Do not view with sql: SELECT * FROM events

Aggregate at the grain of the chart: if the CFO wants daily views and buys by account, the mart is that grain. Do not make Looker group 4 TB of events at query time to draw a line chart; that is what scheduled SQL is for, and explores should be filters and light aggregations on an already-honest table. Refresh cadence belongs on the mart, not on the spreadsheet: a 6am dbt build of daily_kpis, BATCH, etl reservation, is one scan, while forty Connected Sheets opens of events during the staff meeting are forty scans. The chart looks the same to the executive. The JOBS table does not.

SlashPeak classifies Connected Sheets, Looker, the console, and scheduled queries from job-id prefixes and comment headers. The finding's attribution block is the difference between BI is expensive and the Sales explore on events_star is 38 percent of Monday's bytes. Unnamed BI waste becomes a culture speech. Named BI waste becomes a LookML change.

Sheets jobs often carry a sheets_dataconnector prefix on job_id. Scheduled queries carry scheduled_query. Console jobs often look like bquxjob. Looker, dbt, Dataform, and Airflow usually announce themselves in a SQL comment header. Classification happens at fetch time in classifyJobSource, stored on the job, and rolled into the finding. There is no extra warehouse permission and no table read.

Principal type still matters. A Looker service account versus a human running an explore as themselves changes who you ping and whether it counts as peak-hour ad-hoc. Environment tokens in dataset names (prod, staging) keep you from rewriting a dev explore that never hit the invoice. The attribution block is built for the way cost reviews actually run: who, what tool, which environment.

Without names, platform engineers rewrite a dbt model that was not on the critical path, declare victory, and watch Monday's bytes return. With names, you invite the Looker owner and the spreadsheet owner. Those are different people. The classified finding is the calendar invite.

Filter the SlashPeak backlog to Looker and Connected Sheets when the complaint is dashboards are slow or the invoice jumped after a QBR. Filter to dbt when the complaint is the 6am run. Same warehouse, different producers, different tickets. Attribution is not decoration. It is how the ranked list becomes a staffing plan.

Job-id prefixes are the hard signals. Connected Sheets and scheduled queries stamp a prefix before your SQL ever matters. If you only parse comments, you will call Sheets a standard job and lose the only label the spreadsheet owner will recognize. Prefixes are checked first on purpose.

Comment headers are the compiler's signature. dbt emits JSON with an app field. Looker emits a query context comment. Dataform and Airflow leave their own traces. These patterns live in application constants and run in TypeScript at fetch, not as a brittle INFORMATION_SCHEMA LIKE in SQL. The existing job_source column stores the result. No extra migration, no extra scan round-trip.

-- job_id prefixes (checked first)
--   sheets_dataconnector_…  → Connected Sheets
--   scheduled_query_…       → scheduled query
--   bquxjob_…               → BigQuery console (if no comment match)

-- SQL comment headers
--   {"app":"dbt", "dbt_version":"1.8.0", …}
--   -- Looker Query Context {…}
--   compiled by dataform / dataformCoreVersion
--   apache airflow / Airflow

Console jobs that match the bquxjob shape are labeled as the console when no better signature exists. That label is how you find the human who is exploring events at 10am without a Looker model. It is also how you avoid blaming Looker for a console habit. Mis-attribution is how tickets bounce.

When nothing matches, the source is standard. That bucket is real: scripts, unnamed tools, old clients. Do not pretend standard is Looker. Use top users and query text on those jobs. Classification is a best-effort map, not a lineage graph. SlashPeak does not parse LookML or open Drive. It labels the job that ran.

You can defeat the classifier by stripping comments. Some SQL formatters do. If your Looker jobs all show up as standard, check whether a proxy or formatter is eating the header, and restore the comment. Do not ask the scanner to guess from table names; guessing is how you get confident nonsense.

A Looker view that is SELECT * FROM events will grow every time ETL adds a column. Nested structs and JSON appear as explore fields. Someone will use them because they are there. Each use bills the column for every row in the scanned partitions. The explore grew itself. Nobody filed a PR to make it expensive. The star view was the PR.

CREATE OR REPLACE VIEW `proj.analytics.events_bi` AS
SELECT
  event_date,
  account_id,
  event_name,
  user_id
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
-- Point Looker / Sheets here.
-- Payload, UA, geo, nested structs stay on events for ETL and forensics.

SELECT_STAR_OVERFETCH looks for star projection on wide tables with meaningful bytes billed. Fifteen-plus columns, a gigabyte-plus job, not a cache hit. Savings assume a conservative cut of unused width. That is enough to rank the table. It is not a promise that listing eight columns will save exactly 40 percent. Listing eight columns on a 200-column events table often saves more. The finding is the ticket to go look.

Derived tables in Looker that wrap the star view inherit the star: persistent derived tables that SELECT * FROM events and then get explored with three fields persist the width into storage and then scan it again, so you pay to store the payload and read it for a KPI that never needed it. Materialize the grain, not the heap. Sheets connected to a custom query of SELECT * FROM events has the same problem without LookML; the custom query is the contract, so write the eight columns and save it. If someone needs a ninth, they change the contract. Spreadsheets that connect to an entire table have no contract, only a preview.

Fix projection in the BI layer even if dbt staging still uses SELECT * on a tiny source. The billed jobs are the explores. SlashPeak groups SELECT * by table, not by tool, but attribution tells you which tool to open first when that table is events and the top source is Looker.

Opening a spreadsheet can fire a query. Refreshing it fires again. A weekly QBR deck with twelve Connected Sheets tabs is twelve INTERACTIVE jobs, often during the meeting, often against the same heap, often without a partition filter the storage layer likes. That is BI waste and peak-hour ad-hoc in one artifact.

Because Sheets often runs as the human, it counts as human ad-hoc. That is correct. It is also why sandbox reservations and marts both apply. If you only move Sheets to BATCH, the meeting waits on a queue and someone will copy data into a static tab. If you only mart and leave Sheets on the BI reservation with SELECT *, you still have width. Do both: mart plus, if needed, a scheduled extract into a sheet that does not query live.

Scheduled extracts in Looker or a scheduled query that WRITEs a slim table for Sheets are boring on purpose. Boring is cheap. Live Connected Sheets against events is exciting and 1.2 TB. The chart in the meeting does not need live. It needs yesterday's grain, which landed at 6am.

CREATE OR REPLACE TABLE `proj.exports.qbr_kpis`
PARTITION BY kpi_date
AS
SELECT kpi_date, account_id, views, buys
FROM `proj.marts.daily_kpis`
WHERE kpi_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
-- Connect Sheets to exports.qbr_kpis.
-- Refresh from this table, or download once for the deck.
-- Do not CONNECT to proj.analytics.events.

Watch for the same spreadsheet in the top jobs list every weekday at 9:05. That is an auto-refresh. Turn it off or point it at the mart. Auto-refresh is a scheduled query with worse labels and INTERACTIVE priority. classifyJobSource will still say Connected Sheets. The schedule is in Drive, not in BigQuery. Someone has to open the workbook.

The fix is not banning Sheets, only banning Sheets on heaps. A Connected Sheet on marts.daily_kpis with a date filter is a reasonable executive tool. SlashPeak will stay quiet when the bytes look like a dashboard, and will not stay quiet when a chart refresh is actually billing like an ETL job.

LIMIT, TABLESAMPLE, and Looker's row limits are not partition filters. Some of them do not even reduce bytes. The console preview has its own billing story. A production explore does not. If the cost review is based on we only show 500 rows, pull total_bytes_billed and end the review.

SELECT *
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
LIMIT 500;
-- 500 rows in Sheets. Seven fat days in JOBS.total_bytes_billed.

-- Looker dimension that skips partitions:
-- sql: DATE(${TABLE}.event_ts)  -- table is PARTITION BY event_date

Pivots multiply measures in the SQL Looker emits: a pivot on 50 accounts can become a wide aggregation that still sits on a star view of events, so the chart is busy but the scan is busier. Pivot on a mart that is already at account grain, not on the heap. Cached results in Looker hide repeats until the cache misses, and a Monday morning cache miss after a weekend of ETL is a stampede of INTERACTIVE jobs, which is why peak-hour spikes and repeated-query findings show up together. Materialize the grain so a cache miss is 2 GB, not 1.2 TB; cache is not a mart.

DATE(event_ts) in a Looker dimension group is the partition-filter miss with a friendly calendar control in the UI. The user thinks they filtered to today. Storage did not. FULL_TABLE_SCAN will fire if the table is partitioned on event_date. Fix the dimension to the partition column. The UI can stay a calendar.

dimension_group: event {
  type: time
  timeframes: [date, week, month]
  sql: ${TABLE}.event_date ;;
  datatype: date
}
-- Filter explores on event_date, not DATE(event_ts).

None of these comforts are wrong, just incomplete. Use LIMIT for the browser, cache for snappy clicks, and a calendar control for humans; use a mart, a partition column, and a named field list for the warehouse. SlashPeak measures the warehouse. Align the three layers or the chart will keep winning arguments it should lose.

Inventory the top Looker and Sheets jobs by bytes. For each, write the grain the chart actually needs. If that grain is not a table, create it. Point the view or the connection at it. Delete or hide the explore on events. Hiding is part of the fix. A leftover explore will be used in a panic at 4pm.

Turn on require_partition_filter on events if you have not. BI jobs that still hit the heap will fail loudly instead of billing quietly. Then either teach those jobs the partition column or, better, finish moving them to the mart. Failures are how you find the twelfth Sheets tab you did not inventory.

Re-scan in SlashPeak. SELECT_STAR_OVERFETCH on events should shrink. FULL_TABLE_SCAN on events from Looker and Sheets should shrink. Peak-hour INTERACTIVE spend may shrink too if those refreshes were the spike. If dbt still rewrites events at 6am, that is MERGE_REWRITE and a different owner. Do not declare the BI sprint a failure because ETL is still on fire.

Keep the mart in the same cost review as the explores. A mart that is SELECT * FROM events nightly is an extract of the bug: column list, partition, cluster, 90-day window or whatever the product needs, since the mart is a product API and should be versioned like one. The verification screenshot is not a prettier chart; it is the same chart with a job that billed megabytes, classified as Looker or Sheets, and a finding group that dropped on the next scan. That is the loop: chart, job_id, bytes, label, fix, re-scan.