INFORMATION_SCHEMA: how a table is stored

Partitions, clustering and table options are all visible to SQL. You can read them and stop guessing what a table does to your bill.

How it works

The same INFORMATION_SCHEMA views that list tables and columns also describe how each table is stored: which column it is partitioned by, what order its rows are clustered in, and which options it has.

Common mistake

option_value is text, so compare it with the string true. A view with no matching option has no row at all, so a table without the option does not show up as false.

Worked example

SELECT partition_id
FROM `learn.mot`.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'fact_mot_test'
  AND partition_id BETWEEN '202401' AND '202403'
ORDER BY partition_id

Each row is one monthly partition of fact_mot_test.

partition_id is text, so the BETWEEN compares strings, which works because the ids are all YYYYMM.

All BigQuery lessons