# External tables in BigQuery

> An external table queries files in Cloud Storage, Drive or Bigtable without loading them. Dry runs report a lower bound of 0 bytes, CSV and Avro files are read in full whatever columns you select, a CSV without autodetect gets string_field_0 column names, and the tables are read-only.

Source: https://docs.querylab.io/bigquery/external-tables/

Updated: 2026-10-14

An external table is a table definition whose data stays outside BigQuery: files in Cloud Storage, a Google Sheet in Drive, or a Bigtable table. Each query reads the source at query time. You skip the load step and always see the current files, but you give up most of what makes BigQuery fast and cheap: columnar storage, clustering, accurate estimates and DML.

## Creating one

```sql
CREATE OR REPLACE EXTERNAL TABLE mydataset.raw_orders
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-bucket/orders/*.parquet']
);
```

The schema comes from the files for Parquet, Avro and ORC. For CSV and JSON, give a column list or set `autodetect`. A wildcard URI covers many files, which must share a schema.

In Querylab.io, importing a Google Sheet with **Connected table** ticked creates an external table over the sheet instead of copying it; every query on it is billed for the bytes read from the sheet (see [Import](https://docs.querylab.io/data-management/import/#google-sheets)).

### CSV column names

We created an external table over a small CSV file with a header row, with `skip_leading_rows = 1` and no schema. The header was skipped, but not used for names: the columns were `string_field_0` and `string_field_1`, all `STRING`. Without `skip_leading_rows` the header row became data. Either list the columns explicitly or set `autodetect = TRUE`.

## What a query reads and costs

We queried the same 50-row US states file as CSV (688 bytes) and as Avro (971 bytes), and a small Parquet file of tweets (1.5 KB, four columns):

| Query                      | Dry-run estimate       | Bytes processed      |
| -------------------------- | ---------------------- | -------------------- |
| CSV, `SELECT *`            | lower bound of 0 bytes | 672                  |
| CSV, one column            | lower bound of 0 bytes | 672, the whole file  |
| CSV, one column, `LIMIT 1` | lower bound of 0 bytes | 23                   |
| Avro, `SELECT *`           | lower bound of 0 bytes | 971                  |
| Avro, one column           | lower bound of 0 bytes | 971, the whole file  |
| Parquet, `SELECT *`        | lower bound of 0 bytes | 318                  |
| Parquet, one column        | lower bound of 0 bytes | 49, that column only |

Each was billed the 10 MB minimum. What this shows:

- **The dry run doesn't know.** BigQuery can't tell how much an external file holds until it reads it, so the estimate is "a lower bound of 0 bytes" whatever the files' size. Cost limits based on the estimate, including Querylab.io's, can't protect you here; maximum bytes billed still applies when the query runs.
- **Row formats are read in full.** CSV, JSON and Avro store rows together, so selecting one column reads every byte. Parquet is columnar: BigQuery read only the column we selected, and counted the data's size rather than the compressed file's. ORC is columnar too. For data you query often, that alone is a reason to use Parquet.
- **`LIMIT` can stop early on external CSV**, as it can on clustered native tables. On an unclustered native table it never reduces bytes. Don't rely on it: with many files or a filter, BigQuery may still read everything.

On-demand queries are billed for the bytes read from the files, the same per-TiB price as native tables. The bucket must be in the same location as the dataset: a bucket in `us-central1` works with a dataset in `us-central1` or the `US` multi-region.

## Limitations

- **Read-only.** No `INSERT`, `UPDATE`, `DELETE` or `MERGE`. Change the files instead.
- **No copy, no extract job, no `tabledata.list`.** To export, query into a native table first.
- **Not cached** for sources other than Cloud Storage (Drive, Bigtable). Every repeat is billed again.
- **No clustering, no time travel, no BI Engine, no storage statistics.** Partitioning is limited to Hive-style folder layouts.
- **No consistency.** If files change while a query runs, the results are undefined.
- Up to 10,000 URIs and 10 million files per table, and 16 concurrent queries against a Bigtable source.

## Partitioned file layouts

If the files are laid out as `gs://bucket/orders/dt=2026-03-02/part-0.parquet`, BigQuery can treat `dt` as a partition column:

```sql
CREATE EXTERNAL TABLE mydataset.raw_orders
WITH PARTITION COLUMNS (dt DATE)
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-bucket/orders/*'],
  hive_partition_uri_prefix = 'gs://my-bucket/orders',
  require_hive_partition_filter = TRUE
);
```

A filter on `dt` then skips the folders it rules out, which is the only way to prune files in a plain external table. `require_hive_partition_filter` stops queries that forget the filter, like `require_partition_filter` on native tables.

## External table, BigLake table or load

| You want                                                                                      | Use                                                                           |
| --------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------- |
| A one-off look at some files                                                                  | an external table, or a temporary one in a single query                       |
| Files that other engines also write and read, with fine-grained security and good performance | a [BigLake table](https://docs.querylab.io/bigquery/biglake-and-iceberg/) over Parquet or Iceberg     |
| Fast, cheap, repeated queries                                                                 | [load the data](https://docs.querylab.io/bigquery/loading-data/) into a native table; loading is free |
| Data in Cloud SQL, Spanner or AlloyDB                                                         | a [federated query](https://docs.querylab.io/bigquery/federated-queries/)                             |
| Images, PDFs and other unstructured files                                                     | an [object table](https://docs.querylab.io/bigquery/object-tables/)                                   |

Metadata caching (`max_staleness` with a connection) speeds up queries over many files by caching the file list and statistics; it is available for BigLake tables, not plain external tables.

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 external tables](https://cloud.google.com/bigquery/docs/external-tables)

- [Introduction to external data sources](https://cloud.google.com/bigquery/docs/external-data-sources)

- [Query externally partitioned data](https://cloud.google.com/bigquery/docs/hive-partitioned-queries)

- [Metadata caching for external tables](https://cloud.google.com/bigquery/docs/metadata-caching-external-tables)