Cheap previews and stable samples

Looking at a big table can cost real money, and with TABLESAMPLE you pay for only part of it. A hash gives you a sample that stays the same from one query to the next.

How it works

BigQuery stores a table in blocks. TABLESAMPLE SYSTEM (10 PERCENT) keeps about a tenth of the blocks and skips the rest, so the query reads and pays for about a tenth of the bytes. It picks blocks and never single rows. A block comes with all its rows, so the number of rows changes from run to run. A hash works the other way round. It reads every block and saves nothing, but it picks the same rows every time. FARM_FINGERPRINT(id) turns an id into a fixed number, and the remainder of that number turns it into a bucket. One id always lands in one bucket.

Common mistake

A table or a partition of one block gives no saving. A sample of it returns every row and reads all of it, and a real run is billed at least 10 MiB for each table anyway. Judge a sample on a table with many blocks.

Worked example

SELECT *
FROM `learn.mot.dim_vehicle` TABLESAMPLE SYSTEM (10 PERCENT)

The editor estimates about 3.2 GiB before you run, where the whole table is 32.17 GiB. dim_vehicle has many blocks, so the sample can skip most of them.

Run it twice. The rows can differ, and so can the number of them, because BigQuery can pick other blocks each time.

All BigQuery lessons