Exam Professional Data Engineer topic 1 question 3 discussion

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

A is incorrect because adding space won't solve the problem of query performance. B is incorrect because there is nothing related to the report generation which is specified and sharding tables on date ranges is not a good option as it will create many tables. C is CORRECT because the statement says "the scope of the project has expanded. The database must now store 100 times more patient records". As the data increases there would be difficulty in managing the tables and querying it. Hence creating different table is correct as per the need. D is Incorrect as it Partitions on each clinic. We have to adjust the database design so that it performs optimally when generating reports. Also nothing is specified for generation of reports in the required statement.

Because you can optimize the storage of each table; furthermore, you will avoid self-joins.

answer is C, the problem here is the self-join (avoid self-join if possible) on a Denormalized table. So the solution is to Normalize

Normalizing the database into separate Patients and Visits tables, along with creating other necessary tables, is the best solution for handling the increased data size while ensuring efficient query performance and maintainability. This approach addresses the root problem instead of applying temporary fixes.

C is the most suitable solution for this situation. It provides a better way for scalability and monitoring. B has a constraint on predefined date range, which is usually not suitable for reporting.

Normalization is a technique used to organize data in a relational database to reduce data redundancy and improve data integrity. Breaking the patient records into separate tables (patient and visits) and eliminating self-joins will make the database more scalable and improve query performance. It also helps maintain data integrity and makes it easier to manage large datasets efficiently. Options A, B, and D may provide some benefits in specific cases, but for a scenario where the project scope has expanded significantly and there are performance issues with self-joins, normalization (Option C) is the most robust and scalable solution.

Normalization is a technique used to organize data in a relational database to reduce data redundancy and improve data integrity. Breaking the patient records into separate tables (patient and visits) and eliminating self-joins will make the database more scalable and improve query performance. It also helps maintain data integrity and makes it easier to manage large datasets efficiently. Options A, B, and D may provide some benefits in specific cases, but for a scenario where the project scope has expanded significantly and there are performance issues with self-joins, normalization (Option C) is the most robust and scalable solution.

"100 times more patient records"immediately brings to create a patient dimensional table to save space on disk if a generical relational database is mentioned.

C - https://cloud.google.com/bigquery/docs/best-practices-performance-patterns

C- This is correct have verified from different sources

Should be C. Basic ER design...

c - is the correct one.

C should be the correct answer

C- Is the correct answer!

C - based on Google documentation, self-join is an anti-pattern: https://cloud.google.com/bigquery/docs/best-practices-performance-patterns

Correct