BigQuery table types

BigQuery has standard tables, external tables, views, materialized views, snapshots, clones and temporary tables. What each one stores, what a query against it is billed for, and how to tell them apart.

Everything you query in BigQuery is called a table, but there are seven kinds. They differ in where the data lives, whether it is stored at all, and what you pay for when you query them.

To see which kind a table is, look at the table_type column of INFORMATION_SCHEMA.TABLES, or at the icon in the Querylab.io schema tree, which separates tables, views, materialized views and external tables (Navigating resources). The console shows the type too. A dataset’s tables can be mixed. bigquery-public-data.covid19_covidtracking holds one BASE TABLE and two VIEWs, and tables you might take for tables are sometimes views: in bigquery-public-data.crypto_bitcoin, transactions, blocks, inputs and outputs are views.

SELECT table_name, table_type
FROM `bigquery-public-data.covid19_covidtracking.INFORMATION_SCHEMA.TABLES`;

Standard tables

A standard table keeps its data in BigQuery’s own columnar storage. Queries on it are billed by the bytes of the columns they read, and you can partition and cluster it. Column sizes are in How BigQuery bills a query.

A table is addressed as project.dataset.table, and its name must be unique in the dataset. Google notes that listing tables through the API slows down as a dataset approaches 50,000 tables, and the console shows no more than 50,000.

External tables

An external table keeps its data outside BigQuery. It has a schema in BigQuery and a pointer to files in Cloud Storage, Google Drive or Bigtable, or to other storage such as S3 and Azure Blob Storage.

CREATE EXTERNAL TABLE shop.raw_orders (order_id INT64, amount NUMERIC)
OPTIONS (format = 'CSV', uris = ['gs://my-bucket/orders/*.csv']);

Google separates three kinds. BigLake tables point at structured data and support fine-grained security at the table level. Object tables point at unstructured files like images. Other external tables point at structured files without that security. More in External tables, BigLake and Iceberg and Object tables. External tables can’t be used in a wildcard query.

Views

A view stores a SELECT, not data. Every query against it runs the stored SELECT:

CREATE VIEW shop.recent_orders AS
SELECT order_id, amount
FROM shop.orders
WHERE placed_at >= TIMESTAMP '2026-01-01';

The bill follows the base table. A query on a view is charged for the columns of the underlying tables that it reads, directly or through the view. Reading two columns from the public view covid19_covidtracking.covid costs the same as reading them from the table it selects from:

SELECT date, state
FROM `bigquery-public-data.covid19_covidtracking.covid`

SELECT date, state
FROM `bigquery-public-data.covid19_covidtracking.summary`

Both are 4.9 KB. Selecting every column through the view reads every column of the table, 58 KB:

SELECT *
FROM `bigquery-public-data.covid19_covidtracking.covid`

So a view doesn’t make a query cheaper. It makes the SQL shorter and can limit what a user sees. If the table under a view requires a partition filter, queries on the view must include one as well.

See Views and Authorized views.

Materialized views

A materialized view stores the result of its query in BigQuery and keeps it up to date. BigQuery refreshes it in the background as the base table changes. When it is queried, BigQuery merges the stored result with changes made since the last refresh, so the answer is current. If a query on the base table could be answered from the view, BigQuery can rewrite it to use the view without you changing the SQL. Google calls that smart tuning.

CREATE MATERIALIZED VIEW shop.orders_by_day AS
SELECT DATE(placed_at) AS day, COUNT(*) AS orders
FROM shop.orders
GROUP BY day;

You pay for the stored result like any table, plus the refresh work. Automatic refreshes are billed to the project that holds the view, and a manual refresh to the project that runs it. Only some queries can be materialized. See Materialized views.

Snapshots and clones

Both are copies that store only the data that differs from the original, so they cost little to create.

A snapshot is a read-only copy of a table at a point in time, now or any time in the last seven days. It can have an expiration. You can query it, and you can restore it into a new standard table. It keeps a table’s contents for longer than time travel does.

A clone is a writable copy. After you create it, it is independent: changes to the base table and the clone don’t show up in each other. Clones suit development copies of a production table, and sandboxes where each user changes some rows.

CREATE SNAPSHOT TABLE shop.orders_march
CLONE shop.orders
OPTIONS (expiration_timestamp = TIMESTAMP '2026-12-31');

CREATE TABLE shop.orders_dev
CLONE shop.orders;

For both, you are billed for storage of the bytes that differ. If the base table changes or deletes rows that a snapshot still holds, the snapshot’s storage grows by those rows. A snapshot or clone of a table can’t be made across projects in different organizations. For INFORMATION_SCHEMA.TABLES, the columns base_table_name and snapshot_time_ms show where one came from. See Table snapshots and clones.

Temporary tables

A temporary table exists only inside a script or a session. It is created without a dataset name, can’t be shared with other users, and is deleted when the script or session ends.

CREATE TEMP TABLE daily_totals (day DATE, orders INT64);
INSERT INTO daily_totals VALUES
  (DATE '2026-03-01', 12),
  (DATE '2026-03-02', 9);
SELECT SUM(orders) AS total_orders FROM daily_totals

Temporary tables are billed for storage like other tables. In our test BigQuery put the table in a hidden dataset named like _script followed by a hash, and creating the same temporary name twice in one script failed with “Already Exists”. Use CREATE OR REPLACE TEMP TABLE to overwrite.

Two limits are easy to hit. There is no temporary view:

CREATE TEMP VIEW big_orders AS
SELECT 1 AS order_id, 250 AS amount;
SELECT * FROM big_orders

The message is about a dataset qualifier, which hides the real cause: temporary views don’t exist. Use a WITH clause or a temporary table. And a temporary table can’t have OPTIONS; creating one with OPTIONS (require_partition_filter = TRUE) fails with “OPTIONS on temporary tables are not supported”. See Temporary tables.

Which one to use

• Data you load or write: a standard table.
• Files you want to read where they are: an external table.
• A name for a query, or access to part of a table: a view.
• An expensive aggregate that many queries repeat: a materialized view.
• A copy to keep before a risky change: a snapshot. A copy to experiment on: a clone.
• Intermediate results inside one script: a temporary table.