Professional Data Engineer

By Google · Question Mode

QuestionQ42

Preparing and using data for analysis

You have a query that filters a BigQuery table with a WHERE clause on timestamp and ID columns. By using bq query "-dry_run", you learn that the query causes a full table scan, although the timestamp and ID filters select only a tiny fraction of the total data. You want to reduce the amount of data BigQuery scans with minimal changes to the existing SQL queries. What should you do?

Explanation

Partitioning the table by the timestamp column enables partition pruning for timestamp filters, and clustering by ID enables block pruning for ID filters within those partitions. Together, these table properties reduce the bytes BigQuery scans while allowing the existing WHERE filters to remain essentially unchanged.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!