TABLESAMPLE

One comment at the top of a query samples every table it reads. You iterate on the logic at a fraction of the cost, and the status bar tells you what the full run would have been.

-- querylab:tablesample=1
SELECT make, COUNT(*) AS tests
FROM `ace-analytics.warehouse.fact_mot_test`
WHERE completed_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY make

Turning it on

The directive is a comment. Type it yourself or let the status bar write it for you.

From the status bar

Click the ⚙ at the left of the status bar and fill in Tablesample. The popover inserts -- querylab:tablesample=N as the first line of the tab; clearing the field removes it again. The ⚙ shows a dot while a sampling directive is in the query. The same popover holds the tab’s byte and price limits; see cost limits.

By hand

-- querylab:tablesample=1

The value is the percentage of each table to read, 0.01 to 100, decimals allowed. Typing -- opens completions for querylab:, then the directive name, then common values (1, 5, 10, 25, 50). Spacing and case don’t matter; a value outside the range is ignored. If the directive appears twice, the last one wins.

What happens at run time

Sampling is applied when the query is priced and when it runs; your query text isn’t changed. An inlay hint after each table name shows where it applies.

-- querylab:tablesample=1
SELECT t.make, v.model
FROM `ace-analytics.warehouse.fact_mot_test` AS t
JOIN `ace-analytics.warehouse.dim_vehicle` AS v ON v.vehicle_id = t.vehicle_id TABLESAMPLE 1%

Every real table the query reads is sampled, including inside CTEs and pipe queries; a table used more than once (a self-join, repeated UNION ALL arms) is sampled once and reused. CTEs, subqueries, UNNEST, table-valued functions and anything that already has an explicit TABLESAMPLE are left alone.

The footer and status bar then show two numbers: the sampled estimate, and the saving against a second dry run of the un-sampled query.

Hover the green text for Full $1.41 → Sampled $0.01. If the comparison dry run fails, the saving isn’t shown.

Reading sampled results

TABLESAMPLE SYSTEM picks random storage blocks, not random rows.

• Counts and sums are of the sample; nothing is scaled up.
• Every run picks different blocks. Don’t compare two sampled runs to each other.
• A small table can return zero rows at 1%. Raise the percentage or drop the directive.
• Views and wildcard tables are sampled too; BigQuery rejects TABLESAMPLE on a view, and you’ll see its error.

A workable rhythm: 1 while shaping the query, 10 to check the numbers look right, then delete the line and run on everything.

Sampling and code-quality warnings

The rule Large Table Scan (a hint, on by default) points at any table above the Large table scan threshold (100 GB unless you change it) and offers the quick fix Add TABLESAMPLE SYSTEM (1 PERCENT), which inserts an explicit clause rather than the directive. Once a query samples, the cost warnings (missing partition filter, missing LIMIT, ORDER BY without LIMIT) stay quiet. See code quality.

Sampling in notebooks

A notebook has one TABLESAMPLE field for the whole notebook (gear on the notebook toolbar) and one per SQL cell (gear on the cell). A cell’s own value wins; an empty cell field shows Inherited from notebook. Both write the same directive into the cell’s SQL, so a cell run alone behaves exactly like a query tab.