Optimizing Data Warehouse Costs with Query Parsing

The Current Landscape of Cost Optimization Practices

As businesses increasingly prioritize optimizing their cloud spend, data platforms have come under closer scrutiny. Operators now demand clearer visibility into their costs, leading to the exploration of various mitigation strategies.

Cloud providers have responded by recommending infrastructure and DevOps-focused solutions. Google emphasizes architectural patterns, AWS advocates for optimized pricing models and waste reduction, and Azure promotes an infrastructure-first approach. Data warehouse vendors like Snowflake and Databricks have followed suit, focusing on resource monitoring and instance sizing. However, these responses predominantly emphasize resource allocation, often overlooking the critical role that query efficiency plays in cost control.

While infrastructure optimizations can yield quick savings, poor configurations can lead to diminishing returns and suboptimal user experiences. The current resource-centric approach lacks the granularity required to balance cost savings with the preservation of user value. A content-based strategy, on the other hand, involves two key aspects: value attribution and content refactoring. This article focuses on content refactoring, specifically by analyzing the queries running within your data warehouse.

The Role of Query Parsing

Data warehouses generate vast amounts of query logs, providing granular insight into data operations. For instance:

• Google BigQuery uses audit logs and information schemas to track bytes billed.
• Snowflake charges based on warehouse usage, but associating costs directly with individual queries can be complex.

Most logs are based on Structured Query Language (SQL), a declarative language that describes what results are needed, but not how to compute them. Importantly, SQL queries can be written in different ways to achieve the same result, with varying levels of efficiency.

Let’s illustrate this with a simple scenario: retrieving employees whose salaries exceed the department average.

SELECT e.employee_id, e.employee_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

The query above recalculates the average salary for each department multiple times. Here’s a more efficient version:

SELECT e.employee_id, e.employee_name, e.salary
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

This version calculates the average once per department, making it significantly more efficient. While both queries produce the same result, the difference in performance can translate to substantial cost savings.

Query Parsers and Optimization

Simply reading a query is insufficient to determine its efficiency. While the same SQL keywords are used, their arrangement—specifically the order of declarations—can significantly impact performance. This is where query parsers come into play. They identify patterns, such as correlated subqueries, common table expressions (CTEs), or inefficient joins, to provide insights into optimization opportunities.

A key tool in this process is the Abstract Syntax Tree (AST). ASTs break down SQL queries into their fundamental components, revealing patterns and inefficiencies that may otherwise go unnoticed.

Example: Analyzing a Simple SQL Query

SELECT a FROM (SELECT a FROM x) AS x;

The AST for this query might look like this:

Select(
   expressions = [Column(this = Identifier(this = a))],
   from = From(this = Subquery(
      this = Select(
         expressions = [Column(this = Identifier(this = a))],
         from = From(this = Table(this = Identifier(this = x)))
      ),
      alias = TableAlias(this = Identifier(this = x)))
   )
)

Credits: SQLGlot

The first SELECT statement contains an expression which contains a single column, a. The from clause consists of a subquery, which itself is a select statement similar to the parent SELECT statement.

To extract components of an SQL statement, then, simply requires traversal through the tree and record query attributes as intended. In the example above, we may traverse through all FROM clauses in any expression and increment the subquery count each time with encounter a Subquery node.

This can be extended in a myriad of ways to find relevant features of a query. For example, we could detect the use of partitioned columns in WHERE clauses to detect how large tables are being queried. Since the AST provides you with the ability to detect base table names (as opposed to common table expressions or subqueries), for an FROM clause, we can extract key value pairs of a table and the set of columns filtered from. Merging this information with the respective tables' information schema, we can surface whether the query uses an available partitioned column or not. This can help triage cases when an entire table is being queried / loaded into memory for further processing.

Knowledge of the underlying database mechanisms is also critical. For instance, we note that Google BigQuery's on-demand pricing model looks at bytes scanned to determine the final cost, and uses columnar storage to make information access performant for analytical use cases. Use of a SELECT * clause could mean that a query loads ALL columns in memory to scan through and retrieve results from, regardless of the number of rows you are interested in. Finding instances of SELECT * and tweaking them can be a quick win with BigQuery. In other cases, e.g. for Apache Spark, SELECT * can make very little difference to cost and performance due to the use of lazy evaluation; loading columns in memory only when actions are applied to them. The data warehouse and a thorough understanding of its underlying mechanism can inform which attributes to extract from an SQL query.

Limitations and Considerations

Not all optimizations directly result in cost reductions. Modern data warehouses use query optimizers like Databricks’ Catalyst Optimizer or Spark’s Adaptive Query Execution to automatically generate efficient execution plans. Moreover, innovations like Databricks’ Liquid Clustering can make partitioning strategies redundant in some cases. Understanding the underlying architecture of your data warehouse is key to making informed decisions about which query attributes to focus on.

Conclusion

Parsing SQL queries to extract meaningful attributes can be a powerful strategy for optimizing data warehouse costs. By leveraging query patterns, ASTs, and a deep understanding of your database’s internal mechanisms, you can refactor queries in ways that dramatically reduce costs without sacrificing performance.

Image Credits: Wikipedia