Exam Professional Data Engineer All Questions

View all questions & answers for the Professional Data Engineer exam

Exam Professional Data Engineer topic 1 question 130 discussion

Actual exam question from Google's Professional Data Engineer

Question #: 130
Topic #: 1

[All Professional Data Engineer Questions]

The marketing team at your organization provides regular updates of a segment of your customer dataset. The marketing team has given you a CSV with 1 million records that must be updated in BigQuery. When you use the UPDATE statement in BigQuery, you receive a quotaExceeded error. What should you do?

A. Reduce the number of records updated each day to stay within the BigQuery UPDATE DML statement limit.
B. Increase the BigQuery UPDATE DML statement limit in the Quota management section of the Google Cloud Platform Console.
C. Split the source CSV file into smaller CSV files in Cloud Storage to reduce the number of BigQuery UPDATE DML statements per BigQuery job.
D. Import the new records from the CSV file into a new BigQuery table. Create a BigQuery job that merges the new records with the existing records and writes the results to a new BigQuery table.

Show Suggested Answer

Suggested Answer: D 🗳️

by madhu1171 at March 15, 2020, 4:01 p.m.

Comments

Submit Cancel

rickywck

Highly Voted 4 years, 3 months ago

Should be D. https://cloud.google.com/blog/products/gcp/performing-large-scale-mutations-in-bigquery

upvoted 30 times

Rajuuu

3 years, 11 months ago

There is no mention about merge or limit in the link provided.

upvoted 3 times

Chelseajcole

2 years, 8 months ago

A common scenario within OLAP systems involves updating existing data based on new information arriving from source systems (such as OLTP databases) on a periodic basis. In the retail business, inventory updates are typically done in this fashion. The following query demonstrates how to perform batch updates to the Inventory table based on the contents of another table (where new arrivals are kept) using the MERGE statement in BigQuery:

upvoted 2 times

...

[Removed]

Highly Voted 4 years, 2 months ago

Should be D https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement https://cloud.google.com/blog/products/gcp/performing-large-scale-mutations-in-bigquery

upvoted 16 times

AzureDP900

1 year, 5 months ago

D is right

upvoted 1 times

...

AACHB

2 years, 6 months ago

I had it in the exam (14/12/2021)

upvoted 3 times

nellyoaid

2 years, 6 months ago

Please what was the answer? @AACHB

upvoted 2 times

...

GCPLearning2021

2 years, 5 months ago

Does examtopics questions help?

upvoted 1 times

...

Nirca

Most Recent 8 months, 2 weeks ago

Selected Answer: D

Should be D.

upvoted 1 times

...

vaga1

1 year ago

Selected Answer: D

import all the data into a separate table and use that for updates is better than creating smaller csv which leads to more operational time to get it done and harder to manage it.

upvoted 1 times

...

juliobs

1 year, 3 months ago

Selected Answer: D

This limit was removed a long time ago already. Anyway, bulk imports are better.

upvoted 2 times

...

Atnafu

1 year, 7 months ago

D BigQuery DML statements have no quota limits. https://cloud.google.com/bigquery/quotas#data-manipulation-language-statements However, DML statements are counted toward the maximum number of table operations per day and partition modifications per day. DML statements will not fail due to these limits. In addition, DML statements are subject to the maximum rate of table metadata update operations. If you exceed this limit, retry the operation using exponential backoff between retries.

upvoted 2 times

...

MisuLava

1 year, 7 months ago

there is no update quota anymore. but i would say D

upvoted 2 times

...

amitsingla012

2 years, 1 month ago

Option D is the right answer

upvoted 1 times

...

tavva_prudhvi

2 years, 2 months ago

No DML limits from 3rd march 2020 But if the questions is given in the exam, choose D asfor the options A, B,C as they are speaking about the limitations of the DML Limits. Atleast, D is giving an alternative!

upvoted 1 times

...

nidnid

2 years, 4 months ago

Is this question still valid? What about DML without limits? https://cloud.google.com/blog/products/data-analytics/dml-without-limits-now-in-bigquery

upvoted 3 times

...

MaxNRG

2 years, 5 months ago

Selected Answer: D

D: BigQuery is primarly designed and suit to append-only technology with some limited DML statements. It's not a relational database where you constantly update your user records if they edit their profile. Instead you need to arhitect your code so each edit is a new row in Bigquery, and you always query the latest row. The DML statement limitation is low, because it targets different scenarios and not yours, aka live update on rows. You could ingest your data into a separate table, and issue 1 update statement per day. https://stackoverflow.com/questions/45183082/can-we-increase-update-quota-in-bigquery https://cloud.google.com/blog/products/gcp/performing-large-scale-mutations-in-bigquery

upvoted 2 times

...

medeis_jar

2 years, 5 months ago

Selected Answer: D

https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement https://cloud.google.com/blog/products/gcp/performing-large-scale-mutations-in-bigquery

upvoted 2 times

...