Task 5.3: Organizing workloads based on business requirements.

• AMove the BI projects to on-demand pricing during business hours

  Incorrect

  On-demand capacity is shared and not reserved, so it guarantees nothing, and BI would stop using idle reserved slots.

• BCreate separate ELT and BI reservations, each with its own baseline

  Correct

  Separate reservations give each workload its own baseline slots, so ELT cannot take the slots BI is guaranteed.

• CAssign the BI projects and the ELT projects to their own reservations

  Correct

  Projects use the most specific reservation they are assigned to, so project assignments route each workload to its reservation.

• DGive the ELT queries batch priority so that they queue behind BI

  Incorrect

  Query priority reserves no capacity for the BI workload, so it gives BI no guarantee.

• ESet ignore_idle_slots to true on both of the reservations

  Incorrect

  ignore_idle_slots true stops a reservation from borrowing idle slots, which is the sharing the company wants to keep.

Reservations isolate workloads: each has baseline slots, and assignments map projects, folders or the organization to a reservation, with the most specific assignment winning. Idle slots can flow between reservations unless ignore_idle_slots is set.

• ATurn on query queues with a lower concurrency target for the project

  Incorrect

  An on-demand project's concurrency target is computed dynamically and cannot be set, and a lower target would make more queries wait, analysts' included; it does not put the reports behind them.

• BRewrite the reports as scheduled queries that write to tables

  Incorrect

  Scheduled queries still run as ordinary queries competing for the same capacity.

• CBuy an Enterprise edition reservation for the analysts' project

  Incorrect

  Buying capacity is a bigger change than needed and still leaves both workloads in one project on the same reservation.

• DSubmit the report queries with batch priority instead of interactive

  Correct

  Batch queries have lower priority than interactive ones and are more likely to wait in the queue when capacity is busy, which is fine for reports read the next morning.

BigQuery runs queries as interactive by default, starting them as soon as possible. Batch priority marks a query as lower priority, so when the project's capacity is busy the batch queries wait and the interactive ones go first; once a batch query starts it runs like any other.

• AEnterprise edition

  Correct

  Enterprise includes CMEK, row-level and column-level security and BigQuery ML, without paying for the Enterprise Plus extras that are not needed.

• BStandard edition

  Incorrect

  Standard edition has no CMEK, no fine-grained security controls and no BigQuery ML.

• CEnterprise Plus edition

  Incorrect

  Enterprise Plus adds managed disaster recovery and Assured Workloads, which are not needed, at a higher slot price.

• DStandard edition with BI Engine

  Incorrect

  BI Engine is not available in Standard edition, and it would not add the missing security features anyway.

BigQuery editions differ in features as well as price. Standard lacks CMEK, VPC Service Controls, row-level and column-level security, BigQuery ML and BI Engine; Enterprise adds them; Enterprise Plus adds managed disaster recovery and Assured Workloads compliance controls.

• AA baseline of 1,000 slots with no autoscaling

  Incorrect

  Baseline slots are paid for all the time, so 1,000 idle slots cost the most during the idle hours.

• BA 1,000-slot capacity commitment for one year

  Incorrect

  A commitment pays for capacity continuously, the opposite of paying little while idle.

• CA zero baseline with autoscaling up to 1,000 slots

  Correct

  Autoscaled slots are added on demand and billed per second while in use, so a zero baseline pays nothing for capacity while the team is idle.

• DA 500-slot baseline with ignore_idle_slots set to true

  Incorrect

  A baseline still bills when idle, and ignoring idle slots only stops borrowing; it does not lower the bill.

A reservation combines baseline slots, which are always available and always billed, with autoscaling up to a maximum, which is billed only while used. Google recommends a non-zero baseline when predictable performance matters; for a bursty, cost-driven workload, a zero baseline with autoscaling fits.

• AThe INFORMATION_SCHEMA.TABLE_STORAGE view, summed per project

  Incorrect

  Storage metrics say nothing about the compute capacity queries used.

• BA load test that replays a week of queries in a sandbox project

  Incorrect

  A custom replay costs time and money and reproduces what the built-in estimator already models.

• CThe query plan of the single largest query of the month

  Incorrect

  One query's plan cannot describe the concurrency and pattern of the whole workload.

• DThe slot estimator in the BigQuery capacity management pages

  Correct

  The slot estimator shows 30 days of slot usage and models how other reservation sizes would affect performance, with cost-optimal recommendations.

Capacity planning should start from measured demand. The slot estimator charts the last 30 days of slot capacity and usage at hourly granularity, models changes in maximum slots against that history, and recommends commitment and autoscaling slots for edition workloads.

• AA read-only replica added to the instance for the analysts

  Incorrect

  Read-only replicas are provisioned compute that is paid for all the time, which is the capacity the team did not want to add.

• BSpanner Data Boost for the federated analytics queries

  Correct

  Data Boost runs eligible queries on independent, serverless compute with near-zero impact on the provisioned instance, billed only for the processing used.

• CStale reads with a 15-second bound for the federated queries

  Incorrect

  Stale reads avoid waiting for the latest data, but they still run on the instance's own compute.

• DA nightly export of the database to Cloud Storage for the analysts

  Incorrect

  A nightly export misses the hourly need and also runs on the instance unless it uses Data Boost.

Separating analytical from transactional load can be done without provisioning more nodes: Spanner Data Boost gives federated queries from BigQuery and exports their own serverless compute, so analytics barely touches the instance that serves the application.