Low-Cost BigQuery Ingestion from Cloud Storage

PDE By islever Editorial Last updated

Selecting storage systems. Planning the data pipelines.

Short answer

B Use the BigQuery Data Transfer Service from Cloud Storage to automate recurring, free-of-charge ingest of compressed JSON files into BigQuery storage, avoiding query scan costs associated with external tables.

Question

You need to load a dataset with multiple terabytes of clickstream data into BigQuery. The data arrives each day as compressed JSON files in a Cloud Storage bucket. You need a low-cost, programmatic, and scalable solution to load the data into BigQuery. What should you do?
  1. Create an external table in BigQuery pointing to the Cloud Storage bucket and run the INSERT INTO ... SELECT * FROM external_table command.
  2. Use the BigQuery Data Transfer Service from Cloud Storage. Correct Answer
  3. Create a Cloud Run function to run a Python script to read and parse each JSON file, and use the BigQuery streaming insert API.
  4. Use Cloud Data Fusion to create a pipeline to load the JSON files into BigQuery.

Key Takeaways

The main trap is confusing 'external tables' (which allow querying but charge per scan) with 'loaded tables' (which require upfront ingestion but have no ongoing query costs for the stored data). Free load jobs are the key to low-cost storage.

This question tests the most cost-effective method for loading large datasets into BigQuery from Cloud Storage. The correct solution leverages the BigQuery Data Transfer Service to ensure free, automated, and scalable ingestion without incurring query scanning fees.

Common mistake: Candidates often choose Option A (External Tables) because they interpret 'programmatic' as needing SQL queries or assume external tables are sufficient for analysis. However, external tables incur per-query scanning costs, making them high-cost for frequent access to terabyte-scale data, violating the 'low-cost' requirement.

Expert Analysis

The core requirement is to load terabytes of data into BigQuery storage in a low-cost, programmatic, and scalable manner. The key differentiator here is cost optimization for the ingestion process itself.

Option A suggests creating an external table and running INSERT INTO... SELECT. While this is programmatic, it violates the 'low-cost' constraint. Every time the query runs, BigQuery charges for the data scanned (query pricing). For multiple terabytes of daily data, these recurring scan costs are significantly higher than a one-time load. Additionally, external tables do not store the data in BigQuery's native columnar format, which limits performance and functionality compared to loaded data.

Option C proposes using Cloud Run with the streaming insert API. This is incorrect because the streaming API is designed for low-latency, real-time inserts (typically small volumes) and carries a premium price per GB ingested. It is neither low-cost nor optimal for batch processing terabytes of data. It also adds operational overhead for parsing JSON manually.

Option D involves Cloud Data Fusion. While powerful, this is a managed integration tool that incurs licensing and compute costs. For a simple bulk load from Cloud Storage, this is over-engineered and more expensive than native BigQuery capabilities.

Option B, using the BigQuery Data Transfer Service (DTS), is the correct choice. DTS automates recurring loads from Cloud Storage into BigQuery. Crucially, load jobs performed via DTS (or manual load jobs) are free; you only pay for the storage used in BigQuery. This satisfies the 'low-cost' requirement by eliminating query scan fees or streaming fees. It is highly scalable, handles compressed JSON natively, and can be configured programmatically via the Google Cloud Console or APIs, meeting all constraints.

Official Reference

https://cloud.google.com/bigquery/docs/batch-loading-data

This link opens the official vendor documentation in a new tab.

Exam Strategy

When a question emphasizes 'low-cost' or 'cost-effective' for data loading, always prioritize methods that avoid recurring compute charges like query scanning or streaming inserts. Look for native batch loading mechanisms (like Load Jobs or Data Transfer Service) which are free to execute, charging only for storage. Avoid solutions that imply repeated computation on large datasets.

Frequently Asked Questions

Why is Option A (External Table) considered high-cost?

External tables do not store data in BigQuery. Every time you query them, you pay for the amount of data scanned. For terabytes of daily data, these recurring query costs add up quickly, whereas loading the data once is free.

Is BigQuery Data Transfer Service free?

Yes, the service itself does not charge for the transfer job. You only pay for the storage consumed in BigQuery after the data is loaded. This makes it significantly cheaper than streaming inserts or repeated queries.

More PDE FAQ →

Community Votes

B
67%
A
33%

67% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 6 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 6

  1. Mohamed_Tareq 👍 2 Answered A
    Between A & B, I will Go for A to fulfill the question requirement, the solution should be programmatic
  2. hola_prep 👍 2 Answered A
    why option A is correct Low-cost Scalable Programmatic
  3. Divya_Naidu 👍 1 Answered B
    For Transferring the data from Cloud Storage to Big Query, The Big Query Transfer service will work apt. BigQuery Data Transfer Service provides a managed, scalable way to schedule recurring loads from Cloud Storage into BigQuery. It can be configured programmatically and supports newline-delimited JSON files, including compressed files. It is preferable because: BigQuery batch loading is more cost-effective than streaming inserts. Daily transfers can be automated. Google manages scaling for terabytes of input files. No custom parsing infrastructure is required.
  4. 06bbd0e 👍 1 Answered B
    In BigQuery, load jobs are free. When you use the Data Transfer Service (DTS) or manual load jobs to move data from Cloud Storage into BigQuery storage, you do not pay for the ingest processing. Option A is a "Query," which costs money per terabyte scanned.
  5. NickForDiscussions 👍 1 Answered B
    Should be B. When you use BigQuery Data Transfer Service you don't pay for the load job, you only pay for the storage in BigQuery. If you go with Option A you are paying for the query plus the storage.
Show 1 more comment
  1. Andy_Khan1 👍 1 Answered B
    B (BigQuery Data Transfer Service): Correct. DTS provides automated, scheduled, and free native batch ingestion from Cloud Storage to BigQuery.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

Ensuring Consistency in BigQuery Streaming Analytics

S-grade

← Back to PDE Study Guide