Free ADP sample questions

Correct answer: bq update --dataset --require_partition_filter=true --default_table_expiration 31536000 --time_partitioning_type DAY finance_reports

To enforce that all new tables in a dataset require partitioning, you must set the `--require_partition_filter` flag to `true` when updating the dataset. The `--default_table_expiration` sets the lifetime for new tables (31536000 seconds = 365 days), and `--time_partitioning_type DAY` specifies ingestion-time partitioning. This command updates an existing dataset (`bq update`) with all the required enforcement settings. The other options either use the wrong command (`mk` for create instead of update), miss the `--require_partition_filter` flag, or use `--default_partition_expiration` which applies to partitions within a table, not the table itself.

Correct answer: The pipeline has a non-parallelizable operation (hot key) in the transformation logic.

High System Latency with low CPU utilization is a classic symptom of a 'hot key' problem. This occurs when a large volume of data with the same key is processed by a single worker in a `GroupByKey` or similar operation, creating a bottleneck. The other workers remain underutilized because they are waiting for the overloaded worker to finish. Increasing vCPUs or workers (autoscaling) wouldn't solve this fundamental logic issue. While BigQuery streaming quotas can be a bottleneck, the primary evidence here (low CPU across workers despite a backlog) points strongly to a non-parallelizable step in the pipeline itself.

Correct answer: SELECT product_id, SUM(revenue) FROM sales_transactions GROUP BY product_id HAVING COUNT(DISTINCT store_id) > 10;

This query correctly calculates the total revenue per product and then filters those results to include only products meeting the specified condition. The `GROUP BY product_id` clause aggregates the data by product. The `HAVING` clause is used to filter the results of an aggregate function (`COUNT(DISTINCT store_id)`) after the grouping has occurred. The `WHERE` clause cannot be used with aggregate functions; it filters rows before aggregation.

Correct answer: Storage Transfer Service

Storage Transfer Service is the ideal choice for this scenario. It is a fully managed service designed for large-scale online data transfers from sources like SFTP servers to Cloud Storage. It handles scheduling, data integrity checks, and can easily saturate the 1 Gbps connection to meet the 48-hour deadline for 10 TB. Transfer Appliance is for offline transfers and is overkill for this data size and timeline. Using `gsutil` would require manual setup, scripting for reliability, and is not a managed solution.

Correct answers: Recreate the underlying table with partitioning on the date column and clustering on frequently filtered columns.; Enable BigQuery BI Engine for the project and create a reservation.

Correct answer: False

This statement is false. With CMEK, the customer manages the key within Google's Cloud Key Management Service (KMS). Google services (like Cloud Storage) still perform the cryptographic operations by requesting the KMS to use the key for encryption/decryption. The customer controls the key's lifecycle and access policies, but the key material resides within KMS. The scenario described (key held and used outside Google) applies to Customer-Supplied Encryption Keys (CSEK) or Cloud External Key Manager (EKM).

Correct answer: Cloud Data Fusion

Cloud Data Fusion is the ideal service for this requirement. It is a fully managed, cloud-native data integration service that provides a graphical interface and a broad library of pre-built transformations, including those for data masking and de-identification. This aligns perfectly with the 'low-code' and 'graphical' requirements. Dataflow would require custom coding, and Cloud Composer is an orchestrator, not a data transformation tool itself.

Correct answer: The model's predictions are, on average, incorrect by $25,000, and the model explains 85% of the variability in the housing price data.

For a regression model, `mean_absolute_error` (MAE) indicates the average absolute difference between the predicted values and the actual values. An MAE of 25000 means the predictions are off by an average of $25,000. The `r2_score` (R-squared) represents the proportion of the variance in the dependent variable (housing price) that is predictable from the independent variables. An r2_score of 0.85 means that the model's features explain 85% of the variability in housing prices, which is generally considered a good fit.

Correct answer: The Apache Airflow web interface

Cloud Composer is a managed Apache Airflow service. The primary interface for managing, monitoring, and troubleshooting DAGs (Directed Acyclic Graphs) is the Airflow web UI. This interface provides detailed views of DAG runs, task statuses, logs for individual tasks, trigger history, and the rendered code. While logs are also available in Cloud Logging and the environment can be managed in the Google Cloud Console, the Airflow UI is the specific tool designed for in-depth DAG troubleshooting.

Correct answer: BigQuery Data Editor

The `BigQuery Data Editor` role is the most appropriate choice. It grants permissions to read, query, create, update, and delete tables within a dataset. This meets the requirements of running queries and creating new tables. It does not, however, grant permissions to delete the dataset itself or change its IAM policies, which are part of the `BigQuery Data Owner` role. `BigQuery Data Viewer` is too restrictive as it only allows reading data, not creating tables.