Table & column-usage tools

Three tools for understanding how tables and columns are used over a time window: per-column usage (the primary signal for choosing cluster keys and finding dead columns), table-level storage/usage metrics, SQL-parsing health per table, and per-signature scan cost with pruning efficiency.

column_usage_list and table_usage default to the last 30 days — they resolve the window locally and echo it back as effective_processing_time_start / effective_processing_time_end, so pass those bounds back when paginating. table_scan_cost defaults to the most recent 5 processing days. Pass explicit dates to override.

All sample responses below use synthetic data.

column_usage_list

Reports how individual columns are used by queries over a time window — the primary signal for cluster-key choice. Default mode aggregates per-column usage (which columns appear in WHERE / JOIN / GROUP BY / etc. and how often); pass unused: true to list columns no query touched. Filter usage_types (any of WHERE, SELECT, LEFT_JOIN, RIGHT_JOIN, INNER_JOIN, FULL_JOIN, GROUPBY, ORDERBY, AGGREGATE) and group_by (USAGE_TYPE, default, or COLUMN_NAME). table_fqn is optional — omit it to scan all tables.

Sample call

"Over the last month, which columns of analytics.orders_fact are filtered or grouped on most?"

Sample response — usage

{
  "data": [
    { "table_fqn": "analytics_db.analytics.orders_fact", "column_name": "order_date", "usage_type": "WHERE", "usage_expression": "", "usage_operator": ">=", "usage_value": "", "sum_count": 1842 },
    { "table_fqn": "analytics_db.analytics.orders_fact", "column_name": "region", "usage_type": "GROUPBY", "usage_expression": "", "usage_operator": "", "usage_value": "", "sum_count": 970 }
  ],
  "next_page_token": "",
  "effective_processing_time_start": "2026-04-29T00:00:00+00:00",
  "effective_processing_time_end": "2026-05-29T00:00:00+00:00"
}

Sample response — unused: true

{
  "data": [
    { "table_fqn": "analytics_db.analytics.orders_fact", "column_name": "legacy_flag", "data_type": "BOOLEAN" }
  ],
  "next_page_token": "",
  "effective_processing_time_start": "2026-04-29T00:00:00+00:00",
  "effective_processing_time_end": "2026-05-29T00:00:00+00:00"
}

table_usage

Lists tables with their storage and usage metrics — the storage-overview view. Each row carries fqn, table_type, row_count, query_count, has_schema_data, size_byte, column_count, and unused_column_count. Optionally filter by catalog / schema / table (substring) and usage (USED / UNUSED); sort by ROW_COUNT (default), SIZE, or QUERY_COUNT.

Sample call

"What are the largest tables in the analytics schema, and how many have unused columns?"

Sample response

{
  "data": [
    {
      "fqn": "analytics_db.analytics.orders_fact",
      "table_type": "TABLE",
      "row_count": 1400000000,
      "query_count": 1842,
      "has_schema_data": true,
      "size_byte": 3100000000000,
      "column_count": 24,
      "unused_column_count": 3
    }
  ],
  "next_page_token": "",
  "effective_processing_time_start": "2026-04-29T00:00:00+00:00",
  "effective_processing_time_end": "2026-05-29T00:00:00+00:00"
}

table_scan_cost

Returns precomputed daily scan cost and pruning efficiency, aggregated per (table, signature, filter_cols) group and ordered by total scan cost descending. Use it to see which signatures scan the most data against a table and how well partition pruning is working. Optionally scope by fqn and/or signature; pass start_date / end_date (YYYY-MM-DD) to override the default window (the most recent 5 processing days). Paginate with size (default 10) and page.

Each row carries processing_time, table_fqn, signature, filter_cols, execution_count, total_scan_cost, total_query_cost, avg_scan_pct (scan cost as a share of query cost), and avg_pruning_fraction (0–1; higher means more partitions pruned away).

Sample call

"Break down scan cost and pruning for analytics.orders_fact over the last week."

Sample response

[
  {
    "processing_time": "2026-05-28T00:00:00Z",
    "table_fqn": "analytics_db.analytics.orders_fact",
    "signature": "analytics_daily &&& orders_rollup||a1b2",
    "filter_cols": "order_date",
    "execution_count": 14,
    "total_scan_cost": 142.50,
    "total_query_cost": 168.20,
    "avg_scan_pct": 84.7,
    "avg_pruning_fraction": 0.62
  }
]

The /singleorigin:table_scan_analysis built-in prompt (see Built-in Prompts) wraps this tool to detect table→view migrations and summarize scan-cost trends.