Preparing and Using Data for Analysis

You are building a binary classification model using AutoML Tables on Vertex AI. The dataset has a severe class imbalance (1% positive class). Which strategy should you use to handle the imbalance?

You are using Vertex AI Feature Store to serve features for online predictions. Your model requires features from multiple sources with low latency (<10ms). Which type of serving should you use?

A company needs to predict whether a product image contains a specific defect. They have 10,000 labeled images and want to build a model quickly without writing custom code or training from scratch. Which GCP service should they use?

Your analytics team queries a large BigQuery table `events` that is partitioned by `event_date` and clustered by `user_id`. A new dashboard runs a query filtering on `user_id = 'abc123'` but does not include a filter on `event_date`. You want to minimize the bytes scanned by this query. What should you do?

A company uses Looker Studio to build dashboards from BigQuery data. They notice that queries take several seconds to return. They want to improve performance without changing the schema or adding materialized views. Which option should they use?

You need to load a 500 GB CSV file from Cloud Storage into BigQuery. The file has a header row and uses comma delimiters. You want to load it as quickly as possible without transforming the data. Which approach should you use?

An organization wants to integrate BigQuery Omni to query data stored in AWS S3. They have set up the necessary connections. What is the primary benefit of using BigQuery Omni over simply copying the data to BigQuery?

A data engineer is building a Looker Studio dashboard that requires a calculated field to compute the running total of sales per day per store. Which Looker Studio function should they use?

Which BigQuery SQL function returns the rank of a row within a window, with gaps in the ranking for ties?

What is the primary purpose of Vertex AI Feature Store?

A company uses Dataplex to manage data quality across multiple BigQuery datasets. They want to define a data quality rule that checks if a column 'email' contains a valid email format. Which Dataplex feature should they use?

You are building a forecasting model to predict daily sales for the next 90 days using historical sales data with clear seasonality and trend. You want to use BigQuery ML with minimal manual tuning. Which model type should you choose?

A data engineer needs to design a data pipeline that ingests streaming data from Cloud Pub/Sub, performs real-time aggregations, and loads the results into BigQuery for dashboarding. Which Google Cloud service should they use for the streaming aggregation step?

A company uses BigQuery and wants to reduce query costs by using BI Engine for Looker Studio dashboards. The data is stored in a BigQuery dataset with 5 TB of frequently accessed tables. The dashboards run dozens of concurrent queries. What is the recommended approach to enable BI Engine acceleration?

You want to quickly estimate the number of distinct visitors to your website from a large BigQuery table. Which function provides an approximate count with low latency?

Which BigQuery function can be used to retrieve the value of a column from the previous row within a partition, ordered by a timestamp?

Your Looker dashboard uses a BigQuery connection. You notice that some queries take over a minute. Which service can you enable to cache results in memory for sub-second Looker queries?

A data engineer needs to query data from BigQuery and another cloud provider's storage (AWS S3) using a single SQL query. The data must not be moved or copied to GCP. Which Google Cloud service should they use?

You are using BigQuery ML to train a matrix factorization model for a recommendation system. The training data consists of user-item interactions. You notice that the model is overfitting. Which of the following hyperparameter changes would most likely reduce overfitting?

You need to load a 2 TB CSV file from Cloud Storage into BigQuery. The CSV has a header row and uses a comma delimiter. You want to minimize cost and ensure the load completes quickly. Which method should you use?

A data engineer is using Vertex AI Workbench to develop a custom ML model. They want to store and version datasets, track experiments, and register models. Which three Vertex AI services should they use? (Choose THREE)

You are loading a CSV file into BigQuery. The file has a column `price` that contains values like `$1,234.56`. You want to load it into a NUMERIC column. What should you do?

A machine learning engineer needs to deploy a custom TensorFlow model for online predictions with low latency. The model is already trained and saved in SavedModel format. Which Vertex AI service should they use?

Your team uses Looker to develop a model on top of BigQuery. The data is partitioned by ingestion time, and analysts frequently query the last 7 days. However, Looker queries are scanning the entire table, causing high costs. Which change should you implement?

You are designing a BigQuery table to store clickstream events. Queries will frequently filter by a user_id column and a TIMESTAMP column named event_time, and the table will grow to several petabytes. You want to minimize bytes scanned and cost for these queries. Which TWO actions should you take? (Choose two.)

You need to create a time-series forecast for inventory demand using BigQuery ML. The data includes daily sales for 5 years. Which model type should you use?

Your company ingests streaming data into BigQuery using the Storage Write API. You need to ensure that duplicate records are not inserted when the streaming job retries due to transient errors. Which feature should you use?

You need to split a time-series dataset into training and evaluation sets for a forecasting model. The data is ordered by timestamp. Which splitting technique should you use?

Your team stores raw JSON event logs in Cloud Storage. Analysts need to run ad-hoc SQL queries on this data with minimal setup and cost, but they also require the ability to join it with existing BigQuery tables. Which approach should you recommend?

You are designing a BigQuery schema for a table that will store user profile data. The data includes a unique user ID, a list of email addresses (each with a type and address), and a timestamp of last update. You need to support efficient queries that retrieve all email addresses for a given user and also filter users by email type. Which schema design should you use?

You are building a data pipeline that ingests JSON files from Cloud Storage into BigQuery. The JSON files contain deeply nested arrays and objects. You need to load these files with minimal transformation so that analysts can query individual nested fields using dot notation and also unnest arrays when needed. Which approach should you use?

A data scientist wants to train a custom TensorFlow model on Vertex AI using a managed Jupyter notebook. Which Vertex AI service should they use to set up a notebook environment with pre-installed deep learning frameworks?

Which BigQuery SQL function can be used to get an approximate count of distinct values in a large column faster than COUNT(DISTINCT) with lower accuracy?

You want to train a custom TensorFlow model on Vertex AI using a managed Jupyter notebook environment. Which service should you use?

You are using Looker to model data from BigQuery. You have a dimension that should be filtered by a user attribute (e.g., user's region). Which LookML concept allows you to apply dynamic row-level security based on user attributes?

You need to create a Looker model that defines a 'sales' view based on a BigQuery table, with a measure for total revenue. Which LookML object defines the table and dimensions?

You are designing a Dataflow pipeline that reads from a Cloud Storage bucket containing thousands of small JSON files, transforms the data, and writes to BigQuery. The pipeline is slow and expensive because of the large number of small files. You want to improve throughput and reduce cost. What should you do?

You need to track the lineage of data in BigQuery, showing how tables are derived from other tables via queries. Which service provides this capability?

You have a BigQuery table `sales` with columns `order_id`, `customer_id`, `order_date` (DATE), and `amount` (NUMERIC). You need to create a new table that contains, for each customer, the total sales amount and the date of their most recent order. The result should include `customer_id`, `total_amount`, and `last_order_date`. Which SQL query achieves this correctly?

You are ingesting streaming data into BigQuery using the Storage Write API. The data arrives with occasional duplicate records due to at-least-once delivery semantics from the source system. You need to ensure that the final BigQuery table contains no duplicates based on a unique event_id column. Which approach should you use?

A financial services firm stores customer transaction data in a BigQuery table. The table contains a column `customer_id` that is frequently used in WHERE clauses, but the table is not partitioned. Queries filtering on a specific `customer_id` scan the entire table, which is large and costly. The data engineer wants to reduce the bytes scanned for these queries without changing the table's partitioning scheme. What should the engineer do?

You are preparing data in BigQuery for analysis. You have a table `orders` with columns `order_id`, `customer_id`, `order_date`, and `amount`. You need to create a new table that includes all orders, plus a column `prev_order_amount` that contains the amount of the customer's previous order by `order_date`. If there is no previous order, the value should be NULL. Which SQL feature should you use?

You are migrating a large on-premises data warehouse to BigQuery. The data includes sensitive PII columns that must be masked for certain users. Which BigQuery feature can automatically redact PII in query results based on user roles?

A data scientist wants to use Vertex AI Workbench for exploratory data analysis. Which TWO statements are true about Vertex AI Workbench?

A data analyst wants to compute the rank of sales per region and also the difference in sales between consecutive months for each region. Which BigQuery analytic functions should they use? (Select TWO)

You need to load a large CSV file from Cloud Storage into BigQuery. The file contains a header row and is comma-delimited. You want to ensure that the header row is skipped and that the schema is automatically detected. Which BigQuery load option should you use?

A financial analytics team uses Looker to explore BigQuery data. They need to allow business users to filter by a custom date range that is not tied to an existing dimension. The date range must be user-input at query time. What is the best approach in Looker?

A data scientist is training a binary classification model on an imbalanced dataset (95% negative, 5% positive) using AutoML Tables. Which strategy should they use to handle the class imbalance?

A company uses Looker Studio to create dashboards from BigQuery data. They notice that dashboard queries take several seconds to load. They want to improve performance without changing the underlying data or creating materialized views. Which option should they use?

Your team uses Cloud Dataflow to stream events from Pub/Sub into BigQuery. Some events arrive late, up to 10 minutes after their event timestamp. You need the pipeline to produce correct aggregations per 5-minute window, including late data, while keeping latency low for on-time events. What should you do?

You have a BigQuery table 'logs' with a column 'timestamp' of type TIMESTAMP. You need to create a new table that contains only the logs from the last 7 days, partitioned by day on the 'timestamp' column. Which SQL statement should you use?

A data engineer needs to query data across BigQuery (in Google Cloud) and Snowflake (in AWS) without moving the data. Which service should they use?

A company wants to use AutoML Tables to build a classification model on a dataset with 100 features and 500,000 rows. They need to deploy the model for online predictions with low latency (<100 ms). Which deployment option should they choose?

You have a BigQuery table `orders` with columns `order_id` (STRING), `customer_id` (STRING), `order_date` (DATE), and `amount` (NUMERIC). You need to create a view that shows, for each order, the cumulative sum of `amount` for that customer, ordered by `order_date` ascending. Which SQL window function or clause should you use?

You are building a multi-cloud analytics solution to join data from Google Cloud and AWS S3. You need to query the S3 data using BigQuery without moving it. Which Google Cloud service should you use?

You have a BigQuery table with sales data and want to pivot product categories into columns. Which SQL clause should you use?

You are using Cloud Dataflow to stream data from Pub/Sub into BigQuery. The incoming messages are JSON strings with a field `event_time` in ISO 8601 format (e.g., "2024-03-15T14:30:00Z"). You need to write the data to a BigQuery table with a column `event_timestamp` of type TIMESTAMP. Which transformation should you apply in your Dataflow pipeline?

A company uses Dataplex to manage data lakes on Google Cloud. They want to enforce data quality rules on a BigQuery table, such as ensuring that a 'email' column is not null and matches a regex pattern. Which Dataplex feature should they use?

A data scientist wants to import a pre-trained TensorFlow model into BigQuery ML for batch predictions. The model is stored in a Cloud Storage bucket. Which statement is correct?

Your team stores IoT sensor readings in a BigQuery table `project.sensors.readings` with columns `sensor_id` (STRING), `reading_time` (TIMESTAMP), and `temperature` (FLOAT64). You need to create a new table that adds a column `avg_temp_7d` containing, for each row, the average temperature of that sensor over the preceding 7 days (including the current row). Which SQL feature should you use to compute this efficiently?

A data engineer needs to split time-series data for training a forecasting model. The data is sorted by timestamp. The engineer wants to avoid leakage where future data influences training. Which data splitting approach should they use?

You have a BigQuery table 'events' with a TIMESTAMP column 'event_time'. You need to compute, for each event, the difference in seconds from the previous event of the same user. Which window function should you use?

A company wants to use BigQuery's PIVOT operator to transform their sales data. They have a table with columns: 'year', 'quarter', 'revenue'. They want to create a report where each row is a year and each column is a quarter (Q1, Q2, Q3, Q4) showing revenue. Which SQL statement is correct?

You have a BigQuery table `logs` with a column `message` that contains JSON strings. You need to extract the value of the `user_id` field from each JSON string and return it as a separate column. Which function should you use?

You are using BigQuery to analyze a dataset. You need to create a new table that contains only rows from `sales` where the `region` is 'North America' and the `sale_date` is in the year 2023. You also want to add a column `sale_month` that extracts the month from `sale_date`. Which two actions should you take? (Choose two.)

To enable data lineage tracking in BigQuery, which feature should be activated?

You need to load a large CSV file from Cloud Storage into BigQuery. The file has a header row and contains a column with date values in the format 'YYYY-MM-DD'. You want to ensure the date column is correctly recognized as a DATE type. Which method should you use?

You train a BigQuery ML linear regression model to predict house prices. The model has high bias during evaluation. Which action BEST reduces bias?

You are designing a data pipeline for ML training with Vertex AI. You need to split time-series data into train/validation/test sets without leaking future data. Which THREE practices should you follow?

A retail company uses BigQuery to store sales data and wants to forecast weekly demand for the next 8 weeks using historical data from the past 2 years. They need to account for seasonality and holidays. Which BigQuery ML model type and configuration is most appropriate?

A data team uses Looker Studio to create a report that combines data from two different BigQuery tables: one with sales transactions and another with customer demographics. They need to join these tables in the report without writing SQL. Which feature should they use?

Which Google Cloud service would you use to create a unified data catalog that automatically captures lineage from BigQuery, Cloud Storage, and other sources?

Your team maintains a BigQuery dataset where a partitioned table 'transactions' is partitioned by DATE(transaction_time) and clustered by customer_id. Analysts frequently run queries filtering on a specific customer_id and a date range of the last 7 days. They report that these queries scan more data than expected, and you notice that the table was created without a partition filter requirement but with a clustering column. Which action should you take to optimize query performance and reduce bytes scanned?

A data team wants to use Approximate Aggregation Functions in BigQuery to get faster query results. Which two functions can they use? (Choose 2)

A data scientist needs to perform feature engineering for a machine learning model using Vertex AI. They want to preprocess data using a pipeline that includes scaling, one-hot encoding, and handling missing values. Which TWO services can they use to define and execute this preprocessing pipeline? (Choose 2.)