PDE practice questions 221–230

1. Question 221 · 340 in bank

   You designed a database for patient records as a pilot project to cover a few hundred patients in three clinics. Your design used a single database table to represent all patients and their visits, and you used self-joins to generate reports. The server resource utilization was at 50%. Since then, the scope of the project has expanded. The database must now store 100 times more patient records. You can no longer run the reports, because they either take too long or they encounter errors with insufficient compute resources. How should you adjust the database design?

   • AAdd capacity (memory and disk space) to the database server by the order of 200.
   • BShard the tables into smaller ones based on date ranges, and only generate reports with prespecified date ranges.
   • CNormalize the master patient-record table into the patient table and the visits table, and create other necessary tables to avoid self-join.
   • DPartition the table into smaller tables, with one for each clinic. Run queries against the smaller table pairs, and use unions for consolidated reports.

   Show answer

   Answer: C

   Explanation

   C is correct because this option provides the least amount of inconvenience over using pre-specified date ranges or one table per clinic while also increasing performance due to avoiding self-joins. A is not correct because adding additional compute resources is not a recommended way to resolve database schema problems. B is not correct because this will reduce the functionality of the database and make running reports more difficult. D is not correct because this will likely increase the number of tables so much that it will be more difficult to generate reports vs. the correct option. https://cloud.google.com/bigquery/docs/best-practices-performance-patterns https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#explicit-alias-visibility

   Source: Adapted from community discussion — MaxNRG

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

2. Question 222 · 340 in bank

   Your company's customer and order databases are often under heavy load. This makes performing analytics against them difficult without harming operations. The databases are in a MySQL cluster, with nightly backups taken using mysqldump. You want to perform analytics with minimal impact on operations. What should you do?

   • AAdd a node to the MySQL cluster and build an OLAP cube there.
   • BUse an ETL tool to load the data from MySQL into Google BigQuery.
   • CConnect an on-premises Apache Hadoop cluster to MySQL and perform ETL.
   • DMount the backups to Google Cloud SQL, and then process the data using Google Cloud Dataproc.

   Show answer

   Answer: B

   Explanation

   I think it is B and not D: 1) There are no info regarding date freshness required for analytics. So nightly backup might be not enough as a source because it will only provide info one tie a day. 2) Dataproc is recommended as easiest way for migration of hadoop processes. SO no reason to use Dataproc for designing a new analytics processes. 3) The solution is really very limited if you will extend it in the future and add new data sources or create new aggregate tables. Where they should be created? 4) There is no info on which version is on prem MySQL database (I am not an expert in MySql) but I can imagine there might be compartibility issue for backup / restore between different releases

   Source: Adapted from community discussion — Alexej_123

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

3. Question 223 · 340 in bank

   You currently have transactional data stored on-premises in a PostgreSQL database. To modernize your data environment, you want to run transactional workloads and support analytics needs with a single database. You need to move to Google Cloud without changing database management systems, and minimize cost and complexity. What should you do?

   • AMigrate and modernize your database with Cloud Spanner.
   • BMigrate your workloads to AlloyDB for PostgreSQL.
   • CMigrate to BigQuery to optimize analytics.
   • DMigrate your PostgreSQL database to Cloud SQL for PostgreSQL.

   Show answer

   Answer: B

   Explanation

   In real life clearly how performant it needed to be would be a massive factor. AlloyDB is more expensive (see https://cloud.google.com/alloydb/pricing, vs https://cloud.google.com/sql/pricing), but when they say "minimise cost" is that per query, or is it per year assuming similar instance size. There's no way for us to know, we have to guess. I'm guessing AlloyDB, as the question seem to be telegraphing that, but it could just as easily be CloudSQL postgres based on the cheaper costs. We simply cannot know.

   Source: Adapted from community discussion — baimus

   References

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

   Next question

4. Question 224 · 340 in bank

   You are architecting a data transformation solution for BigQuery. Your developers are proficient with SQL and want to use the ELT development technique. In addition, your developers need an intuitive coding environment and the ability to manage SQL as code. You need to identify a solution for your developers to build these pipelines. What should you do?

   • AUse Dataform to build, manage, and schedule SQL pipelines.
   • BUse Dataflow jobs to read data from Pub/Sub, transform the data, and load the data to BigQuery.
   • CUse Data Fusion to build and execute ETL pipelines.
   • DUse Cloud Composer to load data and run SQL pipelines by using the BigQuery job operators.

   Show answer

   Answer: A

   Explanation

   - Aligns with ELT Approach: Dataform is designed for ELT (Extract, Load, Transform) pipelines, directly executing SQL transformations within BigQuery, matching the developers' preference. -SQL as Code: It enables developers to write and manage SQL transformations as code, promoting version control, collaboration, and testing. - Intuitive Coding Environment: Dataform provides a user-friendly interface and familiar SQL syntax, making it easy for SQL-proficient developers to adopt. - Scheduling and Orchestration: It includes built-in scheduling capabilities to automate pipeline execution, simplifying pipeline management.

   Source: Adapted from community discussion — raaad

   Next question

5. Question 225 · 340 in bank

   You work for a farming company. You have one BigQuery table named sensors, which is about 500 MB and contains the list of your 5000 sensors, with columns for id, name, and location. This table is updated every hour. Each sensor generates one metric every 30 seconds along with a timestamp, which you want to store in BigQuery. You want to run an analytical query on the data once a week for monitoring purposes. You also want to minimize costs. What data model should you use?

   • A1. Create a metrics column in the sensors table. 2. Set RECORD type and REPEATED mode for the metrics column. 3. Use an UPDATE statement every 30 seconds to add new metrics.
   • B1. Create a metrics column in the sensors table. 2. Set RECORD type and REPEATED mode for the metrics column. 3. Use an INSERT statement every 30 seconds to add new metrics.
   • C1. Create a metrics table partitioned by timestamp. 2. Create a sensorId column in the metrics table, that points to the id column in the sensors table. 3. Use an INSERT statement every 30 seconds to append new metrics to the metrics table. 4. Join the two tables, if needed, when running the analytical query.
   • D1. Create a metrics table partitioned by timestamp. 2. Create a sensorId column in the metrics table, which points to the id column in the sensors table. 3. Use an UPDATE statement every 30 seconds to append new metrics to the metrics table. 4. Join the two tables, if needed, when running the analytical query.

   Show answer

   Answer: C

   Explanation

   Partitioned Metrics Table: Creating a separate metrics table partitioned by timestamp is a standard practice for time-series data like sensor readings. Partitioning by timestamp allows for more efficient querying, especially when you're only interested in a specific time range (like weekly monitoring). Reference to Sensors Table: Including a sensorId column that references the id column in the sensors table allows you to maintain a relationship between the metrics and the sensors without duplicating sensor information. INSERT Every 30 Seconds: Using an INSERT statement every 30 seconds to the partitioned metrics table is a standard approach for time-series data ingestion in BigQuery. It allows for efficient data storage and querying. Join for Analysis: When you need to analyze the data, you can join the metrics table with the sensors table based on the sensorId, allowing for comprehensive analysis with sensor details.

   Source: Adapted from community discussion — raaad

   References

   • Google Cloud documentation

   Next question

6. Question 226 · 340 in bank

   You are managing a Dataplex environment with raw and curated zones. A data engineering team is uploading JSON and CSV files to a bucket asset in the curated zone but the files are not being automatically discovered by Dataplex. What should you do to ensure that the files are discovered by Dataplex?

   • AMove the JSON and CSV files to the raw zone.
   • BEnable auto-discovery of files for the curated zone.
   • CUse the bg command-line tool to load the JSON and CSV files into BigQuery tables.
   • DGrant object level access to the CSV and JSON files in Cloud Storage.

   Show answer

   Answer: A

   Explanation

   While none of the original options (A, B, C, or D) directly address the issue, the closest solution is: Move the JSON and CSV files to a raw zone. (This was previously marked as the most voted option, but it's not ideal due to data organization disruption) Here's why this approach might be necessary (but not ideal): Dataplex curated zones currently don't support native processing of JSON and CSV formats. They are designed for structured data formats like Parquet, Avro, or ORC.

   Source: Adapted from community discussion — Anudeep58

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

7. Question 227 · 340 in bank

   You have a table that contains millions of rows of sales data, partitioned by date. Various applications and users query this data many times a minute. The query requires aggregating values by using AVG, MAX, and SUM, and does not require joining to other tables. The required aggregations are only computed over the past year of data, though you need to retain full historical data in the base tables. You want to ensure that the query results always include the latest data from the tables, while also reducing computation cost, maintenance overhead, and duration. What should you do?

   • ACreate a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
   • BCreate a materialized view to aggregate the base table data. Configure a partition expiration on the base table to retain only the last one year of partitions.
   • CCreate a view to aggregate the base table data. Include a filter clause to specify the last year of partitions.
   • DCreate a new table that aggregates the base table data. Include a filter clause to specify the last year of partitions. Set up a scheduled query to recreate the new table every hour.

   Show answer

   Answer: A

   Explanation

   - Materialized View: Materialized views in BigQuery are precomputed views that periodically cache the result of a query for increased performance and efficiency. They are especially beneficial for heavy and repetitive aggregation queries. - Filter for Recent Data: Including a clause to focus on the last year of partitions ensures that the materialized view is only storing and updating the relevant data, optimizing storage and refresh time. - Always Up-to-date: Materialized views are maintained by BigQuery and automatically updated at regular intervals, ensuring they include the latest data up to a certain freshness point.

   Source: Adapted from community discussion — raaad

   References

   • Google Cloud documentation

   Next question

8. Question 228 · 340 in bank

   Your organization uses a multi-cloud data storage strategy, storing data in Cloud Storage, and data in Amazon Web Services’ (AWS) S3 storage buckets. All data resides in US regions. You want to query up-to-date data by using BigQuery, regardless of which cloud the data is stored in. You need to allow users to query the tables from BigQuery without giving direct access to the data in the storage buckets. What should you do?

   • ASetup a BigQuery Omni connection to the AWS S3 bucket data. Create BigLake tables over the Cloud Storage and S3 data and query the data using BigQuery directly.
   • BSet up a BigQuery Omni connection to the AWS S3 bucket data. Create external tables over the Cloud Storage and S3 data and query the data using BigQuery directly.
   • CUse the Storage Transfer Service to copy data from the AWS S3 buckets to Cloud Storage buckets. Create BigLake tables over the Cloud Storage data and query the data using BigQuery directly.
   • DUse the Storage Transfer Service to copy data from the AWS S3 buckets to Cloud Storage buckets. Create external tables over the Cloud Storage data and query the data using BigQuery directly.

   Show answer

   Answer: A

   Explanation

   - BigQuery Omni: This is an extension of BigQuery that allows you to analyze data across Google Cloud, AWS, and Azure without having to manage the infrastructure or move data across clouds. It's suitable for querying data stored in AWS S3 buckets directly. - BigLake: Allows you to create a logical abstraction (table) over data stored in Cloud Storage and S3, so you can query data using BigQuery without moving it. - Unified Querying: By setting up BigQuery Omni to connect to AWS S3 and creating BigLake tables over both Cloud Storage and S3 data, you can query all data using BigQuery directly.

   Source: Adapted from community discussion — raaad

   References

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

   Next question

9. Question 229 · 340 in bank

   You are preparing an organization-wide dataset. You need to preprocess customer data stored in a restricted bucket in Cloud Storage. The data will be used to create consumer analyses. You need to comply with data privacy requirements. What should you do?

   • AUse Dataflow and the Cloud Data Loss Prevention API to mask sensitive data. Write the processed data in BigQuery.
   • BUse customer-managed encryption keys (CMEK) to directly encrypt the data in Cloud Storage. Use federated queries from BigQuery. Share the encryption key by following the principle of least privilege.
   • CUse the Cloud Data Loss Prevention API and Dataflow to detect and remove sensitive fields from the data in Cloud Storage. Write the filtered data in BigQuery.
   • DUse Dataflow and Cloud KMS to encrypt sensitive fields and write the encrypted data in BigQuery. Share the encryption key by following the principle of least privilege.

   Show answer

   Answer: A

   Explanation

   - Prioritizes Data Privacy: It protects sensitive information by masking it, reducing the risk of exposure in case of unauthorized access or accidental leaks. - Reduces Data Sensitivity: Masking renders sensitive data unusable for attackers, even if they gain access to it. - Preserves Data Utility: Masked data can still be used for consumer analyses, as patterns and relationships are often preserved, allowing meaningful insights to be derived.

   Source: Adapted from community discussion — raaad

   Next question

10. Question 230 · 340 in bank

    You need to connect multiple applications with dynamic public IP addresses to a Cloud SQL instance. You configured users with strong passwords and enforced the SSL connection to your Cloud SQL instance. You want to use Cloud SQL public IP and ensure that you have secured connections. What should you do?

    • AAdd CIDR 0.0.0.0/0 network to Authorized Network. Use Identity and Access Management (IAM) to add users.
    • BAdd all application networks to Authorized Network and regularly update them.
    • CLeave the Authorized Network empty. Use Cloud SQL Auth proxy on all applications.
    • DAdd CIDR 0.0.0.0/0 network to Authorized Network. Use Cloud SQL Auth proxy on all applications.

    Show answer

    Answer: C

    Explanation

    - Using the Cloud SQL Auth proxy is a recommended method for secure connections, especially when dealing with dynamic IP addresses. - The Auth proxy provides secure access to your Cloud SQL instance without the need for Authorized Networks or managing IP addresses. - It works by encapsulating database traffic and forwarding it through a secure tunnel, using Google's IAM for authentication. - Leaving the Authorized Networks empty means you're not allowing any direct connections based on IP addresses, relying entirely on the Auth proxy for secure connectivity. This is a secure and flexible solution, especially for applications with dynamic IPs.

    Source: Adapted from community discussion — raaad

    References

    • Google Cloud documentation

    Next question

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

PDE practice, question 221. 340 questions available.