BigQuery for analytics in GCP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding BigQuery Query Pricing

You run a query in BigQuery that scans 10 GB of data. Your project has a flat-rate pricing plan of 200 slots. What will be the cost behavior for this query?

A The query cost is fixed because of the flat-rate plan, so no additional cost for scanning 10 GB.

B The query cost depends on the amount of data scanned, so it will be charged for 10 GB scanned.

C The query cost depends on the number of slots used, so it will be charged per slot regardless of data scanned.

D The query cost is free for queries under 10 GB scanned per month.

💡 Hint

Think about how flat-rate pricing works compared to on-demand pricing in BigQuery.

With flat-rate pricing, you pay a fixed monthly fee for a set number of slots. Queries use these slots without additional cost per data scanned. So scanning 10 GB does not add extra cost beyond the flat rate.

❓ Architecture

intermediate

Choosing Partitioning Strategy for BigQuery Tables

You have a large dataset with sales data including a sale_date column. You want to optimize query performance and cost for queries filtering by date ranges. Which partitioning strategy should you choose?

A Do not partition the table; use clustering on <code>sale_date</code> instead.

B Partition the table by <code>sale_date</code> using ingestion time partitioning.

C Partition the table by <code>customer_id</code> using integer range partitioning.

D Partition the table by <code>sale_date</code> using column-based partitioning.

💡 Hint

Consider which partitioning method allows filtering by an existing date column efficiently.

Column-based partitioning on sale_date allows queries filtering by date ranges to scan only relevant partitions, improving performance and reducing cost. Ingestion time partitioning partitions by load time, which may not align with sale_date.

❓ security

advanced

BigQuery Data Access Control

You want to restrict access so that a user can only query a specific dataset but cannot see other datasets in the project. Which IAM role and permission setup achieves this?

A Grant the user the <code>roles/bigquery.admin</code> role on the project.

B Grant the user the <code>roles/bigquery.user</code> role at the project level.

C Grant the user the <code>roles/bigquery.dataViewer</code> role on the specific dataset only.

D Grant the user the <code>roles/viewer</code> role on the project.

💡 Hint

Think about least privilege and dataset-level permissions.

Granting roles/bigquery.dataViewer on the dataset limits the user to read data only in that dataset. Other roles at project level grant broader access.

✅ Best Practice

advanced

Optimizing BigQuery Table Design for Frequent Small Queries

Your application runs many small queries filtering on user ID and timestamp. Which table design improves query speed and reduces cost?

A Partition the table by timestamp and cluster by user ID.

B Partition the table by user ID and cluster by timestamp.

C Do not partition; cluster by user ID and timestamp.

D Partition the table by ingestion time and cluster by user ID and timestamp.

💡 Hint

Consider which column is best for partitioning to reduce scanned data for time-based filters.

Partitioning by timestamp reduces scanned data for time filters. Clustering by user ID improves filtering on user ID within partitions, speeding queries and lowering cost.

🧠 Conceptual

expert

BigQuery Slot Allocation Behavior

You have a BigQuery reservation with 100 slots shared by multiple projects. If one project runs a large query using 80 slots and another project runs a small query, how does BigQuery allocate slots to the small query?

A The small query waits until slots free up because the large query uses most slots.

B The small query gets a minimum guaranteed number of slots reserved for it.

C The small query can use any free slots immediately, even if the large query is running.

D The small query is canceled automatically if slots are not available.

💡 Hint

Think about slot reservations and guarantees in BigQuery.

BigQuery reservations can guarantee minimum slots per project. Even if one project uses many slots, others get their guaranteed minimum slots to run queries without waiting.