Minimum Viable SQL Patterns
I’m obsessed with patterns.
They help you build on the combined experience of practitioners and codify best practices that have been battle-tested in the real world. Every pattern deals with a specific, recurring problem in the domain and provides proven solutions.
When experts work on a particular problem, it’s unusual for them to tackle it by creating new solutions. They often recall similar problems they (or others) have already solved and reuse the essence of the solution. That essence is codified as a pattern.
Who am I
I’ve been writing SQL for nearly 20 years. I’ve seen and written hundreds of thousands of lines of code. Over time I noticed a set of patterns and best practices I always came back to when writing queries. These patterns made my code more efficient, easier to understand and a breeze to maintain.
Why I wrote this book
I have a background in computer science. As part of the curriculum we learn how to make our code more efficient, more readable, easier to maintain and debug. As I started to write SQL I applied many of these lessons to my own code.
When reviewing other people’s code I kept spotting the same mistakes. Chunks of code that would repeat everywhere; 500+ lines queries that were complex and slow; indecipherable CTE names and table aliases that made no sense, etc.. I would often have to rewrite the queries just so I could understand what they were doing.
After doing this over and over again I thought there must be a book or course that teaches analysts and data scientists code design patterns. They already exist in the world of software engineering and they're used extensively to tame the complexity of software system design. But why now?
In recent years SQL has gone from being just a querying language to being used for heavy duty, in-database data transformation. Especially with the advent of tools like dbt it has become a lot easier to use SQL for building complex data workflows. As you would expect there were tens if not hundreds of thousands of SQL books, database books, data modeling books, etc. but nothing that fit my need. So I wrote one myself.
Who this book is for
This book is for anyone who is familiar with SQL and wants to take their skills to the next level. I assume you're already familiar basic SQL syntax and you know how to join tables and do basic filtering and aggregation.
- If you're using SQL to build complex data processing workflows – like I have – this book is a must for you.
- If you find that your SQL code is often inefficient and slow and you want to make it faster, this book is for you.
- If you find that your SQL code is long, messy and hard to understand and you want to make it cleaner, this book is for you
- If you find that your SQL code breaks easily when data changes and you want to make it more resilient, this book is for you.
- If you work (or plan to work) with tools like dbt or sqlmesh this book is a must for you.
Contents
- Introduction
- Chapter 1: Understanding the Database
- Understanding the Data Model
- Chapter 2: Core Concepts and Patterns
- Concept 1: Granularity
- Concept 2: Granularity Collapsing
- Concept 3: Granularity Multiplication
- Concept 4: Granularity Addition
- Chapter 3: Modularity Patterns
- Concept 1: Modularity
- Concept 2: Don’t Repeat Yourself (DRY)
- Concept 3: Single Responsibility Principle (SRP)
- Concept 4: Move Logic Upstream
- Chapter 4: Performance Patterns
- Concept 1: Reducing Unnecessary Processing
- Concept 2: Keep the WHERE Clause Simple
- Chapter 5: Robustness Patterns
- Concept 1: Handling Type Conversions
- Concept 2: Handling NULLs
- Concept 3: Handling Division By Zero
- Concept 4: Handling Inconsistent Comparisons
- Concept 5: Handling Schema Changes
- Chapter 6: Finishing the Project
- Project Remarks
- Chapter 7: dbt Patterns
- Applying Robustness Patterns
- Applying Modularity Patterns
- Applying SRP Patterns