# CREATE TABLE, ALTER and DROP in BigQuery

> BigQuery DDL runs as a query job. CREATE TABLE AS SELECT is billed like the SELECT, LIKE and COPY are free, CREATE OR REPLACE drops the data, and IF NOT EXISTS keeps an old schema without telling you.

Source: https://docs.querylab.io/bigquery/ddl/

Updated: 2026-10-14

DDL statements create, change and drop datasets, tables, views, functions and other objects. In BigQuery they are ordinary query jobs: you run them from the editor, `bq query` or the API, they show up in job history, and a statement that reads data, like `CREATE TABLE ... AS SELECT`, is billed for what it reads. The forms that only copy metadata or storage are free.

The runnable examples use temporary tables in a script. The statements that need a dataset of your own are shown as plain SQL.

## Creating a table

The four ways to create a table differ in what they copy and what they cost:

```sql
-- An empty table with an explicit schema
CREATE TABLE mydataset.orders (
  order_id STRING NOT NULL,
  ordered_at TIMESTAMP,
  amount NUMERIC,
  lines ARRAY<STRUCT<sku STRING, qty INT64>>
)
PARTITION BY DATE(ordered_at)
CLUSTER BY order_id
OPTIONS (description = 'One row per order');

-- From a query: schema and data come from the SELECT
CREATE TABLE mydataset.big_orders AS
SELECT * FROM mydataset.orders WHERE amount > 1000;

-- Same schema (and partitioning, clustering), no data
CREATE TABLE mydataset.orders_empty LIKE mydataset.orders;

-- Same schema and data, as a storage copy
CREATE TABLE mydataset.orders_backup COPY mydataset.orders;
```

In Querylab.io, right-click a table, dataset or project and choose **SQL Templates** to get `CREATE TABLE`, `COPY`, `CLONE`, `ALTER TABLE ... SET OPTIONS` and `DROP` statements filled in from the real schema. They open in a new tab and run only when you press Run. See [DDL operations](https://docs.querylab.io/schema-browser/ddl-operations/).

| Form                                       | What it is billed for (dry run against `samples.shakespeare`) |
| ------------------------------------------ | ------------------------------------------------------------- |
| `CREATE TABLE ... AS SELECT word FROM ...` | 1.3 MB, the columns the `SELECT` reads                        |
| `CREATE TABLE ... LIKE ...`                | 0 B                                                           |
| `CREATE TABLE ... COPY ...`                | 0 B                                                           |
| `CREATE TABLE ... (columns)`               | 0 B                                                           |

`COPY` is a copy job under the hood, so it costs nothing to run, and the new table is then stored and billed like any other. For a copy that only pays for the rows that later change, use a clone. See [Snapshots and clones](https://docs.querylab.io/bigquery/snapshots-and-clones/).

`CREATE TABLE ... AS SELECT` (CTAS) is the usual way to materialize a query result. Partitioning and clustering go between the table name and `AS`:

**CREATE TABLE AS SELECT**

```sql
CREATE TEMP TABLE big_orders AS
SELECT id, amount
FROM UNNEST([STRUCT(1 AS id, 250 AS amount), (2, 40), (3, 900)])
WHERE amount > 100;
SELECT * FROM big_orders ORDER BY id
```

Result in BigQuery (2 rows):

| id | amount |
| --- | --- |
| 1 | 250 |
| 3 | 900 |

## OR REPLACE vs IF NOT EXISTS

Creating a table that already exists is an error: "Already Exists: Table ...". Two modifiers change that, in opposite ways.

`CREATE OR REPLACE TABLE` drops the existing table and creates a new one. The data is gone, even if the new definition looks the same:

**OR REPLACE empties the table**

```sql
CREATE TEMP TABLE orders (id INT64);
INSERT INTO orders VALUES (1), (2);
CREATE OR REPLACE TEMP TABLE orders (id INT64, amount NUMERIC);
SELECT COUNT(*) AS rows_left FROM orders
```

Result in BigQuery (1 row):

| rows_left |
| --- |
| 0 |

On a permanent table the old data can still be recovered with [time travel](https://docs.querylab.io/bigquery/time-travel/) within the dataset's time travel window, seven days by default.

`CREATE TABLE IF NOT EXISTS` does nothing if the table exists, even if your new definition is different. The second statement below asks for an `amount` column, but the table keeps its old schema without an error:

**IF NOT EXISTS keeps the old schema**

```sql
CREATE TEMP TABLE IF NOT EXISTS orders (id INT64);
CREATE TEMP TABLE IF NOT EXISTS orders (id INT64, amount NUMERIC);
SELECT * FROM orders
```

Result in BigQuery (0 rows):

| id |
| --- |

So `IF NOT EXISTS` is fine for "create once" setup scripts, and wrong for deploying a schema change. Use `ALTER TABLE` for that.

## NOT NULL

A column declared `NOT NULL` (mode `REQUIRED`) rejects NULLs on insert:

**inserting a NULL into a NOT NULL column**

```sql
CREATE TEMP TABLE customers (id INT64 NOT NULL, email STRING);
INSERT INTO customers VALUES (NULL, 'a@example.com');
SELECT * FROM customers
```

BigQuery rejects this query:

```text
Query error: Required field id cannot be null at [2:1]
```

`NOT NULL` is the only constraint BigQuery enforces. Primary and foreign keys can be declared but are `NOT ENFORCED`. See [Primary and foreign keys](https://docs.querylab.io/bigquery/primary-and-foreign-keys/). You can relax `NOT NULL` later with `ALTER COLUMN ... DROP NOT NULL`, but not add it to an existing column.

## ALTER TABLE

The most common changes:

```sql
ALTER TABLE mydataset.orders ADD COLUMN IF NOT EXISTS coupon STRING;
ALTER TABLE mydataset.orders RENAME COLUMN coupon TO promo_code;
ALTER TABLE mydataset.orders DROP COLUMN IF EXISTS promo_code;
ALTER TABLE mydataset.orders ALTER COLUMN amount SET DATA TYPE BIGNUMERIC;
ALTER TABLE mydataset.orders ALTER COLUMN order_id DROP NOT NULL;
ALTER TABLE mydataset.orders SET OPTIONS (expiration_timestamp = TIMESTAMP '2027-01-01');
ALTER TABLE mydataset.orders RENAME TO orders_2026;
```

These change metadata only and process no bytes. What can and can't be changed in place (type changes are limited to widening, nested fields can't be added with SQL, partitioning can't be changed at all) is in [Schema changes](https://docs.querylab.io/bigquery/schema-changes/). To change partitioning or clustering, create a new table with CTAS and swap the names.

## DROP

```sql
DROP TABLE IF EXISTS mydataset.orders_backup;
DROP SCHEMA mydataset CASCADE;  -- a dataset and everything in it
```

`DROP SCHEMA` without `CASCADE` fails if the dataset isn't empty. A dropped table can be restored within the time travel window, and a dropped dataset brought back with `UNDROP SCHEMA mydataset` within the same window. See [Time travel](https://docs.querylab.io/bigquery/time-travel/).

## Datasets

In BigQuery SQL a dataset is a `SCHEMA`. Its location is fixed at creation:

```sql
CREATE SCHEMA IF NOT EXISTS mydataset
OPTIONS (
  location = 'EU',
  default_table_expiration_days = 30,
  description = 'Scratch tables'
);
```

See [Locations](https://docs.querylab.io/bigquery/locations/) and [Table expiration](https://docs.querylab.io/bigquery/table-expiration/).

## Limits

DDL counts toward the table metadata limit: at most 5 metadata updates per table every 10 seconds (50 for a partitioned table), shared with load, copy and query jobs that write to the table. Scripts that `ALTER` the same table in a tight loop hit "Exceeded rate limits: too many table update operations for this table". The error is temporary; retry with backoff.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Data definition language (DDL) statements](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language)

- [Create and use tables](https://cloud.google.com/bigquery/docs/tables)

- [Quotas and limits, table operations](https://cloud.google.com/bigquery/quotas#standard_tables)