Creating External Tables in BigQuery on GCS Data: A Practical Guide

In this article, let us look at BigQuery external tables — what they are, when they make sense, and how to actually set one up over data sitting in Google Cloud Storage. If you have CSV or Parquet files in a GCS bucket and you want to query them without loading them into BigQuery native storage, external tables are probably what you are looking for.

I have used this approach in a few projects where the data lands in GCS from some third-party tool or an upstream system, and the team wants to run ad-hoc SQL on it before deciding whether to build a proper ingestion pipeline. It is also handy when you have archival data that you rarely query — you don’t want to pay for BigQuery storage on something you touch once a quarter, but you still need the option to run a query when someone asks.

We will walk through creating an external table definition, querying it, understanding what happens under the hood, and then cover some gotchas I have run into in production.

What Exactly Is an External Table in BigQuery?

A native BigQuery table stores data in BigQuery’s own storage layer. You pay for that storage, and when you query it, BigQuery can optimise reads because it controls the layout. An external table, on the other hand, leaves the data where it is — in GCS, or sometimes in Drive or Cloud SQL — and BigQuery reads it at query time.

The key thing to understand is that BigQuery does not ingest or copy the data. It reads the files directly from GCS every time you run a query. This has implications for performance, cost, and the kinds of operations that work.

Aspect	Native Table	External Table (GCS)
Data location	BigQuery managed storage	Your GCS bucket
Storage cost	BigQuery storage pricing	Standard GCS pricing
Query performance	Optimised, columnar reads	Depends on file format and layout
DML support	Full INSERT/UPDATE/DELETE/MERGE	Read-only (no mutations)
Partitioning	Native partitioning supported	Hive partitioning via file layout
Query caching	Results cached	No query cache

If you are running production pipelines that query the same data repeatedly, external tables are probably not the right choice. You would want to load that data into native tables. But for exploration, prototyping, or infrequent access, they work well.

Setting Up: What You Need

Before we start, make sure you have:

1. A GCS bucket with some data files. We will use a bucket called my-project-sales-data with Parquet files partitioned by date: gs://my-project-sales-data/year=2026/month=06/day=15/
2. BigQuery permissions — you need bigquery.tables.create on the dataset and storage.objects.get on the GCS bucket
3. A BigQuery dataset where the external table definition will live

That last point is worth emphasising. The external table itself is just a metadata definition — a pointer — stored inside your BigQuery dataset. The actual data never moves.

Creating the External Table: Console Walkthrough

Let us start with the GCP Console so you can see what is happening, then we will look at the SQL approach.

Step 1: Open BigQuery and Create a Table

From the BigQuery Cloud Console, expand your project, find the dataset where you want the table, click the three-dot menu, and choose “Create table.”

Step 2: Configure the Source

In the “Create table” panel:

• Source: Choose “Google Cloud Storage”
• Select file from GCS bucket: Browse to your bucket and pick one of the data files. It does not matter which one — BigQuery uses that file to infer the schema, but it will read the entire path pattern you specify
• File format: Choose Parquet (or CSV, Avro, JSON — whatever your files are)

Step 3: Set the Destination

• Project and Dataset: Where the external table definition lives
• Table name: Give it a name, e.g., sales_external
• Table type: Choose “External table” (this is the important part)

Step 4: Schema and Partition Handling

If your files are partitioned by directory structure (the Hive-style year=2026/month=06/ pattern), check the box for “Hive partitioning” and set the source URI prefix to gs://my-project-sales-data/. BigQuery will detect the partition columns automatically.

You can let BigQuery auto-detect the schema from the file, or you can specify it manually. I usually prefer to specify it myself, especially for CSV files where auto-detect can guess types wrong (everything ends up as STRING or it misreads a numeric field).

Click “Create table” and you are done. The external table now shows up in your dataset with a little icon indicating it points to external data.

Doing It with SQL

If you are like me and prefer keeping things as code, here is the SQL equivalent:

CREATE OR REPLACE EXTERNAL TABLE `my-project.sales_dataset.sales_external`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-project-sales-data/*.parquet'],
  hive_partition_uri_prefix = 'gs://my-project-sales-data/',
  enable_hive_partitioning = TRUE
);

For CSV files with a header row, you would do something like:

CREATE OR REPLACE EXTERNAL TABLE `my-project.sales_dataset.logs_external`
OPTIONS (
  format = 'CSV',
  uris = ['gs://my-project-logs/2026/*.csv'],
  skip_leading_rows = 1,
  field_delimiter = ',',
  max_bad_records = 0
);

The uris field supports wildcards, which is useful when you have a folder with many files. You can also pass multiple URI patterns in the array if your data is spread across buckets.

Querying the External Table

Once the table is created, you query it exactly like a native table:

SELECT
  date,
  product_id,
  SUM(revenue) AS total_revenue
FROM `my-project.sales_dataset.sales_external`
WHERE date BETWEEN '2026-06-01' AND '2026-06-30'
GROUP BY date, product_id
ORDER BY date;

If you have Hive partitioning set up, BigQuery will prune the directories it reads based on your WHERE clause, which saves you from scanning the entire bucket. This is why it is worth getting the partition layout right.

You can also run aggregate queries without GROUP BY:

SELECT COUNT(*) AS row_count
FROM `my-project.sales_dataset.sales_external`;

But here is something I noticed: that SELECT COUNT(*) will scan every file in the bucket. Each query execution reads from GCS directly. No caching, no materialisation. If you have terabytes of data and run SELECT COUNT(*) five times in a row, you are reading those files five times. Keep that in mind.

What You Cannot Do with External Tables

This list grows from practical experience:

1. No DML operations. You cannot INSERT, UPDATE, DELETE, or MERGE into an external table. If you need to change the data, you change the files in GCS.

2. No query result caching. Every query is a fresh read from GCS. For repeated queries on the same data, this gets expensive and slow.

3. No clustering or partitioning beyond Hive layout. Native BigQuery features like integer-range partitioning, time-unit partitioning, and clustering are not available.

4. Schema is fixed at creation time. If you add a new column to your Parquet files, the external table does not pick it up automatically. You need to recreate the table definition.

5. Performance varies wildly with file size and format. Lots of small CSV files are the worst case. Fewer, larger Parquet files with row-group level statistics are the best case.

6. No support for BigQuery BI Engine. If your team uses BI Engine for dashboard acceleration, external tables will not benefit from it.

What I Would Do Differently in Production

For a quick demo or one-off exploration, the console setup is fine. But if you are putting this into a production pipeline, here is what I suggest:

Use Terraform and version-control the table definition. The google_bigquery_table resource supports external_data_configuration. That way your external table is defined alongside the rest of your infrastructure, and you don’t have someone deleting it by accident.

Set up lifecycle rules on the GCS bucket. If the external data is transient, configure object lifecycle policies so you are not paying GCS storage indefinitely for data no one queries.

Consider materialising if query patterns stabilise. After a month or two of ad-hoc queries, you will know which datasets people actually use. At that point, create a scheduled query or a Dataform pipeline that loads the data into a native table. You get better performance, lower query cost per run, and access to DML.

Audit access carefully. Since the data lives in GCS, anyone who can query the external table also needs read access to the underlying bucket. Make sure the permissions at the GCS level match what you intend. It is easy to accidentally grant broader access than you meant to.

Wrapping Up

External tables in BigQuery are one of those features that are easy to set up and genuinely useful for the right use cases. If you need to run SQL on files in GCS without building a full ingestion pipeline, this is probably the quickest path. Just be aware of the trade-offs — no caching, no DML, and performance that depends heavily on how your data is laid out.

For ad-hoc exploration, archival data, or prototyping a data model before committing to a pipeline, I have found external tables to be a solid tool. For anything that needs to run reliably at scale, plan to move that data into native tables.