SQL: One of the most valuable skills(craigkerstiens.com)
craigkerstiens.com
SQL: One of the most valuable skills
http://www.craigkerstiens.com/2019/02/12/sql-most-valuable-skill/
382 comments
My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything.
Data cleaning? SQL. Feature engineering? SQL.
Pipelines of stored procedures, stored in other stored procedures. Some of these procedures were so convoluted that they outputted tables with over 700 features, and had queries that were hundreds of lines long.
Every input, stored procedure, and output timestamped, so a change to one script involved changing every procedure downstream of it. My cries to use git were unheeded (would have required upskilling everyone on the team).
It was probably the worst year of my life. By the end of it I built a framework in T-SQL that would generate T-SQL scripts. In the final week of a project (which had been consistent 60-70 hour weeks), the partner on the project came in, saw the procedures written in my framework and demanded that they all be converted back into raw SQL. I moved teams a few weeks later.
The only good bit looking at it, is that now I'm REALLY good with SQL. It's incredibly powerful stuff, and more devs should work on it.
Data cleaning? SQL. Feature engineering? SQL.
Pipelines of stored procedures, stored in other stored procedures. Some of these procedures were so convoluted that they outputted tables with over 700 features, and had queries that were hundreds of lines long.
Every input, stored procedure, and output timestamped, so a change to one script involved changing every procedure downstream of it. My cries to use git were unheeded (would have required upskilling everyone on the team).
It was probably the worst year of my life. By the end of it I built a framework in T-SQL that would generate T-SQL scripts. In the final week of a project (which had been consistent 60-70 hour weeks), the partner on the project came in, saw the procedures written in my framework and demanded that they all be converted back into raw SQL. I moved teams a few weeks later.
The only good bit looking at it, is that now I'm REALLY good with SQL. It's incredibly powerful stuff, and more devs should work on it.
I spent a year in a role where 50% of my duties was writing sql reports. These reports where usually between 500 and 1000 lines of sql a pop. Sometimes the runtime of the report was measured in hours, so learning efficient sql was important. The company had a lot of people that had been writing sql for awhile, and there were lots of cool code snippets floating around. I learned a lot in that year.
I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. Every once in a while my sql skills save the day and several people in other departments contact me directly when they need excel files of data in our database we don't have UIs to pull yet.
I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. Every once in a while my sql skills save the day and several people in other departments contact me directly when they need excel files of data in our database we don't have UIs to pull yet.
SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking.
- Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output.
I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and processing the data.
Does anyone else face this problem?
Any suggested guides / books to make me think more SQL'ley ?
- Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output.
I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and processing the data.
Does anyone else face this problem?
Any suggested guides / books to make me think more SQL'ley ?
SQL is the most powerful query language ever invented and widely implemented over relation databases IMO (qualified heavily for lurking RDF zealots). Every time you see someone start to invent their own query language, I almost always mark is as folly (similar to when people invent their own configuration languages). Prometheus and GraphQL stand out as recent examples.
DBs like Kafka who recognize this and instead offer SQL on top of their things take the right approach IMO KSQL.
DBs like Kafka who recognize this and instead offer SQL on top of their things take the right approach IMO KSQL.
SQL is nice on a surface level and helpful in practice.
Having a working intuition for relational databases is valuable on a deep level. I mean having a sense of how to organize the tables, what sizes are large and small, when to add what kind of index and what the size and speed limits are likely to be for a given data structure. That's extremely valuable.
BTW, we're preparing to move a postgres database that's a few TB from Heroku to AWS RDS. The catch is that we can't afford more than a few minutes of downtime. If this is in your wheelhouse, reach out! We'd like to talk.
Having a working intuition for relational databases is valuable on a deep level. I mean having a sense of how to organize the tables, what sizes are large and small, when to add what kind of index and what the size and speed limits are likely to be for a given data structure. That's extremely valuable.
BTW, we're preparing to move a postgres database that's a few TB from Heroku to AWS RDS. The catch is that we can't afford more than a few minutes of downtime. If this is in your wheelhouse, reach out! We'd like to talk.
I've been saying this for years, because I started doing web apps when we pretty much were sending raw SQL over to a database. Even after ORMs got bigger, I still stress knowing SQL even if you don't use it often, because it helps you understand the ORM.
And oddly enough, now I'm a data engineer. Everything I do, even when I'm not doing pure SQL, is influenced by years of experience in SQL. Either the languages of my big tools are still based on SQL in some fashion, or it simply helps to have an understanding of the ecosystem to figure out what's going on at scale.
Everything else has come and gone and come again, but SQL has help up pretty nicely. The only other skills that come close for me are working in languages that branched out of C (because the syntax isn't so different over time) and years of on again off again procedural languages (ColdFusion, vanilla Javascript, etc. leads to it being way easier to pick up Python).
And oddly enough, now I'm a data engineer. Everything I do, even when I'm not doing pure SQL, is influenced by years of experience in SQL. Either the languages of my big tools are still based on SQL in some fashion, or it simply helps to have an understanding of the ecosystem to figure out what's going on at scale.
Everything else has come and gone and come again, but SQL has help up pretty nicely. The only other skills that come close for me are working in languages that branched out of C (because the syntax isn't so different over time) and years of on again off again procedural languages (ColdFusion, vanilla Javascript, etc. leads to it being way easier to pick up Python).
For those trying to up their SQL game from basic selects and grouping, I suggest learning:
- Common Table Expressions (CTEs). They will equip you to write clean, organized, and expressive SQL.
- CREATE TABLE AS SELECT. This command powers SQL-driven data transformation.
- Window functions, particularly SUM, LEAD, LAG, and ROW_NUMBER. These enable complex calculations without messy self-joins.
Learning those three will change your relationship with SQL and blow the door open of problems you can solve with the language.
- Common Table Expressions (CTEs). They will equip you to write clean, organized, and expressive SQL.
- CREATE TABLE AS SELECT. This command powers SQL-driven data transformation.
- Window functions, particularly SUM, LEAD, LAG, and ROW_NUMBER. These enable complex calculations without messy self-joins.
Learning those three will change your relationship with SQL and blow the door open of problems you can solve with the language.
I consider myself functional, but not proficient in SQL. Is there a reliable and easy way to assess one's skills in SQL? I feel that most of the new things I learn about SQL these days are database specific. I wonder whether I'm missing out on something, or do I already have the "core" SQL knowledge down, and everything else is special cases?
The more queries I write on Elasticsearch, the more I value SQL.
Hell Yes.
Although it takes some time to switch from Procedural or OOP to highly Declarative world of SQL, but man, its worth pursuing.
Only thing which really helped me build my confidence was solving more real world problems which involves 2NF and 3NF design, JOINs, Triggers, Indexing, Views & Materialized Views for denormalization, CTE and Recursive CTE's.
You may call me an extremist but from server side programming point of view with Postgres and FDW (Foreign Data Wrappers) which has ton of features other than SQL only thing i miss is HTTP server. :)
You may call me an extremist but from server side programming point of view with Postgres and FDW (Foreign Data Wrappers) which has ton of features other than SQL only thing i miss is HTTP server. :)
I’ve been a career analyst for about 12 years so I write SQL every day. I know it very well. I’ve developed a very strong love / hate relationship with it over the years.
On one hand, you write what you want to find in fairly common language and you almost always get back what you want if you do it correctly. In many ways, it’s like a precursor to Alexa but in written form. It’s super easy to pick up for non technical people.
On the other hand, it’s extremely difficult to code review, and on a very complicated piece of business logic, errors could mean the difference between hiring 10 more people or laying off 100. So almost always it’s just easier to re-write.
Imagine if engineers couldn’t understand each other’s work, and had to rewrite code every time someone leaves the team. It’s insane to me that this is standard practice.
On one hand, you write what you want to find in fairly common language and you almost always get back what you want if you do it correctly. In many ways, it’s like a precursor to Alexa but in written form. It’s super easy to pick up for non technical people.
On the other hand, it’s extremely difficult to code review, and on a very complicated piece of business logic, errors could mean the difference between hiring 10 more people or laying off 100. So almost always it’s just easier to re-write.
Imagine if engineers couldn’t understand each other’s work, and had to rewrite code every time someone leaves the team. It’s insane to me that this is standard practice.
SQL is for data systems what IP protocol is for networks: it is the neck of the hourglass. You can build plenty of various things on it (the top of the hourglass: applications, reporting frameworks and so on) using various underlying technologies (the bottom: storage engines etc.) but you can't remove the neck without breaking the hourglass.
This is why SQL language and IP protocol are two most valuable things in computer world.
This is why SQL language and IP protocol are two most valuable things in computer world.
> Because so few actually know SQL well you can seem more elite than you actually are.
Thank you Craig, I'm convinced. Anyone know where best to begin learning SQL?
Thank you Craig, I'm convinced. Anyone know where best to begin learning SQL?
In University, I needed one more CS elective course, and the ONLY class available that fit my schedule was SQL. I had no interest in SQL, wanted nothing to do with it, and only took the class under duress. Even to the point of admitting as much to the prof in a casual conversation (he was a good guy, easy to talk to).
Within two months into my first job out of school, I was assigned to implement a SQL parser as a modern new interface for an ancient proprietary database. Every job since, I've written tons of SQL queries. SQL rocks.
Life is funny that way.
Within two months into my first job out of school, I was assigned to implement a SQL parser as a modern new interface for an ancient proprietary database. Every job since, I've written tons of SQL queries. SQL rocks.
Life is funny that way.
It's very interesting how many application developers shun SQL. I believe a lot of it is due to a pervading sentiment that SQL is unruly or inelegant. There are certainly quirks to the language, and it is a difficult transition to think in a declarative rather than imperative manner, but once you make the jump, it's an invaluable skill to have. Fortunately, I have the luxury of being able to manipulate our codebase from any level of the stack. What this means is that rather than jump through hoops on the API or frontend side to accomplish some task that would require reams of code, I can just get my output via a simple query, and it will perform faster in almost all cases.
From an analytics point of view, I can't imagine not using SQL. I've seen people pull reports from multiple websites, text files, etc., spend an entire day manipulating them in Excel, and still not get their data model working as expected, not to mention that it is very slow. A couple of queries with some temp tables and voila, magic happens. It really does make you look like a superhero when you can deliver more accurate results in a fraction of the time it originally took. I'm surprised there isn't more of a market for this skill, surely there's a lot of programmers from the 80's and 90's who have this skillset in abundance.
From an analytics point of view, I can't imagine not using SQL. I've seen people pull reports from multiple websites, text files, etc., spend an entire day manipulating them in Excel, and still not get their data model working as expected, not to mention that it is very slow. A couple of queries with some temp tables and voila, magic happens. It really does make you look like a superhero when you can deliver more accurate results in a fraction of the time it originally took. I'm surprised there isn't more of a market for this skill, surely there's a lot of programmers from the 80's and 90's who have this skillset in abundance.
I'm a frontend web developer, but for a year I worked on a custom ETL system and wrote lots & lots of SQL. I still find myself using knowledge I learned back then and applying it today - for example, being able to review BI's queries is immensely useful. I'm also much less intimidated when I look at backend code, since a lot of it is interfacing with a database in some way.
I think SQL is one of those essential "secondary" skills for developers.
I think SQL is one of those essential "secondary" skills for developers.
Reading the comments here is like someone with years of experience with jQuery saying how jQuery is simple and powerful and nothing will replace it. SQL is to relational databases what jQuery is to the DOM, only shittier (maybe like mootools) and refuses to die, probably because all these SQL experts aren't really good programmers. Reading some compare SQL to 70s style procedural code tells me they haven't moved on from expert beginner territory.
SQL has opened a lot of doors for me. It's a starting point, not an end though. Learning about dictionaries, lists, and other data structures has proven valuable and compliments SQL and tabular datasets very nicely. I got into those areas by working with SQL generators (ORM's) written in Python (airflow).
My thinking on SQL has evolved and lately I see it as a set definition tool. "Do action X on dataset Y." It's really useful for understanding data structures and data meaning too.
I've worked with more than a few SDE's who look down on SQL, but it's a really good tool when used properly, and it cuts across many technologies. Writing code to write SQL can be very powerful. And sometimes coded or scripted data wrangling without SQL is very useful too.
15 years ago SQL knowledge was not that widespread and it was easy to get tagged as a report writer. Today, a lot more business, product, finance, and accounting people are really strong with SQL, and rely heavily on exporting data to excel for further analysis. Knowing how to answer business questions, get insights out of the data, and define or categorize sets of data are all enhanced by SQL. Report writing is not as much of a thing anymore because people want to view the data in diverse ways.
The barrier to entry is low with SQL, but learning it well takes time and some mistakes to get efficient and precise with it. 15 years later I am still learning new uses for it. One example is JSON querying and transformation which is supported by hive, presto, and some other compute platforms. It's easy to mix and match JSON, arrays, and tabular data structures, in one or more tables, from the same SQL query.
My thinking on SQL has evolved and lately I see it as a set definition tool. "Do action X on dataset Y." It's really useful for understanding data structures and data meaning too.
I've worked with more than a few SDE's who look down on SQL, but it's a really good tool when used properly, and it cuts across many technologies. Writing code to write SQL can be very powerful. And sometimes coded or scripted data wrangling without SQL is very useful too.
15 years ago SQL knowledge was not that widespread and it was easy to get tagged as a report writer. Today, a lot more business, product, finance, and accounting people are really strong with SQL, and rely heavily on exporting data to excel for further analysis. Knowing how to answer business questions, get insights out of the data, and define or categorize sets of data are all enhanced by SQL. Report writing is not as much of a thing anymore because people want to view the data in diverse ways.
The barrier to entry is low with SQL, but learning it well takes time and some mistakes to get efficient and precise with it. 15 years later I am still learning new uses for it. One example is JSON querying and transformation which is supported by hive, presto, and some other compute platforms. It's easy to mix and match JSON, arrays, and tabular data structures, in one or more tables, from the same SQL query.
I recently had a candidate that had very good Scala skills and very poor SQL skills. Despite Scala and spark being powerful, you can over complicate a solution in those languages that could otherwise be easily solved in SQL. That’s interesting to me because I see SQL as a fundamental tool that you build on top of and some people these days seems to skip it.
I wonder if we'll one day get something like SQL, but that is based on hypergraphs instead.
Correct me if I'm wrong; I understand you can represent any relational model in hypergraph. And a lot that aren't representable in relational model, but are natural in hypergraph form.
Correct me if I'm wrong; I understand you can represent any relational model in hypergraph. And a lot that aren't representable in relational model, but are natural in hypergraph form.
SQL is pretty great. I've been doing advance SQL in Postgres and BigQuery for quite a while and it's one of the most satisfying experiences.
Once you wrap your head around basics such as GROUP BYs, JOINs, CASE statements etc, you move on to advance concepts such as Window functions and there suddenly a new world of possibilities opens up to you for analytics! I've dabbled a bit in PL/pgSQL, but the syntax is way too arcane.
SQL can also be counter-intuitive sometimes. I rewrote a particular PG query and reduced time from ~30minutes down to 2 seconds by adding a subquery. :)
And no, ORMs can't really do what SQL can do in an equivalent manner.
Once you wrap your head around basics such as GROUP BYs, JOINs, CASE statements etc, you move on to advance concepts such as Window functions and there suddenly a new world of possibilities opens up to you for analytics! I've dabbled a bit in PL/pgSQL, but the syntax is way too arcane.
SQL can also be counter-intuitive sometimes. I rewrote a particular PG query and reduced time from ~30minutes down to 2 seconds by adding a subquery. :)
And no, ORMs can't really do what SQL can do in an equivalent manner.
Valuable yet fairly common.
I remember interviewing at FAANG and being asked to code up various tree traversal algorithms... and moments later I would be asked to write window function aggregations in SQL. And it was like this for all the interviews with that company - it was fairly bizarre as I wasn't sure what the aim there was. I understand that SQL is omnipresent, but surely people with algorithmic knowledge would be able to pick up SQL in hours or days, while the opposite doesn't quite hold.
(Oh and I agree with the article and I do like SQL for all sorts of workloads, that's not the point.)
I remember interviewing at FAANG and being asked to code up various tree traversal algorithms... and moments later I would be asked to write window function aggregations in SQL. And it was like this for all the interviews with that company - it was fairly bizarre as I wasn't sure what the aim there was. I understand that SQL is omnipresent, but surely people with algorithmic knowledge would be able to pick up SQL in hours or days, while the opposite doesn't quite hold.
(Oh and I agree with the article and I do like SQL for all sorts of workloads, that's not the point.)
Given such positive response, I want to learn SQL.
What are some good resources to learn?
I need to learn from the very beginning and it would be helpful if it had practical training excercizes as part of the course.
What are some good resources to learn?
I need to learn from the very beginning and it would be helpful if it had practical training excercizes as part of the course.
I learned SQL around 1996 or 1997 and had an absolutely fantastic college course or two involving it.
I've mostly been a backend developer... having the ability to go in and fix SQL and make things run in < 1 second instead of 5-10 minutes has been one of the best skills I could have picked up.
It is sad that so often "self described 10x programmers" build solutions to go around SQL that are horrible failures. Poor use of ORMs, weird abstractions at the application layer that force developers to use poor data access patterns, unnecessary "locking" at the application layer, unnecessary "existence checks" at the application layer, processing objects 1-by-1 in the application. All these things lead to terrible performance and huge wastes of application memory/IO.
I love some of the NoSQL solutions too as in some cases they can force teams to use better data organizations/patterns and scale so well. Those patterns are often possible in an RDBMS but the system doesn't guide a team to using those patterns. The way CQL in Cassandra forces you to think about data organization is great for example.
I've mostly been a backend developer... having the ability to go in and fix SQL and make things run in < 1 second instead of 5-10 minutes has been one of the best skills I could have picked up.
It is sad that so often "self described 10x programmers" build solutions to go around SQL that are horrible failures. Poor use of ORMs, weird abstractions at the application layer that force developers to use poor data access patterns, unnecessary "locking" at the application layer, unnecessary "existence checks" at the application layer, processing objects 1-by-1 in the application. All these things lead to terrible performance and huge wastes of application memory/IO.
I love some of the NoSQL solutions too as in some cases they can force teams to use better data organizations/patterns and scale so well. Those patterns are often possible in an RDBMS but the system doesn't guide a team to using those patterns. The way CQL in Cassandra forces you to think about data organization is great for example.
I love SQL. I love SQL for Data Warehousing even more. There’s a school of thought for DW that goes back many years. Many dimensional modeling practitioners know who Ralph Kimball is and also know the Inmon vs Kimball battles. To this day, star schema as a design pattern has helped dozens of analysts in my company abstract the relationship between measures (facts) and how to slice them (dimensions).
Well, I'm not a fan of SQL. To me the math underpinnings are not well exposed, probably because the language doesn't "see" obvious relations and that because of this, joins must be performed explicitly, over and over again, and come to dominate the query.
Isn't it obvious that when a column has the name of a table that the idea is that the columns of that refactored out tables become available as if it were a part of the main table?
When the query works regardless of the structure of the table, the database can be refactored with ease. Most such queries would simply specify column names, and a filter to apply on the records. Any joins needed, and which result from the structure of the database, would be inferred.
Such a "NoJoinDB" would clearly boost productivity, and lots of applications could be written with no explicit join at all. A language like SQL seems to hide the simplicity of most queries!
Please comment about the validity of this perspective.
Isn't it obvious that when a column has the name of a table that the idea is that the columns of that refactored out tables become available as if it were a part of the main table?
When the query works regardless of the structure of the table, the database can be refactored with ease. Most such queries would simply specify column names, and a filter to apply on the records. Any joins needed, and which result from the structure of the database, would be inferred.
Such a "NoJoinDB" would clearly boost productivity, and lots of applications could be written with no explicit join at all. A language like SQL seems to hide the simplicity of most queries!
Please comment about the validity of this perspective.
Another aspect of SQL that I have come to appreciate is it's underlying philosophy: state what you want at a high level with a strict syntax; in exchange, your backend will make aggressive optimisations for you.
This sort of paradigm really made me understand that writing 'high-level' code and performant code are not mutually exclusive.
This sort of paradigm really made me understand that writing 'high-level' code and performant code are not mutually exclusive.
The reason SQL is great in my opinion is partially what the author highlights. In addition I think one of the reasons is that it is a very mature technology that baked over decades. I first was exposed to ANSI-92 SQL in school and that was already like the 2nd or 3rd revision of the standard. I used it in my first couple of jobs and the skill applied everywhere, Oracle, mySQL, SQL Server and there was even something called Access that understood it and Access was widely used amongst small businesses.
Knowing SQL to me was and even now is extremly helpful to understand data and how to tackle data problems. There weren't many other technologies that had such profound long term applicability, besides maybe knowing and understanding HTML and C. Many new things are just variations and improvements of those core technologies, and those can easily be understood and learned with good foundational knowledge.
Knowing SQL to me was and even now is extremly helpful to understand data and how to tackle data problems. There weren't many other technologies that had such profound long term applicability, besides maybe knowing and understanding HTML and C. Many new things are just variations and improvements of those core technologies, and those can easily be understood and learned with good foundational knowledge.
I would say the same about bash.
I run a early stage company that builds analytics infrastructure for companies. We are betting very heavily on SQL, and Craigs post rings true now more than ever.
Increasingly, more SQL is written in companies by analysts and data scientists than typical software engineers.
The advent of the MMP data warehouse (redshift, bigquery, snowflake, etc) has given companies with even the most limited budget the ability to warehouse and query an enormous amount of data just using SQL. SQL is more powerful and valuable today than it ever has been.
When you look into a typical organization, most software engineers aren't very good at SQL. Why should they be? Most complex queries are analytics queries. ORMs can handle a majority of the basic functions application code needs to handle.
Perhaps going against Craig's point is the simple fact that we've abstracted SQL away from a lot of engineers across the backend and certainly frontend and mobile. You can be a great developer and not know a lot about SQL.
On the other end of the spectrum are the influx of "data engineers" with basic to intermediate knowledge of HDFS, streaming data or various other NoSQL technologies. They often know less about raw SQL than even junior engineers because SQL is below their high-power big data tools.
But if you really understand SQL, and it seems few people truly today, you command an immense amount of power. Probably more than ever.