Schema BrowserTemplates

DDL operations

Every create, alter, copy or drop you can run from the tree starts as generated SQL in a new tab — filled in from the entity's real schema, and never executed until you press Run.

Generate Template

Table dim_fuel_type

Search templates…

Use arrow keys to navigate, Enter to select, Escape to close

SQL Templates on a table. Categories collapse; the search box filters across all of them.

Using a template

Right-click a table, dataset or project and choose SQL Templates. Pick a template with the mouse or arrow keys; Enter opens the generated statement in a new query tab named after it. Nothing runs until you press Run Query — read it, edit the parts marked as placeholders, then run.

The generator uses what the tree already knows: column names and types come from the fetched schema, partitioning and clustering from the table’s metadata, so a CREATE TABLE reproduces the table exactly.

Table templates

Category	Template	Generates
Query	SELECT *	`SELECT * FROM …`
Query	SELECT all columns	Every column listed explicitly — nested fields included
Query	SELECT all columns (unnested)	The same, with `UNNEST` joins for ARRAY columns
DDL	CREATE TABLE	Full schema with modes, descriptions, partitioning, clustering and options
DDL	INSERT INTO	`INSERT INTO … (columns) SELECT …` with the column list filled in
DDL	COPY TABLE	`CREATE TABLE … COPY …` — duplicates data
DDL	CLONE TABLE	`CREATE TABLE … CLONE …` — metadata only
DDL	TRUNCATE TABLE	`TRUNCATE TABLE …`
DDL	ALTER TABLE OPTIONS	`ALTER TABLE … SET OPTIONS (…)` with the current description, labels and expiration
DDL	RENAME TABLE	`ALTER TABLE … RENAME TO …`
DDL	DROP TABLE	`DROP TABLE IF EXISTS …` (or `DROP VIEW` for a view)
Share	Share Table	`GRANT` on the table to a principal
Data Transfer	Export to GCS	`EXPORT DATA OPTIONS (uri = 'gs://…')`
Data Transfer	Load from GCS	`LOAD DATA INTO … FROM FILES (…)`

COPY vs CLONE

1234567

-- COPY: writes every byte again. Billed as a query, takes as long as the table is big.
CREATE TABLE `ace-analytics.warehouse.orders_copy`
COPY `ace-analytics.warehouse.orders`;

-- CLONE: copy-on-write. Free and instant; you pay only for rows that later diverge.
CREATE TABLE `ace-analytics.warehouse.orders_clone`
CLONE `ace-analytics.warehouse.orders`;

Clone for backups, staging and experiments. Copy when the result must be independent of the source — a clone of a table that is later dropped keeps working, but a clone’s storage bill grows as the source changes underneath it.

A clone can also be taken at a point in the past with FOR SYSTEM_TIME AS OF, and given an expiry so it cleans itself up:

1234

CREATE TABLE `ace-analytics.warehouse.orders_backup_20260828`
CLONE `ace-analytics.warehouse.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 30 DAY))

ALTER TABLE OPTIONS

The template pre-fills the table’s current values so you change one thing and leave the rest intact:

1234567

ALTER TABLE `ace-analytics.warehouse.orders`
SET OPTIONS (
description = 'Orders fact table, one row per line item',
labels = [('domain', 'sales'), ('layer', 'fact')],
expiration_timestamp = NULL,
require_partition_filter = TRUE
)

Dataset templates

Right-click a dataset → SQL Templates. The Create category holds one template per object type you can create inside it — Table, View, Procedure, SQL Function, JavaScript Function, Python Function, Remote Function, Aggregate SQL Function, Aggregate JS Function, Table Function — each a commented skeleton with the dataset filled in and the options BigQuery accepts for that type.

The rest mirror the table list at dataset level: CREATE SCHEMA and DROP SCHEMA (DDL), ALTER SCHEMA OPTIONS and ALTER SCHEMA COLLATE (Alter), Share Dataset, Export to GCS and Load from GCS. An Information Schema category lists the INFORMATION_SCHEMA views for the dataset — TABLES, COLUMNS, COLUMN_FIELD_PATHS, PARTITIONS, TABLE_OPTIONS, TABLE_CONSTRAINTS, VIEWS, MATERIALIZED_VIEWS, ROUTINES and the rest — each as a ready SELECT.

Project templates

Right-click a project → SQL Templates for a CREATE SCHEMA template and the Information Schema Recipes — ten cost and hygiene queries over region-…INFORMATION_SCHEMA, written for the project’s region:

Recipe	Answers
Unused Tables	Which tables no query has touched in N days, and what their storage costs
Top Expensive Queries	The most expensive queries by bytes billed, with on-demand cost
Slots vs On-Demand Cost	What your workload would cost on each edition versus on-demand
Storage Analysis	Large and stale tables with time travel, fail-safe and cost breakdown
Cost by User	Query cost grouped by user email
Slot Utilization Timeline	Allocated vs used capacity from the reservation timeline
Failed Queries	Recent failures with error text
Query Source Distribution	Which tools run queries — dbt, Looker, Dataform, ad hoc
Top Expensive Tables	Tables ranked by query plus storage cost
Partitioning & Clustering Audit	Tables that are scanned a lot and could be partitioned or clustered

CREATE Dataset in the same menu skips the picker and copies a CREATE SCHEMA statement to the clipboard.

Many tables at once

To copy, clone, move, drop or truncate every table matching a name pattern in one script, right-click a project, dataset or table and choose Bulk Generate. See Bulk operations.

What permissions you need

Generated DDL runs as you. Creating needs bigquery.tables.create on the dataset, altering bigquery.tables.update, dropping bigquery.tables.delete; GRANT statements need bigquery.datasets.update (or the table-level equivalent). A missing permission surfaces as a dry-run error in the status bar before anything runs.