Google Associate Cloud Engineer Practice Test 53
Question 520
A Spanner tenant adds a change stream but consumers assume event messages are unique and apply duplicate updates when partitions retry. What consumer design is required?
- Use position/sequence or transaction metadata to process change-stream records idempotently and handle partition lifecycle.
- Use supported transaction retries with exponential backoff and reduce unnecessary overlapping mutations or transaction duration.
- Increase client connection retries without examining the Spanner schema or consistency requirement.
- Store the hot key values in a single monotonically increasing primary-key range.
Explanation
Correct Answer
A:Change stream consumers can encounter retries or partition changes; exactly-once business effects require consumer-level controls.
Incorrect Answers
B:It does not supply the specified transactional schema or consistency behavior. The necessary distinction is the transactional schema or consistency requirement. The appropriate action here is: Use position/sequence or transaction metadata to process change-stream records idempotently and handle partition lifecycle.
C:It does not supply the specified transactional schema or consistency behavior. The case instead depends on the transactional schema or consistency requirement. The appropriate action here is: Use position/sequence or transaction metadata to process change-stream records idempotently and handle partition lifecycle.
D:It does not supply the specified transactional schema or consistency behavior. It leaves the transactional schema or consistency requirement unaddressed. The appropriate action here is: Use position/sequence or transaction metadata to process change-stream records idempotently and handle partition lifecycle.
Question 521
A BigQuery query scans an entire date-partitioned table because the WHERE clause wraps the partition key in a non-prunable expression. What should be rewritten?
- Use idempotent partition replacement or MERGE keyed on stable event identifiers rather than unconditional appends.
- Increase BigQuery query concurrency while leaving the table design and filters unchanged.
- Filter directly on the partition column with supported constant bounds to enable partition pruning.
- Copy the data to a Compute Engine disk and run the same analytics workload without query changes.
Explanation
Correct Answer
C:Partitioned tables only reduce scanned partitions when the query predicate allows the optimizer to eliminate irrelevant partitions.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. The case instead depends on the requested BigQuery operation. The appropriate action here is: Filter directly on the partition column with supported constant bounds to enable partition pruning.
B:The alternative does not address the query or storage behavior driving the workload. It leaves the requested BigQuery operation unaddressed. The appropriate action here is: Filter directly on the partition column with supported constant bounds to enable partition pruning.
D:The alternative does not address the query or storage behavior driving the workload. The specific requirement concerns the requested BigQuery operation. The appropriate action here is: Filter directly on the partition column with supported constant bounds to enable partition pruning.
Question 522
A query repeatedly reads a large table to compute one small aggregation every minute; a full rescan exceeds the budget. Which optimization should be evaluated?
- Make supported HTTP range requests or object slicing reads when the file format supports random access.
- Move objects into an unrelated project without first checking their location and policies.
- Use a suitable materialized view or incremental pre-aggregation and check refresh eligibility and consistency.
- Increase the caller VM memory without modifying any bucket or object behavior.
Explanation
Correct Answer
C:Materialized aggregations can avoid repeated full scans for supported patterns, subject to freshness and maintenance constraints.
Incorrect Answers
A:The option does not enforce the required object location, access or lifecycle behavior. It leaves the object's required data or access behavior unaddressed. The appropriate action here is: Use a suitable materialized view or incremental pre-aggregation and check refresh eligibility and consistency.
B:The option does not enforce the required object location, access or lifecycle behavior. The specific requirement concerns the object's required data or access behavior. The appropriate action here is: Use a suitable materialized view or incremental pre-aggregation and check refresh eligibility and consistency.
D:Memory sizing may change capacity but cannot replace the specified platform feature. The necessary distinction is the object's required data or access behavior.
Question 523
A BigQuery scheduled query inserts duplicates into a partition after a scheduler retry. Which write design should be used?
- Filter directly on the partition column with supported constant bounds to enable partition pruning.
- Set a shorter result-cache lifetime as the only correction for the described query behavior.
- Copy the data to a Compute Engine disk and run the same analytics workload without query changes.
- Use idempotent partition replacement or MERGE keyed on stable event identifiers rather than unconditional appends.
Explanation
Correct Answer
D:Scheduled execution can retry; data pipelines must not create duplicate business records when a task is repeated.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. The specific requirement concerns the requested BigQuery operation. The appropriate action here is: Use idempotent partition replacement or MERGE keyed on stable event identifiers rather than unconditional appends.
B:The alternative does not address the query or storage behavior driving the workload. The necessary distinction is the requested BigQuery operation. The appropriate action here is: Use idempotent partition replacement or MERGE keyed on stable event identifiers rather than unconditional appends.
C:The alternative does not address the query or storage behavior driving the workload. The case instead depends on the requested BigQuery operation. The appropriate action here is: Use idempotent partition replacement or MERGE keyed on stable event identifiers rather than unconditional appends.
Question 524
A BigQuery query is blocked with a location error because its job runs in US while the referenced dataset is in EU. What should change?
- Grant dataset-level access for the approved dataset and remove the unnecessarily broad project grant.
- Increase BigQuery query concurrency while leaving the table design and filters unchanged.
- Set a shorter result-cache lifetime as the only correction for the described query behavior.
- Run the query in the dataset's authorized compatible processing location or redesign data residency appropriately.
Explanation
Correct Answer
D:BigQuery job location must align with referenced datasets; a project-wide region choice does not move data automatically.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. The necessary distinction is the requested BigQuery operation. The appropriate action here is: Run the query in the dataset's authorized compatible processing location or redesign data residency appropriately.
B:The alternative does not address the query or storage behavior driving the workload. The case instead depends on the requested BigQuery operation. The appropriate action here is: Run the query in the dataset's authorized compatible processing location or redesign data residency appropriately.
C:The alternative does not address the query or storage behavior driving the workload. It leaves the requested BigQuery operation unaddressed. The appropriate action here is: Run the query in the dataset's authorized compatible processing location or redesign data residency appropriately.
Question 525
An analytics user needs access to one narrow dataset but has BigQuery Data Viewer at the entire project scope. What should be changed?
- Run the query in the dataset's authorized compatible processing location or redesign data residency appropriately.
- Materialize an unrestricted full-table copy every minute regardless of data volume.
- Add a dataset label instead of changing the query or table feature involved.
- Grant dataset-level access for the approved dataset and remove the unnecessarily broad project grant.
Explanation
Correct Answer
D:BigQuery permissions can be scoped to datasets; project-wide data access may expose unrelated tables.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. The case instead depends on the requested BigQuery operation. The appropriate action here is: Grant dataset-level access for the approved dataset and remove the unnecessarily broad project grant.
B:The alternative does not address the query or storage behavior driving the workload. It leaves the requested BigQuery operation unaddressed. The appropriate action here is: Grant dataset-level access for the approved dataset and remove the unnecessarily broad project grant.
C:The alternative does not address the query or storage behavior driving the workload. The specific requirement concerns the requested BigQuery operation. The appropriate action here is: Grant dataset-level access for the approved dataset and remove the unnecessarily broad project grant.
Question 526
A tenant needs restricted access to sensitive rows of one shared BigQuery table without creating separate physical tables. Which feature fits?
- Define approved row access policies and test query behavior for each authorized principal.
- Use Bigtable key/value reads for this SQL analytical reporting workflow.
- Use Cloud SQL as a single-instance replacement for the requested large-scale analytics query.
- Store the dataset only as Cloud Storage objects and assume SQL aggregation happens automatically.
Explanation
Correct Answer
A:BigQuery row-level access policies filter table visibility by identity, independent of dataset-level metadata access.
Incorrect Answers
B:The alternative does not address the query or storage behavior driving the workload. It leaves the requested BigQuery operation unaddressed. The appropriate action here is: Define approved row access policies and test query behavior for each authorized principal.
C:The alternative does not address the query or storage behavior driving the workload. The specific requirement concerns the requested BigQuery operation. The appropriate action here is: Define approved row access policies and test query behavior for each authorized principal.
D:The alternative does not address the query or storage behavior driving the workload. The necessary distinction is the requested BigQuery operation. The appropriate action here is: Define approved row access policies and test query behavior for each authorized principal.
Question 527
A finance table exposes credit-card columns to general analysts even though they should see only permitted fields. What should be configured?
- Define approved row access policies and test query behavior for each authorized principal.
- Copy the data to a Compute Engine disk and run the same analytics workload without query changes.
- Add a dataset label instead of changing the query or table feature involved.
- Apply BigQuery policy tags/data masking or column-level security and verify authorized role permissions.
Explanation
Correct Answer
D:Column-level controls restrict sensitive fields without requiring a complete duplicate of the table for every user group.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. The specific requirement concerns the requested BigQuery operation. The appropriate action here is: Apply BigQuery policy tags/data masking or column-level security and verify authorized role permissions.
B:The alternative does not address the query or storage behavior driving the workload. The necessary distinction is the requested BigQuery operation. The appropriate action here is: Apply BigQuery policy tags/data masking or column-level security and verify authorized role permissions.
C:The alternative does not address the query or storage behavior driving the workload. The case instead depends on the requested BigQuery operation. The appropriate action here is: Apply BigQuery policy tags/data masking or column-level security and verify authorized role permissions.
Question 528
An interactive BigQuery report is slow because large concurrent batch queries contend for the same reservation capacity. What should be inspected?
- Run the query in the dataset's authorized compatible processing location or redesign data residency appropriately.
- Inspect slot usage, reservation assignment and workload priorities, then allocate supported separate capacity for critical queries.
- Materialize an unrestricted full-table copy every minute regardless of data volume.
- Set a shorter result-cache lifetime as the only correction for the described query behavior.
Explanation
Correct Answer
B:BigQuery query latency depends on allocated slots and job scheduling, not merely dataset storage size.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. The necessary distinction is the requested BigQuery operation. The appropriate action here is: Inspect slot usage, reservation assignment and workload priorities, then allocate supported separate capacity for critical queries.
C:The alternative does not address the query or storage behavior driving the workload. The case instead depends on the requested BigQuery operation. The appropriate action here is: Inspect slot usage, reservation assignment and workload priorities, then allocate supported separate capacity for critical queries.
D:The alternative does not address the query or storage behavior driving the workload. It leaves the requested BigQuery operation unaddressed. The appropriate action here is: Inspect slot usage, reservation assignment and workload priorities, then allocate supported separate capacity for critical queries.
Question 529
A team accidentally truncates a BigQuery table and needs yesterday's contents within the supported recovery window. What should they use?
- Review the external connection identity or caller's required Cloud Storage read permissions separately from BigQuery dataset authorization.
- Grant every analyst BigQuery Admin without modifying the requested dataset behavior.
- Recover the appropriate historical table state through supported time travel or snapshot mechanisms and verify before replacement.
- Increase BigQuery query concurrency while leaving the table design and filters unchanged.
Explanation
Correct Answer
C:BigQuery time travel permits querying or restoring recent table states within configured windows; dropping data need not always mean losing the recovery point.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. The case instead depends on the requested BigQuery operation. The appropriate action here is: Recover the appropriate historical table state through supported time travel or snapshot mechanisms and verify before replacement.
B:The alternative does not address the query or storage behavior driving the workload. It leaves the requested BigQuery operation unaddressed. The appropriate action here is: Recover the appropriate historical table state through supported time travel or snapshot mechanisms and verify before replacement.
D:The alternative does not address the query or storage behavior driving the workload. The specific requirement concerns the requested BigQuery operation. The appropriate action here is: Recover the appropriate historical table state through supported time travel or snapshot mechanisms and verify before replacement.
Question 530
A BigQuery external table reads Cloud Storage objects but an auditor expects BigQuery table ACLs to grant every user direct object-storage access. What boundary must be checked?
- Define approved row access policies and test query behavior for each authorized principal.
- Set a shorter result-cache lifetime as the only correction for the described query behavior.
- Review the external connection identity or caller's required Cloud Storage read permissions separately from BigQuery dataset authorization.
- Increase BigQuery query concurrency while leaving the table design and filters unchanged.
Explanation
Correct Answer
C:External data sources have independent storage permissions; BigQuery access does not automatically grant Google Cloud Storage object read rights.
Incorrect Answers
A:The alternative does not address the query or storage behavior driving the workload. It leaves the requested BigQuery operation unaddressed. The appropriate action here is: Review the external connection identity or caller's required Cloud Storage read permissions separately from BigQuery dataset authorization.
B:The alternative does not address the query or storage behavior driving the workload. The specific requirement concerns the requested BigQuery operation. The appropriate action here is: Review the external connection identity or caller's required Cloud Storage read permissions separately from BigQuery dataset authorization.
D:The alternative does not address the query or storage behavior driving the workload. The necessary distinction is the requested BigQuery operation. The appropriate action here is: Review the external connection identity or caller's required Cloud Storage read permissions separately from BigQuery dataset authorization.