ExamFinch.com My Study

Study guide

3.1 Batch Loading Data into BigQuery

Practice this exam

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

3.1 Batch Loading Data into BigQuery

Key Takeaways Explain This Topic AI

Batch loading data into BigQuery from Cloud Storage via load jobs is completely free of charge, with billing applied only to source Cloud Storage storage and subsequent BigQuery table storage.

Production workloads should always stage large files in Cloud Storage rather than uploading locally to leverage horizontal parallel slot distribution, wildcards, and resilience.

Self-describing binary and columnar formats like Apache Avro and Parquet are splittable across parallel workers, whereas GZIP-compressed CSV files force single-threaded decompression.

BigQuery Data Transfer Service (DTS) automates scheduled, managed ingestion from Google SaaS platforms (Google Ads, Campaign Manager) and third-party cloud object storage (AWS S3, Azure Blob Storage).

Load job write dispositions (WRITE_TRUNCATE, WRITE_APPEND, WRITE_EMPTY) govern how incoming batches handle existing table data and partition overwrites.

Last updated: October 2026

3.1 Batch Loading Data into BigQuery

Data ingestion is the foundational gateway of any enterprise analytical architecture. In Google Cloud, BigQuery serves as the enterprise data warehouse and lakehouse engine, designed to ingest and analyze petabytes of structured and semi-structured information. When architecting ingestion pipelines, data engineers and practitioners must make critical design decisions regarding ingestion modalities, source file formats, staging architectures, cost optimization, and error tolerance. Batch loading represents the most common, cost-effective, and throughput-optimized pattern for moving historical, scheduled, or bulk datasets into BigQuery.

1. Batch Ingestion Methods & Tooling

Google Cloud provides multiple interfaces for initiating batch load operations into BigQuery, each suited to specific operational contexts and levels of automation:

The bq Command-Line Tool

The bq CLI is the primary workhorse for shell scripting, cron jobs, and CI/CD pipelines. A batch load is executed using the bq load command, which submits an asynchronous load job to the BigQuery control plane. # Basic bq load command loading Parquet data from Cloud Storage

  • bq load \
  • -source_format=PARQUET \
  • -write_disposition=WRITE_APPEND \
  • retail_dw.orders \
  • gs://company-data-lake-prod/retail/orders/2026/10/*.parquet

The command submits a job configuration to BigQuery, which allocates shared background compute slots to process and convert the source data into BigQuery's proprietary columnar storage format, Capacitor .

BigQuery Web Console

The Google Cloud Console provides an intuitive graphical interface for ad-hoc, exploratory, or one-off data loads. Within the BigQuery studio workspace, users can select a destination dataset, click Create Table , and specify the source data origin (Google Cloud Storage, local file upload, Google Drive, or Google Cloud Bigtable). While convenient for quick analysis, local uploads via the console are subject to browser session timeouts and strict file size constraints (files uploaded from a local source in the console are limited to 100 MB each, and wildcards are not supported).

Cloud Client Libraries

For custom data applications and programmatic pipelines, Google Cloud offers idiomatic client libraries in Python, Java, Go, Node.js, and C#. Programmatic loading allows fine-grained job monitoring, metadata tagging, and programmatic error handling. from google.cloud import bigquery

client = bigquery.Client()

  • table_id = "my-project.retail_dw.customer_profiles"
  • uri = "gs://company-data-lake-prod/profiles/*.avro"
  • job_config = bigquery.LoadJobConfig(
  • source_format=bigquery.SourceFormat.AVRO,
  • write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
  • use_avro_logical_types=True,
  • )
  • load_job = client.load_table_from_uri(uri, table_id, job_config=job_config)
  • load_job.result() # Blocks until the load job completes
  • print(f"Successfully loaded {load_job.output_rows} rows into {table_id}.")
  • BigQuery Data Transfer Service (DTS)

The BigQuery Data Transfer Service is a fully managed, scheduled automation service designed to migrate data into BigQuery on a recurring basis without writing boilerplate pipeline code. DTS provides native connectors for:

Google Application & SaaS Sources: Google Ads, Campaign Manager 360, Google Analytics 4, YouTube Channel and Content Owner reports, Google Merchant Center.

External Cloud Storage: Amazon S3 (authenticated with AWS access credentials) and Azure Blob Storage (authenticated via Shared Access Signatures).

Cloud Storage: Scheduled recurring batch ingestion of files landing in GCS buckets.

Data Warehouse Migrations: Managed schema and data offloading from legacy warehouses like Teradata and Amazon Redshift.

2. Staging Architecture: Cloud Storage vs. Local Uploads

One of the most critical architectural principles tested on Google Cloud data certifications is the distinction between local uploads and Cloud Storage staging.

Parameter Local File Upload Cloud Storage Staging ( gs:// )

Maximum File Size 100 MB per file in the console; one file at a time 15 TB per load job; 5 TB per uncompressed CSV or JSON file; 4 GB per gzip-compressed CSV or JSON file

  • Job Resilience Vulnerable to network drops & browser timeouts Highly resilient; managed retry on server side
  • Parallelism Single stream from client machine Multi-slot distributed parallel read
  • Wildcard Support No (single file selection) Yes ( gs://bucket/folder/*.parquet )
  • Production Suitability Ad-hoc testing only Mandatory enterprise production pattern

In enterprise architectures, source systems never upload directly from on-premises drives into BigQuery tables. Instead, pipelines implement a two-step pattern:

Upload or stream source files into a dedicated Google Cloud Storage landing or raw bucket (e.g., gs://landing-bucket-region/domain/year/month/day/ ).

Trigger a BigQuery load job pointing to the Cloud Storage URI. BigQuery's distributed workers read directly from Cloud Storage across Google's high-speed internal Jupiter network fabric, completing the load in a fraction of the time.

3. The Economics of Batch Loading: Zero Ingestion Cost

A fundamental financial and architectural concept in Google Cloud is that batch loading data into BigQuery is free of charge .

Important

BigQuery does not charge for the compute slots, CPU cycles, or memory utilized to execute batch load jobs. Whether you load 1 gigabyte or 50 terabytes of data via a bq load job or Data Transfer Service, the load job compute cost is exactly $0.00 .

What You Are Billed For:

Cloud Storage Storage Costs: While the data resides in Cloud Storage prior to ingestion, you pay standard GCS storage pricing (for example, about $0.020 per GB-month for Standard storage in us-central1 ).

Network Transfer / Egress: If your Cloud Storage bucket is located in a different region or multi-region than your BigQuery destination dataset (e.g., GCS bucket in us-central1 and BigQuery dataset in europe-west1 ), cross-region network egress charges apply. Loading data within the same region or multi-region incurs zero egress fees.

BigQuery At-Rest Storage: Once data is successfully loaded into the BigQuery table, it is billed according to BigQuery storage rates: Active Storage ($0.020 per GB/month for tables modified within 90 days) or Long-Term Storage ($0.010 per GB/month for tables or partitions untouched for 90 consecutive days).

4. Supported Source Formats & Splittability Mechanics

BigQuery supports six primary batch ingestion formats. Understanding their internal structures and compression behaviors directly impacts pipeline performance: +-----------------------------------------------------------------------------------+

  • | SUPPORTED SOURCE FORMATS |
  • +-----------------------------------------------------------------------------------+
  • | CSV | Plain text, tabular, requires delimiter parsing, unindexed |
  • | JSON (NDJSON) | Newline-delimited JSON only; 1 JSON object per line |
  • | Apache Avro | Row-oriented binary, self-describing, fast parallel split |
  • | Apache Parquet | Columnar binary, highly compressed, embedded statistics |
  • | Apache ORC | Optimized Row Columnar, high compression, Hive ecosystem |
  • | Datastore/Firestore| Specialized multi-file entity exports from Google NoSQL |
  • +-----------------------------------------------------------------------------------+
  • The Splittability Problem and the Compression Trap

When BigQuery loads a dataset, it assigns multiple background worker slots to process chunks of the file in parallel. However, a file format's physical layout determines whether it can be split across multiple workers:

Apache Avro & Parquet: These formats contain internal block sync markers and metadata footers. BigQuery can split a single 50 GB Parquet or Avro file into hundreds of distinct byte-range offsets, assigning each byte range to a separate slot for concurrent processing.

Uncompressed CSV and JSON: BigQuery can scan for newline characters and split uncompressed text files across parallel workers.

GZIP-Compressed CSV and JSON: GZIP compression uses a continuous sliding dictionary. A worker cannot decompress byte offset 1,000,000 without having decompressed all preceding bytes from byte 0 . Consequently, GZIP-compressed CSV and JSON files cannot be split . BigQuery is forced to allocate a single slot to sequentially decompress and parse the entire file. BigQuery also caps each gzip-compressed CSV or JSON file at 4 GB, so large exports must be split anyway. Google's guidance is that uncompressed files load significantly faster because they can be read in parallel, and Avro is preferred because its compressed blocks can still be read in parallel.

Tip

If you must ingest text files, prefer uncompressed files split into multiple 100 MB to 1 GB chunks, or compress them using splittable compression codecs like Snappy within container formats like Avro or Parquet.

5. Load Job Configurations & Operational Flags

When executing bq load or configuring a client library load job, specific flags govern schema resolution, data validation, and write behaviors:

Schema Handling: --autodetect vs. Explicit Schemas

--autodetect : When enabled, BigQuery samples up to the first 500 rows of the source file to infer field names and data types. While convenient for exploratory analysis, autodetection is an anti-pattern in production pipelines . If early rows contain null values or integer-like strings (e.g., zip codes like "02138" ), BigQuery may incorrectly infer INT64 instead of STRING , stripping leading zeros or crashing when subsequent alphanumeric strings appear.

Explicit Schemas: Production pipelines should supply an explicit JSON schema file or inline schema definition to guarantee strict type enforcement. # Loading with an explicit JSON schema definition

  • bq load \
  • -source_format=CSV \
  • -skip_leading_rows=1 \
  • -schema=/deploy/schemas/orders_schema.json \
  • retail_dw.orders \
  • gs://company-data-lake-prod/retail/orders/*.csv
  • Data Cleaning & Error Tolerance Flags

--skip_leading_rows=N : Instructs BigQuery to skip header rows at the beginning of CSV files (typically --skip_leading_rows=1 ).

--max_bad_records=N : Sets the maximum number of malformed or invalid rows BigQuery will silently skip before failing the entire load job. The default is 0 (any invalid row immediately aborts the transaction). Setting a non-zero threshold allows resilient ingestion of noisy external logs.

--ignore_unknown_values : By default, if a row contains more columns than defined in the target table schema, the load job fails. When --ignore_unknown_values is set to true , BigQuery ignores extra columns and loads the recognized fields.

Write Disposition Modes

The --write_disposition flag specifies what happens when the destination table already contains data:

WRITE_APPEND (Default): Appends newly loaded rows to the existing table. If the table does not exist, it is created.

WRITE_TRUNCATE (or --replace in bq CLI): Completely drops existing data in the target table or targeted partition and replaces it with the newly loaded rows. If loading into a partitioned table partition (e.g., orders$20261008 ), only that specific partition is truncated and replaced, leaving other partitions untouched.

WRITE_EMPTY : Guarantees that data is only loaded if the destination table is completely empty. If any rows exist, the job fails immediately, preventing accidental duplicate loads.

6. BigQuery Load Quotas and Operational Limits

To ensure multi-tenant stability and fair resource allocation, Google Cloud enforces specific quotas on batch load jobs:

Load jobs per table per day: 1,500 load jobs (including failed jobs). Exceeding this quota results in quotaExceeded errors. For high-frequency ingestion exceeding 1,500 batches daily, pipelines must aggregate data into fewer, larger files, or migrate to the BigQuery Storage Write API.

Load jobs per project per day: 100,000 load jobs.

Maximum row size: 100 MB per individual row for CSV and JSON source files.

Maximum load job size: 15 TB across all input files, for CSV, JSON, Avro, Parquet, and ORC alike (the limit does not apply to jobs running on a reservation).

Per-file limits: 4 GB for a gzip-compressed CSV or JSON file; 5 TB for an uncompressed CSV or JSON file.

Source URIs and files: One load job can list up to 10,000 source URIs and read up to 10 million files.