Question

A data science team at a logistics company needs to explore three years of shipment event logs stored as newline-delimited JSON files in a Cloud Storage bucket (roughly 40 TB, organized in Hive-style date directories like /year=2023/month=05/). Analysts want to run ad hoc exploratory SQL in BigQuery to profile the data before committing to any modeling approach, but leadership is worried about query costs and does not want to duplicate the raw data into BigQuery-managed storage yet. Which approach best supports low-cost, iterative exploration?

Accepted Answer

Create a BigQuery external table over the Cloud Storage files with Hive partitioning enabled, and have analysts filter on the partition columns in their exploratory queries.

Answer

Load all 40 TB into a native BigQuery table with ingestion-time partitioning so analysts can query without touching Cloud Storage.

Answer

Create a non-partitioned external table over the JSON files and instruct analysts to add LIMIT clauses to reduce the amount of data scanned.

Answer

Use a Dataflow pipeline to convert the JSON to Parquet in Cloud Storage first, then query each file individually through the BigQuery Storage Read API.