TABLESAMPLE

One comment at the top of a query samples the tables 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 Options (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: a number above 0, up to 100, decimals allowed (0.5). A % or percent after the number is fine (5%). Typing -- opens completions for querylab:, then the directive name, then common values (1, 5, 10, 25, 50). Spacing around = and case don’t matter, and a note after the value is fine (-- querylab:tablesample=1 -- while I build this). If the directive appears twice, the last one wins.

Any other value (0, 150, abc) is an error. The editor underlines it and the query is not run. A directive-looking comment that isn’t one, such as -- querylab: tablesample=5 with a space after the colon, is reported the same way.

Only large tables

Add a size threshold to leave small tables alone:

-- querylab:tablesample=5
-- querylab:tablesample-threshold=10gb
SELECT * FROM `ace-analytics.warehouse.fact_mot_test`

The value is a size (500mb, 10gb, 1tb) or a number of bytes. Tables below it are read in full. A table whose size Querylab.io doesn’t know yet is sampled.

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%

The tables that are sampled are real tables, including the ones inside CTEs, subqueries and pipe queries. A plain WHERE x IN (SELECT ...) is sampled too.

BigQuery can’t sample these, so they are read in full:

• views and materialized views, when Querylab.io knows the table is one
• INFORMATION_SCHEMA views
• CTEs, UNNEST sources and table-valued functions, and tables passed to a function with TABLE
• tables read through PIVOT or UNPIVOT
• tables in a NOT IN, or in an IN that is not a plain filter (under OR, in a function, in the select list)

Querylab.io also leaves these alone:

• tables read by a SET, a DECLARE, an IF condition or a function body. BigQuery accepts a sample there, but these reads feed variables and control flow, so a script sees the real data
• tables that already have an explicit TABLESAMPLE
• tables below the size threshold
• in a notebook, a cell that reads the result of another cell

The status bar lists them. If a table is a view that Querylab.io hasn’t loaded yet, it is sampled and BigQuery refuses the clause with its own error.

Two kinds of query are refused instead, and the error says why:

• A table read twice in one statement. BigQuery refuses a sampled table that appears more than once, so a self-join, two UNION ALL arms over the same table, or a CTE that holds a table and is read twice stops with an error that names the table. Read the table once, or remove the directive.
• A query that writes. INSERT, UPDATE, DELETE, MERGE, CREATE ... AS SELECT and EXPORT DATA would write a sample as if it were the data, and CREATE VIEW would save a view over a sample. Remove the directive to run them. A script with DECLARE, SET and SELECT statements is fine: each SELECT is sampled.

What the status bar shows

The footer and status bar show the sampled estimate, and the saving against a second dry run of the unsampled query.

The money in the saving is in your preferred currency. Hover the green text for the estimated cost with and without sampling. If the price isn’t known, or the query runs on slots (where the on-demand prices aren’t what you pay), the saving shows the percentage alone. If the comparison dry run fails the saving isn’t shown, and a sample that saves nothing, such as a table of one block, shows no saving.

N not sampled (or Nothing sampled) appears beside the saving when tables were left out. Hover it for each table and the reason. With nothing sampled, the query runs at full cost.

A byte limit works on the unsampled size. BigQuery checks maximum bytes billed against the size of the query before sampling, so Querylab.io compares your limit to the unsampled size too, and the warning says so. When the dry run of the unsampled query doesn’t answer, the status bar shows Limit not checked, and BigQuery checks the limit when the query runs. See cost limits.

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, and results of a sampled query are never cached. Don’t compare two sampled runs to each other.
• A table of up to about 175 MB is a single block, so a sample of it is the whole table: it returns every row and BigQuery bills all of it. Sampling only saves money on larger tables. See TABLESAMPLE in BigQuery for the numbers.
• Even at a very small percentage a sample reads at least one block.
• One clause pools the blocks of every partition it reads. A sample of a few partitions of a large table is a few blocks, not a fraction of each.

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, with an explicit clause or the directive, the cost warnings for that table (missing LIMIT, ORDER BY without LIMIT, and Large Table Scan itself) stay quiet. A table the directive leaves alone, such as a view or a table below the threshold, keeps its warnings. The missing partition filter warning is not affected by sampling. 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). Setting the notebook field writes the directive into the cells that inherit the notebook’s value (a cell with a value of its own keeps it). An empty cell field shows Inherited from notebook. Each field applies what you typed when you leave it or press Enter. A value that isn’t above 0 and up to 100 shows an error and changes nothing. Decimals such as 0.5 are fine, and 0,5 is read as 0.5; the directive is always written with a dot.

A cell is sampled by the same rules as a query tab: the same checks, the same refusals, the same notes under the cell. A cell that reads the result of another cell reads it in full, because that result was already sampled (or not) when the first cell ran.

Prism AI and MCP clients

A directive in the SQL that Prism AI or an MCP client sends to execute_query or estimate_cost is applied the same way. The result has a tableSample note that says how many tables were sampled and which weren’t, and estimate_cost prices the sample. A refusal comes back as a tool error that says what to change. BigQuery checks a byte limit against the size before sampling, so the agent’s limit is checked against that size too, and estimate_cost reports both sizes.