A Confession

Daniel Mintz, Chief Data Evangelist

Jun 15, 2016

Hi. My name is Daniel, and I love SQL. Ever since I first started learning how to write queries on MySQL a decade ago, I've loved the discipline that SQL imposed on me. It forced me to think through my analytic questions in a, well, structured way.

And using SQL, I could answer just about any question I could come up with. It's an immensely satisfying and empowering feeling, and it's made me better at whatever job I held. With SQL and the right data, whenever I had to make a decision I could make it better using data.

Having professed my love for SQL, I should also point out that I'm fully aware of its warts. SQL is, for all intents and purposes, a write-only language. Even when I'm looking at a query I wrote, if it's more than a couple weeks old, it's generally easier to start from scratch than try to figure out what the heck I was doing.

And collaboration is a nightmare, since making sense of others' SQL is even worse than reading my own. And that's without even getting into the lack of versioning or difficulty of organizing large amounts of code. Or even the stupid, simple stuff, like how hard it is to pivot results or do a median calculation in most dialects.

But we analysts have reconciled ourselves to SQL's drawbacks, because it's great at what it's built for: letting us quickly and directly get at the data we're looking for. Plus, it's not going anywhere. The growing popularity of NoSQL datastores notwithstanding, SQL has a 40-year track record and, along with C, runs the world.

So, when I first saw a demo of Looker three years ago, the first thing that struck me was that, unlike most analytic tools, Looker doesn't try to hide the fact that it's using SQL. The SQL that Looker generates and sends off to your database is always right there for examination. And what's more, unlike most machine-generated SQL, it's quite readable!

Naturally, I wanted to know how Looker managed that, and the answer was (and is) LookML.

Unlike some proprietary analytics languages, LookML doesn't try to reinvent the wheel. It's written by people who know and love SQL, so LookML aims to keep all the power and flexibility of SQL, but to smooth off some of its rough edges.

The result is a pleasure to use. LookML:

• breaks your SQL down into bite-sized chunks so that future-you can actually make sense of how now-you defined a particular measure or dimension.
• encourages collaboration, because others can make sense of what you wrote at a glance.
• lets you define the way tables relate to each other once, and then uses that information to construct joins properly everywhere.
• allows you to specify formatting for measures and dimensions right in the code, so you don't have to export data to Excel to format numbers as dollars or percents or decimals.
• makes it as simple to do pivots and running totals and complex case statements as it is to do sums and averages.
• ensures that no one forgets that one critical predicate that you always have to include if you want accurate results. (For me, it was always specifying that order status was 'completed'.)

Fundamentally, LookML isn't a mess of unreadable SQL files. It's real code. Code that's organized and version-controlled and extensible.

Is there a learning curve? Sure, there is with anything worth learning. But if you already know and love SQL, LookML will feel like home. You can learn the basics in a few minutes, and in an hour you'll be using LookML to build complicated queries that would have been a huge pain to write in raw SQL.

As you get comfortable with LookML, you'll find that instead of spending time trying to find that query you saved in untitled43.sql last month or looking up the differences between Postgres 8.x and 9.x, you'll start relying on LookML to handle that stuff so you can focus on the interesting stuff.

And that's how programming languages get better, not by throwing out the old, but by abstracting away the low-level concerns so developers can focus on building cool stuff. LookML does exactly that for SQL. It's not a replacement, it's an evolutionary step, and if you want to stop worrying about the silly stuff and focus on doing great analyses, I think it's one you'll love.