BigQuery ALTER TABLE: add, rename and drop columns, change data types

Add a column

ALTER TABLE mydataset.orders
ADD COLUMN discount NUMERIC;

Existing rows get NULL in the new column. To make the statement safe to run twice, add IF NOT EXISTS. Without it, a column that already exists is an error; with it, nothing happens:

ALTER TABLE mydataset.orders
ADD COLUMN IF NOT EXISTS discount NUMERIC;

Add multiple columns

Repeat ADD COLUMN for each, separated by commas, in one statement:

ALTER TABLE mydataset.orders
ADD COLUMN IF NOT EXISTS discount NUMERIC,
ADD COLUMN IF NOT EXISTS coupon STRING,
ADD COLUMN IF NOT EXISTS tags ARRAY<STRING>;

An array column is empty, not NULL, for existing rows.

What ADD COLUMN cannot do

• Add a REQUIRED (NOT NULL) column to an existing table.
• Add a partitioning or clustering column.
• Add a nested field inside an existing RECORD.
• Work on an external table.

A new column is always added at the end. BigQuery has no AFTER clause; if the order matters for a reader, select the columns in that order in a view.

Rename a table

ALTER TABLE mydataset.orders
RENAME TO orders_2026;

The new name is only the table name, not a full path, and it must not already exist. Two things from the reference that are easy to miss:

• The renamed table keeps its original creation time. If the dataset has a default table expiration, a table older than that window can be deleted right after the rename.
• Renaming removes Data Catalog tags and Knowledge Catalog aspects attached to the table and its columns.

To move a table to another dataset, a rename does not do it. Copy the table, then drop the original.

Rename a column

ALTER TABLE mydataset.orders
RENAME COLUMN amount TO amount_usd;

Several can be renamed in one statement, separated by commas. Limits: it is not supported on external tables, it fails if the table has active row-level access policies, and primary key columns cannot be renamed.

Renaming a column breaks every query that uses the old name, and BigQuery will not tell you which ones. Search the query history for the column name first to see what reads it.

Drop a column

ALTER TABLE mydataset.orders
DROP COLUMN IF EXISTS coupon;

Dropping is a metadata change. The storage is not freed immediately: it is released the next time the table is rewritten, and Google gives no guaranteed window. To reclaim it at once, overwrite the table:

CREATE OR REPLACE TABLE mydataset.orders AS
SELECT * EXCEPT (coupon)
FROM mydataset.orders;

You cannot drop a partitioning column, a clustering column, or a column that is part of a primary or foreign key. A column dropped by mistake can be recovered with time travel while the window lasts.

Change a column’s data type

ALTER COLUMN SET DATA TYPE only goes from a more restrictive type to a less restrictive one:

ALTER TABLE mydataset.orders
ALTER COLUMN amount SET DATA TYPE NUMERIC;

The conversions the reference lists:

• INT64 to NUMERIC, BIGNUMERIC or FLOAT64
• NUMERIC to BIGNUMERIC or FLOAT64
• a parameterized type to a wider one, for example STRING(5) to STRING(7), or NUMERIC(10, 6) to NUMERIC(12, 8)

Everything else is not possible in place: STRING to INT64, STRING to DATE, FLOAT64 to INT64. For those, rebuild the column with a cast:

CREATE OR REPLACE TABLE mydataset.orders AS
SELECT
  * EXCEPT (order_date),
  SAFE.PARSE_DATE('%d/%m/%Y', order_date)
    AS order_date
FROM mydataset.orders;

Two cautions. CREATE OR REPLACE ... AS SELECT does not carry over partitioning, clustering or table options unless you write them again in the statement. And with SAFE., values that fail to convert become NULL, so count them before you replace the table. The conversion functions are in CAST and SAFE_CAST and conversion functions.

If data is streaming into the table, the reference says to stop the stream first; a type change is usually possible about five hours after the last streaming operation.

NOT NULL and defaults

Remove a NOT NULL constraint:

ALTER TABLE mydataset.orders
ALTER COLUMN customer DROP NOT NULL;

There is no statement to add NOT NULL to an existing column. That again means recreating the table.

Set or remove a default for rows inserted from now on:

ALTER TABLE mydataset.orders
ALTER COLUMN created_at
SET DEFAULT CURRENT_TIMESTAMP();

ALTER TABLE mydataset.orders
ALTER COLUMN created_at DROP DEFAULT;

A default applies to new rows only. Existing rows keep what they have.

Change table and column options

Descriptions, expiration and labels are options:

ALTER TABLE mydataset.orders
SET OPTIONS (
  description = 'One row per order, paid and refunded',
  expiration_timestamp =
    TIMESTAMP '2027-01-01 00:00:00 UTC',
  labels = [('team', 'analytics')]
);

ALTER TABLE mydataset.orders
ALTER COLUMN amount_usd
SET OPTIONS (description = 'Order total in USD, tax included');

Column descriptions are the cheapest documentation a table can have, and they show up in the schema tab for everyone who opens it. For partition options such as expiration and required filters, see partitioned tables.

When ALTER TABLE is not enough

Recreate the table when you need to:

• change a type in a way SET DATA TYPE does not allow
• add NOT NULL to a column
• add or change partitioning or clustering
• reorder columns

The pattern is always CREATE OR REPLACE TABLE ... AS SELECT ..., with the partitioning and options restated. It rewrites the whole table and is billed as a full scan. Creating and dropping tables is covered in creating and deleting BigQuery tables.

Before you change a table other people read

A schema change is quick to run and slow to recover from. The statement succeeds, and the failures show up later in someone else’s dashboard, as an error or, worse, as a column of NULL.

Three checks cost a few minutes: search the query history for the column or table name, look at which scheduled queries and reports reference it, and tell the owners. The third is hard when nobody knows who they are.

That is one of the things a governed layer is for. In OWOX Data Marts each Data Mart is your SQL with an owner and a description, and the reports built on it are listed with it. So when a table changes, the place to check is a short list of queries with named owners, not every dashboard in the company. OWOX does not alter your tables or migrate schemas. It makes it clear whose query sits between the table and the report.

Google BigQueryBigQuery date functions: syntax, examples and common errorsOctober 6, 2026→Google BigQueryBigQuery DML Commands: A Complete Guide for Data AnalystsMarch 14, 2024→Google BigQueryHow to connect BigQuery to Google Sheets: three waysOctober 6, 2026→

See all articles →