# 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.

Source: https://docs.querylab.io/writing-sql/tablesample/

**Query 1 · 2.19 GB | ~$0.01**

```sql
-- 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
```

-99% by sampling (-$1.40)

## 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](https://docs.querylab.io/writing-sql/cost-limits/#per-query-limit).

### By hand

```sql
-- 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:

```sql
-- 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.

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

Hover the hint: 'This table will be sampled at 1% when the query is executed.'

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](https://docs.querylab.io/writing-sql/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](https://docs.querylab.io/bigquery/table-sampling/) 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](https://docs.querylab.io/writing-sql/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.