The BigQuery Table Scan Nobody Noticed Until the Bill Did

An IoT telemetry dashboard doesn't look like the kind of thing that runs up a cloud bill. Each device reports a stream of readings. Each device's detail page shows four charts: daily, monthly, yearly, lifetime. Open a page, get four numbers back. Nothing about that sounds expensive.

It got expensive anyway, because of how those four numbers were computed.

The query pattern

Every chart on the page was its own BigQuery query, and every query ran against the same raw readings table, filtered only by device:

SELECT
    MAX(reading_value) - MIN(reading_value) AS diff
FROM
    `project.dataset.readings`
WHERE
    device_id = 4821
    AND logged_time BETWEEN '2026-07-01T00:00:00' AND '2026-07-31T23:59:59'

Reasonable-looking SQL. A date range, a device filter, an aggregate. The problem isn't the query. It's the table underneath it: a flat, unpartitioned table holding every reading from every device since the product launched. BigQuery has no way to know that BETWEEN '2026-07-01' AND '2026-07-31' means "skip everything outside July." Without a partition column to prune on, that WHERE clause is just a filter applied after the scan, not a way to avoid the scan. The engine reads the whole table, every column referenced in the query, every row ever inserted, then throws most of it away.

Now multiply by four. The daily, monthly, yearly, and lifetime charts were each their own thread, firing in parallel the moment the page loaded:

class ChartDataThread(threading.Thread):
    def __init__(self, property_id, timestamp, flag):
        super().__init__()
        self.property_id = property_id
        self.timestamp = timestamp
        self.flag = flag
 
    def run(self):
        if self.flag == 'daily':
            self.output = get_daily_chart(self.property_id, self.timestamp)
        elif self.flag == 'monthly':
            self.output = get_monthly_chart(self.property_id, self.timestamp)
        elif self.flag == 'yearly':
            self.output = get_yearly_chart(self.property_id, self.timestamp)
        elif self.flag == 'lifetime':
            self.output = get_lifetime_chart(self.property_id, self.timestamp)

Parallel threads made the page feel fast. They also meant one page load did four full-table scans instead of one, and did them all at the same time, so there was never a slow query to notice in isolation. Nothing timed out. Nothing errored. The cost just showed up somewhere else: the BigQuery bill at the end of the month.

Why nobody caught it sooner

This is the annoying part about BigQuery cost problems. There's no query that's slow enough to complain about and no error that fires. You get billed for bytes scanned, and a full scan of a growing table just quietly scans more bytes every week. By the time someone actually looks at the billing dashboard, the number has already crept up gradually enough that no single day looks alarming. It's a boiling-frog problem, not a five-alarm fire.

The fix, once someone did look, took about ten minutes to describe and a lot longer to actually feel confident shipping, because it meant migrating a table that every report query depended on.

The fix

Partition the table by date, cluster it by device id. Nothing about the queries themselves had to change.

CREATE TABLE `project.dataset.readings_partitioned`
PARTITION BY DATE(logged_time)
CLUSTER BY device_id
AS
SELECT * FROM `project.dataset.readings`

Partitioning by DATE(logged_time) means BigQuery can look at the WHERE logged_time BETWEEN ... clause and skip every partition outside that range before scanning a single row. A query for one month of data now touches roughly one month's worth of partitions instead of the entire table's history. Clustering by device_id does the same trick one level deeper: within whatever partitions do get scanned, rows get physically grouped by device, so a query filtering on device_id = 4821 skips most of the blocks in that partition too.

Same SQL. Same application code. The only thing that changed was what the table looked like underneath the query, and that was enough, because the expensive part was never the query logic. It was a full scan pretending to be a filtered one.

What it bought

Roughly an 80% drop in bytes scanned, and the bill dropped with it. Four queries per page load were still four queries, but four partition-pruned queries cost a fraction of four full scans, so the redundancy stopped being the thing draining the budget.

That redundancy is still worth fixing on its own. Four near-identical queries, one per time range, could collapse into a single query with conditional aggregation and one round trip instead of four. But partitioning and clustering came first, because they didn't require touching a line of application code; they just changed what "scan the table" actually meant. When a cost problem lives in the schema, fix the schema before you touch the call sites built on top of it.

For the mechanics behind why that schema change works, see what partitioning and clustering actually do inside BigQuery.