PDE practice questions 121–130

1. Question 121 · 340 in bank

   You are collecting IoT sensor data from millions of devices across the world and storing the data in BigQuery. Your access pattern is based on recent data, filtered by location_id and device_version with the following query:

   

   You want to optimize your queries for cost and performance. How should you structure your data?

   • APartition table data by create_date, location_id, and device_version.
   • BPartition table data by create_date, cluster table data by location_id, and device_version.
   • CCluster table data by create_date, location_id, and device_version.
   • DCluster table data by create_date, partition by location_id, and device_version.

   Show answer

   Answer: B

   Explanation

   1. Partitioning the data by create_date will allow BigQuery to prune partitions that are not relevant to the query by date. 2. Clustering the data by location_id and device_version within each partition will keep related data close together and optimize the performance of filters on those columns. This provides both the pruning benefits of partitioning and locality benefits of clustering for filters on multiple columns. The query provided indicates that the access pattern is primarily based on the most recent data (within the last 7 days), filtered by location_id and device_version. Given this pattern, you would want to optimize your table structure in such a way that queries scanning through the data will process the least amount of data possible to reduce costs and improve performance.

   Source: Adapted from community discussion — MaxNRG

   Next question

2. Question 122 · 340 in bank

   A live TV show asks viewers to cast votes using their mobile phones. The event generates a large volume of data during a 3-minute period. You are in charge of the "Voting infrastructure" and must ensure that the platform can handle the load and that all votes are processed. You must display partial results while voting is open. After voting closes, you need to count the votes exactly once while optimizing cost. What should you do?

   

   • ACreate a Memorystore instance with a high availability (HA) configuration.
   • BCreate a Cloud SQL for PostgreSQL database with high availability (HA) configuration and multiple read replicas.
   • CWrite votes to a Pub/Sub topic and have Cloud Functions subscribe to it and write votes to BigQuery.
   • DWrite votes to a Pub/Sub topic and load into both Bigtable and BigQuery via a Dataflow pipeline. Query Bigtable for real-time results and BigQuery for later analysis. Shut down the Bigtable instance when voting concludes.

   Show answer

   Answer: D

   Explanation

   Since cost optimization and minimal latency are key requirements, option D is likely the best choice to meet all the needs: The key reasons option D works well: Using Pub/Sub to ingest votes provides scalable, reliable transport. Loading into Bigtable and BigQuery provides both: Low latency reads from Bigtable for real-time results. Cost effective storage in BigQuery for longer term analysis. Shutting down Bigtable after voting concludes reduces costs. BigQuery remains available for cost-optimized storage and analysis. So you are correct that option D combines the best of real-time performance for queries using Bigtable, with cost-optimized storage in BigQuery. The only additional consideration may be if 3 minutes of Bigtable usage still incurs higher charges than ingesting directly into BigQuery. But for minimizing latency while optimizing cost, option D is likely the right architectural choice given the requirements.

   Source: Adapted from community discussion — MaxNRG

   Next question

3. Question 123 · 340 in bank

   A shipping company has live package-tracking data that is sent to an Apache Kafka stream in real time. This is then loaded into BigQuery. Analysts in your company want to query the tracking data in BigQuery to analyze geospatial trends in the lifecycle of a package. The table was originally created with ingest-date partitioning. Over time, the query processing time has increased. You need to copy all the data to a new clustered table. What should you do?

   • ARe-create the table using data partitioning on the package delivery date.
   • BImplement clustering in BigQuery on the package-tracking ID column.
   • CImplement clustering in BigQuery on the ingest date column.
   • DTier older data onto Cloud Storage files and create a BigQuery table using Cloud Storage as an external data source.

   Show answer

   Answer: B

   Explanation

   Query Focus: Analysts are interested in geospatial trends within individual package lifecycles. Clustering by package-tracking ID physically co-locates related data, significantly improving query performance for these analyses. Addressing Slow Queries: Clustering addresses the query slowdown issue by optimizing data organization for the specific query patterns. Partitioning vs. Clustering: Partitioning: Divides data into segments based on a column's values, primarily for managing large datasets and optimizing query costs. Clustering: Organizes data within partitions for faster querying based on specific columns.

   Source: Adapted from community discussion — e70ea9e

   References

   • Google Cloud documentation

   Next question

4. Question 124 · 340 in bank

   Your company uses a proprietary system to send inventory data every 6 hours to a data ingestion service in the cloud. Transmitted data includes a payload of several fields and the timestamp of the transmission. If there are any concerns about a transmission, the system re-transmits the data. How should you deduplicate the data most efficiency?

   • AAssign global unique identifiers (GUID) to each data entry.
   • BCompute the hash value of each data entry, and compare it with all historical data.
   • CStore each data entry as the primary key in a separate database and apply an index.
   • DMaintain a database table to store the hash value and other metadata for each data entry.

   Show answer

   Answer: A

   Explanation

   The best answer is "A". Answer "D" is not as efficient or error-proof due to two reasons 1. You need to calculate hash at sender as well as at receiver end to do the comparison. Waste of computing power. 2. Even if we discount the computing power, we should note that the system is sending inventory information. Two messages sent at different can denote same inventory level (and thus have same hash). Adding sender time stamp to hash will defeat the purpose of using hash as now retried messages will have different timestamp and a different hash. if timestamp is used as message creation timestamp than that can also be used as a UUID.

   Source: Adapted from community discussion — dg63

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

5. Question 125 · 340 in bank

   You are designing a data mesh on Google Cloud with multiple distinct data engineering teams building data products. The typical data curation design pattern consists of landing files in Cloud Storage, transforming raw data in Cloud Storage and BigQuery datasets, and storing the final curated data product in BigQuery datasets. You need to configure Dataplex to ensure that each team can access only the assets needed to build their data products. You also need to ensure that teams can easily share the curated data product. What should you do?

   • A1. Create a single Dataplex virtual lake and create a single zone to contain landing, raw, and curated data. 2. Provide each data engineering team access to the virtual lake.
   • B1. Create a single Dataplex virtual lake and create a single zone to contain landing, raw, and curated data. 2. Build separate assets for each data product within the zone. 3. Assign permissions to the data engineering teams at the zone level.
   • C1. Create a Dataplex virtual lake for each data product, and create a single zone to contain landing, raw, and curated data. 2. Provide the data engineering teams with full access to the virtual lake assigned to their data product.
   • D1. Create a Dataplex virtual lake for each data product, and create multiple zones for landing, raw, and curated data. 2. Provide the data engineering teams with full access to the virtual lake assigned to their data product.

   Show answer

   Answer: D

   Explanation

   So the steps would be: 1. Create a Dataplex virtual lake for each data product. 2. Within each lake, create separate zones for landing, raw, and curated data. 3. Provide each data engineering team with access only to the zones they need within their assigned virtual lake. 4. Configure cross-lake sharing on the curated data zones to share curated data products between teams. This provides isolation and access control between teams for raw data while enabling easy sharing of curated data products. https://cloud.google.com/dataplex/docs/introduction#a_domain-centric_data_mesh

   Source: Adapted from community discussion — MaxNRG

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

6. Question 126 · 340 in bank

   You are using BigQuery with a multi-region dataset that includes a table with the daily sales volumes. This table is updated multiple times per day. You need to protect your sales table in case of regional failures with a recovery point objective (RPO) of less than 24 hours, while keeping costs to a minimum. What should you do?

   • ASchedule a daily export of the table to a Cloud Storage dual or multi-region bucket.
   • BSchedule a daily copy of the dataset to a backup region.
   • CSchedule a daily BigQuery snapshot of the table.
   • DModify ETL job to load the data into both the current and another backup region.

   Show answer

   Answer: A

   Explanation

   Why not C: A table snapshot must be in the same region, and under the same organization, as its base table. https://cloud.google.com/bigquery/docs/table-snapshots-intro#limitations

   Source: Adapted from community discussion — MaxNRG

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

7. Question 127 · 340 in bank

   You are troubleshooting your Dataflow pipeline that processes data from Cloud Storage to BigQuery. You have discovered that the Dataflow worker nodes cannot communicate with one another. Your networking team relies on Google Cloud network tags to define firewall rules. You need to identify the issue while following Google-recommended networking security practices. What should you do?

   • ADetermine whether your Dataflow pipeline has a custom network tag set.
   • BDetermine whether there is a firewall rule set to allow traffic on TCP ports 12345 and 12346 for the Dataflow network tag.
   • CDetermine whether there is a firewall rule set to allow traffic on TCP ports 12345 and 12346 on the subnet used by Dataflow workers.
   • DDetermine whether your Dataflow pipeline is deployed with the external IP address option enabled.

   Show answer

   Answer: B

   Explanation

   The best approach would be to check if there is a firewall rule allowing traffic on TCP ports 12345 and 12346 for the Dataflow network tag. Dataflow uses TCP ports 12345 and 12346 for communication between worker nodes. Using network tags and associated firewall rules is a Google-recommended security practice for controlling access between Compute Engine instances like Dataflow workers. So the key things to check would be: 1. Ensure your Dataflow pipeline is using the Dataflow network tag on the worker nodes. This tag is applied by default unless overridden. 2. Check if there is a firewall rule allowing TCP 12345 and 12346 ingress and egress traffic for instances with the Dataflow network tag. If not, add the rule. Options A, C and D relate to other networking aspects but do not directly address the Google recommended practice of using network tags and firewall rules.

   Source: Adapted from community discussion — MaxNRG

   References

   • Google Cloud documentation

   Next question

8. Question 128 · 340 in bank

   Your company's customer_order table in BigQuery stores the order history for 10 million customers, with a table size of 10 PB. You need to create a dashboard for the support team to view the order history. The dashboard has two filters, country_name and username. Both are string data types in the BigQuery table. When a filter is applied, the dashboard fetches the order history from the table and displays the query results. However, the dashboard is slow to show the results when applying the filters to the following query:

   

   How should you redesign the BigQuery table to support faster access?

   • ACluster the table by country and username fields.
   • BCluster the table by country field, and partition by username field.
   • CPartition the table by country and username fields.
   • DPartition the table by _PARTITIONTIME.

   Show answer

   Answer: A

   Explanation

   Correct answer: A. Cluster the table by country and username fields. Why not B and C - > Intiger is required for partitioning https://cloud.google.com/bigquery/docs/partitioned-tables#integer_range

   Source: Adapted from community discussion — datapassionate

   References

   • Google Cloud documentation

   Next question

9. Question 129 · 340 in bank

   You have a Standard Tier Memorystore for Redis instance deployed in a production environment. You need to simulate a Redis instance failover in the most accurate disaster recovery situation, and ensure that the failover has no impact on production data. What should you do?

   • ACreate a Standard Tier Memorystore for Redis instance in the development environment. Initiate a manual failover by using the limited-data-loss data protection mode.
   • BCreate a Standard Tier Memorystore for Redis instance in a development environment. Initiate a manual failover by using the force-data-loss data protection mode.
   • CIncrease one replica to Redis instance in production environment. Initiate a manual failover by using the force-data-loss data protection mode.
   • DInitiate a manual failover by using the limited-data-loss data protection mode to the Memorystore for Redis instance in the production environment.

   Show answer

   Answer: B

   Explanation

   The best option is B - Create a Standard Tier Memorystore for Redis instance in a development environment. Initiate a manual failover by using the force-data-loss data protection mode. The key points are: • The failover should be tested in a separate development environment, not production, to avoid impacting real data. • The force-data-loss mode will simulate a full failover and restart, which is the most accurate test of disaster recovery. • Limited-data-loss mode only fails over reads which does not fully test write capabilities. • Increasing replicas in production and failing over (C) risks losing real production data. • Failing over production (D) also risks impacting real data and traffic. So option B isolates the test from production and uses the most rigorous failover mode to fully validate disaster recovery capabilities.

   Source: Adapted from community discussion — MaxNRG

   References

   • Google Cloud documentation

   Next question

10. Question 130 · 340 in bank

    You are administering a BigQuery dataset that uses a customer-managed encryption key (CMEK). You need to share the dataset with a partner organization that does not have access to your CMEK. What should you do?

    • AProvide the partner organization a copy of your CMEKs to decrypt the data.
    • BExport the tables to parquet files to a Cloud Storage bucket and grant the storageinsights.viewer role on the bucket to the partner organization.
    • CCopy the tables you need to share to a dataset without CMEKs. Create an Analytics Hub listing for this dataset.
    • DCreate an authorized view that contains the CMEK to decrypt the data when accessed.

    Show answer

    Answer: C

    Explanation

    - Create a copy of the necessary tables into a new dataset that doesn't use CMEK, ensuring the data is accessible without requiring the partner to have access to the encryption key. - Analytics Hub can then be used to share this data securely and efficiently with the partner organization, maintaining control and governance over the shared data.

    Source: Adapted from community discussion — raaad

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

PDE practice, question 121. 340 questions available.