How to Change Column Type in BigQuery

How do I alter a column’s data type in BigQuery?

Use ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_type. BigQuery only permits type changes it deems loss-free, such as FLOAT64 → NUMERIC or widening a STRING to allow more characters.

What is the exact syntax?

The concise pattern is:

ALTER TABLE `project.dataset.table`
ALTER COLUMN column_name SET DATA TYPE new_type [COLLATE collation_name];

You can chain multiple ALTER COLUMN clauses in one statement.

Practical example: change total_amount to NUMERIC

The Orders table stores money as FLOAT64. Switch to NUMERIC for exact precision:

ALTER TABLE `ecom.sales.Orders`
ALTER COLUMN total_amount SET DATA TYPE NUMERIC;

BigQuery validates that every existing FLOAT64 value fits into NUMERIC. If any value exceeds NUMERIC range, the query fails.

Can I change INT64 to STRING?

Yes. Converting to a wider type like STRING is considered safe because no information is lost.

What restrictions should I know?

BigQuery forbids narrowing conversions (e.g., STRING → INT64) and changes inside materialized views or partitioning columns.

Best practices for altering column types

1. Run a SELECT with SAFE_CAST to preview failures.
2. Alter during low-traffic windows.
3. Update downstream queries and BI tools that assume the old type.

Common mistakes and how to avoid them

Mistake 1: Trying to convert STRING dates to DATE directly. Fix: Create a new column, populate with PARSE_DATE, validate, then drop the old column.
Mistake 2: Forgetting to update UDFs that use strict typing.

How can I roll back a failed change?

Use time-travel: SELECT * FROM `ecom.sales.Orders` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) to restore data into a new table, then recreate the original schema.