# Schema changes in BigQuery tables

> What ALTER TABLE can change in a BigQuery table: add columns at the end, relax REQUIRED to NULLABLE, widen a few types, rename and drop columns. What it can't: add REQUIRED columns, add nested fields with SQL, narrow a type, or reorder.

Source: https://docs.querylab.io/bigquery/schema-changes/

Updated: 2026-10-14

BigQuery changes a schema without rewriting data, so the changes it allows are the ones that need no rewrite: add a nullable column at the end, loosen a rule, widen a type, rename or drop a column. Anything that would need existing rows to change or move, such as a new `REQUIRED` column, a narrower type or a different column order, is refused, and you rebuild the table with `CREATE OR REPLACE TABLE ... AS SELECT`. The DDL statements are free.

The examples here run on temporary tables in a script. `ALTER TABLE` works on them the same way as on stored tables.

## Add a column

New columns are `NULLABLE` or `REPEATED`, and always go at the end. Existing rows get `NULL`, or an empty array for a repeated column:

**adding a nullable and a repeated column**

```sql
CREATE TEMP TABLE orders (order_id INT64, status STRING);
INSERT INTO orders VALUES (1, 'paid'), (2, 'open');

ALTER TABLE orders
  ADD COLUMN coupon STRING,
  ADD COLUMN tags ARRAY<STRING>;

SELECT order_id, coupon, tags, ARRAY_LENGTH(tags) AS tag_count
FROM orders
ORDER BY order_id
```

Result in BigQuery (2 rows):

| order_id | coupon | tags | tag_count |
| --- | --- | --- | --- |
| 1 | NULL | [] | 0 |
| 2 | NULL | [] | 0 |

`ADD COLUMN IF NOT EXISTS` skips a column that is already there. You can add several columns in one statement.

### REQUIRED columns can't be added

A `REQUIRED` column would need a value for every existing row, so BigQuery refuses:

**ADD COLUMN ... NOT NULL**

```sql
CREATE TEMP TABLE orders (order_id INT64, status STRING);
INSERT INTO orders VALUES (1, 'paid');

ALTER TABLE orders ADD COLUMN coupon STRING NOT NULL
```

BigQuery rejects this query:

```text
Query error: Cannot add required fields to an existing schema. (field: coupon) at [4:1]
```

`REQUIRED` columns can only be created together with the table. The workaround is a nullable column that you fill with an `UPDATE`, or a new table.

### Nested fields can't be added with SQL

`ALTER TABLE ... ADD COLUMN shipping.country STRING` is a syntax error, because the statement has no way to name a field inside a `STRUCT`. A new field in an existing `STRUCT` or `RECORD` is added by sending the whole schema: edit the JSON schema file and run `bq update`, or call `tables.patch`. Google's guide says the console can't do it either. In Querylab.io, **Copy schema as JSON** on a table's Schema tab copies the whole schema in BigQuery's JSON format ([table information](https://docs.querylab.io/schema-browser/table-information/#schema)).

```sh
bq show --schema --format=prettyjson mydataset.orders > schema.json
# add {"name": "country", "type": "STRING", "mode": "NULLABLE"} to the shipping fields
bq update mydataset.orders schema.json
```

In our test this added `country` to a struct holding `city` and `zip`, and existing rows read `{"city":"Oslo","zip":"0150","country":null}`. The new field is `NULLABLE` or `REPEATED` like any added column.

The same file can't remove a nested field. Leaving `zip` out of the schema failed with `Field shipping.zip is missing in new schema`. A field inside a struct can't be dropped or renamed in place at all. Rebuild the column: see [STRUCT](https://docs.querylab.io/bigquery/structs/).

### Column order

You can't insert a column in the middle. We put a new field second in a schema file and ran `bq update`, and the column still ended up last (`ordinal_position` 11 of 11). Google's guide says this case returns `Precondition Failed`. Our test returned success and appended it. Either way, the position you ask for is not what you get. To reorder columns, create a new table.

## Relax REQUIRED to NULLABLE

Going from `REQUIRED` to `NULLABLE` is the only mode change BigQuery allows. It loosens the rule, so every existing row still fits:

**DROP NOT NULL, then insert a NULL**

```sql
CREATE TEMP TABLE customers (customer_id INT64 NOT NULL, email STRING NOT NULL);
INSERT INTO customers VALUES (1, 'ana@example.com');

ALTER TABLE customers ALTER COLUMN email DROP NOT NULL;
INSERT INTO customers VALUES (2, NULL);

SELECT customer_id, email
FROM customers
ORDER BY customer_id
```

Result in BigQuery (2 rows):

| customer_id | email |
| --- | --- |
| 1 | ana@example.com |
| 2 | NULL |

Before the change the same insert fails:

**inserting NULL into a REQUIRED column**

```sql
CREATE TEMP TABLE customers (customer_id INT64 NOT NULL, email STRING NOT NULL);

INSERT INTO customers VALUES (2, NULL)
```

BigQuery rejects this query:

```text
Query error: Required field email cannot be null at [3:1]
```

There is no `SET NOT NULL`. `ALTER COLUMN order_id SET NOT NULL` returns a syntax error: after `SET` the parser expects `DATA`, `DEFAULT`, `GENERATED` or `OPTIONS`. `NULLABLE` to `REQUIRED` and `REPEATED` to `NULLABLE` aren't supported. Rebuild the table. `DROP NOT NULL` on a column that isn't `NOT NULL` is an error, not a no-op.

## Change a column's type

`ALTER COLUMN ... SET DATA TYPE` only goes to a less restrictive type. The supported conversions are:

| From             | To                                 |
| ---------------- | ---------------------------------- |
| `INT64`          | `NUMERIC`, `BIGNUMERIC`, `FLOAT64` |
| `NUMERIC`        | `BIGNUMERIC`, `FLOAT64`            |
| `STRING(5)`      | `STRING(7)`, `STRING`              |
| `NUMERIC(10, 6)` | `NUMERIC(12, 8)`                   |

The last two rows are parameterized types, where a bigger length, precision or scale is a widening. `NUMERIC` to `BIGNUMERIC(40, 20)` is allowed too.

**INT64 to NUMERIC, then a decimal value**

```sql
CREATE TEMP TABLE payments (payment_id INT64, amount INT64);
INSERT INTO payments VALUES (1, 40), (2, 15);

ALTER TABLE payments ALTER COLUMN amount SET DATA TYPE NUMERIC;
INSERT INTO payments VALUES (3, NUMERIC '12.34');

SELECT payment_id, amount
FROM payments
ORDER BY payment_id
```

Result in BigQuery (3 rows):

| payment_id | amount |
| --- | --- |
| 1 | 40 |
| 2 | 15 |
| 3 | 12.34 |

Going back is refused:

**NUMERIC back to INT64**

```sql
CREATE TEMP TABLE payments (payment_id INT64, amount INT64);

ALTER TABLE payments ALTER COLUMN amount SET DATA TYPE NUMERIC;
ALTER TABLE payments ALTER COLUMN amount SET DATA TYPE INT64
```

BigQuery rejects this query:

```text
Query error: ALTER TABLE ALTER COLUMN SET DATA TYPE requires that the existing column type (NUMERIC) is assignable to the new type (INT64) at [4:22]
```

We tried the other pairs on a test table. These all failed with the same "is assignable to the new type" error: `BIGNUMERIC` to `NUMERIC`, `FLOAT64` to `NUMERIC`, `STRING` to `INT64`, `DATE` to `TIMESTAMP`, `BOOL` to `INT64`. `STRING` to `STRING(3)` failed with `Narrowing type parameters is not compatible`, and `DATE` to `DATETIME` with `Field d has changed type from DATE to DATETIME`.

Two cases the guide leaves unclear:

- A field inside a struct can be widened by restating the struct: `SET DATA TYPE STRUCT<a NUMERIC, b STRING>` changed `a` from `INT64` to `NUMERIC`. The reference page also says "Modifying subfields is not supported", which means you can't name the subfield on its own.
- An array of a widenable type isn't widenable. `ARRAY<INT64>` to `ARRAY<NUMERIC>` failed, even though `INT64` to `NUMERIC` works. Fields inside an array of structs aren't supported either, and Google's workaround is `CREATE OR REPLACE TABLE ... AS SELECT` with an `UNNEST` over the array.

For any other change, cast and rewrite the table:

```sql
CREATE OR REPLACE TABLE mydataset.orders AS
SELECT * REPLACE (CAST(order_date AS STRING) AS order_date)
FROM mydataset.orders;
```

This scans the whole table, so it costs the same as reading it, and it isn't a good choice for very large tables.

## Rename a column

```sql
ALTER TABLE mydataset.orders RENAME COLUMN amount TO total;
```

Several renames can go in one statement and are applied in order, so a swap is possible with a temporary name:

**swapping two column names**

```sql
CREATE TEMP TABLE route (origin STRING, destination STRING);
INSERT INTO route VALUES ('Oslo', 'Bergen');

ALTER TABLE route
  RENAME COLUMN origin TO temp_col,
  RENAME COLUMN destination TO origin,
  RENAME COLUMN temp_col TO destination;

SELECT origin, destination FROM route
```

Result in BigQuery (1 row):

| origin | destination |
| --- | --- |
| Bergen | Oslo |

The statement has restrictions:

- It can't be combined with other `ALTER TABLE` actions:

  **RENAME COLUMN together with ADD COLUMN**

```sql
CREATE TEMP TABLE orders (order_id INT64, amount INT64);

ALTER TABLE orders
  RENAME COLUMN amount TO total,
  ADD COLUMN coupon STRING
```

BigQuery rejects this query:

```text
Query error: ALTER TABLE does not support combining ADD COLUMN with RENAME COLUMN at [5:3]
```

- It can't rename a partitioning column, a clustering column, a key column, a nested field, or any column of a table with row access policies. On a table partitioned by `d` and clustered by `status`, both renames failed with `Columns used in table partitioning or clustering cannot be renamed.`
- After a rename, the table can't be queried with legacy SQL or as a wildcard table. Renaming back to the original names lifts that.

The main trap is views. A rename does not touch views, materialized views or saved queries that use the column. A view over `order_id, amount` kept working until `amount` was renamed, then the next `SELECT * FROM the_view` failed with:

```text
Unrecognized name: amount; failed to parse view 'orders_view'
```

The error comes from the view when it is queried, not from the `ALTER`. Recreate every dependent view after a rename.

## Drop a column

```sql
ALTER TABLE mydataset.orders DROP COLUMN internal_note;
```

Dropping is a metadata change. It doesn't free the storage, and it doesn't make the table smaller. We dropped the only big column of a 300,000-row table (41.4 MB logical). The table still reported 41,400,000 bytes afterwards. After a `DELETE` of one row, which writes the table, it reported 2,399,992. Google says the space comes back the next time the table is written to or when a background optimization runs, with no guaranteed time. To free it at once, rewrite the table:

```sql
CREATE OR REPLACE TABLE mydataset.orders AS
SELECT * EXCEPT (internal_note) FROM mydataset.orders;
```

A dropped column comes back with [time travel](https://docs.querylab.io/bigquery/time-travel/): a read from before the drop returns the old schema and the old values, even after a new column with the same name is added. In Querylab.io the **TT** picker on a table's Preview tab shows the table as it was within the time travel window ([time travel](https://docs.querylab.io/schema-browser/time-travel/#in-the-preview-tab)). You can't drop a partitioning or clustering column, a key column, a nested field, or a column of a table with row access policies. A table whose columns were dropped can't be queried with legacy SQL or as a wildcard table, as with renames.

## Add columns from load and query jobs

A load job that appends to a table can add columns, and relax `REQUIRED` columns, through `schemaUpdateOptions` (`ALLOW_FIELD_ADDITION`, `ALLOW_FIELD_RELAXATION`). A query job that appends to a destination table can do the same. This is how a schema evolves during ingestion with no `ALTER`. New columns are `NULL` in the old rows. See [Loading data](https://docs.querylab.io/bigquery/loading-data/).

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Modifying table schemas](https://cloud.google.com/bigquery/docs/managing-table-schemas)

- [Data definition language statements](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language)

- [Access historical data](https://cloud.google.com/bigquery/docs/access-historical-data)