Hard1 markMultiple Choice
Domain 5: Managing Implementation and Ensuring ReliabilityDomain 5BigQueryCost OptimizationData Analytics

GCP PCA · Question 48 · Domain 5: Managing Implementation and Ensuring Reliability

Your data analytics team runs massive SQL queries on BigQuery. The CFO has noticed that BigQuery costs are spiraling out of control because analysts are running SELECT * queries on petabyte-scale tables. Which THREE strategies should you implement to optimize BigQuery costs? (Select THREE)

Answer options:

A.

Implement table partitioning based on a date/time column.

B.

Switch the BigQuery billing model from On-Demand to Flat-Rate (Capacity Pricing) and buy exactly 100 slots.

C.

Implement table clustering on frequently filtered columns.

D.

Export the data to Cloud Storage and use Dataproc to run the queries instead.

E.

Enforce maximum bytes billed limits at the project and user level.

F.

Compress the data using gzip before loading it into BigQuery.

How to approach this question

BigQuery on-demand pricing is based on 'bytes scanned'. To reduce costs, you must reduce bytes scanned (Partitioning, Clustering) or set hard limits (Quotas).

Full Answer

A,C,E
BigQuery's on-demand pricing charges based on the amount of data scanned during a query. `SELECT *` scans the entire table. To optimize costs, you must reduce the data scanned. Partitioning (by date) and Clustering (by frequently filtered columns) allow BigQuery to prune data and only scan the relevant blocks. Setting Custom Quotas (maximum bytes billed) acts as a financial circuit breaker to stop analysts from accidentally spending thousands of dollars on a single bad query.

Common mistakes

Thinking compression helps (F). BigQuery handles its own storage optimization. You are billed on the logical size of the data scanned, not the compressed size.

Practice the full GCP Professional Cloud Architect Practice Exam 3

50 questions · hints · full answers · grading

More questions from this exam