External tables vs scheduled loads

BigQuery external tables read GCS data without copying it in. Load tables copy the data into BigQuery storage and bill at storage rates. The choice depends on query frequency and data freshness needs.

External wins for: large infrequently-queried datasets where storage cost beats query cost, hot data already landing in GCS as Parquet, multi-engine read patterns. Loaded wins for: hot dashboards, partition-aware queries, joins where storage layout matters.

scripts/external_table.sql

sql

CREATE OR REPLACE EXTERNAL TABLE `${GCP_PROJECT_ID}.${BQ_RAW_DATASET}.orders_external`
WITH PARTITION COLUMNS
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://learnwithparam-ecommerce-raw/raw/orders/*'],
  hive_partition_uri_prefix = 'gs://learnwithparam-ecommerce-raw/raw/orders'
);

External table over Hive-partitioned GCS Parquet. BigQuery prunes partitions on the year/month columns automatically.

For columnar formats (Parquet, ORC), external tables are competitive. For CSV externals, queries are slower because BigQuery cannot use its native indexing. Default rule: load for hot dashboards, external for cold data.

Quiz: Quiz

Loading practice…