In BigQuery, when should data stay in an external table on Cloud Storage instead of being loaded?

## What an external table actually is A BigQuery external table stores metadata only — a schema, a format and a set of URIs. Nothing is copied. When a query touches it, BigQuery opens the underlying objects, parses them, and feeds rows into the same execution engine that serves native tables. The files stay wherever they are, owned by whoever writes them. The supported sources include Cloud Storage (CSV, JSON, Avro, Parquet, ORC), Google Drive, and Bigtable. For live relational sources there is federation instead: `EXTERNAL_QUERY('project.region.connection', 'SELECT ...')` sends the inner SQL string to a Cloud SQL or Spanner instance through a connection resource and streams the result back for BigQuery to join against warehouse tables. ```sql SELECT o.id, o.status, c.segment FROM EXTERNAL_QUERY('my-project.us.orders-conn', 'SELECT id, status, customer_id FROM orders') AS o JOIN `analytics.customers` AS c ON c.id = o.customer_id; ``` ## BigLake and the security story A plain external table requires each querying user to hold Cloud Storage read permission on the underlying bucket, which pushes your access control down into object storage where it is hard to reason about. A **BigLake** table routes the read through a connection's service account instead: the service account can read the bucket, users cannot, and access is granted in BigQuery. That is what makes fine-grained controls — row-level and column-level — meaningful over lake files rather than stopping at the bucket boundary. ## Why external is slower Several advantages of native storage simply do not exist for arbitrary files. **Layout.** Loaded data is stored in BigQuery's own columnar format on Colossus, laid out and encoded by the system. External files were written by someone else, in whatever row-group sizes and compression their writer chose. Parquet and ORC at least preserve columnar reads and let BigQuery skip columns; CSV and JSON must be parsed in full, every row, every query. **Metadata.** Native tables carry statistics and clustering information the engine uses to skip data. An external table has whatever the file footers offer and no clustering at all. **Pruning.** The only structural pruning available is directory-level. If the objects are laid out as `gs://lake/events/dt=2024-05-01/...`, declare Hive partitioning in the DDL and the partition keys become real columns that filters can eliminate on. Without that, a filter on date reads every file. Setting `require_hive_partition_filter = true` refuses queries that forget the predicate — a cheap guard against a full-lake scan. **Caching.** Result caching cannot be relied upon for external data, because BigQuery does not control when the files change. **Mutability.** External tables are read-only: no `UPDATE`, no `DELETE`, no streaming inserts. Correcting data means rewriting files with whatever engine owns them. ## The decision Leave data external when at least one of these is true: - Another engine owns the files and must keep writing them; the lake is the system of record. - The data is queried rarely — compliance archives, raw landing zones, one-off backfills. - You are exploring before committing to a schema; an external table costs nothing to define and nothing to drop. - Duplication is unacceptable for governance or size reasons. Load it when: - The same tables back dashboards or scheduled jobs, so the per-query parsing overhead is paid over and over. - You need partitioning plus clustering to make interactive filters cheap. - You need DML, streaming ingestion, or transactional correction of rows. - You want predictable, boring performance that does not depend on how upstream wrote its files. The pragmatic middle is a two-tier design: keep the lake as the durable raw layer with external tables over it, and materialise the curated, frequently-queried subset into native BigQuery tables that are partitioned and clustered for the access pattern. The external tables serve discovery and backfill; the native tables serve the business. ## Cost framing Querying an external table still bills the query, and you additionally pay object-storage costs and any egress the layout implies. There is no scenario where an external table is free; the saving is that you skipped a copy, and the price is that every query re-does work a load would have done once. Formats matter enormously here — the same data as compressed Parquet versus raw CSV is not a small difference in bytes read, it is the difference between reading three columns and reading the whole record.