BigQuery CREATE TABLE and DROP TABLE: syntax and examples

CREATE TABLE with columns and options

CREATE TABLE IF NOT EXISTS mydataset.orders (
  order_id INT64 NOT NULL OPTIONS (description = 'Order number'),
  order_date DATE NOT NULL,
  customer_id STRING,
  amount NUMERIC,
  status STRING DEFAULT 'new'
)
PARTITION BY order_date
CLUSTER BY customer_id
OPTIONS (
  description = 'Orders, one row per order',
  partition_expiration_days = 400,
  require_partition_filter = TRUE
);

What each part does:

• NOT NULL makes the column REQUIRED. A load with an empty value fails instead of storing a NULL.
• OPTIONS (description = ...) on a column is the documentation every later reader sees in the schema.
• DEFAULT 'new' fills the column when an insert leaves it out.
• PARTITION BY order_date splits the table by day, so a query that filters on the date reads only those days. See partitioned tables.
• CLUSTER BY customer_id sorts data within each partition, so filters on that column read less.
• partition_expiration_days deletes partitions older than that many days.
• require_partition_filter = TRUE rejects any query on the table that does not filter on the partition column. It is the simplest guard against a full-table scan by accident.

The column types are explained in BigQuery data types. For a TIMESTAMP column, partition by its date: PARTITION BY DATE(event_ts).

IF NOT EXISTS or OR REPLACE

Use IF NOT EXISTS in setup scripts that may run twice. Use OR REPLACE for tables that are rebuilt from a query on every run. A replaced table can be recovered for a few days with time travel; see restoring a deleted table.

CREATE SCHEMA IF NOT EXISTS works the same way for datasets.

CREATE TABLE AS SELECT

CREATE OR REPLACE TABLE mydataset.daily_clicks
PARTITION BY data_date AS
SELECT
  data_date,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM searchconsole.searchdata_site_impression
WHERE data_date >= '2026-09-01'
GROUP BY data_date;

The schema comes from the query: here data_date DATE, clicks INT64, impressions INT64. The dry run reported 18.7 MB to read from our Search Console export, which is what the statement would be billed for. Partitioning and clustering go before AS.

This is the usual way to build a summary table that reports read instead of the raw data. Scheduled, it is a nightly rebuild. One thing to know: with OR REPLACE, a change to the query changes the table’s schema on the next run, and whatever reads the table sees it at once.

LIKE and COPY

CREATE TABLE mydataset.orders_empty LIKE mydataset.orders;
CREATE TABLE mydataset.orders_copy COPY mydataset.orders;

LIKE creates an empty table with the same columns and, per Google’s DDL reference, the same partitioning, clustering and options. COPY copies columns and data. For a backup that only costs storage for what later changes, use a table snapshot instead; the restore guide shows it.

CREATE SCHEMA: datasets

CREATE SCHEMA IF NOT EXISTS `myproject.reporting`
OPTIONS (
  location = 'US',
  default_table_expiration_days = 30
);

In BigQuery, SCHEMA means dataset. The location cannot be changed later, and a query can only join tables in the same location, so create every dataset a report uses in the same one. default_table_expiration_days deletes new tables after that many days, which suits a scratch dataset.

DROP

DROP TABLE IF EXISTS mydataset.orders;
DROP SCHEMA mydataset;
DROP SCHEMA mydataset CASCADE;

• IF EXISTS stops the statement from failing when the table is already gone.
• DROP SCHEMA refuses a dataset that still has tables, with “Dataset … is still in use”. CASCADE drops the tables with it.
• A dropped table can be restored within the dataset’s time travel window, seven days by default, and a dropped dataset with UNDROP SCHEMA. After that it is gone. Details in the restore guide.

Deleting rows rather than the table is DML: see the DML guide.

Views

CREATE VIEW and CREATE MATERIALIZED VIEW follow the same IF NOT EXISTS and OR REPLACE rules. When to use which is in BigQuery views and materialized views.

Keep the DDL with the logic

A CREATE OR REPLACE TABLE ... AS SELECT that runs every night is report logic, and it usually lives in one person’s scheduled queries. When that person is away and the numbers look wrong, nobody can see which query built the table. In OWOX Data Marts the same SQL is a Data Mart with an owner, a description per column and a run history, and reports read it directly. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data.

Google BigQueryIs BigQuery a Data Warehouse or a Data Lake? A Marketing GuideOctober 6, 2026→Google BigQueryHow to Connect BigQuery to Google Sheets: Three WaysOctober 6, 2026→Google BigQueryBigQuery Aggregate Functions: COUNT, SUM, STRING_AGG, ARRAY_AGG and MoreOctober 16, 2026→

See all articles →