---
title: "BigQuery ALTER TABLE: add column, rename, change type"
canonical: "https://www.owox.com/blog/articles/bigquery-modify-tables-data-definition-language"
updated: "2026-10-06"
---

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

How to change an existing BigQuery table with ALTER TABLE: add one or several columns, rename a table or a column, drop a column, change a data type, and set defaults and options. Each statement comes with the limits Google documents and the workaround when ALTER cannot do it.

[Google BigQuery](/blog/topics/bigquery) · Updated October 6, 2026 · [Alyona Samovar](/team/alyona-samovar) · 6 min read

![BigQuery ALTER TABLE: add, rename and drop columns, change data types](https://cdn.owox.ai/www/webflow/696a766a549b0f8b707bfd33_MODIFYING-TABLES-WITH-ADVANCED-DDL.png/public)

`ALTER TABLE` changes an existing BigQuery table without recreating it. This page lists the statements people look up, in the order of how often they are needed.

| To do this | Statement |
| --- | --- |
| Add a column | `ALTER TABLE t ADD COLUMN c TYPE` |
| Add several columns | `ADD COLUMN a TYPE, ADD COLUMN b TYPE` |
| Rename a table | `ALTER TABLE t RENAME TO new_name` |
| Rename a column | `ALTER TABLE t RENAME COLUMN a TO b` |
| Drop a column | `ALTER TABLE t DROP COLUMN c` |
| Widen a column’s type | `ALTER COLUMN c SET DATA TYPE NUMERIC` |
| Any other type change | recreate with `CREATE OR REPLACE TABLE` |
| Change table options | `ALTER TABLE t SET OPTIONS (...)` |

The syntax and limits are from Google’s [DDL reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language), checked on 6 October 2026. The statements were written from that reference and not executed for this article, because they change tables.

**_Note:_** _Originally published in June 2024. Fully updated in October 2026 against the current reference._

## 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.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## 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](/blog/articles/bigquery-query-history-log) 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](/blog/articles/bigquery-time-travel-guide) 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](/blog/articles/bigquery-cast-and-safe-cast) and [conversion functions](/blog/articles/bigquery-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](/blog/articles/bigquery-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](/blog/articles/bigquery-create-drop-tables-data-definition-language).

## 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](https://www.owox.com/app-signup) 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.

![Destinations tab of a Data Mart in OWOX Data Marts listing the reports built on it, each with its last run date and status – what depends on the query is visible next to it](https://cdn.owox.ai/www/content-marketing-article/bigquery-modify-tables-data-definition-language/screens/1.png/public)

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How do I change the data type of a column in BigQuery?

ALTER TABLE t ALTER COLUMN c SET DATA TYPE works only towards a less restrictive type: INT64 to NUMERIC, BIGNUMERIC or FLOAT64, NUMERIC to BIGNUMERIC or FLOAT64, or a wider parameterized type. For any other change, recreate the table with CREATE OR REPLACE TABLE ... AS SELECT and a cast.

When do I have to recreate a BigQuery table instead of altering it?

To change a type in a way SET DATA TYPE does not allow, to add NOT NULL to a column, to add or change partitioning or clustering, or to reorder columns. Restate the partitioning and options in the CREATE OR REPLACE statement, because they are not carried over.

How do I rename a table in BigQuery?

ALTER TABLE mydataset.orders RENAME TO orders\_2026. The new name is the table name only, not a full path. The renamed table keeps its original creation time, so a dataset-level table expiration can delete it immediately if it is older than the window.

How do I add a column to an existing BigQuery table?

ALTER TABLE mydataset.orders ADD COLUMN IF NOT EXISTS discount NUMERIC. Existing rows get NULL. To add several, repeat ADD COLUMN separated by commas in one statement. You cannot add a REQUIRED column, a partitioning column or a clustering column this way.

Does dropping a column in BigQuery free storage?

Not immediately. DROP COLUMN is a metadata change, and the storage is released the next time the table is rewritten, with no guaranteed window. To reclaim it at once, overwrite the table with CREATE OR REPLACE TABLE ... AS SELECT \* EXCEPT (column).

How do I rename a column in BigQuery?

ALTER TABLE mydataset.orders RENAME COLUMN amount TO amount\_usd. It is not supported on external tables or tables with active row-level access policies, and primary key columns cannot be renamed. Queries that use the old name will fail afterwards.

## Who wrote this

![Alyona Samovar](https://cdn.owox.ai/www/webflow/6799099d337aa8a997b03b56_Alyona-Samovar.png/public)

[Alyona Samovar](/team/alyona-samovar) · Senior Digital Analyst

Alyona Samovar is a Senior Digital Analyst who spent years at OWOX helping enterprise clients design analytics systems, build BigQuery reporting pipelines, and optimize marketing measurement. She specializes in SQL-based analytics, data modeling, and turning raw marketing data into actionable insights. Alyona writes about practical analytics workflows, BigQuery best practices, and data-driven decision-making.

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · BigQuery date functions: syntax, examples and common errors · October 6, 2026](/blog/articles/bigquery-date-functions)

[Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](/blog/articles/bigquery-data-manipulation-language)

[Google BigQuery · How to connect BigQuery to Google Sheets: three ways · October 6, 2026](/blog/articles/bigquery-reports-in-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Alyona Samovar](https://www.owox.com/team/alyona-samovar) — /team/alyona-samovar.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [query history](https://www.owox.com/blog/articles/bigquery-query-history-log) — /blog/articles/bigquery-query-history-log.md
- [time travel](https://www.owox.com/blog/articles/bigquery-time-travel-guide) — /blog/articles/bigquery-time-travel-guide.md
- [CAST and SAFE_CAST](https://www.owox.com/blog/articles/bigquery-cast-and-safe-cast) — /blog/articles/bigquery-cast-and-safe-cast.md
- [conversion functions](https://www.owox.com/blog/articles/bigquery-conversion-functions) — /blog/articles/bigquery-conversion-functions.md
- [partitioned tables](https://www.owox.com/blog/articles/bigquery-partitioned-tables) — /blog/articles/bigquery-partitioned-tables.md
- [creating and deleting BigQuery tables](https://www.owox.com/blog/articles/bigquery-create-drop-tables-data-definition-language) — /blog/articles/bigquery-create-drop-tables-data-definition-language.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [Google BigQuery · BigQuery date functions: syntax, examples and common errors · October 6, 2026](https://www.owox.com/blog/articles/bigquery-date-functions) — /blog/articles/bigquery-date-functions.md
- [Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](https://www.owox.com/blog/articles/bigquery-data-manipulation-language) — /blog/articles/bigquery-data-manipulation-language.md
- [Google BigQuery · How to connect BigQuery to Google Sheets: three ways · October 6, 2026](https://www.owox.com/blog/articles/bigquery-reports-in-google-sheets) — /blog/articles/bigquery-reports-in-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md