How to CREATE TABLE in BigQuery

How do I use CREATE TABLE in BigQuery to build partitioned and clustered tables?

CREATE TABLE creates a new permanent table in a BigQuery dataset with user-defined schema, partitioning, clustering, options, and optional CTAS.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CREATE TABLE do in BigQuery?

CREATE TABLE persists structured data inside a dataset so teams can query, share, secure, and optimize it. Use it when you need a reusable table instead of an ad-hoc query result.

Which syntax variants are available?

BigQuery supports four patterns: CREATE TABLE, CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE TABLE, and CREATE TABLE AS SELECT (CTAS). Each pattern accepts column definitions, partitioning, clustering, table OPTIONS, and labels.

How do I define columns and types?

List each column followed by a data type and optional NOT NULL or OPTIONS(description="…"). Common types include INT64, NUMERIC, STRING, BOOL, BYTES, DATE, DATETIME, TIMESTAMP, JSON, and ARRAY.

How can I partition an ecommerce table?

Add PARTITION BY with a date/timestamp column or expression. Example: PARTITION BY DATE(order_date) splits Orders by day, reducing scanned data on time-filtered queries.

How do I cluster for faster filters?

Append CLUSTER BY and up to four high-cardinality columns used in equality predicates. For Orders, CLUSTER BY customer_id speeds customer-specific lookups.

When should I use CREATE TABLE AS SELECT?

CTAS materializes a query and creates the table in one step—ideal for snapshots, backfills, or denormalized reporting tables.

Best practices for CREATE TABLE

Always qualify the dataset, use descriptive names, document columns with OPTIONS(description=), partition before loading data, and apply least-privilege roles like bigquery.dataEditor only where needed.

Complete example

The example below creates a partitioned, clustered Orders table with labels and a description in an idempotent way.

Why How to CREATE TABLE in BigQuery is important

How to CREATE TABLE in BigQuery Example Usage


CREATE OR REPLACE TABLE `shop.analytics.Orders`
(
  id           INT64        NOT NULL OPTIONS (description="Primary key"),
  customer_id  INT64        NOT NULL,
  order_date   DATETIME     NOT NULL,
  total_amount NUMERIC      NOT NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  updated_at   TIMESTAMP
)
PARTITION BY DATE(order_date)
CLUSTER BY customer_id
OPTIONS(
  description="Transactional orders table",
  labels=[("env","prod"), ("source","app")]
);

How to CREATE TABLE in BigQuery Syntax


CREATE [OR REPLACE] TABLE [IF NOT EXISTS] `project_id.dataset.table_name`
(
    column_name data_type [NOT NULL] [OPTIONS(description="text")],
    ...
)
[PARTITION BY partition_expression]
[CLUSTER BY column_list]
[OPTIONS(
    description="table description",
    labels=[("env","prod"),("team","analytics")]
)]
[AS query_statement];

Common Mistakes

Omitting the dataset prefix (e.g., writing CREATE TABLE Orders instead of dataset.Orders). BigQuery then looks in your default project and may create the table in an unintended location. Always fully qualify tables with project and dataset.
Using STRING for every column. This blocks partition pruning, clustering, and correct calculations. Select the closest native type (e.g., INT64 for IDs, NUMERIC for money) to gain performance and storage benefits.

Frequently Asked Questions (FAQs)

Can I add partitions later?

You can convert an unpartitioned table to a partitioned one with CREATE TABLE new_table PARTITION BY … AS SELECT * FROM old_table, then swap names. Direct ALTER isn’t supported.