A shipping company has live package-tracking data that is sent to an Apache Kafka stream in real time. This is then loaded into BigQuery. Analysts in…

The analysts are querying geospatial trends in the lifecycle of a package, which means their queries will frequently filter by a specific packagetrackingID. The current table is partitioned by ingest date, which forces BigQuery to scan the entirety of each relevant date partition to find all events for a single package. By implementing clustering on the packagetrackingID column, BigQuery will physically co-locate all data for the same package within each partition. This allows the query engine to prune the blocks it needs to scan, significantly reducing query processing time and cost for these common analytical queries.

A. Clustering on the ingest date column provides minimal benefit because the table is already partitioned by that same column, which already segregates the data.

C. Tiering data to Cloud Storage and using external tables is a cost-optimization strategy for cold data, not a performance enhancement for frequently accessed data.

D. Re-partitioning by delivery date is ineffective for analyzing the full package lifecycle, as many records for in-transit packages would have a NULL delivery date.

1. Google Cloud Documentation, "Introduction to clustered tables": "When you submit a query that filters by a clustered column, BigQuery uses the sorted blocks to eliminate scans of unnecessary data... Use clustering for columns that are frequently used in query filters... especially columns with high cardinality (many distinct values)." The package-trackingID is a high-cardinality column frequently used for filtering, making it an ideal candidate for clustering. (Source: Google Cloud, Introduction to clustered tables, https://cloud.google.com/bigquery/docs/clustered-tables#whentouseclustering)

2. Google Cloud Documentation, "Introduction to partitioned tables": This document explains that partitioning and clustering can be used together. "For example, if you partition your table by date and cluster it by customerid, each partition will contain data for that date, sorted by customerid." This directly maps to the scenario of partitioning by ingest date and clustering by package-trackingID to optimize queries. (Source: Google Cloud, Introduction to partitioned tables, https://cloud.google.com/bigquery/docs/partitioned-tables#combinepartitioningandclustering)

3. Google Cloud Blog, "BigQuery explained: An overview of BigQuery's architecture": This article discusses the separation of storage and compute. "Colossus... organizes data in a columnar format... When you run a query, the BigQuery query engine reads only the columns it needs... Clustering tables can further improve query performance by sorting the data based on the contents of one or more columns in the table’s schema." This confirms that clustering physically organizes data to improve scan efficiency. (Source: Google Cloud Blog, BigQuery explained: An overview of BigQuery's architecture, https://cloud.google.com/blog/products/bigquery/bigquery-explained-an-overview-of-bigquerys-architecture)