Less is More: Engineering Data Warehouse Efficiency with Minimalist Design

Maintaining Uber’slarge-scale data warehouse comes with an operational cost in terms of ETL functions and storage. In our experience, optimizing for operational efficiency requires answering one key question: for which tables does the maintenance cost supersede utility? Once identified, we can off-board these tables from our warehouse and migrate the relevant use cases to lower-cost analytics engines, achieving greater overall efficiency.

With petabytes of data to manage, answering the above question is crucial for Uber. However, this turns out to be an interesting data science problem that raises many more considerations:

In order to address our central question—determining which tables should be off-boarded from our central data warehouse, Uber’s Data Infrastructure and Data Science teams joined forces to solve this optimization problem.

To tackle this optimization problem, we split it into two parts. First was the computational cost of running queries. Since there are millions of queries that run against our analytics database, we decided to group queries into query classes. At a minimum, a query class consists of queries using the same set of tables but can be further classified by other criteria, such as the type of operators used and the resources consumed.

Let’s assume there are Q query classes. For each query class q, we also keep track of the number of queries, L, and the total cost of running all the queries belonging to a query class, V. The query cost is calculated based on the compute resources utilized by the query. Thus, the total compute cost due to all the queries can be represented as:

Above Xis a binary decision variable. X= 1 indicates that query classshould continue to execute on the given database. In turn, this means that all the tables needed to support query classshould also be available in the database.

The second aspect of formalizing the optimization problem is the cost of maintaining tables, which includes storage and ETL costs. But utility is another important factor. Ideally, the cost of table maintenance should take into account the table’s utility, i.e., if a table has high utility, its effective maintenance cost should be considered low. We model utility using table weight multiplied by table cost. The table weight is computed based on weekly active users pertaining to a given table.

We leverage an inverse sigmoid model in order to decrease the effective cost of the table with the increasing number of users. Further, we use sigmoid function-related parameters to control the slope and the point where the function crosses 0.5 on the y-axis.

Thus, the cost of maintaining all the tables can be represented as:

Above Wis the weight function that represents the reciprocal of utility. Dis the cost of maintaining a table. Yis a binary decision variable that indicates whether a table is assigned to the database or not.

The total operational cost of a database is the sum of its compute and maintenance costs, depicted below:

In order to identify tables that should be off-boarded, we need to minimize the above cost function subjected to the following four constraints. These constraints serve as guardrails, preserving the cost function’s utility for our use case:

Constraint 1 and 2 enforce the decision variables X and Y to be binary.