How to Overwrite a Table in BigQuery

The statement is atomic: readers see either the old table or the new one, never an empty table in between. It also replaces the schema, so any column you leave out of the SELECT is gone. Partitioning and clustering are not inherited from the old table and have to be restated.

CREATE OR REPLACE TABLE analytics.daily_orders
PARTITION BY order_date
CLUSTER BY country
AS
SELECT DATE(created_at) AS order_date, country, COUNT(*) AS orders
FROM raw.orders
GROUP BY order_date, country;

WRITE_TRUNCATE in load jobs

Outside SQL, the write disposition controls the same behaviour. WRITE_TRUNCATE replaces the data, WRITE_APPEND adds to it, and WRITE_EMPTY fails if the table already has rows. In the bq CLI, --replace selects WRITE_TRUNCATE.

bq load --replace --source_format=CSV \
  analytics.daily_orders gs://my-bucket/orders.csv \
  order_date:DATE,country:STRING,orders:INT64

Replacing only part of a table

Rebuilding a whole table to refresh one day is wasteful on a large partitioned table. Write to a single partition with the $YYYYMMDD decorator, or use MERGE when the change is a set of upserts rather than a wholesale replacement.

DELETE FROM analytics.daily_orders WHERE order_date = '2026-08-23';
INSERT INTO analytics.daily_orders
SELECT DATE(created_at), country, COUNT(*)
FROM raw.orders
WHERE DATE(created_at) = '2026-08-23'
GROUP BY 1, 2;

Recovering from a mistake

A replaced table can be read as it was up to seven days ago through time travel, as long as you have not dropped the dataset. The FOR SYSTEM_TIME AS OF clause is the fastest undo available.

CREATE OR REPLACE TABLE analytics.daily_orders AS
SELECT * FROM analytics.daily_orders
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

Do more with BigQuery

• Export BigQuery to Google Sheets on a schedule
• Send BigQuery results to Slack
• BigQuery SQL client in the browser