Edit Your BigQuery Columns Without Ever Dropping A Table

Prevent BigQuery type errors and table deletions with a 5-minute method to edit columns without putting your table at risk.

Currently job searching? Give yourself an edge by developing a personal project using my free 5-page project ideation guide.

File this post under the category of data engineering things I thought I wrote about but apparently (and surprisingly) didn’t.

In the next 5 minutes I’ll demonstrate a process I use weekly to quickly change BigQuery types to avoid the considerably riskier act of dropping and recreating a table. For all the ease and utility of BigQuery Studio, there is one non-negotiable when it comes to working with data sources:

Once your schema is created, it’s final.

Sure, you can add or drop a column using the ALTER command, like so:

-- Add column

ALTER TABLE `project.dataset.table` ADD COLUMN id STRING;

-- Drop column

ALTER TABLE `project.dataset.table` DROP COLUMN id;

But what about if you wanted to do something like this?

ALTER TABLE CHANGE COLUMN `project.dataset.table` CAST(id AS INTEGER)

If you couldn’t tell from the lack of lit up SQL, this command is nonexistent in Google SQL, BigQuery’s SQL dialect.