Critics of SQL (structured query language) have tried, but they’ve never truly managed to chip away at its appeal. Even decades after it was invented, most of the world’s databases still rely on SQL, and most data analysis still takes place through SQL queries. It isn’t much of a leap to say the digital world runs on SQL.
Yet for all its popularity, SQL still has limitations that reduce how useful it is, even for advanced users. In this interview with Fivetran co-founder and CEO George Fraser, we look at one of them: the fact that SQL lacks an open source ecosystem of software libraries for handling certain common use cases, and that those libraries would work across popular SQL systems. Because of that, knowledge gained on one SQL database may not carry over to another, and far too many complicated queries are getting written.
This is a hard issue to fix because of the way the SQL ecosystem works, but solving it could spark a whole new wave of innovation in data analysis. And Fraser believes one potential answer is already in front of us.
To make sure everyone can follow along, can you give a quick explanation of what SQL is?
GEORGE FRASER: SQL is a programming language used only for working with databases. It is everywhere, sitting beneath the surface of nearly every software application. When you open your Facebook feed, all the data about who commented on what, what your uncle just posted, is stored across a bunch of SQL databases. And when the page loads, a whole set of SQL queries runs to retrieve all that information.
The same is true if, for example, you’re taking your car in for repairs – the information about what has been done to your car is likely kept in a SQL database somewhere. This Zoom call we’re on right now, I’m sure there are a bunch of records in a SQL database somewhere that represent this call. Really, the world runs on SQL.
Structured data – in the case of a social media post, that could be ‘name,’ ‘post content,’ ‘time of post,’ whether it contains an image, things like that – is usually kept in SQL databases. There are other types of databases, but their usage is tiny compared with SQL databases.
And yet, people are always saying that SQL can’t do certain things, or that SQL is not something else. Why is that?
I can’t remember the exact original saying, but it’s something like, ‘There are two kinds of technology: technology people complain about, and technology that doesn’t matter.’ Database management systems and SQL, the language used to interact with them 99 percent of the time, were among the earliest computer applications. When computers were invented, one of the first things people built was databases, because one of the most useful things you can do with computers is keep a lot of data, modify it, pull it back out, and summarize it. So this goes way back.
SQL itself was developed in the ’70s, and it has many strong qualities. It was also extremely successful and became broadly adopted. It’s a bit like the air we breathe. At this point, for technologists, it’s like asking a fish about water.
People criticize it because it isn’t flawless. Nothing is. But it’s difficult to alter for a number of reasons. And some of its flaws have really persisted for a very long time.
Software library
Code that carries out specific, clearly defined operations, often alongside or on top of an application’s or language’s built-in capabilities. Popular library examples include pandas for data analysis in Python, MLlib for machine learning with Apache Spark, and PostGIS for working with geographic data in SQL.
One of those flaws, which you’ve written about, is that SQL isn’t a library language – you can’t conveniently pair software libraries with it. Why is that something worth fixing?
If I step back one level, there are many problems with SQL. And some of them are minor issues that probably aren’t worth addressing. People like to note – and this is a kind of programmer inside-baseball – that the order of the clauses is arguably incorrect and that it would have been better in a different order. But by now, it’s not a major issue and it’s far too late to change. I view that as a small problem. There’s a reason that hasn’t turned out to be SQL’s fatal flaw. And there are other things like this that are small problems.
But I think there is one really large problem with SQL, which is that it isn’t a library language. The open source software revolution, which has transformed how we build every other kind of software, has not reached SQL.
This matters especially when you’re trying to use SQL for analytical workloads. There is no need for libraries when you’re using SQL the way it is used in my examples of your data on Facebook, or the data about your car repair, or the data about this call we’re on right now. You’re just pulling records and updating them one at a time. It isn’t a library language, but who cares?
But because 99 percent of the world’s important data lives in SQL databases, people use SQL not only to retrieve data, but to actually summarize and analyze it. And when analysts write SQL, they write large SQL queries that are highly complex and that do fancy things like rolling averages and nearly anything you can think of. And there, the fact that there is no open source SQL ecosystem, that it is not a strong library language, is a major problem. Because every analyst who uses SQL to analyze data has to begin from scratch.
That’s the situation we’re still in with SQL, but I think there is a way out.
Analytical databases
Analytical databases are used to support business decision-making through dashboards, reports, and other forms of data analysis. This is different from transactional databases, which generally read, write, and fetch application data in response to events (such as someone making a car-repair appointment online.)
Why doesn’t SQL have an ecosystem of software libraries?
I think there are two reasons. One is that SQL is not really one language. Every database management system that supports SQL – and there are a lot of them – supports a somewhat different SQL. If we’re talking about analytics, we’re talking about perhaps 10 databases. That alone makes it harder because whatever you build, you’re going to have to build it 10 times.
And the other reason is that there is simply no way to distribute SQL. Even if you create an amazing open source SQL library for a specific database, how on earth do you get it to other people? There is no package manager for SQL. Golang has a built-in package manager. Rust has Cargo. Java has Maven. Every programming language has some package manager that either came with the language at the start, or achieved community adoption or escape velocity, and then became the de facto package manager. That’s how you share code, and until recently SQL had no package manager.
There are actually a couple of open source libraries for SQL. My favorite example is PostGIS, which is the exception to what I’m talking about. It’s a library for Postgres that handles geographic data, something a lot of people wanted to do. Even with all those obstacles, it was so valuable to a small group of people – and that’s what you really need when you’re doing new things: you need to appeal a lot to a few people and not a little to everyone – that they would install binaries on their database by following instructions from websites. Then the big cloud vendors, because PostGIS was popular, simply pre-packaged it with their databases. So through heroic effort, people were able to adopt this one example of an SQL library, for a particular flavor of SQL, Postgres, over the years, in practice.
But if you look at that exception, you can see the issue: It’s extremely difficult to get distribution for an open source SQL library.
How do you solve this problem, given the way the SQL ecosystem works today?
I think the answer is dbt, a build tool and a package manager for SQL. Package managers let programmers share code. I can write some code, publish it to a package repository, and then you can use that code immediately in your project right now.
Build tools are a little harder to explain. If you’re a programmer and you write a lot of code, your job still isn’t finished. Something always has to happen to turn that code into something that actually does something. In the dbt example, you need to deploy that code into a database. Or you need to compile that code into a program or God knows what else. There is always some kind of build step where you take this code, which is basically a bunch of text written by a human being, and turn it into something that is actually useful. In dbt’s case, what it does is deploy that code into the database so it really starts doing things in the real world, instead of just sitting on your screen and staring back at you all day long.
Now, if dbt were to become the platform that makes this possible, it would need to be almost ubiquitous, which I think could happen. SQL analysts had never really had good developer tools. They only had proprietary products made by companies trying to sell them their database, or whatever it was. I like to joke that dbt was analysts’ first good relationship, and so they’re all intensely loyal to dbt for now anyway.
And we’ve started to see this happen to some extent already, although it’s not a perfect example of what we’re talking about. But Fivetran, for example, built a library that gets reused across our dozens of dbt models, so users don’t have to reinstall and relearn the same things for each model they want to use. The fragmentation problem, that each database management system implements slightly different SQL, should be manageable at the database level because if you’re targeting analysts who use SQL for data analysis, you’re basically just targeting Snowflake, Databricks, BigQuery, Redshift, and SQL Server. Maybe somebody else will break through and then there will be one more, but it’s a reasonable number, not a thousand in the end here today.
“The open source software revolution, which has changed how we build every other kind of software, has not come to SQL.”
SQL databases have been around forever, so what changed over the past several years that means we need more libraries and a better overall developer experience now?
I think it’s the performance of SQL databases for analytical workloads. Years ago, there were no SQL databases—except really, really expensive ones—that were fast enough for complex analytical workloads. So when you wanted to do a complex analytical workload on a large set of data, you would pull the data out of the database and put it into a special-purpose tool, often an OLAP cube optimized to run very specific kinds of queries very quickly. These OLAP cubes all have their own languages, tools, GUIs, and whatever else. It was very much a commercial ecosystem that was not centered around a single language like SQL is in practice.
In the last 10 years, though, SQL databases got so fast and cheap for analytical workloads that a lot of this has simply moved down into the database. A lot of these special-purpose data-analysis tools that you would connect to just vanished. The database was fast enough on its own, and better in some ways because it was more flexible, so people started doing a lot of their analysis right in the database. That led to more SQL code and more complicated code, which created the need for a better way to organize it and build it well.
Before, everyone was just using ad hoc SQL build processes. It could be as simple as copying and pasting some code from one place to another and pressing a button to run it. That would work fine if your code was not that complicated and there were only, like, two people in the whole company who worked on this part of it. But then as people started doing their analysis from soup to nuts inside the database, they needed something like dbt.
Assuming your theory bears fruit, what do you think would make for useful or popular SQL libraries?
Time-series analysis might be it. Doing rolling averages and things like that is very awkward in SQL using the built-in capabilities, like window functions in practice.
Another really useful open-source library I would love to see is approximate aggregation. It’s a thing that exists in all these different databases, but it’s usually not very user-friendly, so people hardly use it. Or it’s just different across systems, so nobody bothers to learn it; they just learn the regular average. And, boy, it would be nice if there were just a uniform way to handle approximate aggregate cases. It would be great if someone wrote a friendly wrapper around the built-in approximate aggregation capabilities of popular systems, and then as a user you could just use that there.
“Open source code was an absolute revolution in software development, so the same thing could happen for SQL developers – it could be a catalyst.”
What’s the net effect on the SQL ecosystem if this idea takes off and becomes hugely popular?
Well, open source code was an absolute revolution in software development, so the same thing could happen for SQL developers – it could be a catalyst. You could see these widely used libraries emerge, the ones everyone learns, puts on their LinkedIn profiles, and uses every day at work. And it helps analysts be more productive: one analyst, twice as much done because they’re leaning on this open source code they’ve been using for years in real life today.
It could also mean fewer mistakes, because every line of code you write is a chance to make one. The more you can rely on widely tested things, the fewer mistakes you make. These are all things that have happened in Java and C++ and other languages, and it’s just sort of waiting to happen in SQL there.
You mentioned LinkedIn. A standard set of tools and skills would seem valuable for employees changing jobs and companies trying to hire, too.
It’s huge. Everyone wants to think of these things in terms of the tech dimensions, but one of the most important parts of open source code is that you can take it with you. You learn once how to use that library and, if it’s popular enough, there’s a good chance your next job will use it, too. So it creates more of an incentive for individuals to learn these things because they don’t have to worry that this knowledge will become useless in a couple of years if they change jobs down the line.