Introducing PopSQL + dbt. The first SQL editor with built-in dbt supportRead more →

How to Drop a Column in BigQuery

In most databases, it's easy and common to drop a column. In BigQuery data warehouse? Not so much.

You can't use a command like ALTER TABLE TABLE_NAME DROP COLUMN_NAME in BigQuery, nor is there a flow in the BigQuery web UI.

Before you get all 😡 at BigQuery, it's important to realize why this common command is missing.

According to Elliott Brossard who helped build BigQuery:

"Dropping a column would mean removing the data from all of the Capacitor files that make up the table, which is an expensive operation. If BigQuery were simply to remove metadata related to the column, you would be charged storage for a phantom column that you can't actually query, which wouldn't be ideal. When you add a column, conversely, BigQuery treats the missing column in past files as having all NULL values, which doesn't require modifying them."

Workaround to Dropping a Column in BigQuery

Select from the original table, but use the except command to exclude the column you don't want. You can then save the results as a new BigQuery table. Here's an example:

select * except(author, ranking) from `bigquery-public-data.hacker_news.comments` limit 10;

Then click Save Results: alt text

And choose BigQuery table as your desired option: alt text

It's not a perfect solution, but it may work for you. Use with care.

Ready for a modern SQL editor?