How would you query specific partitions in a BigQuery table?

To query specific partitions in a BigQuery ingestion-time partitioned table, you must use a pseudo-column in the WHERE clause. The PARTITIONTIME pseudo-column contains the UTC timestamp for the start of the partition. By filtering on PARTITIONTIME (or the simpler PARTITIONDATE for daily partitions), you instruct BigQuery to scan only the relevant partitions. This process, known as partition pruning, significantly reduces the amount of data scanned, which lowers query costs and improves performance.

A. Using a column named DAY only works if the table is partitioned on a time-unit column with that specific name, which is not the general or required method for ingestion-time partitioning.
B. Applying functions like EXTRACT() on a partitioning column can prevent BigQuery's query optimizer from being able to prune partitions, leading to a full table scan and higher costs.
D. DATE BETWEEN is a condition used within a WHERE clause. While it can be used on a time-unit partitioning column to prune partitions, it is not the specific pseudo-column mechanism for ingestion-time partitioning.

1. Google Cloud Documentation, "Querying partitioned tables": "To filter queries on ingestion-time partitioned tables, use the PARTITIONTIME pseudo-column... When you filter on PARTITIONTIME, you can use a date or timestamp function to specify the partition." This document explicitly details the use of PARTITIONTIME for partition pruning.

2. Google Cloud Documentation, "Introduction to partitioned tables": "BigQuery provides two pseudo columns in ingestion-time partitioned tables: PARTITIONTIME... and PARTITIONDATE... To prune partitions, filter on these pseudo columns in your query." This source reinforces that PARTITIONTIME is the designated mechanism.

3. UC Berkeley, Data 101: Data Engineering, Lecture 12: BigQuery: In the section on "Partitioned Tables," the course material explains: "For ingestion time partitioned tables, use the pseudo column PARTITIONTIME to filter partitions." This confirms the concept in an academic context. (Reference to specific slide/page number varies by semester, but the concept is consistently taught in this course module).