Exam Professional Data Engineer topic 1 question 43 discussion

Answer will be A because when you create View it does not store extra space and its a logical representation, for rest of the option you need to write large code and extra processing for dataflow/dataproc

Correct: B BigQuery has no quota on the DML statements. (Search Google - does bigquery have quota for update). Why not C: This is a one time activity and SQL is the easiest way to program it. DataFlow is way overkill for this. You will need to find an engineer who can develop DataFlow pipelines. Whereas, SQL is so much more widely known and easier. One of the great features about BigQuery is its SQL interface. Even for BigQueryML services.

E. Say to the IT specialist to take care of it on the app side... B would work for historical data if we had an underlying change made to automate the concatenation for new records. It is not clear, so I would say A is a quick solution.

Requirement is to be able to filter on full name. So, you would be querying all data unless you have materialized full name column.

Definitely A

The question might be outdated, but I would like to offer my perspective: 1. Ideally, I would opt for a materialized view to avoid updating pipelines 2. In 2023, I see no concerns regarding the costs involved in storing denormalized data for analytical needs 3. Regarding this question I would choose option A, although the concern about extra costs due to recalculations is valid for me

Answer should be A 'cos the First request is: make that data available.

Its A ..... "asked to change schema" is a trick to test your skills. Better to make use of MV's if anyhow the application is gonna query repeatedly. MV's will rebuild itself, if query invalidates from cache results

In the case of B, the data pipeline that adds new employee information must also be modified, which is not the correct answer in terms of cost minimization.

It's A. If you add a column to the table, you will be billed every time you query that new column. The same way you would be billed with the view created by A. B,C and D create a new column. A does not create a new column. It just provides the interface for the application to access the data. B,C and D will have to be rerun to compute the column value of new customers. A is done only once, costs 0 for storage, and is charged about the same as all the others when it comes to compute because even if you choose B C and D you would have to query the data in the end anyway.

modify the schema

Can you code a script for a BQ Column? I don't think it's "B", it is pretty tricky

Everything but A) new view is wrong. B) sounds okay, but introduces a new column which means more storage, thus increasing cost. C) Dataflow is obvious overkill for a simple task such as concatenating two strings. D) Starting up a Dataproc cluster just for string concatenation is super overkill.

if a new field is only necessary for one project, and it is only the concatenation of two existing fields, it is ok to create a view that gets used for a specific task.

I'd go for A. The main issue with answers B,C,D is that they are just temporary solution. Whenever a new employee comes in (there are 400.000 of them at the moment, so we can expect every day a few new guys) we need to update the fullname table/field again. Additionally each of these answers need twice as much capacity (BigQuery stores data in a columnar format, so optimizing is not possible). Although the price for the needed capacity will be far below 0.01$/month. The main argument against A is that compute power costs more than capacity. Please look how BQ is priced: https://cloud.google.com/bigquery/pricing#query_pricing In the default On-demand compute pricing it is charged for "the number of bytes processed by each query" so there will be no any difference in computing costs for any option. Yeah, there is also this argument about modyfing schema in the requirements. Lets be professional - it is not a requirement for OUR schema. If you can resolve the issue with 0 change to YOUR schema then you are more than ok. And anyway, from requestor point of view, the schema HE uses in his app will be modifed as he needed.

Should be A.

I vote B. A is expensive, the requirements say we need to minimize cost. B works and meets the requirements. We create an empty column and then UPDATE it to set it to a desired values. https://cloud.google.com/bigquery/docs/managing-table-schemas#console https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#update_statement