Exam Professional Data Engineer topic 1 question 123 discussion

I think AD is the answer. E will not improve performance.

Answer: A, D Description: Denormalization will help in performance by reducing query time, update are not good with bigquery

If we denormalize the data, the Data Science team will shout at us. Preserving it is the way to go

A and D: A- Improve performance D- Is better for DS have all the history and not the last update...

AD is the answer. https://cloud.google.com/bigquery/docs/best-practices-performance-nested Best practice: Use nested and repeated fields to denormalize data storage and increase query performance. Denormalization is a common strategy for increasing read performance for relational datasets that were previously normalized. The recommended way to denormalize data in BigQuery is to use nested and repeated fields. It's best to use this strategy when the relationships are hierarchical and frequently queried together, such as in parent-child relationships.

The criteria for selecting a strategy are the performance and usability for the data science team. This team performs the analysis by querying stored data. So we don't care for performance related with data ingestion. According to this point of view: A: YES - undisputedly favours query performance B: YES - Keeping the structure unchanged promotes usability (the team won't need to update queries or ML models) C: Questionable - Updating the status of a row instead of appending newer versions is keeping the size smaller. But does this affect significantly the analysis performance? Even if it does, creating materialized views to keep the most recent status per row eliminates it D: NO - has nothing to do with DS team's tasks, affects ingestion performance E: NO - demotes usability

The DML quota limit is removed since 2020, I think C is better than D now.

A: Denormalization increases query speed for tables with billions of rows because BigQuery's performance degrades when doing JOINs on large tables, but with a denormalized data structure, you don't have to use JOINs, since all of the data has been combined into one table. Denormalization also makes queries simpler because you do not have to use JOIN clauses. https://cloud.google.com/solutions/bigquery-data-warehouse#denormalizing_data D: BigQuery append

requirements are -> performance and usability. Denormalization will help in performance by reducing query time, update is not good with big query. And append has better performance than Update.

I think AD. E is not valid because it use external table which is not good for performance

A - correct (denormlization will help) B - data already heavily structured (no use and no impact) C - more than 1500 Updates not possible D - Not sure..(because appending will increase size and cost) E - Does not look good (increase cost..also we are storing for all days....again for query we need to issue mutiple query for all days....) So, A & D (left out of 5)

Correct Answer: AE A – Denormalisation helps improve performance. B, C - Not helping to address the problem. D – Append will increase the db size and cost involved for storage and also for large number of records to scan for queries by data science team which is costlier. E - Addresses the problem of maximising the usability of the data science team and the data. They can anayse the data exported to cloud storage instead of reading from bigquery which is expensive and impact performance considerably.

A, D: Using BigQuery as an OLTP store is considered an anti-pattern. Because OLTP stores have a high volume of updates and deletes, they are a mismatch for the data warehouse use case. To decide which storage option best fits your use case, review the Cloud storage products table. BigQuery is built for scale and can scale out as the size of the warehouse grows, so there is no need to delete older data. By keeping the entire history, you can deliver more insight on your business. If the storage cost is a concern, you can take advantage of BigQuery's long term storage pricing by archiving older data and using it for special analysis when the need arises. If you still have good reasons for dropping older data, you can use BigQuery's native support for date-partitioned tables and partition expiration. In other words, BigQuery can automatically delete older data. https://cloud.google.com/solutions/bigquery-data-warehouse#handling_change

should be AC.. "Every hour, thousands of transactions are updated with a new status" if we append how we will handle the new status change..

AC: the problem is exactly about Updating and preserving size of database as much as possible, then denormalization and using UPDATE function from DML will address the issue. they don't want to update faster. then A & C is correct. https://cloud.google.com/solutions/bigquery-data-warehouse

A,D Since the requirements are both performance and usability.

i tink may be ita AC becuase appending its worst to increase dataset size. THe question seems to put like a problem the size of dataset and performance to datascience so inserting more rows decrease performace for them.