BigQuery for analytics in GCP - Step-by-Step Execution

Process Flow - BigQuery for analytics

Data arrives in storage

↓

Load data into BigQuery

↓

Write SQL query

↓

BigQuery processes query

↓

Results returned

↓

Analyze or visualize data

Data is loaded into BigQuery, then SQL queries run to analyze it, and results are returned for insights.

Execution Sample

GCP

SELECT
  country,
  COUNT(*) AS user_count
FROM
  `project.dataset.users`
WHERE
  signup_date >= '2024-01-01'
GROUP BY
  country
ORDER BY
  user_count DESC
LIMIT 5;

This query counts users by country who signed up after Jan 1, 2024, showing top 5 countries.

Process Table

Step	Action	Evaluation	Result
1	Scan table `users`	Filter signup_date >= '2024-01-01'	Rows matching date filter selected
2	Group rows by country	Aggregate count per country	Counts calculated per country
3	Order results	Sort by user_count descending	Countries sorted by user count
4	Limit results	Take top 5 rows	Top 5 countries selected
5	Return results	Send data to user	Query results delivered

💡 Query completes after returning top 5 countries with user counts.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Filtered Rows	0	Rows with signup_date >= '2024-01-01'	Grouped by country	Sorted by user_count desc	Top 5 rows	Final result set

Key Moments - 2 Insights

Why does BigQuery scan the entire table even if we only want 5 results?

Because the filter and grouping happen before limiting results, BigQuery must process all matching rows to find the top 5.

What happens if the date filter is missing?

BigQuery will process all rows in the table, which can be slower and cost more, as shown in Step 1 of the execution table.

Visual Quiz - 3 Questions

Look at the execution table, what is the result after Step 2?

A Rows filtered by signup_date

B Top 5 countries selected

C Counts calculated per country

D Results sent to user

💡 Hint

Check the 'Result' column in Step 2 of the execution table.

At which step does BigQuery sort the countries by user count?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the 'Action' column for sorting in the execution table.

If we remove the WHERE clause, how does the 'Filtered Rows' variable change after Step 1?

A It stays the same

B It includes all rows in the table

C It becomes zero

D It only includes rows after 2024-01-01

💡 Hint

Refer to the variable_tracker and key moment about filtering.

Concept Snapshot

BigQuery for analytics:
- Load data into BigQuery storage
- Use SQL queries to filter, group, and sort data
- BigQuery processes entire filtered data before limiting
- Results returned for analysis or visualization
- Efficient for large datasets with pay-per-query pricing

Full Transcript

BigQuery is a cloud service for analyzing large datasets using SQL. Data is loaded into BigQuery storage. When you run a query, BigQuery scans the data, applies filters like dates, groups data by fields such as country, sorts the results, and then limits the output to the top rows requested. This process ensures accurate results but means BigQuery processes all matching data before limiting. Understanding each step helps optimize queries and control costs.

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