SQL remains the language for data. Developed back in the 1970s, it’s one of the few technologies that has remained constant. Regardless of what drag and drop tools come around or what new query paradigms try to overtake it.
SQL remains the most widely used technologies to interact with data. With the advent of databases that utilize NOSQL or (Not Only SQL), layers like Presto and Hive have been developed on top, to provide a friendly SQL interact.
Not only that, but the use of SQL has far expanded beyond data engineers and analysts. Product managers, analytical partners, and software engineers at large tech companies all use SQL to access data and answer questions quickly.
The point is, SQL is worth knowing. But once you know the basics, how do you progress? What takes a SQL user from novice to advanced?
Over the past few years, we’ve spent a lot of time writing SQL for data pipelines, dashboards, data products, and other odds and ends.
We don’t think advanced SQL is about syntax. There aren’t too many fancy clauses after you learn about analytic clauses. Sure, you can loop in SQL and even edit files. However, they’re all actions that can occur in code.
So what separates basic SQL users from advanced SQL users? We believe it’s more about thinking big picture. Advanced SQL developers think long-term vs short-term. They develop SQL that is maintainable, easy to read, and that requires more time and consideration.
In this article, we’ll focus on many of the design decisions that we believe separate novice SQL developers from senior and advanced SQL developers. You’ll notice that this goes beyond SQL. A lot of it will go into more conceptual problems, where there aren’t definite answers to the best solutions.
The format of the tips will be problem or behavior, followed by solution or improved methods. In fact, some of the solutions could be considered design preferences. Some of you might even disagree with the tips we give here. Please leave comments if so — we would love to discuss them further.
With that, let’s get into learning!
Putting Logic In Your SQL vs In a Table
Case clauses are very useful. However, there are two specific ways that some people use them that can come back to bite you.
First, duplicative logic.
What do we mean by duplicative logic? We mean using the same case statement in multiple queries, views, and pipelines.
For example, below, we have the same logic in multiple places. In this case, in an ad-hoc query that is used in a data analysi
#big-data #sql #programming #data-science #technology #data analysis