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.

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

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

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):

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:

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

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.