To resolve the concurrency issue in BigQuery caused by the introduction of hundreds of non-time-sensitive SQL pipelines, the best approach is to differentiate the types of queries based on their urgency and resource requirements. Here's why option C is the best choice:
* SQL Pipelines as Batch Queries:
* Batch queriesin BigQuery are designed for non-time-sensitive operations. They run in a lower priority queue and do not consume slots immediately, which helps to reduce the overall slot consumption during peak times.
* By converting non-time-sensitive SQL pipelines to batch queries, you can significantly alleviate the pressure on slot reservations.
* Ad-Hoc Queries as Interactive Queries:
* Interactive queriesare prioritized to run immediately and are suitable for ad-hoc analysis where users expect quick results.
* Running ad-hoc queries as interactive jobs ensures that analysts can get their results without delay, improving productivity and user satisfaction.
* Concurrency Management:
* This approach helps balance the workload by leveraging BigQuery's ability to handle different types of queries efficiently, reducing the likelihood of encountering quota errors due to slot exhaustion.
Steps to Implement:
* Identify Non-Time-Sensitive Pipelines:
* Review and identify SQL pipelines that are not time-critical and can be executed as batch jobs.
* Update Pipelines to Batch Queries:
* Modify these pipelines to run as batch queries. This can be done by setting the priority of the query job to BATCH.
* Ensure Ad-Hoc Queries are Interactive:
* Ensure that all ad-hoc queries are submitted as interactive jobs, allowing them to run with higher priority and immediate slot allocation.
Reference Links:
* BigQuery Batch Queries
* BigQuery Slot Allocation and Management

Explanation
The conventional method of denormalizing data involves simply writing a fact, along with all its dimensions, into a flat table structure. For example, if you are dealing with sales transactions, you would write each individual fact to a record, along with the accompanying dimensions such as order and customer information.
The other method for denormalizing data takes advantage of BigQuery's native support for nested and repeated structures in JSON or Avro input data. Expressing records using nested and repeated structures can provide a more natural representation of the underlying data. In the case of the sales order, the outer part of a JSON structure would contain the order and customer information, and the inner part of the structure would contain the individual line items of the order, which would be represented as nested, repeated elements.
Reference: https://cloud.google.com/solutions/bigquery-data-warehouse#denormalizing_data