CCNA Preparing and Using Data for Analysis Questions — Page 2 of 2

MCQhard

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?

A.SELECT * FROM sales PIVOT(SUM(revenue) FOR quarter IN ('Q1','Q2','Q3','Q4'))

B.SELECT * FROM sales PIVOT(revenue FOR quarter IN (Q1, Q2, Q3, Q4))

C.PIVOT sales ON quarter USING SUM(revenue)

D.SELECT * FROM (SELECT year, quarter, revenue FROM sales) PIVOT(SUM(revenue) FOR quarter IN (Q1, Q2, Q3, Q4))

AnswerA

Correct syntax with subquery alias and aggregate function.

Why this answer

PIVOT in BigQuery requires specifying an aggregate function, the pivot column, and the list of pivot column values. The syntax is: SELECT * FROM (SELECT year, quarter, revenue FROM sales) PIVOT(SUM(revenue) FOR quarter IN ('Q1','Q2','Q3','Q4')).

Practice this question →

MCQhard

You are building a machine learning pipeline for credit risk assessment. The dataset has a severe class imbalance (1% default rate). You want to use AutoML Tables on Vertex AI. Which strategy should you incorporate to handle imbalance?

A.Downsample the majority class to a 50-50 ratio

B.Apply SMOTE in a Dataflow pipeline before training

C.Upsample the minority class using BigQuery SQL

D.Use the `class_weight` parameter in the AutoML Tables model

AnswerD

AutoML Tables supports adjusting class weights to handle imbalance.

Why this answer

AutoML Tables automatically applies class imbalance handling (e.g., class weighting) by default. You can adjust the weight strategy. SMOTE is not directly supported in AutoML Tables; you would need custom training.

Downsampling and upsampling are manual steps not needed.

Practice this question →

MCQmedium

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

A.Decrease the learning rate in the training options

B.Add more features like number of bedrooms and square footage

C.Remove features that have low correlation with the label

D.Increase L2 regularization

AnswerB

Adding relevant features helps capture patterns, reducing bias.

Why this answer

High bias indicates underfitting, meaning the model is too simple. Adding more relevant features (option B) increases model complexity and reduces bias. Decreasing the learning rate (A) does not address model complexity; it only affects convergence speed.

Removing features with low correlation (C) further reduces complexity, increasing bias. Increasing L2 regularization (D) penalizes large coefficients, which increases bias.

Practice this question →

MCQhard

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.ARIMA_PLUS with holiday_region parameter

B.Boosted tree classifier

C.Linear regression with engineered time features

D.Time-series DECOMPOSE model

AnswerA

ARIMA_PLUS is designed for time-series forecasting with automatic seasonality detection and holiday support.

Why this answer

BigQuery ML's ARIMA_PLUS model is designed for time-series forecasting, automatically detecting seasonality and handling holiday effects via the holiday_region parameter. Linear regression would require manual feature engineering for time components. Time-series DECOMPOSE is not a model type.

Boosted trees are not natively time-series aware without feature engineering.

Practice this question →

MCQmedium

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?

A.Data blending

B.Creating a report with multiple charts

C.Custom query in BigQuery connector

D.Calculated fields

AnswerA

Data blending allows combining data from different sources via a common key without SQL.

Why this answer

Looker Studio's data blending feature allows combining data from multiple sources (including BigQuery tables) using a common key, without writing SQL. It provides a graphical interface to define joins. Creating a custom query requires SQL.

Looker Studio reports support multiple charts, but blending is the specific feature for joining data. Calculated fields transform data within a single source.

Practice this question →

Multi-Selectmedium

A data engineer is planning a time-series forecasting model using BigQuery ML ARIMA+ on a dataset with daily sales data spanning 3 years. Which TWO actions are required to prepare the data for ARIMA+? (Choose 2.)

Select 2 answers

A.Create a partition on the time column to improve performance.

B.Remove any rows with NULL values in the time column.

C.Sort the data by the time column in ascending order.

D.Ensure the time column is of type DATE or TIMESTAMP.

E.Encode the target variable using one-hot encoding.

AnswersC, D

ARIMA+ expects the data to be ordered by time.

Why this answer

ARIMA+ requires a time column and a numeric target column. The time column must be in a date/timestamp format. Additionally, the data should be sorted by time.

Missing values should be handled (e.g., filled with 0 or interpolated) but that's not a requirement of the function itself.

Practice this question →

Multi-Selecteasy

A data engineer is preparing a dataset for ML training in Vertex AI. The dataset includes a timestamp column, a categorical column with high cardinality (1000 distinct values), and a numerical column with outliers. Which two preprocessing steps should they apply? (Choose TWO)

Select 2 answers

A.Drop the timestamp column

B.Label encode the categorical column

C.Winsorize the numerical column to cap outliers

D.Normalize the numerical column using Z-score

E.One-hot encode the categorical column

AnswersB, C

Label encoding maps categories to integers, reducing dimensionality.

Why this answer

One-hot encoding for high cardinality may be too sparse; label encoding (ordinal encoder) is more common. Winsorizing clips outliers.

Practice this question →

MCQeasy

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

A.Cloud Composer

B.Dataflow

C.Data Catalog

D.Dataplex

AnswerD

Dataplex includes a unified catalog, lineage, and governance.

Why this answer

Dataplex provides a unified data catalog (Universal Catalog) with automated lineage, discovery, and governance across GCP. Data Catalog is the older standalone service; Dataplex is the recommended unified solution. Cloud Composer and Dataflow are orchestration/processing tools.

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.APPROX_SUM

B.APPROX_AVG

C.APPROX_QUANTILES

D.APPROX_COUNT_DISTINCT

E.APPROX_MEDIAN

AnswersC, D

Returns approximate quantiles.

Why this answer

BigQuery provides APPROX_COUNT_DISTINCT for approximate distinct counts and APPROX_QUANTILES for approximate quantiles. Other approximate functions include APPROX_TOP_COUNT and APPROX_TOP_SUM.

Practice this question →

Multi-Selectmedium

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.)

Select 2 answers

A.Cloud Dataproc

B.Vertex AI Pipelines

C.BigQuery SQL with ML.TRANSFORM

D.Cloud Dataflow

E.Cloud Functions

AnswersB, C

Allows you to build and run end-to-end ML pipelines, including preprocessing.

Why this answer

Vertex AI Pipelines is the recommended service for building and running ML pipelines, including preprocessing steps. Alternatively, you can use BigQuery SQL for feature engineering directly on the data, then export the processed data for training. Cloud Dataflow is an option for batch/streaming data processing but is not specific to ML pipelines.

Cloud Functions and Dataproc are less suitable for this purpose.

Practice this question →