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.
COLUMNShasis_partitioning_column, which isYESorNO, andclustering_ordinal_position, the place of the column in the clustering list orNULL.TABLE_OPTIONShas one row for each option of each table.require_partition_filteris one of them, andoption_valueholds its value as text.PARTITIONShas one row for each partition of a table, with apartition_idsuch as202406for June 2024.- These views read metadata. The editor estimates 10 MiB for
COLUMNS,PARTITIONSandTABLE_OPTIONS, which is the billing minimum. A view such asJOBSreports the size of the history it holds, and a query on any of these views is never served from the cache.
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_idEach 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.