BigQuery SQL and pricing model in GCP - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is BigQuery in Google Cloud Platform?

BigQuery is a fully managed data warehouse service that lets you run fast SQL queries on large datasets without managing infrastructure.

beginner

How does BigQuery charge for queries?

BigQuery charges based on the amount of data processed by your SQL queries, measured in bytes scanned. You pay only for the data your queries scan.

intermediate

What is the difference between on-demand and flat-rate pricing in BigQuery?

On-demand pricing charges per query based on data scanned. Flat-rate pricing lets you buy dedicated query capacity for a fixed monthly fee, good for predictable workloads.

beginner

What SQL dialect does BigQuery use?

BigQuery uses a standard SQL dialect that is ANSI-compliant with some extensions for analytics and nested data.

intermediate

How can you reduce BigQuery query costs?

You can reduce costs by selecting only needed columns, filtering data early, using partitioned tables, and caching query results.

What does BigQuery charge you for when running SQL queries?

A The number of rows returned

B The amount of data scanned by the query

C The number of queries run

D The storage size of the dataset

BigQuery charges based on the volume of data scanned during query execution, not on the number of queries or rows returned.

Which pricing model is best if you have predictable, heavy query workloads in BigQuery?

A Pay-as-you-go storage

B On-demand pricing

C Flat-rate pricing

D Free tier only

Flat-rate pricing allows you to buy dedicated query capacity for a fixed fee, which is cost-effective for steady, heavy workloads.

What SQL dialect does BigQuery use?

A Standard SQL with extensions

B PostgreSQL dialect

C NoSQL query language

D MySQL dialect

BigQuery uses standard SQL that is ANSI-compliant with some additional features for analytics.

Which of these is NOT a way to reduce BigQuery query costs?

A Selecting only needed columns

B Filtering data early in the query

C Using partitioned tables

D Running queries without filters on entire tables

Running queries without filters scans more data and increases costs; filtering early helps reduce data scanned.

What is the free tier limit for BigQuery query data processed per month?

A 1 TB

B 10 GB

C 100 GB

D No free tier

BigQuery offers 1 TB of free query data processed per month under its free tier.

Explain how BigQuery pricing works and how you can manage costs effectively.

Think about what affects the amount of data BigQuery reads during queries.

Describe the SQL dialect used in BigQuery and why it is suitable for analytics.

Consider how BigQuery handles complex data and analytics.

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