GCP Data Analysis & Reporting 4 — Questions and Answers
Question 1: You need to run a machine learning model directly on data stored in BigQuery without exporting it. Which feature enables this?
- BigQuery Data Transfer Service
- BigQuery ML (BQML) (Correct answer)
- Cloud AutoML
- Vertex AI Pipelines
Correct answer: BigQuery ML (BQML)
BigQuery ML allows you to create and execute machine learning models using SQL directly within BigQuery, eliminating the need to move data.
Question 2: What does the UNNEST() function do in BigQuery SQL?
- Removes duplicate rows from a result set
- Flattens an array column into individual rows (Correct answer)
- Converts a nested struct into a flat table
- Joins two tables on a common key
Correct answer: Flattens an array column into individual rows
UNNEST() converts an ARRAY into a set of rows, allowing you to query individual elements of array-type columns in BigQuery.
Question 3: Which GCP service would you use to orchestrate a multi-step data pipeline that includes BigQuery jobs, Dataflow jobs, and Cloud Storage operations?
- Cloud Scheduler
- Cloud Composer (Apache Airflow) (Correct answer)
- Cloud Functions
- Cloud Run
Correct answer: Cloud Composer (Apache Airflow)
Cloud Composer is a managed Apache Airflow service that orchestrates complex workflows across multiple GCP services with dependency management.
Question 4: In BigQuery, what is the difference between a clustered table and a partitioned table?
- Clustered tables store data in GCS; partitioned tables store data in Bigtable
- Partitioning divides data by a column value; clustering sorts data within partitions by specified columns (Correct answer)
- Clustering is for streaming data; partitioning is for batch data
- Partitioned tables support DML; clustered tables do not
Correct answer: Partitioning divides data by a column value; clustering sorts data within partitions by specified columns
Partitioning splits a table into segments (e.g., by date), while clustering sorts the data within each partition by one or more columns to further optimize query performance.
Question 5: Which Looker Studio feature lets you restrict what data a specific user or group can see within a shared report?
- Calculated fields
- Report-level filters
- Row-level security via data source credentials (Correct answer)
- Date range controls
Correct answer: Row-level security via data source credentials
Row-level security in Looker Studio is enforced at the data source level (e.g., BigQuery row-level access policies), controlling which rows each viewer can access.
Question 6: What is the purpose of the BigQuery Data Transfer Service?
- To replicate BigQuery tables across multiple regions
- To automate data movement from Google and third-party sources into BigQuery on a schedule (Correct answer)
- To export BigQuery query results to Cloud Storage
- To migrate on-premises databases to BigQuery
Correct answer: To automate data movement from Google and third-party sources into BigQuery on a schedule
The Data Transfer Service automates scheduled, managed data transfers from sources like Google Ads, YouTube, and SaaS apps directly into BigQuery.
Question 7: When analyzing data in BigQuery, what is a 'materialized view' and its key benefit?
- A read-only snapshot of a table at a specific timestamp
- A precomputed query result stored physically, auto-refreshed for faster queries (Correct answer)
- A virtual table that executes its query definition on every access
- A backup copy of a table stored in a separate region
Correct answer: A precomputed query result stored physically, auto-refreshed for faster queries
Materialized views precompute and store query results, allowing BigQuery to serve queries faster and cheaper by reading the cached result instead of re-running the full query.
GCP Data Analysis & Reporting 4
You need to run a machine learning model directly on data stored in BigQuery without exporting it.
Which feature enables this?