You have three years of historical data in BigQuery and a daily ingest. Queries filtered to the last 30–90 days still scan the entire table, increasing cost. What is the most cost-effective fix while keeping SQL access?

Partitioning BigQuery tables by a TIMESTAMP or DATE column allows queries to scan only the relevant partitions, significantly reducing the amount of data processed and thus lowering costs. This method maintains direct SQL access. Exporting to CSV and using Cloud Datalab (now deprecated in favor of AI Platform Notebooks) removes direct SQL access and introduces data management overhead. Separating data into two tables requires modifying application logic to query both tables, adding complexity. Creating a BigQuery table per day using Apache Beam and wildcards is a form of sharding, which can be effective but is more complex to manage than native partitioning and can lead to a large number of tables, potentially complicating schema evolution and access control.