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.

Practice

1. What is the main purpose of Google BigQuery in data analytics?

easy

A. To run fast SQL queries on large datasets without managing servers

B. To store files like images and videos

C. To create virtual machines for computing

D. To send emails automatically

Solution

1. Step 1: Understand BigQuery's role

   BigQuery is designed to analyze large amounts of data quickly using SQL without needing to manage servers.

2. Step 2: Compare options

   Options A, C, and D describe other services unrelated to BigQuery's main function.

3. Final Answer:

   To run fast SQL queries on large datasets without managing servers -> Option A

4. Quick Check:

   BigQuery = fast SQL analytics [OK]

Hint: BigQuery = fast SQL on big data, no servers needed [OK]

Common Mistakes:

• Confusing BigQuery with storage or compute services
• Thinking BigQuery manages virtual machines
• Assuming BigQuery sends emails

2. Which of the following is the correct syntax to select all columns from a table named sales_data in BigQuery?

easy

A. FETCH * sales_data;

B. GET ALL FROM sales_data;

C. SELECT * FROM sales_data;

D. SELECT ALL COLUMNS sales_data;

Solution

1. Step 1: Recall SQL SELECT syntax

   The correct SQL syntax to select all columns is SELECT * FROM table_name;.

2. Step 2: Check each option

   Only SELECT * FROM sales_data; matches the correct SQL syntax. Options A, B, and D use incorrect keywords or structure.

3. Final Answer:

   SELECT * FROM sales_data; -> Option C

4. Quick Check:

   SELECT * FROM table = correct syntax [OK]

Hint: Use SELECT * FROM table to get all columns [OK]

Common Mistakes:

• Using wrong keywords like GET or FETCH
• Omitting FROM keyword
• Writing SELECT ALL COLUMNS instead of SELECT *

3. Given the table orders with columns order_id, customer_id, and amount, what will this query return?

SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id ORDER BY total_spent DESC LIMIT 2;

medium

A. All orders sorted by amount

B. Top 2 customers who spent the most money

C. Total amount spent by all customers combined

D. First 2 orders by order_id

Solution

1. Step 1: Understand the query components

   The query groups orders by customer_id, sums the amount per customer, orders results by total spent descending, and limits output to 2 rows.

2. Step 2: Interpret the output

   This returns the top 2 customers who spent the most money.

3. Final Answer:

   Top 2 customers who spent the most money -> Option B

4. Quick Check:

   GROUP BY customer_id + SUM + ORDER BY DESC + LIMIT 2 = top 2 spenders [OK]

Hint: GROUP BY + SUM + ORDER BY DESC + LIMIT = top results [OK]

Common Mistakes:

• Thinking it returns all orders instead of grouped data
• Confusing LIMIT with total sum
• Ignoring ORDER BY direction

4. You wrote this query but get an error:

SELECT customer_id, amount FROM orders WHERE SUM(amount) > 100;

What is the problem?

medium

A. SELECT must include all columns in WHERE

B. Missing GROUP BY clause for customer_id

C. amount column does not exist

D. SUM() cannot be used in WHERE clause; use HAVING instead

Solution

1. Step 1: Identify misuse of SUM() in WHERE

   Aggregate functions like SUM() cannot be used in WHERE; they belong in HAVING clause.

2. Step 2: Correct usage

   Replace WHERE SUM(amount) > 100 with HAVING SUM(amount) > 100 and add GROUP BY customer_id.

3. Final Answer:

   SUM() cannot be used in WHERE clause; use HAVING instead -> Option D

4. Quick Check:

   Aggregates in HAVING, not WHERE [OK]

Hint: Use HAVING for conditions on aggregates, not WHERE [OK]

Common Mistakes:

• Using SUM() in WHERE clause
• Forgetting GROUP BY with aggregates
• Assuming WHERE filters aggregates

5. You want to find the average order amount per customer but only for customers who placed more than 5 orders. Which query correctly achieves this in BigQuery?

hard

A. SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 5;

B. SELECT customer_id, AVG(amount) AS avg_amount FROM orders WHERE COUNT(order_id) > 5 GROUP BY customer_id;

C. SELECT customer_id, AVG(amount) AS avg_amount FROM orders WHERE order_count > 5;

D. SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id WHERE COUNT(order_id) > 5;

Solution

1. Step 1: Understand filtering on aggregate

   To filter groups by count, use HAVING clause after GROUP BY.

2. Step 2: Check each option's correctness

   SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 5; uses GROUP BY customer_id and HAVING COUNT(order_id) > 5 correctly. Options A, B, and C misuse WHERE or clause order.

3. Final Answer:

   SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 5; -> Option A

4. Quick Check:

   HAVING filters groups, WHERE filters rows [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE to filter aggregates
• Placing WHERE after GROUP BY
• Referencing undefined columns like order_count