# 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.

Source: https://docs.querylab.io/bigquery/table-types/

Updated: 2026-10-14

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.

| Type              | Stores data in BigQuery?                   | `table_type` in INFORMATION_SCHEMA |
| ----------------- | ------------------------------------------ | ---------------------------------- |
| Standard table    | Yes                                        | `BASE TABLE`                       |
| External table    | No, it points at files or another system   | `EXTERNAL`                         |
| View              | No, it stores a query                      | `VIEW`                             |
| Materialized view | Yes, the stored result of a query          | `MATERIALIZED VIEW`                |
| Table snapshot    | Only what differs from its base table      | `SNAPSHOT`                         |
| Table clone       | Only what differs from its base table      | `CLONE`                            |
| Temporary table   | Yes, for the length of a script or session | not listed                         |

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](https://docs.querylab.io/schema-browser/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 `VIEW`s, and tables you might take for tables are sometimes views: in `bigquery-public-data.crypto_bitcoin`, `transactions`, `blocks`, `inputs` and `outputs` are views.

```sql
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](https://docs.querylab.io/bigquery/partitioning/) and [cluster](https://docs.querylab.io/bigquery/clustering/) it. Column sizes are in [How BigQuery bills a query](https://docs.querylab.io/bigquery/how-queries-are-billed/).

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.

```sql
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](https://docs.querylab.io/bigquery/external-tables/), [BigLake and Iceberg](https://docs.querylab.io/bigquery/biglake-and-iceberg/) and [Object tables](https://docs.querylab.io/bigquery/object-tables/). External tables can't be used in a [wildcard query](https://docs.querylab.io/bigquery/wildcard-tables/).

## Views

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

```sql
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:

**two columns through a view**

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

BigQuery dry run: this query processes 4.9 KB.

**the same two columns from the base table**

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

BigQuery dry run: this query processes 4.9 KB.

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

**SELECT * through a view**

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

BigQuery dry run: this query processes 58.0 KB.

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](https://docs.querylab.io/bigquery/views/) and [Authorized views](https://docs.querylab.io/bigquery/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.

```sql
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](https://docs.querylab.io/bigquery/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.

```sql
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](https://docs.querylab.io/bigquery/snapshots-and-clones/).

## Temporary tables

A temporary table exists only inside a [script](https://docs.querylab.io/bigquery/scripting/) or a [session](https://docs.querylab.io/bigquery/sessions/). It is created without a dataset name, can't be shared with other users, and is deleted when the script or session ends.

**a temporary table in a script**

```sql
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
```

Result in BigQuery (1 row):

| total_orders |
| --- |
| 21 |

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**

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

BigQuery rejects this query:

```text
Invalid value: Table "big_orders" must be qualified with a dataset (e.g. dataset.table). at [1:1]
```

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](https://docs.querylab.io/bigquery/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.

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:

- [Introduction to tables](https://cloud.google.com/bigquery/docs/tables-intro)

- [Introduction to logical views](https://cloud.google.com/bigquery/docs/views-intro)

- [Introduction to materialized views](https://cloud.google.com/bigquery/docs/materialized-views-intro)

- [Introduction to table snapshots](https://cloud.google.com/bigquery/docs/table-snapshots-intro)

- [Introduction to table clones](https://cloud.google.com/bigquery/docs/table-clones-intro)

- [Use multi-statement queries](https://cloud.google.com/bigquery/docs/multi-statement-queries)