CREATE TABLE, ALTER and DROP in BigQuery

BigQuery DDL runs as a query job. CREATE TABLE AS SELECT is billed like the SELECT, LIKE and COPY are free, CREATE OR REPLACE drops the data, and IF NOT EXISTS keeps an old schema without telling you.

DDL statements create, change and drop datasets, tables, views, functions and other objects. In BigQuery they are ordinary query jobs: you run them from the editor, bq query or the API, they show up in job history, and a statement that reads data, like CREATE TABLE ... AS SELECT, is billed for what it reads. The forms that only copy metadata or storage are free.

The runnable examples use temporary tables in a script. The statements that need a dataset of your own are shown as plain SQL.

Creating a table

The four ways to create a table differ in what they copy and what they cost:

-- An empty table with an explicit schema
CREATE TABLE mydataset.orders (
  order_id STRING NOT NULL,
  ordered_at TIMESTAMP,
  amount NUMERIC,
  lines ARRAY<STRUCT<sku STRING, qty INT64>>
)
PARTITION BY DATE(ordered_at)
CLUSTER BY order_id
OPTIONS (description = 'One row per order');

-- From a query: schema and data come from the SELECT
CREATE TABLE mydataset.big_orders AS
SELECT * FROM mydataset.orders WHERE amount > 1000;

-- Same schema (and partitioning, clustering), no data
CREATE TABLE mydataset.orders_empty LIKE mydataset.orders;

-- Same schema and data, as a storage copy
CREATE TABLE mydataset.orders_backup COPY mydataset.orders;

In Querylab.io, right-click a table, dataset or project and choose SQL Templates to get CREATE TABLE, COPY, CLONE, ALTER TABLE ... SET OPTIONS and DROP statements filled in from the real schema. They open in a new tab and run only when you press Run. See DDL operations.

COPY is a copy job under the hood, so it costs nothing to run, and the new table is then stored and billed like any other. For a copy that only pays for the rows that later change, use a clone. See Snapshots and clones.

CREATE TABLE ... AS SELECT (CTAS) is the usual way to materialize a query result. Partitioning and clustering go between the table name and AS:

CREATE TEMP TABLE big_orders AS
SELECT id, amount
FROM UNNEST([STRUCT(1 AS id, 250 AS amount), (2, 40), (3, 900)])
WHERE amount > 100;
SELECT * FROM big_orders ORDER BY id

OR REPLACE vs IF NOT EXISTS

Creating a table that already exists is an error: “Already Exists: Table …”. Two modifiers change that, in opposite ways.

CREATE OR REPLACE TABLE drops the existing table and creates a new one. The data is gone, even if the new definition looks the same:

CREATE TEMP TABLE orders (id INT64);
INSERT INTO orders VALUES (1), (2);
CREATE OR REPLACE TEMP TABLE orders (id INT64, amount NUMERIC);
SELECT COUNT(*) AS rows_left FROM orders

On a permanent table the old data can still be recovered with time travel within the dataset’s time travel window, seven days by default.

CREATE TABLE IF NOT EXISTS does nothing if the table exists, even if your new definition is different. The second statement below asks for an amount column, but the table keeps its old schema without an error:

CREATE TEMP TABLE IF NOT EXISTS orders (id INT64);
CREATE TEMP TABLE IF NOT EXISTS orders (id INT64, amount NUMERIC);
SELECT * FROM orders

So IF NOT EXISTS is fine for “create once” setup scripts, and wrong for deploying a schema change. Use ALTER TABLE for that.

NOT NULL

A column declared NOT NULL (mode REQUIRED) rejects NULLs on insert:

CREATE TEMP TABLE customers (id INT64 NOT NULL, email STRING);
INSERT INTO customers VALUES (NULL, '[email protected]');
SELECT * FROM customers

NOT NULL is the only constraint BigQuery enforces. Primary and foreign keys can be declared but are NOT ENFORCED. See Primary and foreign keys. You can relax NOT NULL later with ALTER COLUMN ... DROP NOT NULL, but not add it to an existing column.

ALTER TABLE

The most common changes:

ALTER TABLE mydataset.orders ADD COLUMN IF NOT EXISTS coupon STRING;
ALTER TABLE mydataset.orders RENAME COLUMN coupon TO promo_code;
ALTER TABLE mydataset.orders DROP COLUMN IF EXISTS promo_code;
ALTER TABLE mydataset.orders ALTER COLUMN amount SET DATA TYPE BIGNUMERIC;
ALTER TABLE mydataset.orders ALTER COLUMN order_id DROP NOT NULL;
ALTER TABLE mydataset.orders SET OPTIONS (expiration_timestamp = TIMESTAMP '2027-01-01');
ALTER TABLE mydataset.orders RENAME TO orders_2026;

These change metadata only and process no bytes. What can and can’t be changed in place (type changes are limited to widening, nested fields can’t be added with SQL, partitioning can’t be changed at all) is in Schema changes. To change partitioning or clustering, create a new table with CTAS and swap the names.

DROP

DROP TABLE IF EXISTS mydataset.orders_backup;
DROP SCHEMA mydataset CASCADE;  -- a dataset and everything in it

DROP SCHEMA without CASCADE fails if the dataset isn’t empty. A dropped table can be restored within the time travel window, and a dropped dataset brought back with UNDROP SCHEMA mydataset within the same window. See Time travel.

Datasets

In BigQuery SQL a dataset is a SCHEMA. Its location is fixed at creation:

CREATE SCHEMA IF NOT EXISTS mydataset
OPTIONS (
  location = 'EU',
  default_table_expiration_days = 30,
  description = 'Scratch tables'
);

See Locations and Table expiration.

Limits

DDL counts toward the table metadata limit: at most 5 metadata updates per table every 10 seconds (50 for a partitioned table), shared with load, copy and query jobs that write to the table. Scripts that ALTER the same table in a tight loop hit “Exceeded rate limits: too many table update operations for this table”. The error is temporary; retry with backoff.