PyData Berlin
Bye-Bye Query Spaghetti: Write Queries You'll Actually Understand Using Pipelined SQL Syntax
Talk
Abstract
Are your SQL queries becoming tangled webs that are difficult to decipher, debug, and maintain? This talk explores how to write shorter, more debuggable, and extensible SQL code using **Pipelined SQL**, an alternative syntax where queries are written as **a series of orthogonal, understandable steps**. We'll survey which databases and query engines currently support pipelined SQL natively or through extensions, and how it can be used on any platform by compiling pipelined SQL to any SQL dialect using open-source tools. A series of real-world examples, comparing traditional and pipelined SQL syntax side by side for a variety of use cases, will show you how to simplify existing code and make complex data transformations intuitive and manageable.
Prerequisites
The talk is best suited for those who have some practical experience with SQL and can recognize the challenges of managing complex query logic in traditional SQL syntax. Attendees should be comfortable with fundamental SQL concepts (SELECT, WHERE, GROUP BY, JOINs) and have experience writing SQL queries. While not necessarily experts, attendees who have encountered or wrestled with complex SQL queries, such as those involving deeply nested subqueries or extensive Common Table Expressions (CTEs), will better appreciate the problems Pipelined SQL aims to solve and the advantages it offers.
Description
This session introduces Pipelined SQL, an alternative syntax for writing complex data queries as a clear, sequential flow of manageable transformations within a single query.
Traditional SQL combines filtering (WHERE), aggregation (GROUP BY), and projection (SELECT expressions) within a single, monolithic block. This can make it challenging to discern individual data transformations or modify one aspect without impacting others. Pipelined SQL, in contrast, encourages building queries like an assembly line. You'll learn to structure your query logic so that each step performs a specific transformation and cleanly passes its result to the next. This pipelined approach, moving away from deeply nested subqueries or sprawling Common Table Expressions (CTEs), leads to queries that are more readable by easily following the logic from start to finish. As an added benefit, the resulting code is simpler to debug and also easily extendable by additional transformation steps.
The talk will explain the core concepts of Pipelined SQL, how it differs from traditional SQL, what its main advantages over traditional SQL are. Native support for pipelined syntax is steadily growing across many modern databases, query engines and cloud data warehouses. We will explore the landscape of emerging dialects and identify which platforms currently offer native support or extensions for this powerful syntax. The session also covers a range of open-source tools that can compile such pipelined query code into any traditional SQL dialect, making this approach suitable for almost any platform.
Through practical, real-world examples using BigQuery's pipe syntax, you'll see side-by-side comparisons demonstrating how Pipelined SQL can drastically reduce complexity and improve clarity for common data manipulation tasks. Prepare for genuine 'a-ha!' moments as you discover how Pipelined SQL offers refreshingly simple approaches to tasks that usually require convoluted traditional SQL.
This session is ideal for data analysts, scientists, engineers, and anyone with basic SQL knowledge who wants to write cleaner, more robust, and more maintainable queries. You'll leave with a solid understanding of Pipelined SQL's benefits and practical knowledge to start simplifying your own SQL workflows.
Speaker
Tobias Lampert
Analytics Engineer
An accomplished technical leader, Tobias brings over two decades of experience in software development, complemented by profound expertise in Data Science and Data Engineering. His career has focused on the end-to-end design and implementation of complex data-intensive applications, spanning the full lifecycle from data ingestion to deployment. In his current role at Lotum he is tackling a data volume of several hundred million events from mobile games per day.