BigQuery Optimization Workflow Attribution MUST READ : Before executing any , REST API, or MCP command, you MUST read Usage Attribution. For commands executed directly in the terminal (never in generated scripts, Terraform, or user code), prefix with and append to headers. Prerequisites & Environment Setup Before executing optimization analyses, evaluating editions, or applying DDL modifications: 1. Google Cloud SDK : Ensure the Google Cloud SDK is installed and configured. 2. Project Selection : Set the active Google Cloud project: 3. API Enablement : Ensure BigQuery and BigQuery Reservation…

)` should be rewritten as `a LIKE '%abc'`\n* `REGEXP_CONTAINS(a, '.*abc.*def.*')` should be rewritten as `a LIKE\n '%abc%def%'`\n\nExample of where `REGEXP_CONTAINS` should not be rewritten:\n\n* `REGEXP_CONTAINS(a, 'abc')` should not be rewritten as `a LIKE '%abc%'`\n since the query does not contain any wildcards.\n\n### Use SEARCH on columns with a search index\n\nIf the query filters a column with `REGEXP_CONTAINS` or `LIKE '%term%'` and the\ncolumn is covered by an `ACTIVE` search index with a `coverage_percentage` above\n0 (the user confirms, or `INFORMATION_SCHEMA.SEARCH_INDEXES` shows the column or\n`ALL COLUMNS` in `ddl`), suggest rewriting the filter to use the `SEARCH`\nfunction. If no index exists, propose the rewrite only together with an index\nproposal, to apply once coverage is above 0. Do not rewrite `=`, `IN`, `LIKE\n'prefix%'`, `STARTS_WITH`, or `ENDS_WITH` comparisons with string literals;\nBigQuery can already use the search index for them.\n\n`SEARCH` matches tokens, not substrings. With the default `LOG_ANALYZER` it is\ncase-insensitive, and `SEARCH(a, '192.0.2.1')` matches any row that contains\nthe tokens `192`, `0`, `2`, and `1` in any order; enclose the term in backticks\nfor an exact match. If the index uses a non-default analyzer, pass the same\nanalyzer to `SEARCH`. Because the results can differ from the original filter,\npropose it and wait for the user to confirm before applying; do not include it\nin an otherwise-automatic rewrite. For when to create an index, see\n[search_indexes.md](search_indexes.md).\n\nRationale: A search index lets BigQuery skip base table data that doesn't\ncontain the search tokens, which reduces bytes processed and slot time. Savings\nare largest when the matching rows are a small fraction of the table.\n\nHere is an example of a query that filters an indexed column with `LIKE`:\n\n```sql\nSELECT\n Level,\n Source,\n Message\nFROM\n my_dataset.Logs\nWHERE\n Message LIKE '%94.60.64.181%';\n```\n\nHere is the rewritten query with `SEARCH`:\n\n```sql\nSELECT\n Level,\n Source,\n Message\nFROM\n my_dataset.Logs\nWHERE\n SEARCH(Message, '`94.60.64.181`');\n```\n\n### In a WHERE clause, use a BETWEEN expression instead of the EXTRACT function\n\nIf the column is a `DATE` type: rewrite filter expressions of the form\n`EXTRACT(YEAR FROM date_column) = 2024` to `date_column BETWEEN '2024-01-01' AND\n'2024-12-31'`.\n\nIf the column is a `TIMESTAMP` type: rewrite filter expressions of the form\n`EXTRACT(YEAR FROM timestamp_column) = 2024` to `timestamp_column >= '2024-01-01\n00:00:00' AND timestamp_column \u003c '2025-01-01 00:00:00'`.\n\nRationale: BigQuery can statically evaluate `BETWEEN` expressions; it cannot\nstatically evaluate functions applied to columns.\n\nExample of a query with a date filter expression:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nWHERE\n EXTRACT(YEAR FROM t1.a2) = 2024;\n```\n\nHere is the query rewritten with a date filter expression:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nWHERE\n t1.a2 BETWEEN '2024-01-01' AND '2024-12-31';\n```\n\n### Add a LIMIT clause to an ORDER BY clause\n\nIf a query has an `ORDER BY` clause without a `LIMIT` clause, suggest adding a\n`LIMIT` clause.\n\nRationale: In vast majority of cases, users do not need the full results of an\n`ORDER BY` clause; `LIMIT` improves the performance of the `ORDER BY` clause.\n\nExample of a query with an `ORDER BY` clause without a `LIMIT` clause:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nORDER BY\n t1.a2;\n```\n\nHere is the example query rewritten with a `LIMIT` clause:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nORDER BY\n t1.a2\nLIMIT\n 1000;\n```\n\n### Remove an ORDER BY clause from a CREATE TABLE statement\n\nIf a query is a `CREATE TABLE` or `CREATE OR REPLACE TABLE` statement, remove an\n`ORDER BY` clause if it has one.\n\nRationale: When writing to a table, BigQuery will order the data as needed,\nmaking the `ORDER BY` unnecessary.\n\nHere is an example of a query with a `CREATE OR REPLACE TABLE` statement with an\n`ORDER BY` clause:\n\n```sql\nCREATE OR REPLACE TABLE test.a AS (\n SELECT\n t1.a0\n FROM\n test.b AS t1\n ORDER BY t1.a2\n);\n```\n\nHere is the query rewritten with the `ORDER BY` clause removed:\n\n```sql\nCREATE OR REPLACE TABLE test.a AS (\n SELECT\n t1.a0\n FROM\n test.b AS t1\n);\n```\n\n### Compare date columns to DATE literals, not string casts\n\nRewrite filter expressions of the form `CAST(date_column AS STRING) =\n'2025-01-15'` to `date_column = DATE('2025-01-15')`.\n\nRationale: BigQuery can statically evaluate comparisons against the column\nunless the column has a function applied to it.\n\nExample of a query with a date filter expression that casts a `DATE` column to a\nstring:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nWHERE\n CAST(DATE(date_column) AS STRING) = '2025-01-15';\n```\n\nHere is the query rewritten with a date filter expression that does not cast the\n`DATE` column but instead casts the string literal to a `DATE`:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nWHERE\n date_column = DATE('2025-01-15');\n```\n\n### Add a null filter to a NOT IN subquery\n\nRewrite subqueries within `NOT IN` clauses to add a `WHERE` clause that excludes\nnulls.\n\nRationale: It allows BigQuery to use the more performant anti-hash join instead\nof the slower left outer hash join.\n\nHere is an example of a query with a subquery in a `NOT IN` clause:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nWHERE\n t1.a2 NOT IN (SELECT t2.a2 FROM test.b AS t2);\n```\n\nHere is the rewritten query that adds a `WHERE` clause to exclude nulls in the\n`NOT IN` subquery:\n\n```sql\nSELECT\n t1.a0\nFROM\n test.a AS t1\nWHERE\n t1.a2 NOT IN (SELECT t2.a2 FROM test.b AS t2 WHERE t2.a2 IS NOT NULL);\n```\n\n### Replace a WHERE IN OR IN clause with LEFT JOINs\n\nIf the query contains a `WHERE ... IN (...) OR ... IN (...)` clause, which is an\nexpression that follows this pattern: `columnA IN (subqueryX) OR columnB in\n(subqueryY)`, then rewrite the query to use `LEFT JOIN` clauses.\n\nRationale: `WHERE ... IN (...) OR ... IN (...)` uses a cross product join, which\ncan be significantly more expensive than `LEFT JOIN`s.\n\nExample of a query with a `WHERE` clause that has two `IN` clauses:\n\n```sql\nSELECT\n t1.a0,\n t1.a1\nFROM\n test.a AS t1\nWHERE\n t1.a2 IN (SELECT t2.a2 FROM test.b AS t2)\n OR t1.a3 IN (SELECT t3.a3 FROM test.c AS t3);\n```\n\nHere is the rewritten query with `LEFT JOIN` clauses:\n\n```sql\nSELECT\n t1.a0,\n t1.a1\nFROM\n test.a AS t1\nLEFT JOIN (SELECT DISTINCT a2 FROM test.b) AS t2\n ON t1.a2 = t2.a2\nLEFT JOIN (SELECT DISTINCT a3 FROM test.c) AS t3\n ON t1.a3 = t3.a3\nWHERE\n t2.a2 IS NOT NULL OR t3.a3 IS NOT NULL;\n```\n\n### Replace exact aggregates with approximate aggregates\n\nIf the query contains the expression `COUNT(DISTINCT ...)`, suggest rewriting to\nuse `APPROX_COUNT_DISTINCT(...)`.\n\nThis changes exact counts to approximate ones. Propose it and wait for the user\nto confirm before applying; do not include it in an otherwise-automatic rewrite.\n\nRationale: Approximate aggregates are significantly more efficient than exact\naggregates, however at the cost of precision. As a general rule of thumb, large\naggregations should use approximate aggregates, and small aggregations should\nuse exact aggregates.\n\nHere is an example of a query with a `COUNT(DISTINCT ...)` expression:\n\n```sql\nSELECT\n COUNT(DISTINCT t1.a0)\nFROM\n test.a AS t1;\n```\n\nHere is the rewritten query with `APPROX_COUNT_DISTINCT`:\n\n```sql\nSELECT\n APPROX_COUNT_DISTINCT(t1.a0)\nFROM\n test.a AS t1;\n```\n\n### Pre-aggregate join inputs before joining\n\nIf a `WITH` CTE joins a detail table to a parent table without aggregating and\nthe outer query immediately aggregates (`GROUP BY`) that CTE's rows by the join\nkey, rewrite the CTE to pre-aggregate the detail table by the join key before\njoining to the parent table in the outer query, and place the largest table\nfirst (leftmost) in the `JOIN` clause. Only apply this rule when the query has a\n`WITH` CTE that performs an unaggregated `JOIN` followed by an outer `GROUP BY`;\ndo not apply it to flat queries without a join CTE or to queries that already\naggregate in a semijoin (`WHERE ... IN (SELECT ... GROUP BY ...)`).\n\nRationale: Reducing data with `GROUP BY` before a `JOIN` avoids shuffling and\nmultiplying detail rows across the join stage (`records_written >>\nrecords_read` and `shuffle_output_bytes_spilled > 0` in `job_stages`), reducing\nboth slot-time and shuffle memory usage. When diagnosing a Cartesian join\nexplosion, also inspect the `JOIN` clauses for unintentional `CROSS JOIN`s,\nmissing `ON` conditions, or non-selective join predicates (duplicate keys on\nboth sides), and filter out `NULL` or skewed dummy keys (e.g., `WHERE\ncustomer_id IS NOT NULL AND customer_id != 'GUEST'`) before joining.\n\nHere is an example of a query that joins `comments` and `users` before\naggregating:\n\n```sql\nWITH\n users_posts AS (\n SELECT\n u.display_name,\n u.reputation,\n c.score,\n c.text,\n c.user_id\n FROM\n `my_dataset.comments` AS c\n JOIN\n `my_dataset.users` AS u\n ON c.user_id = u.id\n )\nSELECT\n display_name,\n reputation,\n AVG(score) AS avg_score,\n COUNT(text) AS comments_count\nFROM\n users_posts\nGROUP BY\n display_name,\n reputation,\n user_id;\n```\n\nHere is the rewritten query that pre-aggregates `comments` by `user_id` before\njoining to `users`:\n\n```sql\nWITH\n comments_by_user AS (\n SELECT\n user_id,\n AVG(score) AS avg_score,\n COUNT(text) AS comments_count\n FROM\n `my_dataset.comments`\n GROUP BY\n user_id\n )\nSELECT\n u.display_name,\n u.reputation,\n c.avg_score,\n c.comments_count\nFROM\n comments_by_user AS c\nJOIN\n `my_dataset.users` AS u\n ON c.user_id = u.id;\n```\n\n## SQL optimization rules for reducing amount of data\n\n### Replace `SELECT *` with a specific column list\n\nIf the query contains a `SELECT *`, tell the user to rewrite to select only the\ncolumns needed. Only suggest this for the top-level `SELECT` in the main query\nor in `CREATE TEMP TABLE` statements. Do not suggest this for subqueries in\n`WITH` and `WHERE` clauses.\n\nRationale: Selecting only the necessary columns reduces the amount of data that\nBigQuery needs to read and process. `SELECT *` in subqueries do not necessarily\nincrease the amount of data read, as BigQuery can prune the columns in the\nsubquery.\n\nExample of a query with a top-level `SELECT *` for which the user should specify\na column list:\n\n```sql\nSELECT\n *\nFROM\n test;\n```\n\nExample of a query with a subquery that has `SELECT *`, which should not need to\nbe rewritten:\n\n```sql\nSELECT\n orders.order_id,\n SUM(orders.quantity) as total_quantity\nFROM\n orders\nWHERE EXISTS (\n SELECT *\n FROM line_items\n WHERE orders.order_id = line_items.order_id\n )\nGROUP BY 1;\n```\n\n### Check the query filters on the partitioned column\n\nCheck the given query on how it filters (either a `WHERE` clause in a `SELECT`\nstatement or `ON` clause in a `JOIN` statement) on the partitioned column.\n\nSteps to follow:\n\n1. Find all referred tables in `SELECT` or `JOIN` statements. If the query does\n not specify the project ID, use the project ID from the context.\n\n Example 1 where the referred tables follow the format `DATASET.TABLE` and do\n not specify the project ID:\n\n ```sql\n SELECT\n COUNT(o_orderkey)\n FROM\n my_dataset.orders\n INNER JOIN\n my_dataset.customers\n ON (o_custkey = c_custkey)\n ```\n\n Assuming that the project ID from the context is `my-project-id`, the\n referred tables in Example 1 are:\n\n - `my-project-id.my_dataset.orders`\n - `my-project-id.my_dataset.customers`\n\n Example 2 where the referred tables follow the format\n `PROJECT_ID.DATASET.TABLE`:\n\n ```sql\n SELECT\n COUNT(o_orderkey)\n FROM\n `my-project-id.my_dataset.orders`\n INNER JOIN\n `my-project-id.my_dataset.customers`\n ON (o_custkey = c_custkey)\n ```\n\n The referred tables in Example 2 are:\n\n - `my-project-id.my_dataset.orders`\n - `my-project-id.my_dataset.customers`\n\n2. Fetch the table schema from BigQuery for each referred table and get the\n partitioned column for each table if it exists.\n\n In the JSON output, use the field\n `partitionDefinition.partitionedColumn.field`. Note that this field is\n repeated, but in practice only one field is populated when the table is\n partitioned.\n\n3. For each table with a partitioned column, check the following rules:\n\n a. Ensure that the query contains a filter condition for the partitioned\n column for each table reference. If there is no filter condition for the\n partitioned column and the table has a partitioned column, tell the user.\n\n Rationale: If a table is partitioned, queries should leverage the\n partitioning to reduce the amount of data to read.\n\n Note that if the table's schema specifies that the partition filter is\n required (as specified in the field `requirePartitionFilter`), then no\n filter condition will cause the query to return an error.\n\n Example of query that does *not* have a filter condition on a partitioned\n column `o_orderdate` for table reference `my_dataset.orders`:\n\n ```sql\n SELECT\n COUNT(o_orderkey)\n FROM\n my_dataset.orders\n INNER JOIN\n my_dataset.customers\n ON (o_custkey = c_custkey)\n ```\n\n Example suggested improvement to show users:\n\n ```sql\n SELECT\n COUNT(o_orderkey)\n FROM\n my_dataset.orders\n INNER JOIN\n my_dataset.customers\n ON (o_custkey = c_custkey)\n WHERE\n -- Ask the user to provide the start and end dates.\n o_orderdate BETWEEN @start_date AND @end_date\n ```\n\n b. If the filter condition for the partitioned column is dynamic\n (specifically, a scalar subquery or a column-dependent aggregation), rewrite\n the query so that the filter condition is in a variable.\n\n Example of a query with a dynamic filter condition that is doing an\n aggregation on a partitioned column:\n\n ```sql\n SELECT o_orderstatus, o_orderdate\n FROM `my_dataset.orders`\n WHERE\n o_orderdate = (\n SELECT max(o_orderdate) FROM `my_dataset.orders`\n );\n ```\n\n Here is the query rewritten so that the filter condition is in a variable:\n\n ```sql\n DECLARE max_order_date DATE DEFAULT (\n SELECT max(o_orderdate) FROM `my_dataset.orders`\n );\n SELECT o_orderstatus, o_orderdate\n FROM `my_dataset.orders`\n WHERE\n o_orderdate = max_order_date;\n ```\n\n Note that the variable `max_order_date` still scans the entire table,\n however the full scan is still more efficient than the original query. If\n the table is configured with `require_partition_filter = true`, then the\n user needs to add an additional filter condition within the variable\n `max_order_date`.\n\n c. Isolate the partitioned column from unsupported functions and arithmetic\n expressions. BigQuery prunes partitions when the `WHERE` filter compares the\n partitioned column (or a documented pruning-supported built-in function on\n the partitioned column, such as `DATE(ts_col)`, `EXTRACT(DATE FROM ts_col)`,\n `CAST(ts_col AS DATE)`, `DATE_TRUNC`, or `TIMESTAMP_TRUNC` with constant\n arguments) against a constant expression. Wrapping the partitioned column in\n an unsupported function (such as `FORMAT_DATE` or `EXTRACT(MONTH FROM\n ts_col)`) or applying arithmetic to the partitioned column (such as\n `ts_col + INTERVAL 1 DAY > CURRENT_TIMESTAMP()`) prevents partition pruning.\n Rewrite the filter to isolate the partitioned column on one side of the\n comparison:\n\n ```sql\n -- Unpruned (arithmetic on the partitioned column):\n SELECT\n order_id,\n total_amount\n FROM\n `my_dataset.orders`\n WHERE\n order_timestamp + INTERVAL 1 DAY > CURRENT_TIMESTAMP();\n\n -- Rewritten to isolate the partitioned column for partition pruning:\n SELECT\n order_id,\n total_amount\n FROM\n `my_dataset.orders`\n WHERE\n order_timestamp > CURRENT_TIMESTAMP() - INTERVAL 1 DAY;\n ```\n\n#### Fetching table schemas from BigQuery\n\nUse the following shell command to fetch the table schema from BigQuery:\n\n- If the query specifies the project ID:\n\n ```bash\n bq show --format=prettyjson PROJECT_ID:DATASET.TABLE\n ```\n\n For example, given this table reference: `my_project:my_dataset.orders`, run\n the command:\n\n ```bash\n bq show --format=prettyjson my_project:my_dataset.orders\n ```\n\n- If the query does not specify the project ID:\n\n ```bash\n bq show --format=prettyjson DATASET.TABLE\n ```\n\n For example, given this table reference: `my_dataset.orders`, run the\n command:\n\n ```bash\n bq show --format=prettyjson my_dataset.orders\n ```\n\n#### Validating a rewrite with a dry run\n\nAfter rewriting a query to add partition filters or replace `SELECT *` with\nexplicit columns, instruct the user to validate the estimated bytes processed\nbefore executing the query using a dry run:\n\n```bash\nbq query --use_legacy_sql=false --dry_run \"{sql_query}\"\n```\n\nA dry run validates SQL syntax and returns the deterministic\n`totalBytesProcessed` estimate after partition pruning and column pruning\nwithout running the query or incurring compute charges. Note that block-pruning\nsavings from clustered columns are determined dynamically during execution and\nare not reflected in the dry-run upper bound.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":24066,"content_sha256":"a10d0344e9df1f66e3d3c028e4e721231f075fc2717e6bfe4e9c72d14ae548ab"},{"filename":"references/storage_billing_models.md","content":"# Storage Billing Models: Logical vs. Physical Optimization\n\nGuidance for evaluating whether a BigQuery dataset achieves better\ncost-efficiency under `LOGICAL` or `PHYSICAL` storage billing.\n\n## Table of Contents\n\n- [1. Evaluation Workflow](#1-evaluation-workflow) (Lines 17-81)\n - [Step 1: Gather Target Metrics](#step-1-gather-target-metrics) (Lines 22-34)\n - [Step 2: Calculate Cost Comparison](#step-2-calculate-cost-comparison) (Lines 36-61)\n - [Step 3: Evaluate Decision Rules & Trade-offs](#step-3-evaluate-decision-rules-trade-offs) (Lines 63-81)\n- [2. Actionable Recommendations](#2-actionable-recommendations) (Lines 83-107)\n - [If Currently on Logical Billing:](#if-currently-on-logical-billing) (Lines 88-97)\n - [If Currently on Physical Billing:](#if-currently-on-physical-billing) (Lines 99-107)\n- [3. Critical Footguns](#3-critical-footguns) (Lines 109-129)\n\n## 1. Evaluation Workflow\n\nFollow this step-by-step workflow when a user inquires about switching storage\nbilling models:\n\n### Step 1: Gather Target Metrics\n\nRetrieve storage metrics from the regional `INFORMATION_SCHEMA.TABLE_STORAGE`\nview for the target dataset.\n\n- **Scope:** Query ``\n `{project_id}`.`region-{location}`.INFORMATION_SCHEMA.TABLE_STORAGE``\n filtered by `table_schema = '{dataset_id}'`, `table_type = 'BASE TABLE'`,\n and non-zero storage (`total_physical_bytes + fail_safe_physical_bytes > 0`)\n to isolate billable native base tables.\n- **Inputs Required:** `active_logical_bytes`, `long_term_logical_bytes`,\n `active_physical_bytes`, `long_term_physical_bytes`,\n `time_travel_physical_bytes`, and `fail_safe_physical_bytes`.\n\n### Step 2: Calculate Cost Comparison\n\nCalculate the forecasted monthly costs for both models using regional or\ncontract unit rates:\n\n```text\nlogical_cost = (active_logical_gib * price_active_logical)\n + (long_term_logical_gib * price_long_term_logical)\n\nphysical_cost = (\n (active_physical_gib + fail_safe_physical_gib)\n * price_active_physical\n )\n + (long_term_physical_gib * price_long_term_physical)\n\nforecast_total_cost_difference = logical_cost - physical_cost\n```\n\n*(Note: In `TABLE_STORAGE`, `active_physical_bytes` already includes baseline\nactive data and `time_travel_physical_bytes`. Adding `fail_safe_physical_bytes`\ncompletes the active physical storage footprint).*\n\n- **Positive Delta (`> 0`):** Physical billing is forecasted to have lower\n total cost.\n- **Negative Delta (`\u003c 0`):** Logical billing is cheaper; transitioning to\n Physical billing would increase costs.\n\n### Step 3: Evaluate Decision Rules & Trade-offs\n\n1. **Break-Even Compression Threshold:**\n - *Baseline Ratio:* Physical rates (\\$0.04/GiB active, \\$0.02/GiB\n long-term in US) are roughly **2x** Logical rates (\\$0.02/GiB active,\n \\$0.01/GiB long-term). In standard US regions, data must compress\n **better than 2:1 (>2x)** (`logical_volume / physical_volume > 2`)\n before accounting for overhead.\n - *Regional & Contract Adjustments:* For non-US regions or enterprise\n contracts, calculate the specific `price_physical / price_logical` ratio\n dynamically using current rates from\n [BigQuery Storage Pricing](https://cloud.google.com/bigquery/pricing#storage-pricing).\n2. **Time Travel & Fail-Safe Overhead:**\n - Under **Logical** billing, Time Travel and Fail-Safe bytes are **free**.\n - Under **Physical** billing, Time Travel and Fail-Safe bytes are **billed\n at the physical active rate**.\n - High-churn workloads (frequent `UPDATE`/`DELETE` or daily full-table\n overwrites) inflate physical storage and require higher break-even\n compression (often 2.5x–3x+).\n\n## 2. Actionable Recommendations\n\nDetermine the dataset's current storage billing model (via metadata or\n`INFORMATION_SCHEMA.TABLE_STORAGE`):\n\n### If Currently on Logical Billing:\n\n- **Physical is More Cost-Effective (`forecast_total_cost_difference > 0` &\n Low Churn):** Recommend updating the dataset storage billing model to\n `PHYSICAL` in dataset settings.\n- **High Churn but Strong Compression:** Recommend reducing the dataset Time\n Travel window (e.g., from 7 days down to 2 days) *before* transitioning to\n Physical billing to minimize overhead.\n- **Logical is More Cost-Effective (`forecast_total_cost_difference \u003c 0`):**\n Recommend remaining on `LOGICAL` billing.\n\n### If Currently on Physical Billing:\n\n- **Logical is More Cost-Effective (`forecast_total_cost_difference \u003c 0`):**\n Recommend transitioning the dataset back to `LOGICAL` billing in dataset\n settings (verifying the 14-day lock-in cooldown has elapsed).\n- **Physical is Favorable but Inflated by Time Travel:** Recommend reducing\n the dataset Time Travel window to lower billable physical active bytes\n without changing the billing model.\n- **Physical is Optimal:** Recommend maintaining `PHYSICAL` billing.\n\n## 3. Critical Footguns\n\n- **14-Day Cooldown (Hard Lock-in):** Once a dataset's storage billing model\n is updated, it **cannot be changed again for 14 days**. Verify compression\n and historical churn across a full cycle before advising a change.\n- **24-Hour Propagation Delay:** After updating the storage billing model on a\n dataset, it takes up to **24 hours** for the change to take effect and be\n reflected in storage billing calculations.\n- **Time Travel & Fail-Safe Incur Active Physical Rates:** Under Physical\n billing, both Time Travel (1–7 days) and Fail-Safe (7-day non-configurable\n disaster recovery) storage are billed at the **active physical rate**. In\n contrast, Time Travel and Fail-Safe bytes are completely free under Logical\n billing. For high-churn datasets, Time Travel + Fail-Safe overhead can\n outweigh compression savings.\n- **Free Tier Proration & Scope:** The 10 GiB monthly free tier is pooled\n across the Cloud Billing account and prorated per second (e.g., 100 GiB\n stored for 1 day is ~3.3 GiB-months, fitting within the free tier).\n- **Deletion Footgun on Physical Storage:** Deleting a table stops billing\n immediately under Logical storage, but continues accruing active physical\n charges for 9–14 days (Time Travel + 7-day Fail-Safe) under Physical\n storage.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":6381,"content_sha256":"772ef9abb45baee8662db53b6f1aa9c1512a63da550fdac80f920f4bfa9356f3"},{"filename":"references/storage_lifecycle_management.md","content":"# Storage Lifecycle Management (Retention & TTL Optimization)\n\nDecision matrix and parameters for at-rest storage right-sizing.\n\n## 1. Optimization Decision Matrix\n\n\u003c!-- mdformat off -->\n| Target Area | Trigger / Condition | Action / Parameter | Expected Impact |\n| :--- | :--- | :--- | :--- |\n| **Partition TTL** | Staging dataset or rolling logs | Set `default_partition_expiration_days` or `partition_expiration_days` | Automatically drops expired partitions after window |\n| **Time Travel Tuning** | Ephemeral tables on `PHYSICAL` storage | Set `max_time_travel_hours = 48` (2-day min) | Drops `time_travel_physical_bytes` by ~5/7 (~71%) |\n| **Unpartitioned Table** | Mature table (>90d) with active-only spend | Partition table by date or timestamp | Unlocks 50% Long-Term discount on historical rows |\n| **Dev/Test Replication** | Full CTAS staging table copies | Replace with `CREATE TABLE CLONE` | Billed only for delta blocks added/modified |\n| **Disaster Recovery** | Point-in-time table backups | Replace with `CREATE SNAPSHOT TABLE` | Billed only for modified/deleted base rows |\n| **Cold Compliance** | Data accessed \u003c1 time/year | Export to GCS Archive + query via BigLake | ~8x cheaper than BigQuery long-term logical rate |\n\u003c!-- mdformat on -->\n\n## 2. Savings Formulas & Calculation\n\n```text\n# Time Travel Reduction Savings (Physical billing only):\nestimated_tt_savings = (\n current_time_travel_gib\n * (1 - (new_window_hours / current_window_hours))\n ) * price_active_physical\n```\n\n## 3. Gotchas & Constraints\n\n- **DML Clock Reset:** Unpartitioned `UPDATE`/`MERGE` touches all partitions,\n resetting their 90-day long-term timer back to 0. Always filter `WHERE\n _PARTITIONDATE >= ...`.\n- **Fail-Safe Invariant:** Fail-Safe window is fixed at 7 days\n non-configurable; continues to accrue active physical storage charges after\n Time Travel ends.\n- **Logical Billing Applicability:** Time Travel and Fail-Safe bytes are free\n under `LOGICAL` billing; tuning `max_time_travel_hours` yields \\$0 storage\n savings on Logical datasets.\n- **TTL Permanent Drop:** Data dropped via partition expiration cannot be\n recovered once Time Travel + Fail-Safe windows elapse.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":2274,"content_sha256":"bd47ed9fe1e0d2996e712a65be7a0bc331beaf49a911c8497ab6e5e2dd3731b3"},{"filename":"references/table_partitioning_clustering.md","content":"# Table Partitioning & Clustering Strategy\n\nArchitectural guidelines, decision matrices, and DDL patterns for table design,\nquery pruning optimization, and storage efficiency.\n\n## Table of Contents\n\n- [1. Decision Matrix: Partitioning vs. Clustering vs. Hybrid](#1-decision-matrix-partitioning-vs-clustering-vs-hybrid) (Lines 22-32)\n- [2. Partitioning Design Principles](#2-partitioning-design-principles) (Lines 34-66)\n - [Partitioning Types by Column Type](#partitioning-types-by-column-type) (Lines 36-46)\n - [Core Partitioning Invariants & Rules](#core-partitioning-invariants-rules) (Lines 48-66)\n- [3. Clustering Design Principles](#3-clustering-design-principles) (Lines 68-88)\n - [Clustering Rules & Mechanics](#clustering-rules-mechanics) (Lines 70-88)\n- [4. Production DDL Templates](#4-production-ddl-templates) (Lines 90-178)\n - [Recommended Hybrid Pattern (Partition + Cluster)](#recommended-hybrid-pattern-partition-cluster) (Lines 92-112)\n - [Migrating an Existing Unpartitioned Table to Partitioned](#migrating-an-existing-unpartitioned-table-to-partitioned) (Lines 114-135)\n - [Modifying the Clustering Specification](#modifying-the-clustering-specification) (Lines 137-178)\n- [5. Table Design & Recommendation Workflow (Given a Table or Schema)](#5-table-design-recommendation-workflow-given-a-table-or-schema) (Lines 180-235)\n- [6. Table Design Anti-Patterns & Architecture Constraints](#6-table-design-anti-patterns-architecture-constraints) (Lines 237-263)\n- [7. Telemetry & Data Retrieval Reference](#7-telemetry-data-retrieval-reference) (Lines 265-287)\n\n## 1. Decision Matrix: Partitioning vs. Clustering vs. Hybrid\n\n\u003c!-- mdformat off -->\n| Dimension | Partitioning (`PARTITION BY`) | Clustering (`CLUSTER BY`) | Hybrid (`PARTITION BY` + `CLUSTER BY`) |\n| :--- | :--- | :--- | :--- |\n| **Best For** | Low-cardinality values, temporal/date boundaries, date-based TTL retention. | High-cardinality values, multiple filter/group-by columns, frequent DML modifications. | Large temporal tables with multi-dimensional filtering (e.g., date + tenant/status). |\n| **Pruning Mechanism** | **Partition Pruning:** Whole partitions are eliminated from scanning before execution. | **Block Pruning:** Capacitor data blocks within files are skipped dynamically during execution. | Two-tier pruning: Partition pruning narrows the time window, then block pruning skips blocks within partitions. |\n| **Cost Estimation** | Known deterministically before query execution via dry-run (`totalBytesProcessed`). | Estimated dynamically during execution as blocks are evaluated. | Upfront partition scan bounds with execution-time block savings. |\n| **Storage Lifecycle** | Unlocks **partition-level TTL** and 90-day long-term 50% storage price discount. | No partition-level expiration (data is co-located across storage blocks). | Enables partition TTL retention while optimizing intra-partition query performance. |\n| **Limits** | Up to **10,000 partitions per table**. | Up to **4 clustering columns** per table. | 10,000 partition limit applies; clustering operates within each partition. |\n\u003c!-- mdformat on -->\n\n## 2. Partitioning Design Principles\n\n### Partitioning Types by Column Type\n\n* **`TIMESTAMP` Column:** `PARTITION BY TIMESTAMP_TRUNC(column_name, DAY)`\n* **`DATE` Column:** `PARTITION BY date_column` (or for monthly granularity:\n `PARTITION BY DATE_TRUNC(date_column, MONTH)`)\n* **`DATETIME` Column:** `PARTITION BY DATETIME_TRUNC(datetime_column, DAY)`\n* **Ingestion-Time Partitioning:** BigQuery assigns rows to partitions based\n on the ingestion time using the pseudo-column `_PARTITIONDATE` /\n `_PARTITIONTIME`.\n* **Integer-Range Partitioning:** Partition by an `INT64` column using\n `RANGE_BUCKET(column, GENERATE_ARRAY(start, end, interval))`.\n\n### Core Partitioning Invariants & Rules\n\n* **10,000 Partition Quota Limit:** Each partitioned table can have up to\n 10,000 partitions. If a table reaches or approaches this limit:\n * *Solution:* Widen the partition time unit (e.g., switch from `HOUR` to\n `DAY`, or `DAY` to `MONTH`) and **cluster on the granular time column**\n (e.g. partition by month, cluster by timestamp). BigQuery automatically\n clusters data blocks within each partition.\n* **Enforce Partition Pruning (`require_partition_filter = true`):** For large\n tables, always set `require_partition_filter = true` to force queries to\n supply a partition predicate in the `WHERE` clause, preventing accidental\n multi-terabyte unpruned full-table scans.\n* **Avoid Over-Partitioning:** Do not create hourly partitions for low-volume\n tables. Partitioning that results in a small amount of data per partition\n (approximately less than 10 GB) increases the table's metadata, and can\n affect metadata access times when querying the table.\n* **Long-Term Storage Discount:** Partitions that are not modified for 90\n consecutive days automatically qualify for **50% cheaper long-term storage**\n pricing.\n\n## 3. Clustering Design Principles\n\n### Clustering Rules & Mechanics\n\n* **Up to 4 High-Cardinality Filtering & Grouping Columns:** Specify up to 4\n high-cardinality filtering and grouping columns (`WHERE` filters and `GROUP\n BY` keys) in the `CLUSTER BY` clause.\n* **Column Ordering is Critical:** BigQuery clusters data hierarchically based\n on the order of columns specified:\n * Place the **most frequently filtered column** or the column with\n **equality filters** first (`col1`).\n * Place secondary filter columns, range filter columns, or `GROUP\n BY`/`JOIN` keys next (`col2`, `col3`, `col4`).\n * *Trap:* Filtering on `col2` without a predicate on `col1` significantly\n diminishes block pruning effectiveness.\n* **Supported Data Types:** `STRING` (first 1,024 characters), `INT64`,\n `NUMERIC`, `BIGNUMERIC`, `BOOL`, `TIMESTAMP`, `DATE`, `DATETIME`,\n `GEOGRAPHY`, `RANGE`.\n* **Automatic Re-Clustering:** BigQuery performs continuous, fully-managed\n background reclustering to maintain block sortedness as new data is appended\n or modified, at **zero slot cost**.\n\n## 4. Production DDL Templates\n\n### Recommended Hybrid Pattern (Partition + Cluster)\n\n```sql\nCREATE OR REPLACE TABLE `{project_id}.{dataset_id}.events`\n(\n event_id STRING,\n customer_id STRING,\n event_type STRING,\n event_timestamp TIMESTAMP,\n payload JSON\n)\n-- Partition by DATE, TIMESTAMP, DATETIME, or INT64 range column:\nPARTITION BY TIMESTAMP_TRUNC(event_timestamp, DAY)\n-- Cluster on up to 4 high-cardinality filtering and grouping columns:\nCLUSTER BY customer_id, event_type\nOPTIONS (\n require_partition_filter = true,\n partition_expiration_days = 365,\n description = 'High-volume events partitioned by day and clustered by customer'\n);\n```\n\n### Migrating an Existing Unpartitioned Table to Partitioned\n\nYou cannot directly convert an existing non-partitioned table to a partitioned\ntable via `ALTER TABLE`, and it is not possible to use the `OR REPLACE` modifier\nto replace a table with a different kind of partitioning. Instead, use a `CREATE\nTABLE ... PARTITION BY ... CLUSTER BY ... AS SELECT * FROM ...` (CTAS) statement\nto create a new partitioned table by querying the data in the existing table (or\n`DROP` the table first and then recreate it)—partitioning by a `DATE`,\n`TIMESTAMP`, `DATETIME`, or `INT64` (`RANGE_BUCKET`) column, clustering on up to\n4 high-cardinality filtering and grouping columns (`WHERE` and `GROUP BY`), and\nsetting `require_partition_filter = true`:\n\n```sql\nCREATE TABLE `{project_id}.{dataset_id}.events_partitioned`\n -- Partition by DATE, TIMESTAMP, DATETIME, or INT64 range column:\n PARTITION BY TIMESTAMP_TRUNC(event_timestamp, DAY)\n -- Cluster on up to 4 high-cardinality filtering and grouping columns:\n CLUSTER BY customer_id, event_type\n OPTIONS (require_partition_filter = TRUE)\nAS\nSELECT * FROM `{project_id}.{dataset_id}.events_unpartitioned`;\n```\n\n### Modifying the Clustering Specification\n\nApply a new clustering specification to unpartitioned or partitioned tables in\ntwo steps. First, update the clustering specification of the table to match the\nnew clustering:\n\n```bash\nbq update --clustering_fields=customer_id,status \\\n {project_id}:{dataset_id}.{table_id}\n```\n\nThe `--clustering_fields` value is a comma-separated list of column names to use\nfor clustering.\n\nThen, to cluster all rows according to the new clustering specification, run the\nfollowing `UPDATE` statement. Propose this statement to the user rather than\nrunning it directly:\n\n```sql\nUPDATE `{project_id}.{dataset_id}.{table_id}`\nSET customer_id = customer_id\nWHERE true;\n```\n\nThe `UPDATE` must supply a partition filter if the table has\n`require_partition_filter = true`, and a single job cannot modify more than\n4,000 partitions (the per-job modification limit). For partitioned tables,\nre-cluster in bounded batches:\n\n```sql\nUPDATE `{project_id}.{dataset_id}.{table_id}`\nSET customer_id = customer_id\nWHERE {partition_column} >= '{range_start}'\n AND {partition_column} \u003c '{range_end}';\n```\n\nRepeat over consecutive ranges of fewer than 4,000 partitions until the whole\ntable is covered.\n\nTell the user that applying a new clustering specification to a table in\nlong-term storage reverts that table to\n[active storage pricing](https://cloud.google.com/bigquery/pricing#storage-pricing).\n\n## 5. Table Design & Recommendation Workflow (Given a Table or Schema)\n\nWhen asked to recommend a partitioning and clustering strategy for a specific\ntable or schema:\n\n1. **Step 1: Identify Partitioning Key & Time Granularity**\n\n * *Audit Existing Table Configuration:* Before proposing changes, check\n `INFORMATION_SCHEMA.COLUMNS` (`is_partitioning_column = 'YES'`,\n `data_type`, `clustering_ordinal_position`) and\n `INFORMATION_SCHEMA.TABLE_OPTIONS` (`option_name =\n 'require_partition_filter'`) for the target dataset, or run `bq show\n --format=prettyjson {project_id}:{dataset_id}.{table_id}`\n (`timePartitioning`, `rangePartitioning`, `clustering`,\n `requirePartitionFilter`) to confirm whether the table already has\n `DATE`, `TIMESTAMP`, `DATETIME`, or `INT64` integer-range partitioning,\n clustering columns, and partition filter enforcement.\n * *Scan Column Types:* Look for `TIMESTAMP`, `DATE`, or `DATETIME` columns\n representing primary event generation time.\n * *Check the 10,000 Partition Quota Limit:*\n * **`DAY` Partitioning (Default):** Optimal for 1–7 year lifespans\n (~365 to ~2,555 partitions).\n * **`HOUR` Partitioning:** Choose it only for tables with a high\n volume of data spanning a short date range — typically less than six\n months of timestamp values. Make sure the resulting partition count\n stays within the 10,000 partition limit.\n * **`MONTH` or `YEAR` Partitioning:** For historical archives (>10\n years), partition by `MONTH` and cluster by the timestamp.\n * *No Temporal Column:* Evaluate integer range partitioning\n (`RANGE_BUCKET`) for incremental IDs, or ingestion-time partitioning\n (`_PARTITIONTIME`) for append-only log streams.\n\n2. **Step 2: Select & Order Clustering Columns (Up to 4 Columns)**\n\n * *Candidate Columns:* High-cardinality dimension columns frequently\n present in `WHERE` equality filters, `JOIN ON` clauses, or `GROUP BY`\n aggregations (e.g., `customer_id`, `organization_id`, `status`).\n * *Column Ordering Rules:*\n * `col1`: The most frequently filtered column or equality/tenant\n identifier (`customer_id`).\n * `col2`, `col3`: Secondary dimensions or range filter attributes\n (`event_type`, `created_at`).\n * *Order Trap:* Queries filtering on `col2` without a predicate on\n `col1` cannot perform efficient block pruning.\n\n3. **Step 3: Output Production DDL & Guardrails**\n\n * Generate complete `CREATE TABLE` DDL, including CTAS migrations. To\n modify a table's clustering specification, use the two-step sequence in\n Section 4.\n * Add `require_partition_filter = true` (`ALTER TABLE\n {dataset_id}.{table_id} SET OPTIONS (require_partition_filter = true)`)\n for high-volume production tables to prevent accidental unpruned\n full-table scans.\n * Add `partition_expiration_days = N` for staging, ETL, or rolling log\n tables to automate data cleanup.\n\n## 6. Table Design Anti-Patterns & Architecture Constraints\n\n- **`ALTER TABLE` Partitioning Invariant:** BigQuery **does not support\n altering an existing unpartitioned table to become partitioned**. You must\n create a new partitioned table using CTAS or a load job. `ALTER TABLE` does\n not support setting or changing a table's clustering; use `bq update\n --clustering_fields` (Section 4).\n- **Small-Partition Anti-Pattern (\u003c10 GB per Partition):** Prefer clustering\n over partitioning when partitioning would yield **less than ~10 GB per\n partition**. Creating many small partitions inflates table metadata and\n slows metadata access at query time without yielding scan savings.\n *(Separately, on-demand query compute charges enforce a 10 MiB data\n processed minimum per referenced table and per query, regardless of table or\n partition size).*\n- **1,024-Character String Limit:** For `STRING` clustering columns, BigQuery\n evaluates only the **first 1,024 characters** for block pruning. Do not\n cluster on long free-text descriptions or raw JSON payloads; extract concise\n identifiers.\n- **Streaming Buffer Compaction (`__UNPARTITIONED__`):** Rows recently\n streamed via the BigQuery Storage Write API initially reside in the\n `__UNPARTITIONED__` partition with a `NULL` `_PARTITIONTIME`. These rows\n cannot be pruned by partition filters until extracted into permanent\n partitions by BigQuery's background compaction.\n- **Query Filter Optimization:** To leverage partition and clustering pruning\n effectively in query execution, predicates must use compile-time constants.\n For query-level `WHERE`-clause rewrites and function anti-patterns, refer to\n the `bigquery-optimization` skill.\n\n## 7. Telemetry & Data Retrieval Reference\n\nIf the `bigquery-observability` skill is available in your workspace, refer to\nits shared telemetry references for canonical query templates and schema\ndictionaries. All essential table design workflows, DDL templates, and\nremediation levers are self-contained within this guide.\n\n* **Table Partitioning, Clustering & Filter Enforcement Audit:** See the\n `bigquery-observability` skill (`references/storage_footprints.md`, section\n \"Table Partitioning, Clustering & Filter Enforcement Audit\") to query\n `INFORMATION_SCHEMA.COLUMNS` and `INFORMATION_SCHEMA.TABLE_OPTIONS` for\n unpartitioned tables, partitioning column types, clustering columns, and\n `require_partition_filter`.\n* **High-Scan Referenced Tables Discovery:** See the `bigquery-observability`\n skill (`references/compute_ondemand_billable.md`, section \"CORE RULE: The\n Golden Base CTE (bytes_billed_cte)\") to group\n `INFORMATION_SCHEMA.JOBS_BY_PROJECT` by\n `query_info.query_hashes.normalized_literals` for exact job-level\n `total_bytes_billed` and `total_bytes_processed` attribution, and unnest\n `referenced_tables` to discover candidate tables (do not sum job-level bytes\n across multi-table joins; cross-check candidate table size via\n `total_logical_bytes` in `INFORMATION_SCHEMA.TABLE_STORAGE` before\n recommending partitioning).\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":15714,"content_sha256":"86b503548f823ba780b61f387c7867e514c01a9d2f801dffa4daa3dbe5ed8d8f"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"BigQuery Optimization Workflow","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Attribution","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST READ","type":"text","marks":[{"type":"strong"}]},{"text":": Before executing any ","type":"text"},{"text":"gcloud","type":"text","marks":[{"type":"code_inline"}]},{"text":", REST API, or MCP command, you MUST read ","type":"text"},{"text":"Usage Attribution","type":"text","marks":[{"type":"link","attrs":{"href":"references/attribution.md","title":null}}]},{"text":". For commands executed directly in the terminal (never in generated scripts, Terraform, or user code), prefix ","type":"text"},{"text":"gcloud","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"CLOUDSDK_METRICS_REQUEST_ATTRIBUTION=\"agent-skills/1.0 (skill:bigquery-optimization)\"","type":"text","marks":[{"type":"code_inline"}]},{"text":" and append ","type":"text"},{"text":"agent-skills/1.0 (skill:bigquery-optimization)","type":"text","marks":[{"type":"code_inline"}]},{"text":" to ","type":"text"},{"text":"User-Agent","type":"text","marks":[{"type":"code_inline"}]},{"text":" headers.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Prerequisites & Environment Setup","type":"text"}]},{"type":"paragraph","content":[{"text":"Before executing optimization analyses, evaluating editions, or applying DDL modifications:","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Google Cloud SDK","type":"text","marks":[{"type":"strong"}]},{"text":": Ensure the ","type":"text"},{"text":"Google Cloud SDK","type":"text","marks":[{"type":"link","attrs":{"href":"https://cloud.google.com/sdk/docs/install","title":null}}]},{"text":" is installed and configured.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Project Selection","type":"text","marks":[{"type":"strong"}]},{"text":": Set the active Google Cloud project:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"gcloud config set project {project_id}","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"API Enablement","type":"text","marks":[{"type":"strong"}]},{"text":": Ensure BigQuery and BigQuery Reservation APIs are enabled:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"gcloud services enable \\\n bigquery.googleapis.com bigqueryreservation.googleapis.com","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Authentication","type":"text","marks":[{"type":"strong"}]},{"text":": Authenticate the environment:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CLI tools and ","type":"text"},{"text":"bq","type":"text","marks":[{"type":"code_inline"}]},{"text":" commands: ","type":"text"},{"text":"gcloud auth login","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SDKs and automation: ","type":"text"},{"text":"gcloud auth application-default login","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Service accounts: Set ","type":"text"},{"text":"GOOGLE_APPLICATION_CREDENTIALS=\"/path/to/key.json\"","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Billing & IAM Roles","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Verify an active Google Cloud Billing account is attached to ","type":"text"},{"text":"{project_id}","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ensure appropriate IAM roles:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"roles/bigquery.admin","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"roles/bigquery.resourceAdmin","type":"text","marks":[{"type":"code_inline"}]},{"text":": Reservation and capacity commitment management.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"roles/bigquery.dataEditor","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"roles/bigquery.admin","type":"text","marks":[{"type":"code_inline"}]},{"text":": Modifying table schemas, partitioning, clustering, and storage billing models.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"roles/bigquery.jobUser","type":"text","marks":[{"type":"code_inline"}]},{"text":": Running evaluation queries.","type":"text"}]}]}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Companion Skills Installation","type":"text","marks":[{"type":"strong"}]},{"text":": This skill is part of a 3-pillar operations suite (","type":"text"},{"text":"bigquery-observability","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"bigquery-optimization","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"bigquery-troubleshooting","type":"text","marks":[{"type":"code_inline"}]},{"text":"). If any companion skill is not yet installed in your environment, install the full suite:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting","type":"text"}]},{"type":"paragraph","content":[{"text":"(If ","type":"text","marks":[{"type":"em"}]},{"text":"bigquery-observability","type":"text","marks":[{"type":"code_inline"},{"type":"em"}]},{"text":" is not installed, use the self-contained baseline formulas and query templates provided directly in the reference sections below).","type":"text","marks":[{"type":"em"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Workflows","type":"text"}]},{"type":"paragraph","content":[{"text":"Determine the optimization focus of the user's request and follow the relevant workflow:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Telemetry & Observability Baseline:","type":"text","marks":[{"type":"strong"}]},{"text":" For direct raw usage telemetry, ","type":"text"},{"text":"INFORMATION_SCHEMA","type":"text","marks":[{"type":"code_inline"}]},{"text":" queries, and baseline metric calculations, consult ","type":"text"},{"text":"bigquery-observability","type":"text","marks":[{"type":"link","attrs":{"href":"../bigquery-observability","title":null}}]},{"text":" (","type":"text"},{"text":"bigquery_observability","type":"text","marks":[{"type":"code_inline"}]},{"text":"). If the ","type":"text"},{"text":"bigquery-observability","type":"text","marks":[{"type":"code_inline"}]},{"text":" companion skill is not available in the active environment, all optimization guidelines, DDL templates, and decision models across this skill and its reference guides are fully self-contained.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Capacity & Editions Modeling:","type":"text","marks":[{"type":"strong"}]},{"text":" Evaluate the cost-efficiency of migrating workloads from On-Demand to Editions, as well as rightsizing active Edition reservations, baseline commitments, and autoscaling caps.","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Instructions:","type":"text","marks":[{"type":"em"}]},{"text":" Read ","type":"text"},{"text":"references/capacity_planning_editions.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" to provide deep links to BigQuery's built-in recommendation UIs (e.g., Slot Estimator) and guide the user through UI navigation: 1. navigate to the Slot Estimator tab, 2. select 'On-Demand' as the source to analyze historical query volume, and 3. review the Cost-Optimized Recommendations and Slot Usage Chart.","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Table & Storage Optimization:","type":"text","marks":[{"type":"strong"}]},{"text":" Optimize storage costs from a billing model, physical layout, and lifecycle perspective.","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Billing Architecture:","type":"text","marks":[{"type":"em"}]},{"text":" Read ","type":"text"},{"text":"references/storage_billing_models.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for guidance on evaluating aggregate compression ratios (e.g. >2:1 threshold in US) to recommend Physical vs. Logical billing, noting that the break-even ratio depends on specific regional rates and custom enterprise contracts. When providing ","type":"text"},{"text":"TABLE_STORAGE","type":"text","marks":[{"type":"code_inline"}]},{"text":" queries, always scope with ","type":"text"},{"text":"WHERE table_schema = '{dataset_id}'","type":"text","marks":[{"type":"code_inline"}]},{"text":", use the regional dataset view, and warn that 0 rows indicates a region mismatch or lack of native tables rather than zero billable usage.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partitioning & Clustering Strategy:","type":"text","marks":[{"type":"em"}]},{"text":" Read ","type":"text"},{"text":"references/table_partitioning_clustering.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" to generate production DDL templates (CREATE TABLE, CTAS migrations for unpartitioned tables, and modifying clustering specifications), enforce pruning with ","type":"text"},{"text":"require_partition_filter = true","type":"text","marks":[{"type":"code_inline"}]},{"text":", and manage partition limits (up to 10,000 partitions/table).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Lifecycle Management:","type":"text","marks":[{"type":"em"}]},{"text":" Read ","type":"text"},{"text":"references/storage_lifecycle_management.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" to pinpoint inactive data and define precise Time-to-Live (TTL) partition expirations, dataset expirations, and Time Travel window reductions.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Acceleration Structures:","type":"text","marks":[{"type":"em"}]},{"text":" Read ","type":"text"},{"text":"references/search_indexes.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" to decide when to propose a search index for selective lookups over ","type":"text"},{"text":"STRING","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"JSON","type":"text","marks":[{"type":"code_inline"}]},{"text":" data, write the ","type":"text"},{"text":"CREATE SEARCH INDEX","type":"text","marks":[{"type":"code_inline"}]},{"text":" DDL, and tell the user how to verify index coverage and usage. Read ","type":"text"},{"text":"references/materialized_views.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" to decide when to propose a materialized view for repeated aggregations or joins over large base tables, write the ","type":"text"},{"text":"CREATE MATERIALIZED VIEW","type":"text","marks":[{"type":"code_inline"}]},{"text":" DDL, and tell the user how to verify that smart tuning uses it. Read ","type":"text"},{"text":"references/bi_engine.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" to decide when to propose BI Engine vs. Materialized Views for BI dashboard acceleration, diagnose ","type":"text"},{"text":"PARTIAL","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"DISABLED","type":"text","marks":[{"type":"code_inline"}]},{"text":" BI Engine fallback reasons (","type":"text"},{"text":"bi_engine_statistics","type":"text","marks":[{"type":"code_inline"}]},{"text":"), and combine BI Engine with materialized views that pre-join or pre-aggregate the data.","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQL Optimization:","type":"text","marks":[{"type":"strong"}]},{"text":" Optimize individual SQL queries to reduce slot-time and the amount of data read.","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Instructions:","type":"text","marks":[{"type":"em"}]},{"text":" Follow the instructions in ","type":"text"},{"text":"references/sql_optimization.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" to provide recommendations to the user on how to rewrite their SQL query to reduce slot-time and the amount of data read.","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Execution Guardrails","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Terminology & Cost Framing:","type":"text","marks":[{"type":"strong"}]},{"text":" Never promise or guarantee \"cost-reduction\" or \"reducing expenditure.\" Always frame recommendations using the terminology ","type":"text"},{"text":"\"optimizing your bill\"","type":"text","marks":[{"type":"strong"}]},{"text":" or ","type":"text"},{"text":"\"improving cost-efficiency.\"","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Explicit Scope Framing & Region Resolution:","type":"text","marks":[{"type":"strong"}]},{"text":" Always state the target ","type":"text"},{"text":"project_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"region","type":"text","marks":[{"type":"code_inline"}]},{"text":" at the very top of your response so the user immediately knows the exact scope being evaluated. Follow this 3-tier resolution hierarchy:","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Explicit Region:","type":"text","marks":[{"type":"em"}]},{"text":" Use the region specified in the user's prompt (e.g., ","type":"text"},{"text":"europe-west1","type":"text","marks":[{"type":"code_inline"}]},{"text":").","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Contextual Region:","type":"text","marks":[{"type":"em"}]},{"text":" Resolve the region from the specific dataset or resource mentioned in the context.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Unspecified Fallback:","type":"text","marks":[{"type":"em"}]},{"text":" Default to ","type":"text"},{"text":"us","type":"text","marks":[{"type":"code_inline"}]},{"text":" / ","type":"text"},{"text":"region-us","type":"text","marks":[{"type":"code_inline"}]},{"text":", explicitly state that ","type":"text"},{"text":"us","type":"text","marks":[{"type":"code_inline"}]},{"text":" was assumed as the default, and instruct the user to substitute their region if their resources reside elsewhere. ","type":"text"},{"text":"Region Formatting:","type":"text","marks":[{"type":"em"}]},{"text":" In Cloud Console deep links, use the region identifier directly (e.g., ","type":"text"},{"text":"region=us","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"region=europe-west1","type":"text","marks":[{"type":"code_inline"}]},{"text":"). In SQL queries against ","type":"text"},{"text":"INFORMATION_SCHEMA","type":"text","marks":[{"type":"code_inline"}]},{"text":", use the regional dataset qualifier (e.g., ","type":"text"},{"text":"region-us","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"region-europe-west1","type":"text","marks":[{"type":"code_inline"}]},{"text":").","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Zero-Row Result Guard:","type":"text","marks":[{"type":"strong"}]},{"text":" If querying ","type":"text"},{"text":"TABLE_STORAGE","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"WHERE table_schema = '{dataset_id}'","type":"text","marks":[{"type":"code_inline"}]},{"text":" returns 0 rows, do not proceed with an empty or zero-usage evaluation. Treat this as an indicator that the dataset may reside in a different region or have no native tables; stop and prompt the user to confirm the dataset's regional location.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Populate Concrete Parameters:","type":"text","marks":[{"type":"strong"}]},{"text":" When generating URLs and SQL queries, always substitute known ","type":"text"},{"text":"project_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"region","type":"text","marks":[{"type":"code_inline"}]},{"text":" values directly into the code and links. Never leave literal ","type":"text"},{"text":"{project_id}","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"{location}","type":"text","marks":[{"type":"code_inline"}]},{"text":" placeholders for the user to manually edit.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No Autonomous Purchasing or Financial Mutations:","type":"text","marks":[{"type":"strong"}]},{"text":" Never provide the user with executable scripts (e.g., ","type":"text"},{"text":"gcloud","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"bq","type":"text","marks":[{"type":"code_inline"}]},{"text":" shell commands like ","type":"text"},{"text":"bq update --storage_billing_model=...","type":"text","marks":[{"type":"code_inline"}]},{"text":") designed to autonomously purchase annual commitments, alter edition tier bindings, or mutate storage billing models. Always guide the user to execute commitment purchases, reservation changes, and storage billing model updates manually via the Cloud Console UI.","type":"text"}]}]}]}]},"metadata":{"date":"2026-11-02","name":"bigquery-optimization","author":"@skillopedia","source":{"stars":21790,"repo_name":"skills","origin_url":"https://github.com/google/skills/blob/HEAD/skills/cloud/bigquery-optimization/SKILL.md","repo_owner":"google","body_sha256":"c3ab16164631beeec73cf34d4bf9a6d248bd9e1a8ec0c12e6d4fa1dc8cba9c2f","cluster_key":"f0e8ee03d1ef93561308625a28638adc0886ed9623ff67e9449939d7b15beae6","clean_bundle":{"format":"clean-skill-bundle-v1","source":"google/skills/skills/cloud/bigquery-optimization/SKILL.md","text_bytes":74098,"source_commit":"dc266e019a32621845b6b2bb81084b3c9657774c","skills_sh_rank":9099,"attachment_count":9,"text_attachments":9,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"skills/cloud/bigquery-optimization/SKILL.md","import_metadata":{"date":"2026-11-02","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","metadata":{"version":"1.1.1","category":"BigDataAndAnalytics"},"import_tag":"skills-sh-top20k-2026-11","description":"Provides workflows to optimize BigQuery environments (capacity planning, editions), storage assets (partitioning, clustering, storage lifecycles, billing models), and SQL queries. Use when optimizing cost, modeling Edition migrations, rightsizing reservations, evaluating logical vs. physical storage, designing table partitioning/clustering, generating table DDL, migrating unpartitioned tables, managing partition expiration, optimizing individual SQL queries, or evaluating acceleration structures (search indexes, materialized views, BI Engine).\nDo not use for raw usage reporting (use bigquery-observability), query execution plan analysis, error troubleshooting, or diagnosing why a specific job was slow (use bigquery-troubleshooting)."}},"renderedAt":1793665932934}

BigQuery Optimization Workflow Attribution MUST READ : Before executing any , REST API, or MCP command, you MUST read Usage Attribution. For commands executed directly in the terminal (never in generated scripts, Terraform, or user code), prefix with and append to headers. Prerequisites & Environment Setup Before executing optimization analyses, evaluating editions, or applying DDL modifications: 1. Google Cloud SDK : Ensure the Google Cloud SDK is installed and configured. 2. Project Selection : Set the active Google Cloud project: 3. API Enablement : Ensure BigQuery and BigQuery Reservation…