GCP Data Analysis & Reporting 3 — Questions and Answers
Question 1: What is the primary advantage of using BigQuery's BI Engine?
- It allows BigQuery to run Spark jobs natively
- It provides in-memory analysis for sub-second query response times in BI tools (Correct answer)
- It automatically partitions all tables by date
- It enables cross-region table replication
Correct answer: It provides in-memory analysis for sub-second query response times in BI tools
BI Engine is an in-memory analysis service that accelerates BigQuery queries for BI tools like Looker Studio, delivering sub-second response times.
Question 2: You want to identify the top 5 products by revenue in a BigQuery query. Which SQL clause should you use?
- WHERE revenue > 5
- GROUP BY product LIMIT 5
- ORDER BY revenue DESC LIMIT 5 (Correct answer)
- HAVING COUNT(*) > 5
Correct answer: ORDER BY revenue DESC LIMIT 5
ORDER BY revenue DESC sorts results from highest to lowest revenue, and LIMIT 5 returns only the top 5 rows.
Question 3: In Cloud Dataflow, what is a 'PCollection'?
- A persistent storage bucket for pipeline outputs
- An immutable distributed dataset that represents pipeline data (Correct answer)
- A configuration file for pipeline parameters
- A monitoring dashboard for pipeline metrics
Correct answer: An immutable distributed dataset that represents pipeline data
A PCollection is the fundamental data abstraction in Apache Beam/Dataflow — an immutable, distributed collection of elements processed by the pipeline.
Question 4: Which BigQuery function would you use to calculate a 7-day moving average of daily sales?
- ARRAY_AGG
- AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) (Correct answer)
- COUNTIF
- DATE_DIFF
Correct answer: AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Window functions with AVG() and a frame clause (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) compute a rolling average over the specified window.
Question 5: What is the role of Cloud Pub/Sub in a data analytics pipeline?
- It stores structured analytical data for SQL queries
- It acts as a message queue to ingest and buffer streaming data (Correct answer)
- It transforms and aggregates data before loading to BigQuery
- It visualizes real-time metrics on dashboards
Correct answer: It acts as a message queue to ingest and buffer streaming data
Pub/Sub is a messaging service that decouples data producers from consumers, buffering streaming events before downstream processing by Dataflow or other services.
Question 6: In Looker Studio, what does 'blending' data sources allow you to do?
- Apply machine learning models to your data
- Combine data from multiple sources into a single chart (Correct answer)
- Export data to BigQuery automatically
- Schedule email delivery of reports
Correct answer: Combine data from multiple sources into a single chart
Data blending in Looker Studio joins data from different sources (e.g., Google Analytics + BigQuery) within a single visualization using a common join key.
Question 7: Which BigQuery pricing model charges based on the amount of data processed by each query?
- Flat-rate pricing
- On-demand pricing (Correct answer)
- Committed use pricing
- Sustained use pricing
Correct answer: On-demand pricing
On-demand pricing in BigQuery charges $5 per TB of data processed by queries, making query optimization important for cost control.
GCP Data Analysis & Reporting 3
What is the primary advantage of using BigQuery's BI Engine?