Adaptive ETL to optimize query performance at Lyft

Description

In today’s big data world, it’s difficult to manually adjust the structure of your data to achieve optimal query performance. In a company-wide ad hoc query environment, many different types of users access the same data in ever-changing ways, gleaning business insights and asking new questions—leading to new query patterns. With a typical skew of 20% of the queries accounting for 80% of the load, it quickly becomes infeasible to analyze and optimize this manually as a one-time task.

To help with this issue, Lyft developed an automated feedback loop to adapt its ETL based on monitoring the cost of queries run on the system by identifying the most common columns being filtered on in the most expensive queries against the highest cardinality values across all tables; feeding this information into a recommendation engine to determine the optimal column sorting to use at ingest time to achieve the maximum partition pruning by Presto’s Parquet reader; and driving column sorting and distribution during ingest using these recommendations.

James Taylor explores the system and the impact it has had on cluster load over time and discusses future work to enhance the system through the use of materialized views to reduce the number of ad hoc joins and sorting performed by the most expensive queries by transparently rewriting queries when possible.