Associate Data Practitioner Free Sample Questions

20 free sample questions307 in the full practice test Other version: GCP-ADP(135)

Try simulator

ADP Sample Questions

  1. Question 1

    Intermediate

    Data Management · Configure lifecycle management

    A financial analytics firm is migrating its data warehouse to BigQuery. A critical requirement is that all new tables created within the finance_reports dataset must automatically enforce a 365-day data retention policy based on an ingestion-time partition. Any attempt to create a table without this specific partitioning and expiration setting should fail. Which bq command should be used to configure the dataset to meet this requirement?

    Answer and explanation

    Correct answer: D

    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.

  2. Question 2

    Advanced

    Data Pipeline Orchestration · Schedule, automate, and monitor basic data processing tasks

    A data engineering team uses Dataflow to process streaming IoT data. The pipeline reads from Pub/Sub, performs a complex transformation, and writes to BigQuery. During a load spike, you observe that the data freshness in BigQuery is degrading significantly, and the Pub/Sub subscription shows a growing backlog of unacknowledged messages. The Dataflow monitoring UI shows high System Latency but CPU utilization across workers remains below 50%. What is the most likely bottleneck causing this issue?

    Answer and explanation

    Correct answer: B

    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.

  3. Question 3

    Intermediate

    Data Analysis and Presentation · Identify data trends, patterns, and insights by using BigQuery and Jupyter notebooks

    A retail company wants to analyze sales data stored in a BigQuery table named sales_transactions. The table contains product_id, store_id, sale_date, and revenue. The analytics team needs a report that shows the total revenue for each product, but only for products that have been sold in more than 10 unique stores. Which SQL query in BigQuery will produce the desired report?

    Answer and explanation

    Correct answer: B

    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.

  4. Question 4

    Beginner

    Data Preparation and Ingestion · Extract and load data into appropriate Google Cloud storage systems

    You are tasked with ingesting a large, 10 TB dataset of historical logs from an on-premises SFTP server to a Cloud Storage bucket for archival and future analysis. The on-premises location has a reliable 1 Gbps internet connection. The transfer must be completed within 48 hours, be fully managed, and provide data integrity checks. Which Google Cloud service should you use for this one-time transfer?

    Answer and explanation

    Correct answer: C

    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.

  5. Question 5

    IntermediateMultiple answers

    Data Analysis and Presentation · Visualize data and create dashboards in Looker given business requirements

    A business analyst has created a dashboard in Looker Studio to track daily sales metrics. The dashboard connects directly to a BigQuery table. Users are reporting that the dashboard is becoming very slow and often times out, especially during peak business hours. The underlying BigQuery table is 5 TB and is not partitioned or clustered. You want to improve the dashboard's performance and reduce query costs with minimal changes to the dashboard itself. Which actions should you take? (Select TWO)

    Answer and explanation

    Correct answers: A, C

  6. Question 6

    Intermediate

    Data Management · Apply security measures and ensure compliance

    True or False: Using a customer-managed encryption key (CMEK) with Cloud Storage means that Google no longer holds any component of the encryption key, and all cryptographic operations happen outside of Google Cloud.

    Answer and explanation

    Correct answer: B

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

  7. Question 7

    Beginner

    Data Preparation and Ingestion · Data quality and cleaning

    A healthcare organization is building a data pipeline to process patient records. The raw data arrives as JSON files in a Cloud Storage bucket. The pipeline must de-identify sensitive information like patient names and social security numbers by applying masking transformations before loading the data into BigQuery for analysis. The solution must be a fully managed, graphical, low-code service to accelerate development. The pipeline should be designed according to the following flow:

    graph TD A[GCS Bucket: Raw JSON] --> B{De-identification Pipeline}; B --> C[BigQuery Table: Anonymized Data];

    Which service should be used to build the de-identification pipeline (B)?

    Answer and explanation

    Correct answer: C

    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.

  8. Question 8

    Intermediate

    Data Analysis and Presentation · Define, train, evaluate, and use ML models

    You are building a regression model in BigQuery ML to predict housing prices. After training your model, you use the ML.EVALUATE function and get the following output: {'mean_absolute_error': 25000, 'r2_score': 0.85}. What do these metrics signify about your model's performance?

    Answer and explanation

    Correct answer: C

    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.

  9. Question 9

    Beginner

    Data Pipeline Orchestration · Schedule, automate, and monitor basic data processing tasks

    An e-commerce company has a daily batch pipeline that updates product inventory in BigQuery. The pipeline is orchestrated by a Cloud Composer DAG. Recently, the DAG has been failing intermittently. You need to investigate the failures by reviewing the execution history, task logs, and the overall structure of the DAG. Which user interface should you use to perform this troubleshooting?

    Answer and explanation

    Correct answer: C

    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.

  10. Question 10

    Intermediate

    Data Management · Configure access control and governance

    A new data analyst has joined your team and needs permissions to run queries on all tables within the production_analytics dataset in BigQuery. They also need to be able to create new tables in this dataset. However, they must not be able to delete the dataset or modify its permissions. Following the principle of least privilege, which single predefined IAM role should you grant to the analyst at the dataset level?

    Answer and explanation

    Correct answer: B

    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.

Register free for 10 more questions

Or unlock all 307 ADP questions with explanations, timed mode and flashcards.