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; both end up as the same line in the query text.

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
-- querylab:tablesample-threshold=10gb

Typing -- opens completions for querylab:, then the directive names, then common values (1, 5, 10, 25, 50; 1gb … 1tb). Spacing and case don’t matter; a value outside the range is ignored. If a directive appears twice, the last one wins.

tablesample-threshold has no effect yet. The directive parses and completes, but the run path never supplies table sizes to it, so every table is sampled regardless of size. Treat it as reserved.

What happens at run time

Before the dry run and before execution, Querylab.io rewrites the query and appends TABLESAMPLE SYSTEM (1 PERCENT) after every physical table. The editor shows where with an inlay hint after each table name; the query text itself is not changed.

-- 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%

Sampled: tables in FROM and on both sides of every JOIN, tables inside CTE bodies, tables in EXISTS subqueries, and pipe-syntax queries. When the same table appears more than once (a self-join, repeated UNION ALL arms), BigQuery rejects an inline TABLESAMPLE, so the table is hoisted into a generated CTE named __ql_sampled_<project_dataset_table> and every reference points at it.

Left alone: CTE references, subqueries in FROM, UNNEST(...), table-valued functions, tables inside IN (SELECT …), and anything that already has an explicit TABLESAMPLE.

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 aren’t excluded from the rewrite; 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.

How sampling interacts with diagnostics

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 rules that would otherwise fire (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.