A nightly BigQuery load of one 300 GB gzipped CSV from Cloud Storage takes hours. Why, and what would you change?

## Where the time goes A BigQuery load job is a parallel read: workers are assigned pieces of the source and decode them concurrently. Anything that prevents the input from being divided collapses that parallelism, and the job degrades to the throughput of one reader. A **gzip stream is not splittable**. Its compression state at any byte depends on every byte before it, so a worker cannot start at the middle of the file. Consequently one gzipped object equals one reader, regardless of size and regardless of available capacity. Three hundred gigabytes read serially is hours. On top of that, **CSV is the most expensive format to ingest**. Every value arrives as text and must be scanned for delimiters and quotes, then parsed and coerced into the column's type. Quoted newlines make it worse, because the reader can no longer treat a newline as a record boundary without tracking quote state. ## The fix, in order **1. Change the format to Avro or Parquet.** Both are binary and self-describing — the schema travels with the data, so there is no type coercion from text and no schema flag to maintain. Both are block-structured: compression is applied per block, so a compressed file remains splittable and workers can read blocks in parallel. Google's guidance is that Avro is the fastest format for loading compressed data for precisely this reason. Parquet, being columnar, is the natural choice when the same files are also read by lake engines or exposed as an external table. **2. If the format cannot change, change the file count.** Parallelism is bounded by the number of independently readable units. One gzip file gives one; a thousand gzip files give a thousand readers. Most export tools can shard output — do that, and keep the shards in the hundreds of megabytes rather than kilobytes, since thousands of tiny files trade one bottleneck for per-file overhead. **3. Consider dropping the compression.** Uncompressed CSV is splittable, so a single large uncompressed file loads in parallel while a single gzipped one does not. You pay more Cloud Storage egress-free bytes to read and more storage on the staging bucket, but the wall-clock difference on a serialised load is usually decisive. Sharded-and-compressed still beats both. ## Confirm before you act Do not assume the load job is the slow part. `INFORMATION_SCHEMA.JOBS` gives creation, start and end times per load job, along with bytes and errors: ```sql SELECT job_id, creation_time, start_time, end_time, TIMESTAMP_DIFF(end_time, start_time, MINUTE) AS minutes, total_bytes_processed, error_result FROM `region-us`.INFORMATION_SCHEMA.JOBS WHERE job_type = 'LOAD' AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) ORDER BY minutes DESC; ``` If the job itself runs for minutes but the pipeline takes hours, the upstream export or the file-arrival wait is the real target. If the job runs for hours on one file, you have confirmed the serialisation. ## Secondary causes worth ruling out - **Slot contention.** Batch loads run on a free shared pool by default. Under heavy contention they can queue. Routing loads to a reservation gives them dedicated capacity — worth doing when a load has an SLA. - **Location mismatch.** The source bucket must be colocated with the dataset; a badly placed bucket is a hard failure rather than slowness, but it is worth checking when a pipeline is newly built. - **Autodetect on a huge CSV.** Schema inference samples the input; pinning an explicit schema removes both the sampling cost and the risk of a type flipping between runs. - **A single wide row.** Very large individual records can throttle a reader independent of the file layout. ## The durable shape The pattern that avoids this class of problem entirely: the upstream job writes **many medium-sized Parquet or Avro files, partitioned by date**, into a dated prefix; the load job targets `gs://bucket/table/dt=YYYY-MM-DD/*.parquet` with `WRITE_TRUNCATE` against that partition. Parallel by construction, cheap to parse, atomic per partition, and idempotent on rerun.