BigQuery SQL and pricing model in GCP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

How does BigQuery charge for storage?

BigQuery charges for data storage based on the amount of data stored. Which of the following best describes how BigQuery pricing works for storage?

A You pay a flat monthly fee regardless of data size.

B You pay per query regardless of data size or storage.

C You pay per gigabyte of data stored per month, with separate rates for active and long-term storage.

D You pay only for the data actively queried, not for stored data.

💡 Hint

Think about how cloud storage usually charges based on data size and duration.

BigQuery charges for storage based on the amount of data stored per month. There are different rates for active storage (recently modified data) and long-term storage (data not modified for 90 days), making option C correct.

🧠 Conceptual

intermediate

What is the output of this BigQuery SQL query?

Consider this BigQuery SQL query:

SELECT COUNT(*) AS total_rows FROM `bigquery-public-data.samples.shakespeare` WHERE word = 'cloud';

What does this query return?

A The number of rows where the word is exactly 'cloud'.

B The number of unique words in the dataset.

C The total number of rows in the Shakespeare dataset.

D An error because the table name is invalid.

💡 Hint

Look at the WHERE clause filtering on the word column.

The query counts rows where the word column equals 'cloud'. It does not count all rows or unique words, and the table name is valid.

❓ Architecture

advanced

Which BigQuery pricing model minimizes cost for frequent small queries?

You run many small queries daily on a dataset. Which BigQuery pricing model helps minimize your cost?

A Flat-rate pricing with a dedicated slot commitment.

B On-demand pricing, paying per query data scanned.

C Storage pricing only, no query charges.

D Paying only for data exported from BigQuery.

💡 Hint

Consider which pricing model offers predictable costs for frequent queries.

Flat-rate pricing lets you buy dedicated query capacity (slots) for predictable costs, which is better for frequent small queries than on-demand pricing.

❓ security

advanced

What happens if a user without permission tries to query a BigQuery table?

A user tries to run a query on a BigQuery table they do not have access to. What is the expected behavior?

A The query runs but returns empty results.

B The query runs and returns all data but logs a warning.

C The query runs but only returns metadata.

D The query fails with a permission denied error.

💡 Hint

Think about how access control works in cloud services.

BigQuery enforces access control strictly. Users without permission get a permission denied error and cannot run the query.

✅ Best Practice

expert

How to optimize BigQuery costs for large datasets with infrequent queries?

You have a large dataset in BigQuery that is queried infrequently. Which approach best optimizes cost?

A Keep all data in active storage and use on-demand queries.

B Partition the table and use long-term storage for older partitions.

C Export data to Cloud Storage and query from there only.

D Use flat-rate pricing with maximum slot commitment.

💡 Hint

Think about storage costs and query efficiency for large datasets.

Partitioning tables and relying on long-term storage for older data reduces storage costs and improves query efficiency for infrequent queries.

Practice

1. What does BigQuery charge you for when you run a SQL query?

easy

A. The amount of data scanned by the query

B. The number of SQL commands used

C. The time it takes to write the query

D. The number of tables in your project

Solution

1. Step 1: Understand BigQuery pricing basics

   BigQuery charges based on how much data your query scans, not on commands or tables.

2. Step 2: Identify the correct pricing factor

   The cost depends on the volume of data scanned during query execution.

3. Final Answer:

   The amount of data scanned by the query -> Option A

4. Quick Check:

   Data scanned = cost [OK]

Hint: Remember: BigQuery bills by data scanned, not query length [OK]

Common Mistakes:

• Thinking cost depends on query complexity
• Assuming storage size affects query cost directly
• Confusing number of tables with cost

2. Which of the following is the correct way to write a simple BigQuery SQL SELECT statement?

easy

A. SELECT * FROM dataset.table;

B. GET * FROM dataset.table;

C. FETCH ALL FROM dataset.table;

D. READ * dataset.table;

Solution

1. Step 1: Recall BigQuery SQL syntax

   BigQuery uses standard SQL syntax where SELECT is used to retrieve data.

2. Step 2: Identify the correct syntax

   Only SELECT * FROM dataset.table; uses the correct SELECT statement format with FROM clause.

3. Final Answer:

   SELECT * FROM dataset.table; -> Option A

4. Quick Check:

   SELECT + FROM = correct query [OK]

Hint: SELECT is the keyword to get data in SQL [OK]

Common Mistakes:

• Using GET or FETCH instead of SELECT
• Omitting FROM keyword
• Incorrect command verbs

3. Consider this query:
SELECT COUNT(*) FROM `project.dataset.sales` WHERE sale_date >= '2023-01-01';
What will this query return?

medium

A. A list of all sales records from 2023-01-01 onwards

B. The sum of all sales amounts after 2023-01-01

C. The total number of sales records from 2023-01-01 onwards

D. An error because of incorrect date format

Solution

1. Step 1: Analyze the SELECT clause

   COUNT(*) counts the number of rows matching the condition.

2. Step 2: Understand the WHERE clause

   It filters records with sale_date on or after 2023-01-01.

3. Final Answer:

   The total number of sales records from 2023-01-01 onwards -> Option C

4. Quick Check:

   COUNT(*) + WHERE = count filtered rows [OK]

Hint: COUNT(*) counts rows, not sums values [OK]

Common Mistakes:

• Confusing COUNT with SUM
• Thinking it returns all records instead of count
• Assuming date format causes error

4. You wrote this query but it fails:
SELECT * FROM dataset.table WHERE date = 2023-01-01;
What is the error and how to fix it?

medium

A. Missing semicolon; add ; at end

B. Date value missing quotes; fix with WHERE date = '2023-01-01'

C. Table name incorrect; use backticks around table

D. SELECT * not allowed; specify columns explicitly

Solution

1. Step 1: Identify the error in WHERE clause

   Date literals must be in quotes in SQL; missing quotes cause syntax error.

2. Step 2: Correct the date format

   Enclose date in single quotes: '2023-01-01' to fix the query.

3. Final Answer:

   Date value missing quotes; fix with WHERE date = '2023-01-01' -> Option B

4. Quick Check:

   Date literals need quotes [OK]

Hint: Always quote date strings in SQL WHERE clauses [OK]

Common Mistakes:

• Forgetting quotes around dates
• Assuming semicolon fixes syntax
• Thinking SELECT * is invalid

5. You want to reduce BigQuery costs by limiting data scanned. Which approach is best?

hard

A. Use SELECT * to get all columns for flexibility

B. Run queries without filters to scan all data quickly

C. Store data in multiple small tables instead of one large table

D. Use partitioned tables and filter on partition column in queries

Solution

1. Step 1: Understand cost drivers in BigQuery

   Costs depend on data scanned; scanning less data reduces cost.

2. Step 2: Identify cost-saving technique

   Partitioning tables lets queries scan only relevant partitions, saving cost.

3. Final Answer:

   Use partitioned tables and filter on partition column in queries -> Option D

4. Quick Check:

   Partition + filter = less data scanned [OK]

Hint: Partition tables and filter to scan less data [OK]

Common Mistakes:

• Thinking scanning all data is cheaper
• Splitting tables without partitioning helps cost
• Using SELECT * increases scanned data