Workload mangement in Databricks SQL, Snowflake, and BigQuery
In a world where data keeps growing, it’s important to know how cloud data platforms scale to handle bigger workloads. This article breaks down how three popular platforms that I have been working on — Databricks SQL, Snowflake, and BigQuery — scale both vertically and horizontally.
The scaling approaches of Snowflake, Databricks SQL (DBSQL), and BigQuery are indeed influenced by their underlying technologies and architectural philosophies. Lets deep dive.
Databricks SQL:
A SQL warehouse is a compute resource that lets you query and explore data on Databricks. Each warehouse contains multiple clusters, and DBSQL automatically scales the number of clusters to handle concurrency demands.
Philosophy to scaling: Serverless DBSQL leverages Databricks’ built-in AI-driven workload manager, with Spark as its foundation. The system intelligently routes and load-balances queries across different clusters within the warehouse, optimizing performance. AI-powered predictions analyze incoming queries to determine the fastest and most efficient execution path, while adjustments are made to reduce overhead and improve responsiveness.
Vertical Scaling:manual
- In DB SQL, vertical scaling is referred to as “t-shirt sizing”. Each t-shirt size corresponds to a specific configuration for the cluster within the warehouse, such as the number of workers. Larger t-shirt sizes provide more CPU and RAM, enabling the processing of higher volumes of data or more complex queries that require significant resources.
Horizontal Scaling:automatic
- Horizontal scaling in DB SQL involves “autoscaling”; setting the minimum and maximum number of clusters that a warehouse can utilize to meet concurrency requirements. If the system consistently hits the maximum number of clusters and user queries are being queued, you can increase the max clusters. Conversely, if auto-scaling frequently exceeds the minimum number of clusters, increasing the min clusters can reduce cluster spin-up time, improving user experience.
Technical Detail: For highly concurrent workloads, the AI-driven manager automatically adds clusters based on the volume of incoming queries. This avoids bottlenecks, prevents queuing, and improves overall user experience.
BigQuery:
In BigQuery, scaling is indirectly handled through the allocation of slots. Slots are the fundamental units of compute power in BigQuery, consisting of CPU and RAM. When you run a query, BigQuery automatically allocates the required number of slots to process that query based on its complexity and data volume. Slots are central to both vertical and horizontal scaling in BigQuery.
In BigQuery, the concepts of vertical and horizontal scaling are not applied in the traditional sense, as seen in other database systems. BigQuery abstracts the concepts of vertical and horizontal scaling away from the user, allowing Google’s infrastructure to manage these aspects automatically. This makes BigQuery easy to use, as you don’t need to manually manage resources — BigQuery dynamically adjusts resources based on the query’s needs.
Philosophy to scaling: BigQuery was developed as a serverless data warehouse, drawing on Google’s experience with its internal data processing tools (e.g., Dremel) and cloud infrastructure.
Vertical Scaling:automatic
- BigQuery doesn’t require manual vertical scaling like adjusting CPU or memory. Instead, it automatically allocates the necessary compute resources for your queries. This means that you don’t have to worry about selecting instance sizes or adjusting compute power — BigQuery handles it behind the scenes.
Technical Detail: BigQuery uses a shared-nothing architecture where data is distributed across multiple nodes. Queries are processed in parallel across these nodes, with resources allocated dynamically based on the complexity and volume of the query. This allows BigQuery to efficiently manage compute resources without user intervention
Horizontal Scaling:automatic
- BigQuery is inherently designed for automatic horizontal scaling and to handle high levels of concurrency. It distributes data across multiple servers and processes queries in parallel. This distributed architecture allows BigQuery to handle large datasets and complex queries efficiently by spreading the workload across many nodes.
Technical Detail: Internally, BigQuery uses a combination of Dremel-style columnar storage and a highly distributed execution engine. The system scales up resource allocation by managing the number of slots across a large pool of compute resources, dynamically adjusting based on query requirements.
Snowflake:
A Snowflake virtual warehouse is a scalable, isolated compute cluster that executes SQL queries and performs data processing tasks in the Snowflake data platform. It operates independently of the data storage layer and can be resized or scaled out to handle varying workloads, providing efficient and elastic compute power.
Philosophy to scaling: Snowflake was designed from the ground up with SQL-based data warehousing in mind. It integrates SQL processing with a cloud-native architecture. Snowflake’s SQL-centric design means that its scaling capabilities are closely tied to optimizing SQL query performance and handling large volumes of data efficiently within the SQL paradigm.
Vertical scaling:manual
- Vertical scaling in Snowflake involves increasing the size of a virtual warehouse to provide more CPU and memory resources. This is done by selecting a larger warehouse size, which helps improve performance for demanding queries or data processing tasks. Vertical scaling is a manual adjustment that enhances the compute power of a single warehouse to handle more intensive workloads
Technical Detail: Snowflake’s architecture separates storage and compute, with compute resources (virtual warehouses) providing SQL query processing. Vertical scaling involves changing the size of the warehouse, which adjusts the number of compute nodes and the amount of CPU and memory allocated to handle complex queries and large datasets.
Horizontal scaling:automatic
- Horizontal scaling in Snowflake is achieved through a feature called multi-cluster warehouses. Snowflake allows you to efficiently handle fluctuating workloads by dynamically adjusting the number of compute clusters. Snowflake automatically starts additional clusters within the warehouse to share the load. These clusters run in parallel, allowing Snowflake to process multiple queries simultaneously
Technical Detail: Multi-cluster warehouses use a shared data architecture where each cluster operates independently but accesses the same data storage layer. This approach enables Snowflake to distribute queries across multiple clusters, balancing the load and reducing query response times.
