I have been trying to understand the limitations of BigQuery based on how it works under the hood, but it's hard for me to find an answer to the question in the title: Can BigQuery queries get assigned to a different root nodes?
I currently work with Redshift where there is only one cluster with one leader node. Everything goes through and is put together by the leader node. If the leader node does not have enough compute or storage, that means lower concurrency capabilities. Leader node is a bottleneck.
What I am trying to understand is, do we have a similar problem in BigQuery? I know we have a root node akin to the leader node in Redshift when a query is run. See picture below:
Google claims you can practically never run out of resources, which implies that BigQuery doesn't have the same issue as Redshift, but there is no clear explanation. Can the Dremel node tree depicted above be spun up multiple times based on how much compute you require? Could the root node be a bottleneck?
BigQuery architecturally works different than Redshift. Whenever a query is executed the work gets parallelly processed in slots by BQ. There is no master node which controls the resources however, it has an internal scheduler which allots the resources automatically. We don't have to worry about the resources being allocated since BQ handles it under the hood. That is why you can never run out of resources.
There are however scenarios where the waiting time (in the job execution) of a query might take long, in those cases the best way is to buy additional BQ slots for your query execution.
@Jordan Tigani explained the dremel architecture and query execution process of BigQuery here which you can refer for more information.