97 Things Every Data Engineer Should Know

Chapter 35. Getting the “Structured” Back into SQL

Elias Nema

Not that many questions in computer science have been circulating for nearly 50 years and are still around. How to write SQL is one of them.

Relational databases have dominated the market since the ’70s. Then the whole NoSQL movement arose and smoothly evolved into “NewSQL.” Recently, all major streaming systems have been adding support for SQL. There must be something really powerful about this language.

With great power comes—you know. SQL is so flexible that it allows you to write queries in almost any form and still get results. The problem is that understanding whether the result makes sense usually requires more effort than producing it. You’ve seen it yourself, “fixing” joins with a DISTINCT statement or counting rows multiple times.

I argue that most of this can be avoided by writing queries in a structured manner, optimizing for readability first. So how do you make structure? First, begin with the end in mind: what should the answer look like? For example, say you want to analyze revenue for a specific sales channel, categorized by regions. See, it’s already a prepared SELECT statement. (In this example, I’m using pseudo-SQL to avoid unrelated details.)

Usually, the main subject would be in the question ...