ADP Practice Questions

Case Study: Global Logistics Co.

Company Background: Global Logistics Co. is a multinational shipping company that tracks millions of packages daily. They have a massive on-premises data infrastructure that they are migrating to Google Cloud to improve scalability and analytics capabilities.

Current Situation: They currently receive real-time package status updates from their fleet of trucks and planes. These updates are small JSON messages sent to an on-premises message queue. A legacy Java application processes these messages, enriches them with data from a PostgreSQL database, and stores the results in a local data warehouse. This system is struggling to keep up with the increasing volume of data, leading to delays in package tracking visibility for customers.

Technical Requirements:

1. The new cloud-native pipeline must process messages in near real-time with low latency.
2. The solution must be serverless and auto-scaling to handle unpredictable spikes in message volume.
3. The pipeline needs to perform a stateful enrichment: for each package, it must look up the package's origin and destination from a reference table.
4. The final, enriched data must be written to a BigQuery table for real-time analytics and a Cloud Storage bucket for archival.

Which combination of Google Cloud services provides the most suitable architecture for this new pipeline?

You are joining a project that uses Looker for its business intelligence. You are given a link to a dashboard, but you need to understand how the metrics are calculated. Specifically, you want to see the underlying data model, including the table joins and dimension/measure definitions. Where in the Looker interface would you navigate to find this information?

A data practitioner needs to load a 500 MB CSV file from a local machine into a new BigQuery table named campaign_results. Which bq command-line tool invocation is the correct way to accomplish this?

True or False: When using Analytics Hub to share a BigQuery dataset with another organization, a complete copy of the data is replicated into the subscriber's Google Cloud project, potentially incurring high storage costs for the subscriber.

You need to create a simple, scheduled workflow that runs a BigQuery query every morning at 9 AM, and if the query is successful, sends a notification to a Pub/Sub topic. The workflow has no complex dependencies or branching logic. You want to use a serverless, managed orchestration service that requires defining the workflow in YAML or JSON. Which service is the best fit for this requirement?

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?

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?

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?

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?

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)