You are designing storage for very large text files for a data pipeline on Google Cloud. You want to support ANSI SQL queries. You also want to support compression and parallel load from the input…

For large text files requiring ANSI SQL queries and parallel loading, transforming to compressed Avro and storing directly in BigQuery is the Google-recommended approach.

Submitted by thandi_sa· Mar 30, 2026Designing data processing systems

Question

You are designing storage for very large text files for a data pipeline on Google Cloud. You want to support ANSI SQL queries. You also want to support compression and parallel load from the input locations using Google recommended practices. What should you do?

Options

ATransform text files to compressed Avro using Cloud Dataflow. Use BigQuery for storage and query.
BTransform text files to compressed Avro using Cloud Dataflow. Use Cloud Storage and BigQuery permanent linked tables for query.
CCompress text files to gzip using the Grid Computing Tools. Use BigQuery for storage and query.
DCompress text files to gzip using the Grid Computing Tools. Use Cloud Storage, and then import into Cloud Bigtable for query.

Why each option

For large text files requiring ANSI SQL queries and parallel loading, transforming to compressed Avro and storing directly in BigQuery is the Google-recommended approach.

ATransform text files to compressed Avro using Cloud Dataflow. Use BigQuery for storage and query.

BTransform text files to compressed Avro using Cloud Dataflow. Use Cloud Storage and BigQuery permanent linked tables for query.

Using Cloud Storage with permanent linked external tables adds configuration complexity and query performance overhead compared to loading data natively into BigQuery storage, making it a suboptimal pattern for this use case.

CCompress text files to gzip using the Grid Computing Tools. Use BigQuery for storage and query.

gzip is a non-splittable compression format, meaning BigQuery cannot load gzip-compressed files in parallel; this directly violates the requirement to support parallel loading using Google recommended practices.

DCompress text files to gzip using the Grid Computing Tools. Use Cloud Storage, and then import into Cloud Bigtable for query.

Cloud Bigtable is a NoSQL wide-column store optimized for high-throughput key-value operations and does not support ANSI SQL queries, making it unsuitable for the SQL analytics workload described.

Concept tested: BigQuery parallel ingestion with Avro format via Cloud Dataflow

Source: https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro

Topics

#Avro format#BigQuery storage#Cloud Dataflow#data compression

Community Discussion

No community discussion yet for this question.

Full PROFESSIONAL-DATA-ENGINEER Practice