How to Change Column Type in BigQuery

Galaxy Glossary

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

ALTER TABLE … ALTER COLUMN … SET DATA TYPE converts an existing BigQuery column to a new data type when the change is considered safe.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Description

Table of Contents

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.

Why How to Change Column Type in BigQuery is important

How to Change Column Type in BigQuery Example Usage


-- Improve monetary precision
ALTER TABLE `ecom.sales.Orders`
ALTER COLUMN total_amount SET DATA TYPE NUMERIC;

-- Allow product names with Unicode collations
ALTER TABLE `ecom.catalog.Products`
ALTER COLUMN name SET DATA TYPE STRING COLLATE "und:ci";

How to Change Column Type in BigQuery Syntax


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

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

ALTER TABLE `ecom.crm.Customers`
ALTER COLUMN created_at SET DATA TYPE TIMESTAMP;

Common Mistakes

Frequently Asked Questions (FAQs)

Does ALTER COLUMN lock the table?

No. The operation is metadata-only and completes almost instantly for supported conversions.

Can I change a column used in clustering?

Yes, as long as the conversion is safe. Clustering keys do not block type changes.

Will my views break after the change?

Views using SELECT * remain fine, but those with strict casts may need updates.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.