PROFESSIONAL-DATA-ENGINEER Question #321: Real Exam Question with Answer & Explanation

Question

The data analyst team at your company uses BigQuery for ad-hoc queries and scheduled SQL pipelines in a Google Cloud project with a slot reservation of 2000 slots. However, with the recent introduction of hundreds of new non time-sensitive SQL pipelines, the team is encountering frequent quota errors. You examine the logs and notice that approximately 1500 queries are being triggered concurrently during peak time. You need to resolve the concurrency issue. What should you do?

Options

• AIncrease the slot capacity of the project with baseline as 0 and maximum reservation size as 3000.
• BUpdate SQL pipelines to run as a batch query, and run ad-hoc queries as interactive query jobs.
• CIncrease the slot capacity of the project with baseline as 2000 and maximum reservation size as 3000.
• DUpdate SQL pipelines and ad-hoc queries to run as interactive query jobs.

Explanation

Option B is correct because BigQuery enforces a concurrent interactive query limit (100 per project by default), and with ~1500 queries running simultaneously, that limit is what's causing the quota errors - not slot capacity. Converting the non-time-sensitive pipelines to batch queries removes them from this concurrency limit since batch jobs are queued and execute whenever slots are free, while keeping ad-hoc queries as interactive ensures analysts still get immediate results.

A and C are wrong because adding more slots (with autoscaling or a higher baseline) addresses throughput/compute capacity, not the concurrent query limit - you'd still hit the concurrency quota with 1500 simultaneous interactive queries regardless of slot count. D is wrong because running everything as interactive makes the problem worse - it increases the number of concurrent interactive queries rather than offloading the non-urgent ones to the batch queue.

Memory tip: Think "Batch = Background, Interactive = Immediate." Non-time-sensitive pipelines are perfect batch candidates - they don't need to run right now, so let BigQuery queue and schedule them, freeing up concurrency headroom for the ad-hoc work that analysts actually need fast.

No community discussion yet for this question.